Order by count

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nashyboy
Forum Newbie
Posts: 14
Joined: Thu May 02, 2002 8:46 am

Order by count

Post by nashyboy »

Hi peeps

I have created a referral program and all works fine expect....

I have a page showing recommended sites which displays all sites registered and how many hits they have sent us. The aim is to have these displayed in order by hits desc.

however all of a sudden its all over the shop!

i think the prob is something to do with the order but cant get it right.

I have 6 entries so far and its displaying results in this order

Site name | 4
Site name | 3
Site name |16
Site name | 1
Site name | 1
Site name | 1


its as though its not counting the 6 on the site with 16 hits and listing it as though its 1.

Please can anyone help.

Here is part of the code im using:

<?

echo "<table width=100% border=0>";
echo "<td width=300 valign=top>";
echo "<table width=300 border=0>";
echo "<tr bgcolor=#000099>";

echo "<td width=50 align=\"center\">
<font face=Verdana size=1 color=#FFFFFF><b>Rank</b></font></td>";

echo "<td width=200 align=\"left\"><font face=Verdana size=1 color=#FFFFFF><b>Site Name</b></font></td>";

echo "<td width=50 align=\"center\"><font face=Verdana size=1 color=#FFFFFF><b>Hits</b></font></td>";

echo "</tr>";

$db = mysql_connect("localhost", "root");
if(!$db)
{
echo " An Error has occured. Unable to connect to the database.\n";
exit;
}

$sql = "SELECT site_name,count,site_url,descrip from referrer where site_status ='Live' ORDER by count desc, site_name";

$resultSet=mysql_db_query("mydb",$sql,$db);
$count==0;
if ($resultSet)
{
while ($theresult=mysql_fetch_row($resultSet))
{
$count=$count+1;
echo "<tr bgcolor=#FF6600>";

echo "<td align=\"center\">
<font face=Verdana size=1 color=#000099>$count</font></td>";

echo "<td><font face=Verdana size=1><a href=\"$theresult[2]\" target=\"_blank\"><font color=#FFFFFF>$theresult[0]</font></a></td>";

echo "<td align=\"center\"><font face=Verdana size=1 color=#000099>$theresult[1]</td>";

echo "</tr>";
}

mysql_close();

?>
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What type of field (in the database) are you storing the count in?

Mac
nashyboy
Forum Newbie
Posts: 14
Joined: Thu May 02, 2002 8:46 am

Re: Order by count

Post by nashyboy »

the field is called count and its varchar - should it be something else? if so can i edit it through telnet without scrapping the whole thing?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Well considering it contains whole numbers (integers) it really should be an integer field, tiny/small/medium, depending on what the size of the numbers could be:
http://www.mysql.com/doc/C/h/Choosing_types.html
http://www.mysql.com/doc/C/o/Column_types.html

You can change the type of the count column to an integer type with no loss of data (unless you are storing values in the column which aren't whole numbers of course). You can do this through the command line using ALTER TABLE.

Mac
nashyboy
Forum Newbie
Posts: 14
Joined: Thu May 02, 2002 8:46 am

Re: Order by count

Post by nashyboy »

Cheers mate :)
Post Reply