SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: Problem with Order by query
-
Nov 24, 2004, 11:40 #1
- Join Date
- Jan 2004
- Location
- Wheelersburg
- Posts
- 115
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Problem with Order by query
Hey guys,
Not sure if this should be in mysql or php, but I thought it included more php than mysql, so put it here..
I have four things in my database, and in their downloads column I have the numbers 0,9,12 and 15. I am making a top 5 downloads thing, and instead of coming out 15,12,9,0, it comes out as.. 9,15,12,0
Here is how I have it set up..
PHP Code:<?php
$sql=mysql_query("select * from jamie_download ORDER BY downloads DESC");
$numrows=mysql_num_rows($sql);
for($i=0;$i<=$numrows;$i++) {
while ( $row = mysql_fetch_array($sql)) {
echo($row['name']);
}
}
?>
Thanks,
Chasehttp://chaseschwalbach.com
-
Nov 24, 2004, 11:47 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
what you did wrong (i'm guessing) is you didn't use a numeric datatype like INTEGER for your downloads column, but rather a string type like VARCHAR
you can use the following workaround, which isn't very efficient but nevertheless still works:
... ORDER BY 0+downloads desc
in this expression, mysql automatically converts the downloads string value to a number, in order to add it to 0
this would cause a "datatype mismatch" syntax error in any other database
sometime's mysql's sloppiness can come in handy!
-
Nov 24, 2004, 11:49 #3
- Join Date
- Jan 2004
- Location
- Wheelersburg
- Posts
- 115
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ty. I didn't know it made a difference
I changed it to INT and it worked perfect..
Thanks againhttp://chaseschwalbach.com
-
Nov 24, 2004, 12:07 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
well, when you think about it, integers sort numerically, and strings sort like, well, strings, one character at a time, from left to right
let me guess something else
when someone downloads something, you first do a SELECT, grab the download number, do the incrementing in php, and store the result back into the database with an UPDATE
now that you have a numeric field, you can double the efficiency of your application by skipping the SELECT and going right to the UPDATE
UPDATE yourtable SET downloads=downloads+1 where id = $id
Bookmarks