[SOLVED] Sorting/Dates kinda question :$

[stoneymedia]

Hello PHP Freaks!

 

Im a brand-new noobie, so please forgive me if this is in the wrong place, or not helpful at all! Basically, im stumped with a PHP/MYSQL issue im having..

 

I have a table in my database, and a php page which displays data from the table.. I need to only display particular content, and I need it sorted by date, let me try and explain where I am.

 

SELECT * FROM event_ WHERE field_1='F501 BF1' ORDER BY field_6

 

^^ is my current Query for MySQL, if I type that into the SQL Query box within PHPmyAdmin, I get back

 

http://www.formula500.org/nextrace/f501schedule.php which seems to think that June comes before July..

 

Can anyone explain if I've gone wrong... where and how? Im sorry, ive not done a great job explaining it here.. it sounded easier in my head..

 

I basically need the data in field_1 to display all the entries which are "F501 BF1" and order them by field_6..  ???

[dptr1988]

So 'field_6' contains the month for that entry?

 

Could you describe your table, and the data format of the relevent colums.

[DarkWater]

June DOES come before July.  That's in date order....

[dptr1988]

@DarkWater:

The table in the URL he mentioned shows June then July, then June again. I am concerned that he is not useing a standardized date/time format for his database

[DarkWater]

Ahaha!  I didn't notice that.  Thanks for pointing it out.

 

Try running the query in the mysql console.

[AndyB]

I'd agree with that last post. They appear to be sorted alphabetically so I'm guessing the 'date' is stored in a varchar field.  No,no,no. Store dates in a date field and use the standard, sortable, ISO form yyyy-mm-dd.  You will be able to 'operate' on dates in that format and you can display them in your choice of date style.

[stoneymedia]

I'd agree with that last post. They appear to be sorted alphabetically so I'm guessing the 'date' is stored in a varchar field.  No,no,no. Store dates in a date field and use the standard, sortable, ISO form yyyy-mm-dd.  You will be able to 'operate' on dates in that format and you can display them in your choice of date style.

 

You guys are absolute stars. Thank you.

 

I worked through the responses in this thread, and was just starting to put my reply together, clicked submit and forums told me another post had been made, so I read it through, seen about the date field and using the yyyy-mm-dd format, so I tried this, and it works lovely.

 

Thank you again.

[stoneymedia]

Afternoon guys ,

 

I have a second request/question.. I have done what I wish to do for the above, so thats put to bed with, however, I now need a page to display data from the same database slightly differently.

 

I wish to pull the data from the database automaticly based on the actual date.

 

For example:

 

If the actual date was 6th July 2008, and I wanted to show events happening in the week between 6th July and 12th July, would there be a function that could pull events that fall between those dates from the database, and display them in next event order?

 

I realise this could get a little complex, so its not essential, as in, I could do this manually by hand, but if it was something which automaticly updated, that would be very cool

 

Thanks again for any advice.