Combining SQL queries in PHP

[ffsja]

Ok, this might be more relevant as a SQL question, but since it uses PHP, I figured I would post my question here anyway. If you take a look at the code below, you see that i have a bunch of queries. Is there an easy way to combine these queries together into one query?

$sql = "SELECT COUNT(*) as total from $table_name WHERE ID > 0";

$result = mysql_query($sql) or die(mysql_error());
$total = mysql_result($result,0,'total');

$sql = "SELECT AVG(knowledge) as average from $table_name WHERE knowledge > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_knowledge = mysql_result($result,0,'average');

$sql = "SELECT AVG(response) as average from $table_name WHERE response > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_response = mysql_result($result,0,'average');

$sql = "SELECT AVG(courtesy) as average from $table_name WHERE courtesy > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_courtesy = mysql_result($result,0,'average');

$sql = "SELECT AVG(comm) as average from $table_name WHERE comm > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_comm = mysql_result($result,0,'average');

$sql = "SELECT AVG(ownership) as average from $table_name WHERE ownership > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_ownership = mysql_result($result,0,'average');

$sql = "SELECT AVG(service) as average from $table_name WHERE service > 0";

$result = mysql_query($sql) or die(mysql_error());
$avg_service = mysql_result($result,0,'average');

Thanks,
S./

[drew010]

not really, the php mysql api doesnt support multiple queries in one call of mysql_query..

[ffsja]

Note: the code I'm about to write hasn't worked for me yet. 🙂

Isn't it possible to combine the queries, for example, like so:

$sql = "SELECT COUNT(*) AS total, AVG(knowledge) as kaverage, etc... FROM $table_name WHERE knowledge > 0 AND response > 0 etc...";

It seems like it would make the code so much more compact and therefore simpler, if I could just combine them like this. There's no way to do this?

Thanks drew010,

S./

[drew010]

yah its possible to combine like that using and clause, i thought you were meaning something like
mysql_query("SELECT ... FROM ... WHERE ...; SELECT ... FROM ...; SELECT.....");