MySql: Select all entries with count...

Question

Hey guys quick question, I have a query that I want to count all entries it finds, and select all so when I use while($row=mysql_fetch_assoc($query)){ it will list all entries. The problem I am encountering is that while, all the entries are successfully counted and the right number is listed, only the latest entry is select and listed when I echo $row['title']. If I delete , COUNT(*) as total then it selects all but I was wondering if it was possible to use count and select *. I was wondering if anyone knew what I am doing wrong?

SELECT *, COUNT(*) as total FROM new_messages WHERE username='$session->username

Answer 1

If you're going to fetch all records anyway count the records on the client-side (as seen from the MySQL's perspective, i.e. your php script).
If you're using an unbuffered query fetch all records first then call the function/methods that counts the result records (e.g. mysql_num_rows() ). If you're using a buffered query the order of function/method calls doesn't matter.

p.s.: You only get the value of "the last record" because using an aggregate function like Count() without a GROUP BY clause makes all records one group. see http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
MySQL allows you to select columns that are not part of the GROUP BY clause, but http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html says:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Answer 2

Why do you need count for each row? You only need to fetch it once, you can use mysql_num_rows for that:

$res = mysql_query("SELECT * FROM new_messages WHERE username='$session->username");
$count = mysql_num_rows($res);

Or you can fetch the count directly:

list($count) = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM new_messages WHERE username='$session->username"));

Answer 3

Well, first of all, you shouldn't be using mysql_fetch_assoc() because the mysql_ functions are obsolete.

At the least, you should switch to using the MySQL Improved Extension (mysqli) functions or alternatively the more general PDO functions.

If you use mysqli, you can use mysqli_result->num_rows() to count the number of rows in your result, there's a code example on that page showing how to use it.

PDO has no function for counting rows in a result set, but you can use fetchAll() and then just check the size of the array you fetched into.

And just for completeness, if you stick with the mysql_ functions (which you shouldn't, because you're risking security problems), you could use mysql_num_rows().