Tutorial :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?


Solution: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.


Solution: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.


Solution:3

this example should help
mysql_connect("localhost", "mysql_user", "mysql_password") or die("Could not connect: " . mysql_error()); mysql_select_db("mydb");

$result = mysql_query("SELECT id, name FROM mytable");

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {

printf("ID: %s Name: %s", $row[0], $row[1]);

}
from here http://us2.php.net/mysql_fetch_array


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »