Coding multiple count queries in mysql / php

Question

I am coding the following, Im still very new to php so my coding is a bit rubbish, is there a better way of doing the following, ideally putting into one query or something, as this is the only way I know how to do it :-S . Thanks :-)

<?php
$query = ("SELECT COUNT(receivegasoilmailinglist) FROM     hqfjt_chronoforms_data_addupdatelead WHERE      receivegasoilmailinglist='yes'"); 
$result = mysql_query($query) or die(mysql_error()); 

$row = mysql_fetch_row($result); 
echo '<h1> gasoil :';
echo $row[0];  
echo '</h1>';
 ?>

<?php
$query2 = ("SELECT COUNT(receivedervmailinglist) FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes'"); 
 $result2 = mysql_query($query2) or die(mysql_error()); 

 $row2 = mysql_fetch_row($result2); 
 echo '<h1> derv :';
 echo $row2[0];  
 echo '</h1>';
 ?>

<?php
 $query3 = ("SELECT COUNT(receivekeromailinglist) FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes'"); 
 $result3 = mysql_query($query3) or die(mysql_error()); 

$row3 = mysql_fetch_row($result3); 
echo '<h1> kero :';
echo $row3[0];  
echo '</h1>';
?>

It is officially recommended that you not use the mysql_* functions. Instead, one should use MySQLi or PDO. — Justin Martin
– Justin Martin, Commented Jan 6, 2012 at 21:08

Eugen Rieck · Accepted Answer · 2012-01-06 21:35:51Z

3

If (and only if) either of these is true:

most of the rows in hqfjt_chronoforms_data_addupdatelead want some sort of newsletter
there are no indices on the receive*mailinglist fields

you can save a lot of DB muscle by doing

SELECT 
  sum(if(receivegasoilmailinglist='yes',1,0)) AS gasoil,
  sum(if(receivedervmailinglist='yes',1,0)) AS derv,
  sum(if(receivekeromailinglist='yes',1,0)) AS kero
FROM hqfjt_chronoforms_data_addupdatelead;

as this will traverse the table only once.

For the rest of the job you use ofcourse

$sql="... as above ...";
$qry = mysql_query($sql) or die("Query failed: <br>SQL=$sql<br>Error=".mysql_error());
$qry = mysql_fetch_row($qry);
echo "<h1> gasoil: ".$qry[0]."</h1>";
echo "<h1> derv: ".$qry[1]."</h1>";
echo "<h1> kero: ".$qry[2]."</h1>";

edited Jan 6, 2012 at 21:35

answered Jan 6, 2012 at 21:06

Eugen Rieck

65.6k10 gold badges76 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Iain Simpson Over a year ago

Hi, that query in mysql returns gasoil derv kero 2 4 3 , but when I use it with the php in a page I just get - gasoil: derv: kero:

Kai Qing Over a year ago

you need to run a while loop or some other way of fetching the results. You do this in your original post. while($row = mysql_fetch_assoc($qry)){ ... }

Eugen Rieck Over a year ago

This is because I am a moron. Fixed the PHP, should work now.

Eugen Rieck Over a year ago

@KaiQing no need for a while, we return exactly 1 row.

Kai Qing Over a year ago

yeah, no need for while but there WAS need to fetch row or any other fetch. the original response just tried to access the query var itself. A while loop may have not been efficient, but it would have worked.

|

Kai Qing · Accepted Answer · 2012-01-06 21:04:23Z

0

I would do a union personally.

$query = "(SELECT COUNT(receivegasoilmailinglist) AS gasoil FROM hqfjt_chronoforms_data_addupdatelead WHERE receivegasoilmailinglist='yes') UNION (SELECT COUNT(receivedervmailinglist) AS derv FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes') UNION (SELECT COUNT(receivekeromailinglist) AS kero FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes')";

and since others have answered I figure you can tell how to loop through the results.

If you are unsure about unions, you can run queries like this so long as each expected result datatype is the same. In this case, they're all counts, so it should work well.

answered Jan 6, 2012 at 21:04

Kai Qing

18.9k6 gold badges41 silver badges61 bronze badges

Comments

Mathieu Dumoulin · Accepted Answer · 2012-01-06 21:29:58Z

0

You could use a UNION and set a type in each query then loop the three records all at once. UNION collates results together but must feature the same exact output structure.

$query = 
"SELECT 'gasoil' AS counttype, COUNT(receivegasoilmailinglist) AS countdata FROM     hqfjt_chronoforms_data_addupdatelead WHERE      receivegasoilmailinglist='yes'
UNION
SELECT 'derv' AS counttype, COUNT(receivedervmailinglist) AS countdata FROM hqfjt_chronoforms_data_addupdatelead WHERE receivedervmailinglist='yes'
UNION
SELECT 'kero' AS counttype, COUNT(receivekeromailinglist) AS countdata FROM hqfjt_chronoforms_data_addupdatelead WHERE receivekeromailinglist='yes'";
$result = mysql_query($query) or die(mysql_error()); 

while($row = mysql_fetch_assoc($result)){
    echo '<h1> '.$row['counttype'].' :';
    echo $row['countdata'];  
    echo '</h1>';
}

And also, my code is much shorter for the exact same processing! :)

edited Jan 6, 2012 at 21:29

answered Jan 6, 2012 at 20:59

Mathieu Dumoulin

12.3k7 gold badges47 silver badges71 bronze badges

6 Comments

Iain Simpson Over a year ago

Hi, I just tried this but get a row of three : 's but thats it, it doesnt seem to be pulling any data out.

Mathieu Dumoulin Over a year ago

I seriously don't know why it is not working since i copy pasted your SQL queries... If you run the big query in PHPMyAdmin (or your favorite tool) does it return the expected data?

Iain Simpson Over a year ago

Yes that works in php my admin, I will check iv not made a mistake somewhere.

Iain Simpson Over a year ago

I have had a look and cant see anything wrong, I have literally just wrapped it in php tags and thats it, it is echoing the colons and thats it, no errors either, yet if I run that query in phpmyadmin I get:

Iain Simpson Over a year ago

counttype countdata gasoil 2 derv 4 kero 3

|

Collectives™ on Stack Overflow

Coding multiple count queries in mysql / php

3 Answers 3

7 Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related