Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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>';
?>
share|improve this question
 
It is officially recommended that you not use the mysql_* functions. Instead, one should use MySQLi or PDO. –  FrozenFire Jan 6 '12 at 21:08

3 Answers

up vote 2 down vote accepted

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>";
share|improve this answer
 
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: –  Iain Simpson Jan 6 '12 at 21:31
 
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)){ ... } –  Kai Qing Jan 6 '12 at 21:33
 
This is because I am a moron. Fixed the PHP, should work now. –  Eugen Rieck Jan 6 '12 at 21:36
 
@KaiQing no need for a while, we return exactly 1 row. –  Eugen Rieck Jan 6 '12 at 21:37
 
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 Jan 6 '12 at 21:39
show 2 more comments
up vote 1 down vote

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

share|improve this answer
 
Hi, I just tried this but get a row of three : 's but thats it, it doesnt seem to be pulling any data out. –  Iain Simpson Jan 6 '12 at 21:05
 
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? –  Mathieu Dumoulin Jan 6 '12 at 21:16
 
Yes that works in php my admin, I will check iv not made a mistake somewhere. –  Iain Simpson Jan 6 '12 at 21:18
 
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 Jan 6 '12 at 21:24
 
counttype countdata gasoil 2 derv 4 kero 3 –  Iain Simpson Jan 6 '12 at 21:24
show 1 more comment
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.