Training, Open Source computer languages

This is page http://www.wellho.net/forum/Writing-PHP/Count-qu ... MySQL.html

Our email: info@wellho.net • Phone: 01144 1225 708225

 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
Count query in MySQL

Posted by JimL (JimL), 29 January 2004
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?

Posted by admin (Graham Ellis), 29 January 2004
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

Posted by JimL (JimL), 29 January 2004
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    

Posted by admin (Graham Ellis), 29 January 2004
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.

Posted by JimL (JimL), 29 January 2004
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



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

© WELL HOUSE CONSULTANTS LTD., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho