Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Sorting results

Codes here !

Moderators: macek, egami, gesf

Sorting results

Postby dwd » Wed Oct 23, 2002 10:22 pm

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
dwd
New php-forum User
New php-forum User
 
Posts: 2
Joined: Thu Oct 17, 2002 10:28 pm

Postby Jay » Thu Oct 24, 2002 2:06 am

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

Postby dwd » Thu Oct 24, 2002 3:34 am

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.
dwd
New php-forum User
New php-forum User
 
Posts: 2
Joined: Thu Oct 17, 2002 10:28 pm

Postby Jay » Thu Oct 24, 2002 7:02 am

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?
Jay
 

Postby Woersty » Thu Oct 24, 2002 10:47 am

: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
User avatar
Woersty
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Oct 17, 2002 9:51 am
Location: Berlin


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 3 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.