MySQL Forums
Forum List  »  PHP

Re: Sorting data with query and displaying on page
Posted by: Jonathan Stephens
Date: June 30, 2005 07:05PM

I dunno, I can't figure it out, either. But I can tell you this - using a string column to store numeric data is nearly always A Very Bad Idea. In this case, the strings are almost certainly not getting sorted in the order you think they are.

Consider the following:

# create a table
mysql> CREATE TABLE nw (col VARCHAR(10));
Query OK, 0 rows affected (0.04 sec)

# insert the ordinal forms of the numbers 1-11

mysql> INSERT INTO nw VALUES ('1st'), ('2nd'), ('3rd'), ('4th'), ('5th'), ('6th'), ('7th'), ('8th'), ('9th'), ('10th'), ('11th');
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0

# Let's do an ORDER BY query

mysql> SELECT col FROM nw ORDER BY col DESC;
| col |
| 9th |
| 8th |
| 7th |
| 6th |
| 5th |
| 4th |
| 3rd |
| 2nd |
| 1st |
| 11th |
| 10th |
11 rows in set (0.00 sec)

Oooops. Try doing this with '1st', '2nd', '3rd', ..., '34th', '35th' and the results become even more entertaining. :)

My advice is this: Before doing anything else, store the numbers of the visits in the DB as *numbers*. Use a PHP function to generate the ordinal form after you retrieve it from the DB. You can also perform the increment in the query, like so:

SELECT svisit + 1 AS sv FROM snex_data WHERE spo=$postart ORDER BY svisit DESC LIMIT 1;

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation

Options: ReplyQuote

Written By
Re: Sorting data with query and displaying on page
June 30, 2005 07:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.