Sorting results

[dwd]

Hi,

I am building an online auction and have run into a frustrating problem ;-)

My customer has a .csv file that they export from their Auction program, this contains about 700 'Lots' for the upcoming auction. The Auction program does not have any export options so i have to work to the .csv file structure. The idea is that they upload the .csv file straight into the mysql database using a secure webpage which i have created successfully.

The problem is when i pull the information from mysql onto my php webpage using the SELECT function it will not sort properly because some Lot numbers have letters on the end. e.g. 121a, 121b, 121c

This means that if i use:

SELECT * FROM au_gen ORDER BY Lot_no ASC

and the Lot_no column is set as TEXT, CHAR, or VARCHAR i will get results sorted as 1, 10, 100, 101, etc. instead of proper numerical order.

if i set the Lot_no column to INT then it will sort correctly but it drops the letters of the end because INT doesn't accept letters.

Any ideas on how to sort the Lot_no column in proper numerical order with letters would be appreciated.

Thanks in advance,

Wayne

[Jay]

Separate the numerical and alpha part into two columns, then sort the numerical followed by alpha and it should work OK

[dwd]

Hi Jay (nice avatar!),

The problem i have is that there are over 700 rows in the .csv file and many of these have letters after the lot numbers so it would be very time consuming to go through the database and change it. Plus the customer that i am doing this for just wants to grab the .csv file and upload it without having to do anything. This is a weekly to fortnightly process.
I really need to adjust php/mysql to work without changing the original text file.

I had an idea that i might be able to:
1. Make the mysql Lot_no column TEXT (so it will accept letters & numbers)
2. SELECT * FROM au_gen (no order by clause)
3. Split the Lot numbers into 2 variables $number & $letter by using the php 'list' & 'split' function
4. Get php to sort the results

Am i on the right track? If so, i don't know how to get php to do step 4.

Thanks.

[Jay]

I don't understand why MySQL doesn't sort it accordingly, even if it's got letters after the numbers. It knows how to sort letters. Are you sure there's not a problem in your query or something?

[Woersty]

:idea: Try this:

Create an additionally field Lot_no2 of type integer, because an integer is sorted naturally, and insert the same data into it.

You can use UPDATE au_gen SET Lot_no2=Lot_no; to synchronize your data.

Now you can try: SELECT * FROM au_gen ORDER BY Lot_no2, Lot_no ASC