Jump to content


Photo

SUM & ORDER BY query


  • Please log in to reply
1 reply to this topic

#1 markmuir

markmuir
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 30 August 2006 - 03:10 PM

Hi guys,

Having a minor problem here. On my reports page, I want to display the top 5 books that people have chose.
In my database, i have 3 fields (Book, School, Hits). Inside my insert.php script, it automatically inserts a number 1 into the hits every time I submit the form.

On my reports page, I am able to list the books with the amount of hits they have had but I'm unable get them to display the highest first.

insert.php

<?

$link = mysql_connect("localhost", "username", "password") or die("Could not connect");
$db = mysql_select_db("database", $link) or die("Could not select database");

$book=$_POST['book'];
$school=$_POST['school'];


$result=mysql_query("INSERT INTO `books` VALUES ( '$book', '$school', '1') ");

?>


reports.php

<?

$link = mysql_connect("localhost", "username", "password") or die ('My SQL Error: ' . mysql_error());
$db = mysql_select_db("database");

$result = mysql_query ( "SELECT Book, sum(Hits) FROM `books` GROUP BY 'Book' ") or die("SELECT Error: ".mysql_error());

?>

<?

print "<table width=400 border=3>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td><font face=verdana size=3/>$field</font></td>\n";
print "</tr>\n";
}
print "</table>\n";
?>



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 August 2006 - 04:50 PM

Just use a column alias for the SUM() and then order by that desc.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users