Geek Answers Handbook

MySQL calculates the most common value

I have a MySQL database in which users can view books they have read, want to read, etc. I am trying to write a query to find the most common book that users have indicated.

My current request:

$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>";

It seems to me that this is a logical way to do this, and I see nothing wrong with the syntax, but the result I get is "The most popular book listed by members is resource identifier # 32"

Does anyone know where I am going wrong?

+4
source share
3 answers

I think you are missing this:

  $row = mysql_fetch_assoc($result)

$result is not really an answer, it is a result resource containing a string that has an answer.

+5
source

Your request is beautiful, you need to read it in 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 that return a result set, mysql_query () returns the resource with success or FALSE on error.

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

The returned result resource must be passed to mysql_fetch_array () and other functions to process the result tables to access the returned data.

+4
source

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]);

}
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

+1
source

More articles:


All Articles