MySQL counting most common value

Question

I have a MySQL database where users can list books they've read, want to read etc. I'm trying to write a query to find the most common book that users have listed.

My current query is:

$result = mysql_query("SELECT title, COUNT(title) AS counttitle FROM books GROUP BY title ORDER BY counttitle DESC LIMIT 1"); 
            echo "<p>The most popular book listed by members is $result</p>";

That seems (to me) to be the logical way to do it, and I can't see anything wrong with the syntax, but the result I'm getting is "The most popular book listed by members is Resource id #32"

Anyone any idea where I'm going wrong?

Answer 1

I think you're missing this:

 $row = mysql_fetch_assoc($result)

The $result isn't actually the answer, it's a result resource containing the row that has the answer.

Answer 2

Your query is fine, you need to read up on your PHP.

 result = mysql_query("SELECT title, COUNT(title) AS counttitle FROM books GROUP BY title ORDER BY counttitle DESC LIMIT 1");
 $row = mysql_fetch_assoc($result);
 echo "<p>The most popular book listed by members is " . row['title'] . "</p>";

Docs: http://us3.php.net/mysql_query

Return Values

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

The returned result resource should be passed to mysql_fetch_array(), and other functions for dealing with result tables, to access the returned data.