What is the easiest way to orginize this data?

I am querying data from a database. I need to orginize it in three different ways.

1. By Increasing Number (I'm pretty sure MySQL has a command for this so this one should be fine)

2. By Increasing Date (Like: 6/5/03 > 6/4/03 This one I don't think MySQL has a command?)

3. Alphabetically (I think MySQL has a command for this one, right?)

Could someone tell me how I would either query the data in order or use PHP to parse the data into a correct order?

Thanks!

 

Just use 'ORDER BY column_name' in all 3 cases. (ASC/DESC to change high/low values)

 

This code works for dates too?

 

Yeah - it will also work for dates.

 

It works, kind of... In my database, my date values are store like this 5/6/2005. How would I change it just for this one function (without acctually changing the data in the database) to 200565 (format year,month,day so that MySQL can use ORDER BY correctly. I also have another value like 2/10 and I want to divide the first value by the second value so ORDER BY would read 0.2 How would I do this?

Thanks!

 

If you use datetime fields it should work in the orderby clause, so im guessing this is a custom date/varchar?

You can use the date_format function when you select the data and then use order by on the alias you assign it still.

The second one, short of writing a quick function to do it, is a bit harder - in SQL at least (well.... mysql).

Consider this.

SELECT (CAST('2/10' AS DECIMAL))/10 as thedecimal;

Code (markup):

OUTPUT: 0.200000

It works - but it is not the way to go about doing it, because the casting is only taking the first represented number you are more than likely going to end up with odd results.

Is there any reason it has been stored as a fraction and not as an integer? (for this reason, numbers are - in commercial databases at least - stored as decima*100 and converted back later if need be).

You can probably also chop the parts and do the sum inline but it might affect performance. give me a sec to think about it, i may have missed something obvious, for the reason given above......

 

I'm trying to select all the rows. Why isn't my mysql query no working. I am querying through PHP

//define sort
$sort = 'ORDER BY DATE_FORMAT(\'`date`\', \'%Y %m %d\');
 ASC'; }

Code (markup):

//define query
$sql = 'SELECT * FROM `U_Database` WHERE `name` = '{$varname}' '{$sort}' LIMIT 0, 10'

Code (markup):

Thanks!

 

I'm trying to select all the rows. Why isn't my mysql query no working. I am querying through PHP

//define sort
$sort = 'ORDER BY DATE_FORMAT(\'`date`\', \'%Y %m %d\');
 ASC'; }

Code (markup):

//define query
$sql = 'SELECT * FROM `U_Database` WHERE `name` = '{$varname}' {$sort} LIMIT 0, 10';

Code (markup):

Thanks!

 

oops, I posted a duplicate post. Anyway could someone tell me why my mySQL code isn't working. I think it has something to do with DATE_FORMAT?

 

Hello,
Am not sure about that mysql query (long time since I used it), but I suggest you to store date as timestamp. It would be easier to sort it, because it would be in number format

 

Please see this;

Why arent you storing dates as datetime ? (or date even). You will save yourself all of this hassle if you use the correct data structure to start with.....

 

Okay. I got the date part to work. Now how do I ORDER BY a number like (4/20) Yes. It needs to be in this format, and not in decimal.

I tried this but it isn't working where `number` is the field that contains a variable fraction.

ORDER BY CAST(`number` AS DECIMAL) DESC 

Code (markup):

Thanks,