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,