Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
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.

Comment: "good! i need a help on this topic! ..."
Visitor Ranking 4.0 (5=excellent, 1=poor)

Comment by satheesh (published 2010-03-21)
good! i need a help on this topic!
i have two tables, and i want to fetch the data like travel(2),sports(3),weather(0
) like that in while condition

i cant form a proper query to do that

travel,sports are category_name from category table

well values inside (num) is comes from tab2 which contains category_id

if two records having category id 1 means i have to print category_name(count(category_id))
[#3483]

You can Add a comment or ranking or edit your own comments

Average page ranking - 4.0

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