Get the row count and all row data from a mysql/mysqli SELECT query

Question

I am using a SELECT query to get the data from a number of rows and I also want to get the count of the rows.

When I use while ($row = mysql_fetch_assoc($query)) { it will list all entries.

The problem that I am encountering is that while all the entries are successfully counted and the right number is listed, only the latest entry is selected and listed when I echo $row['title'].

If I delete , COUNT(*) as total then it selects all correctly, but I was wondering if it was possible to use COUNT() and SELECT *.

My problematic query:

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

Answer 1

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().