I am having problems performing a count on one of my MySQL tables.

Whenever I run the code below, the result is a table showing a key but no value:

==================================
|                       i                             |              count(uni_cardnum)                                           |
==================================
|                             1                                     |                                                                                                                                                                                                                           |
==================================

My code is the following:

<?php

$attemptsquery = mysql_query("SELECT COUNT(uni_cardnum) FROM tbl_log WHERE uni_cardnum = \"$unicardnum\"");

$i = 0;
while ($line = mysql_fetch_assoc($attemptsquery)) {
                         $i += 1;
                         $attemptsresult[$i] = $line[count(uni_cardnum)];
}

$htmloutput .= '<table border = "1">';
$htmloutput .= '<th>i</th><th>count(uni_cardnum)</th>';
foreach ($attemptsresult as $key => $value) {
                $htmloutput .= "<tr><td>$key</td><td>$value</td></tr>";
}
$htmloutput .= '</table>';

print($htmloutput);

?>


Any ideas?

Your query is bringing back one row of one column no matter how many matches there were, because you've selected the count of a field.  Having got the query object back, say

Code:

$line = mysql_fetch_array($attempts_query); $counter = $line[0];

and that should give you the count of the number of matching lines in the variable $counter.

By the way, I know there's also a function to return a single line result set more easily, but I'm out of the office and don't have all my notes to hand

Thanks for that Graham!

I understand your code (and by the way it is far more simple and elegant than mine!), but in the interests of learning where I went wrong, why is the following code not doing what I think it should?

$i = 0;
while ($line = mysql_fetch_assoc($attemptsquery)) {
     $i += 1;
     $attemptsresult[$i] = $line[count(uni_cardnum)];
}

As far as I understand, the "while" statement simply cycles through $attemptsquery while there are remaining rows (ok, there is only one row in this example). I am then surely populating my array $attemptsresult with key of 1 and value of the count?

Cheers,  Jim    

Where you wrote $line[count(uni_cardnum)] I *think* you should have written $line["count(uni_cardnum)"] as that'e the name (I think) in the associative array;  if you don't put the quotes in, I expect that PHP ran its count function on the string uni_cardnum and used the result, probably 0, as the subscript to the line array.

As usual you are right!

I've just checked what you said in my PHP & MySQL book and it says the same thing.

I needed to include the double-quotes in $line["count(uni_cardnum)"]

By the way, you mentioned another function which more directly returns the result from the query. After you mentioned that I had a look on PHP.net and found:

mysql_result()

This works just as well!

Thanks for your help.

Cheers,  Jim

---