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

Associate
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!
 
Associate
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 ...
 
Associate
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.
 
Man of Honour
Joined
31 Jan 2004
Posts
16,335
Location
Plymouth
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
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
}
 
Soldato
Joined
26 Dec 2003
Posts
16,522
Location
London
MastermindUK said:
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 ...

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.
 
Associate
OP
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 :)
 
Back
Top Bottom