Is this possible

Sayian

i need to select 5 counts from 5 tables

So far ive got 5 querys, doing the counts...

Is it possible to use only 1 query?

Example:

$sql = mysql_query("SELECT count(*) as myCount
from table_1
where col = whatever");

Is thier anyway i could get these counts into 1 query?

Cheers

bogu

If the tables are linked then u can make only one query using LEFT JOIN ...

Sayian

Ive tried to do this ..

But how can i assign indiv. names to count?

SELECT a.count() from table, b.count() from table doesnt work

Which i didnt expect it to, but theres gotta be a way to get 4 or 5 counts in 1 query yea?

Anyone else wanna help me please? 🙂

bogu

Are your tables linked with each other?

laserlight

Use IN, which takes a comma separated list of values. This list can be generated by implode() if you have the col values in an array.

$sql = "SELECT col, COUNT(*) AS myCount FROM table_1
	WHERE col IN ('what0', 'what1', 'what2', 'what3', 'what4')";
$result = mysql_query($sql);
// associative array of col to the respective count
$counts = array();
while ($row = mysql_fetch_assoc($result)) {
	$counts[$row['col']] = $row['myCount'];
}

Sayian

Laserlight thank you, i think i understand what your telling me.

I just need a little more understanding.

Ok, i have a primary key to search for, it's all search for the same thing

its just the col names are different for every table

table 1 is called c_pagelog which i need to get the count using visitor_id =
table 2 is called c_notes which i need to get the count using to_id =

all the id's will equal the same, b/c their all the same user

Is this still possible?

Im just a little confused?

laserlight

Basically, you are trying to make:

SELECT COUNT(*) FROM c_pagelog WHERE visitor_id=1;
SELECT COUNT(*) FROM c_notes WHERE to_id=1;

become a single query?

You may be able to do a:

SELECT COUNT(c_pagelog.*) as pagelog_count, COUNT(c_notes.*) as notes_count
	FROM c_pagelog, c_notes
	WHERE c_pagelog.visitor_id=c_notes.to_id AND c_pagelog.visitor_id=1;

Sayian

Laserlight, thank you for your help.

Im going to try this out and see how it works for me.

Sxooter

This is a job for union. if you're on a not too ancient version of mysql you can do it that way. problem with a join is that it makes one big data set based on what matches up in them, but you don't have anything here that really matches up. A union joins in the other dimension, piling the results on atop the other:

select 'label1', count() from table1 where something=something
UNION ALL
select 'label2', count() from table2 where...
UNION ALL
select 'label3', count(*)...

You get the idea there?

Sayian

Wow, thanks sxooter .. that really helped me also .. and i learned about unions .. thanks again!

Sxooter

Unions rocks. It's nice to live in the year 2006 when you can mush a couple million rows against each other in one in a few seconds. Waaaay back when I first started playing with databases, we had RBase 5000 on an old Burroughs cluster with 80186 processors (yes, there really was an 80186) with 256k of ram (yes, that's k, not meg) and a union on two 30,000 row sets took about 2 hours. haha. We just dumped all the rows into a temp table and selected distinct from it, and it took about 5 minutes. Much faster, relatively speaking.

I swear I think I can hear the cogs turning inside those boxes in my head...