COUNT question, sort of....

[unrelenting]

I am using a page I built that only I will use so it really doesn't matter how efficient it is because I will rarely execute it but I'd still like to learn the most efficient way to write such a thing. Here is the code. It should be fairly simple to see what I am trying to get it to do but as I add more and more setting numbers it will have more and more queries and I'd like to know how to do it with maybe just one query if possible.  ???

 

<?php
require 'connection.php';

mysql_select_db("board_table", $con);
$query = mysql_query("SELECT COUNT(*) AS num FROM themes WHERE ID_THEME = 5 and variable = 'setting' and value = 0");
$query = mysql_fetch_array($query);
$num0 = $query['num'];

mysql_select_db("my_table", $con);
$query = mysql_query("SELECT COUNT(*) AS num FROM themes WHERE ID_THEME = 5 and variable = 'setting' and value = 1");
$query = mysql_fetch_array($query);
$num1 = $query['num'];

mysql_select_db("my_table", $con);
$query = mysql_query("SELECT COUNT(*) AS num FROM themes WHERE ID_THEME = 5 and variable = 'setting' and value = 2");
$query = mysql_fetch_array($query);
$num2 = $query['num'];

mysql_select_db("my_table", $con);
$query = mysql_query("SELECT COUNT(*) AS num FROM themes WHERE ID_THEME = 5 and variable = 'setting' and value = 3");
$query = mysql_fetch_array($query);
$num3 = $query['num'];

mysql_close();

echo '
<table id="bodycontainer" border="0" width="100%" cellspacing="1" cellpadding="5">
<tr>
	<td>
		<table align="center" border="1" cellpadding="5" cellspacing="1" width="30%">
			<tr bgcolor="#dddddd">
				<td align="center"><b><font size="+1">Setting</font><b></td>
				<td align="center"><b><font size="+1">Users</font><b></td>
			</tr>
			<tr>
				<td align="center">Setting 1</td>
				<td align="center">' . $num0 . '</td>
			</tr>
			<tr>
				<td align="center">Setting 2</td>
				<td align="center">' . $num1 . '</td>
			</tr>
			<tr>
				<td align="center">Setting 3</td>
				<td align="center">' . $num2 . '</td>
			</tr>
			<tr>
				<td align="center">Setting 4</td>
				<td align="center">' . $num3 . '</td>
			</tr>
		</table>
	</td>
</tr>
</table>';
?>

[gevans]

Try this

 

<?php
//database conenction omitted
//used field `id` in count, use a unique field in place of the wildcard - *
$query = mysql_query("SELECT COUNT(`id`) AS num 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting' and 
GROUP BY `value`
ORDER BY `num`");
$query = mysql_fetch_array($query);
$num = $query['num'];
echo $num;
?>

[unrelenting]

Try this

 

<?php
//database conenction omitted
//used field `id` in count, use a unique field in place of the wildcard - *
$query = mysql_query("SELECT COUNT(`id`) AS num 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting' and 
GROUP BY `value`
ORDER BY `num`");
$query = mysql_fetch_array($query);
$num = $query['num'];
echo $num;
?>

 

That's just firing off an error on line 9 which is '$query = mysql_fetch_array($query);'

I tried it with a field name and with just the * wildcard.

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /www/html/test.php on line 9

[gevans]

Can you show your table structure?

[unrelenting]

Can you show your table structure?

FIELD                  TYPE
ID_MEMBER          mediumint(
ID_THEME            tinyint(4)
variable               tinytext
value                  text

[gevans]

<?php
$query = mysql_query("SELECT COUNT(`ID_MEMBER`) AS num 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting'
GROUP BY `value`
ORDER BY `num`");
$query = mysql_fetch_array($query);
$num = $query['num'];
echo $num;
?>

 

try that

[unrelenting]

<?php
$query = mysql_query("SELECT COUNT(`ID_MEMBER`) AS num 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting'
GROUP BY `value`
ORDER BY `num`");
$query = mysql_fetch_array($query);
$num = $query['num'];
echo $num;
?>

 

try that

 

Removing the 'and' from the original line

WHERE ID_THEME = 5 and variable = 'setting' and

keeps it from erroring but it isn't echoing anything at all.

[gevans]

OK, I've tested this on my system and it works

 

<?php
//database connection omitted
$query = mysql_query("SELECT COUNT(`ID_MEMBER`) AS num, value 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting'
GROUP BY `value`
ORDER BY value");
while($result = mysql_fetch_array($query)){
echo $result['value'].' - '.$result['num'];
}
?>

[unrelenting]

OK, I've tested this on my system and it works

 

<?php
//database connection omitted
$query = mysql_query("SELECT COUNT(`ID_MEMBER`) AS num, value 
FROM themes 
WHERE ID_THEME = 5 and variable = 'setting'
GROUP BY `value`
ORDER BY value");
while($result = mysql_fetch_array($query)){
echo $result['value'].' - '.$result['num'];
}
?>

 

Fantastic! Thanks.

 

One last question. The values for 'setting' are empty-3 (meaning blank,1,2,3). When it echos like this ( I added the <br />):

 

-5

1-32

2-12

3-5

 

Anyway to get that one to print as 0 rather than a blank spot?

 

 

[gevans]

I'm not sure I understand your question

[unrelenting]

I'm not sure I understand your question

 

Never mind. I got it like this. Thanks again.

 

while($result = mysql_fetch_array($query)){
$value = ($result['value'] == '') ? '0' : $result['value'];
echo $value.' - '.$result['num'].'<br />';