Using "COUNT"

[AWilliams]

I know this is a really simple question, but for some reason I just can't get the MySQL "COUNT" thing to work for me. Can someone please show me how to use the count feature (such as mysql_query("COUNT(*) FROM news WHERE id = 5") or something along those lines), then take the result so it can be displayed on the site.

Sorry if this is an elementary problem, my mind has gone blank.

[aspen]

Its still a SELECT statement, and you should alias any function you do

So Select Count(*) as recordcount from news where ID='5'

[AWilliams]

I forgot to type that in. I do have SELECT in there already. Here's what I have at the moment:

   $comment_count_sql = "SELECT COUNT(News_Comment_ID) AS comment_count FROM newscomments WHERE NID = ";
$comment_count_sql .= $latest_news["News_ID"];
$comment_count = mysql_query($comment_count_sql); 
$comment_count = $comment_count["comment_count"]; 

[Chris82]

Try putting or die(mysql_error()); at then end.
This will not solve the problem but it will tell you if there's something wrong with the query.

$query = mysql_query("SELECT COUNT(*) AS 'comment_count' FROM newscomments WHERE NID = {$latest_news['News_ID']}") or die(mysql_error());

$total = mysql_fetch_row($query);
print_r($total); 

I think on problem with your code is that you don't use fetch_row / array to actually get the data.

[MattR]

Originally posted by aspen
Select Count(*) as recordcount from news where ID='5'

Please do not enclose numeric values (e.g. auto_increment integers) in single or double quotes.

[aspen]

Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?

If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.

[MattR]

Originally posted by aspen
Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?

In the application which sends the data.

Originally posted by aspen
If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.

Well, you're relying on:
1) non-standard, undocumented behavior and
2) a side effect of MySQL-induced laziness.

If you look up numeric syntax in the docs, it says nothing about enclosing them in quotes:
http://www.mysql.com/doc/N/u/Number_syntax.html

I actually can't find in the documents where it allows numeric to string conversion on the fly but I assume it has to do with this page:
http://www.mysql.com/doc/D/e/Design_Limitations.html

Specifically:

If you insert a 'wrong' value in a column like a NULL in a NOT NULL column or a too big numerical value in a numerical column, MySQL will instead of giving an error instead set the column to the 'best possible value'. For numerical values this is 0, the smallest possible values or the largest possible value. For strings this is either the empty string or the longest possible string that can be in the column.

It sees you are comparing int and char, so it will convert the char to an int in order to follow their ‘ignore problems’ mantra.

The problem with this is, since it is undocumented and relies on the assumption that MySQL will ‘parse out’ everything but the initial $ID val there is a chance that they could change the behavior without notifying anyone (since you shouldn’t be doing this, they are more than in their right to do so).

Plus, it should be your application’s job to not allow tainted data to hit the DB. Simply run it through intval( $var ) in PHP, or some other likeness in another programming language.

[MattR]

Hmm, there is this here:
http://www.mysql.com/doc/C/a/Cast_Functions.html

To cast a string to a numeric value, you don't normally have to do anything; Just use the string value as it would be a number:

mysql> SELECT 1+'1';
-> 2

It, however, does not say what 'expected' results would be to non-numeric data in the enclosed quotes...

What would happen if someone instead passed:

$var = "1\'whatever\'";

That should escape the single quotes to allow something in there, no?

[DR_LaRRY_PEpPeR]

Originally posted by aspen
Hey Matt I know you harp on MySQL allowing the use of quotes being nonstandard but how would you suggest handling potentially tainted data?

If a form or a webpage is accepting $id as input, if you don't quote it in your sql some enterprising individual could improvise a variable value that could do some nasty things.

Thats why I always quote all variables in my SQL, and I have worked with other rdbms that don't allow it so I don't do it there, but I consider I consider it added security in MySQL.

even putting quotes around the data won't solve the problem. you need to addslashes() (if magic_quotes_gpc is off, which it should be) to string data. so just use a different function for numerical data. if someone enters "" or "abc", it will just be turned into a 0 which won't cause any problems in the query. you can use the intval() function, like MattR said, or i prefer to type-cast the variable with (int) since it's a bit faster and less to type.

$id = (int) $_GET['id'];
// OR
$id = intval($_GET['id']); 

just another part of good programming and verifying user data. no quotes around numbers in any of my queries.

P.S. what's even funnier is when people quotes numbers that aren't even from a variable, just a number that they hard-coded in the query.

[AWilliams]

So, erm, how do I count the number of rows?!?!?!!!!!

[Klav]

Couldn't you just use mysql_num_rows()?

[AWilliams]

Originally posted by Klav
Couldn't you just use mysql_num_rows()?

Yes, but that first requires a MySQL Query. That means two processes are done, instead of just one when using COUNT. That means more load on the processor, not much, but it is more.

[MattR]

So why doesn't this work?

$result = mysql_query( "SELECT COUNT( * ) AS comments
                          FROM newscomments
                         WHERE NID = $latest_news['News_ID']" );

$array = mysql_fetch_array( $result );

echo "Your count is $array[comments]"; 

[AWilliams]

That set me off in the right direction!

Thanks

I got it working with this:

   while ($latest_news = mysql_fetch_array($get_latest_news)) {
   $comment_count_sql = "SELECT COUNT(News_Comment_ID) AS comment_count FROM newscomments WHERE NID = ";
   $comment_count_sql .= $latest_news["News_ID"];
   $comment_count = mysql_query($comment_count_sql); 
   //$comment_count = $comment_count["comment_count"];
   

$array = mysql_fetch_array( $comment_count );

$comment_count = $array["comment_count"];