1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

PHP / MySQL - Querying based on a variable in SQL statements - help!

Discussion in 'HTML, Graphics & Programming' started by MossyUK, 9 May 2006.

  1. MossyUK

    Wise Guy

    Joined: 24 Jul 2004

    Posts: 1,580

    Location: Preston, Lancs

    Hello,

    I have two tables, one is called Company, the other called Sites.

    Company has the following:

    Company ID (key)
    Company name


    Sites has the following:

    Site id (key)
    Company Name (matches the table above)
    Site Name
    Site URL


    Its a very simple database, more of a test application currently. Each Company has many sub Sites.

    All I want to do is have a Master table with the following info:

    Company ID, Company Name, (number of sub) Sites


    How can I run a query that will do this for me?

    In psuedo code, basically its:

    SELECT all
    FROM both tables
    WHERE company ID is the same in both
    Then count the above and display a number in my table


    Any help greatfully appreciated!
     
  2. MastermindUK

    Hitman

    Joined: 23 Feb 2004

    Posts: 508

    Location: London

    I don't think it has to be as complicated as that.

    You could do something like this, assuming you're using PHP.

    Select company from sites
    create an array of the companies
    use array_count_values to get number of times a company appears in the above array.
    then foreach of the new array as company_name => count
    find company_name row in companies
    create new cell with count in

    I hope you're using php :p ...
     
  3. Immsy

    Wise Guy

    Joined: 18 Oct 2002

    Posts: 2,367

    SELECT c.*, s.* FROM company c LEFT JOIN sites s ON (c.companyname=s.companyname)

    Then you could do mysql_num_rows to find out how many results match the query and display this in your table.
     
  4. Beansprout

    Man of Honour

    Joined: 31 Jan 2004

    Posts: 16,316

    Location: Plymouth

    The key, the key, and nothing but the key :D
    You put an ID in the company table - use it :)

    Code:
    Company{
               ID (Primary key)
               Name
    }
    
    Sites{
           CID [foreign key]
           Name [primary key]
           URL
    }
    
     
  5. robmiller

    Capodecina

    Joined: 26 Dec 2003

    Posts: 16,522

    Location: London

    oh my god why would you do this and say it wasn't complicated :(

    You're using a database, why not use it?

    Code:
    SELECT
        company.name, COUNT(site.*) AS num_sites
    FROM
        companies AS company, sites AS site
    WHERE
        company.id = site.company
    GROUP BY
        company.name
    
    Use the company field of the site table as a foreign key that is linked to the company table's ID, and it's child's play.
     
  6. MossyUK

    Wise Guy

    Joined: 24 Jul 2004

    Posts: 1,580

    Location: Preston, Lancs

    thank you, im a bit fried today after a bit of a mammoth coding session (2 hours straight, as a newb, it was intense).

    Out of interest, what books did you guys read to learn about this stuff?

    Thanks again! ill report back with results :)
     
  7. zetec452

    Sgarrista

    Joined: 15 Sep 2003

    Posts: 9,454

    Its not just about raw programming. That kind of stuff is database design.