Problem with asc, desc

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

Moderator: General Moderators

Locked
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Problem with asc, desc

Post 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>");
?>
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

You don't need single quotes around rcount.
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Didin't help

Post by illmapu »

Hi,

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

Thanks! :?
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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).
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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>"); 
    }
} 

?>
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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!
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post by microthick »

or

ORDER BY CAST(rcount AS int(4))
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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!
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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! :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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;
illmapu
Forum Commoner
Posts: 47
Joined: Fri Aug 22, 2003 1:48 pm

Post 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! :)
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Continued at:
viewtopic.php?t=14415

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

Mac
Locked