CODEWITHSUNDEEP
phpmysql
lloyd
lloyd

Reputation: 31

Advanced sorting and searching a mysql database using php and mysql

Im facing a unique challenge.

I got a table with 100 numbers called HUNDREDNUMBERS. I want to select the best quarter (75 to 100 numbers), and place them into another table called BESTQUARTER.

I also want to select the worst quarter (1 to 25 numbers) I want to place these into another table called WORSTQUARTER.

here's my Mysql code, so far, 

$Extract_Data = "
CREATE  TABLE $BESTQUARTER  
SELECT 
HUNDREDNUMBERS.number
FROM 
HUNDREDNUMBERS order by 
HUNDREDNUMBERS.number desc LIMIT 25 ";
$QuerySuccess =  mysql_query($Extract_Data, $connection);

and for the other table....

 $Extract_Data = "
 CREATE  TABLE $WORSTQUARTER  
 SELECT 
 HUNDREDNUMBERS.number
 FROM 
 HUNDREDNUMBERS order by 
 HUNDREDNUMBERS.number asc LIMIT 25 ";
 $QuerySuccess =  mysql_query($Extract_Data, $connection);

The problem is that this script is not 100% correct every time. Notice the ASC and the DESC in the two queries. It's an ingenious way of trying to sort the numbers.

BTW, some of the numbers in the HUNDREDNUMBERS table have decimal points. I need the data in the two new tables BESTQUARTER and WORSTQUARTER for further processing.

Any help is greatly appreciated

Upvotes: 1

Views: 419

Answers (4)

lloyd
lloyd

Reputation: 31

After long hours of thinking and testing, i believe i finally cracked it.

1) I changed the fieldname "numbers" to DOUBLE UNSIGNED. (initially i was using VARCHAR(50) )

2) Whenever you are using two or more tables that have the same field names, prefix EVERY fieldname with its tablename. I did that and it worked, as you shall see in the full query below.

3) the original data had multiple occurrences of the same numbers, ie there were several instances of rows with the value 100. MySQL transferred only a single row with the value 100, into the table BESTQUARTER. (i don't know why).

uniqueid    | id    | numbers   
1   200 100
2   6   100
3   76  100
4   64  99.009987655
5   10  95.98765432
6   11  11.98765432
7   12  25.12
8   13  53.173543
9   153 72.87676
10  32  99

So i added "GROUP By" and used the ID field. (nb: "uniqueid" column is the primary key, "id" is a unique key that uniquely identifies each number)

Here's the new code

create table BESTQUATER  
select 
HUNDREDNUMBERS.uniqueid , 
HUNDREDNUMBERS.id, 
HUNDREDNUMBERS.numbers
FROM 
HUNDREDNUMBERS
group by HUNDREDNUMBERS.id
ORDER BY HUNDREDNUMBERS.numbers  DESC  LIMIT 25

Upvotes: 0

Ja͢ck
Ja͢ck

Reputation: 173612

You're doing string comparisons and those follow different rules than numeric data types; I would suggest to change your sort expressions:

ORDER BY CAST(HUNDREDNUMBERS.number AS UNSIGNED) DESC|ASC

Instead of UNSIGNED you could also use SIGNED or DECIMAL(M, N) if you need to support negative numbers or floating points respectively.

Alternatively (and preferably), you could change the number column to a type that sorts properly by itself; VARCHAR should mostly be used for text.

Upvotes: 2

ryan0319
ryan0319

Reputation: 412

You should check the data types. Make sure the the numbers are stored as at least a decimal. Other data types can cause the sorting to be off (and is a quite common mistake). It seems simple, but your code actually looks to be correct from what my understanding is of the question.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

If you have only 100 numbers, I would suggest that you create a view with a rank, and use that for subsequent processing. Using intermediate tables seems like overkill:

select hn.*,
       (select count(*) from hundrednumbers hn2 where hn2.number <= hn.number
       ) as rank
from HundredNumbers hn

With an index on hundrednumbers(number), this will even have decent performance.

It is possible that the problem you are encountering is duplicates in the original data. If so, looking at the ranks can help you figure out what to do in this situation.

Upvotes: 0

Related Questions