php mysql query..
I wonder if anyone can offer me a small bit of advice here..
I have a table in mysql which contains info on local upcoming events in the fields.
When the events.php page is called the page will just show whats going on. Now what i want to try to do is list the events so the most recent coming up is at the top so i put this in.
and everything is fine, but the date field will only really be put in order by the day and disregard the month and year..
$results = mysql_query("SELECT * FROM eventinput ORDER BY date ASC LIMIT $page, $limit");
The user inputs the details of the event as dd/mm/yyyy and that is how it sits in mysql..
Now my question is how would i call this info so the dates ascend or descend correctly. Would i have to somehow alter the event form input, alter something in phpmyadmin so it recieves the date in the right format or have a php script that when calling the page sorts this out..
Im not really sure of the way of doing this...
On another note, whilst i am here would it be also be possible for some kind of script to be put in the query that would in turn recognize the date and delete any out of date events whilst calling the data..
any help or advice would be much appreciated..
Just want to check - are you saying that you're holding the date in a proper date field, but that your query is treating it like text for sorting purposes?
Guess that's my main question: what is the data type of the field in which the date is stored?
Last edited by GaryS; 06-14-2006 at 01:21 PM.
That's your problem - in mysql, that is not how a real date is stored. use a normal date/time type column.
Originally Posted by bolty2uk
At present the date the user inputs is in a varchar field because as i understand it the date would have to be submitted as yyyy mm dd if it was a date field??...which would be a pain..
If there was a way of having a user input the date as dd/mm/yyyy and when entering the table it would be stored as yyyy mm dd and changing back to dd/mm/yyyy on output it would be ideal..
Just a thought can phpmyadmin edit the way the date field handles the date, or simply can it be changed from yyyy mm dd to the way i need...
Im just not really sure of the best way to input, and store this info...
sorry if my ramblings are a bit confusing...
Believe me, the coding challenges that you'll face if you don't stick your date in a date field will be much greater that getting a user's input into the right shape.
If the user submits the date in the form dd/mm/yyyy, you could grab each of the elements ( explode? ) and "create" a date that mySQL can swallow.
Not true! you just have to learn how to convert it to a mysql date. look at http://dev.mysql.com/doc/refman/5.0/...functions.html
Thanks for the help ..
I have now changed the field to a date field and will have a good read of the link provided...i knew i was doing it incorrect but wasnt sure how to adapt to the mysql date format..
Which part isn't true?
Originally Posted by chazzy
You can covert the date within the query using the STR_TO_DATE() MySQL function:
// Assume date value from input form is in variable $date in mm/dd/yyyy format:
$query = "INSERT INTO table_name (date_column) VALUES (STR_TO_DATE('$date', '%m/%d/%Y'))";
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
I once had this problem and I did not feel like messing around with Date/Time fields, so I just created an integer field and converted all dates to Unix Timestamps before storing them on the database. Then, you can order the rows accordingly.
$year = 2006;
$month = 06;
$day = 14;
$timestamp = mktime(0, 0, 0, $day, $month, $year); //1170748800
$query = sprintf("INSERT INTO `table_name` VALUES %d", $timestamp);
$query = "SELECT * FROM `table_name` ORDER BY `date` ASC";
Some good options to try now which i was totally unaware of before..
Thanks for the input from everyone who has offered advice..
Originally Posted by GaryS
because as i understand it the date would have to be submitted as yyyy mm dd if it was a date field
Doing this removes most of the features of a date/time/timestamp column. if you're doing all this, why did you pick int and not timestamp column?? you can sort, yes, but you can't do date manipulation (computing ranges requires conversion to int rather than comparing against dates)
Originally Posted by VoodooSteve
date asc issue
I wonder if someone could shed a bit of light on something here...
Everything is now working fine with date in a date field, only dates that are coming up are being shown but i still still seem to have a problem i cannot resolve....
Using this code will do what i need apart from ascend the date correctly....
The date sits in the date field like this 0000 00 00 and with the above code is displayed like so 1st January 2006
mysql_query("SELECT name, DATE_FORMAT(date,'%D %M %Y') AS date, location, telephone, email, admission1, admission2, website, description FROM `eventinput` WHERE date >=CURDATE() ORDER BY `date` ASC LIMIT $page, $limit");
The ORDER BY date ASC will only work on the days and not affect the months or year..so 9th feb will be listed before 10 january..
So as not to confuse the column is called date and it is a DATE field..
As the above code is actually doing as it is asked im starting to think i may have to alter something in mysql but am very unsure where to start...
Thanks for any responce this may get....
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)