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

[MossyUK]

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!

 

[MastermindUK]

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 ...

 

[Immsy]

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.

 

[Beansprout]

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
You put an ID in the company table - use it

Company{
           ID (Primary key)
           Name
}

Sites{
       CID [foreign key]
       Name [primary key]
       URL
}

 

[robmiller]

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 ...

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

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

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.

 

[MossyUK]

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

 

[zetec452]

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