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


    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


    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 :)

               ID (Primary key)
           CID [foreign key]
           Name [primary key]
  5. robmiller


    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?

        company.name, COUNT(site.*) AS num_sites
        companies AS company, sites AS site
        company.id = site.company
    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


    Joined: 15 Sep 2003

    Posts: 9,454

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