Page 1 of 1

Problem with asc, desc

Posted: Thu Nov 06, 2003 2:06 pm
by illmapu
Hi,

I have a membership and I'm trying to show which members have the most referrals in a list from most to least and eventhough I'm using asc, it is still showing the list of members in random order, ie 10,2,7 -when i would like it to be 10,7,2.... Can anyone tell me what I'm doing wrong in my code?

Thank you in advance! :)

require('include/config.php');
$db = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db($dbname,$db);

$sql = "SELECT * FROM users where rcount>1 ORDER BY 'rcount' ASC";
$result = mysql_query($sql) or die(mysql_error());
$num = mysql_num_rows($result);
print("<table><tr><td>Member</td><td>Referrals</td></tr>");
if($num != 0)
{
{
while($row = mysql_fetch_array($result)) {
$username = $row[0];
$rcount = $row[10];
print("<tr><td>$username</td><td>$rcount</td></tr>");
} } }
print("</table>");
?>

Posted: Thu Nov 06, 2003 3:14 pm
by microthick
You don't need single quotes around rcount.

Didin't help

Posted: Thu Nov 06, 2003 3:42 pm
by illmapu
Hi,

I removed the quotes, but it didn't make any difference, any other ideas?

Thanks! :?

Posted: Thu Nov 06, 2003 4:36 pm
by microthick
What order is it coming out as?

10, 2, 7 like you said?

Are you sure that rcount is an integer field and not a varchar?

Looks like it might be ordering by alphabet (1 before 2 before 7).

Posted: Thu Nov 06, 2003 4:41 pm
by infolock

Code: Select all

sql = "SELECT * FROM users where rcount&gt;1 ORDER BY 'rcount' ASC
shouldn't this be

Code: Select all

sql = "SELECT rcount FROM users where rcount&gt;1 ORDER BY rcount ASC



also in the code :

Code: Select all

<?php
if($num != 0) 
{ 
{ 
while($row = mysql_fetch_array($result)) { 
$username = $row[0]; 
$rcount = $row[10]; 
print("<tr><td>$username</td><td>$rcount</td></tr>"); 
} } } 

?>
looks like you have 1 too many braces...

try :

Code: Select all

<?php

if($num != 0) 
{ 
    while($row = mysql_fetch_array($result)) 
    { 
    $username = $row[0]; 
    $rcount = $row[10]; 
    print("<tr><td>$username</td><td>$rcount</td></tr>"); 
    }
} 

?>

Posted: Thu Nov 06, 2003 4:54 pm
by illmapu
Hi,

I tried,
sql = "SELECT * FROM users where rcount>1 ORDER BY 'rcount' ASC
but that took out the member name and still didn't put them in order from most to least.

It is a varchar, does that hurt me?

Here's an example of how it's coming out:

User Referrals
memx 99
jman 91
xcaliber 9
dog 84
zzz 8
pete 8
iou 77
trafficteam 76
bedazzled 74
theweb 73
willie 7
max 7

So, it's close, but for some reason, it isn't puting all the higher numbers in order first, then going to the smaller numbers, if that makes sense? My example of 10,2,7 was just a hypothetcial to try and make it easier for someone to understand my lingo
:wink:

Please help if you can!

Thanks again in advance!

Posted: Thu Nov 06, 2003 5:04 pm
by microthick
Yes, the fact that the field is a varchar explains why your data is coming out ordered as it is.

It is in fact being ordered by rcount, but alphabetically, since varchars are character fields and not integer fields.

Since you want to order as if it were an integer, you need to cast that varchar field into an int field.

How that's done, I dunno.

Maybe something like:

ORDER BY rcount::text::int

Posted: Thu Nov 06, 2003 5:07 pm
by microthick
or

ORDER BY CAST(rcount AS int(4))

Posted: Thu Nov 06, 2003 5:16 pm
by illmapu
Ok, darn, looks like I'm gonna be stuck on this one.....

If anyone can add to this to try to help me, mucho aprrciated!

Thanks in advance!

Posted: Fri Nov 07, 2003 6:52 pm
by illmapu
Hi,

Can someone tell me if I would have a problem if I changed the VARCHAR to INT, what impact it would have on the database? The only thing being stored in 'rcount' is a number of the referrals.

I already have members so I really don't want to mess them up, but I'd like to beable to shot the leading referrers.

Thanks in advance! :)

Posted: Sat Nov 08, 2003 11:57 am
by Weirdan
You can try the following:

Code: Select all

alter table your_table add column new_rcount int;
 update your_table set new_rcount=rcount;
Then check if all is copied as expected, then:

Code: Select all

alter table drop column rcount;
  alter table change column new_rcount rcount int;

Posted: Sat Nov 08, 2003 12:14 pm
by illmapu
Hi,

Thanks to everyone for trying to help me. Well, I took a risk and changed the varchar to an int. I was worried that it could mess up the database, so I did a backup and changed that field type, and that did it. They are now in order from most to least, and show the refrral count too, all using my original posted code. My database is still working properly, so everyone who wrote and said that all along were right.

Hopefully these posts will help the next person.....thanks again! :)

Posted: Sat Nov 08, 2003 3:09 pm
by twigletmac
Continued at:
viewtopic.php?t=14415

Please try not to start new topics on the same question.

Mac