www.webdeveloper.com
Results 1 to 14 of 14

Thread: php mysql query..

  1. #1
    Join Date
    Feb 2006
    Posts
    91

    php mysql query..

    hello guys..

    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.

    PHP Code:
    $results mysql_query("SELECT * FROM eventinput ORDER BY date ASC LIMIT $page$limit"); 
    and everything is fine, but the date field will only really be put in order by the day and disregard the month and year..

    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..

    ian

  2. #2
    Join Date
    Apr 2006
    Posts
    370
    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 12:21 PM.

  3. #3
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by bolty2uk
    The user inputs the details of the event as dd/mm/yyyy and that is how it sits in mysql..
    That's your problem - in mysql, that is not how a real date is stored. use a normal date/time type column.
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  4. #4
    Join Date
    Feb 2006
    Posts
    91
    Hello GaryS
    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...

    Thanks..

    sorry if my ramblings are a bit confusing...

  5. #5
    Join Date
    Apr 2006
    Posts
    370
    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.

  6. #6
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  7. #7
    Join Date
    Feb 2006
    Posts
    91
    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..

    Thanks guys...

  8. #8
    Join Date
    Apr 2006
    Posts
    370
    Quote Originally Posted by chazzy
    Not true![/url]
    Which part isn't true?

  9. #9
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,227
    You can covert the date within the query using the STR_TO_DATE() MySQL function:
    PHP Code:
    // 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

    eBookworm.us

  10. #10
    Join Date
    Sep 2005
    Location
    BC, Canada
    Posts
    6

    Unix Timestamps

    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.

    PHP Code:
    //example date
    $year 2006;
    $month 06;
    $day 14;

    //make timestamp
    $timestamp mktime(000$day$month$year); //1170748800

    //insert query
    $query sprintf("INSERT INTO `table_name` VALUES %d"$timestamp);

    //select query
    $query "SELECT * FROM `table_name` ORDER BY `date` ASC"

  11. #11
    Join Date
    Feb 2006
    Posts
    91

    Thank you

    Thanks guys..

    Some good options to try now which i was totally unaware of before..
    Thanks for the input from everyone who has offered advice..

    Cheers...

  12. #12
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by GaryS
    Which part isn't true?
    this
    because as i understand it the date would have to be submitted as yyyy mm dd if it was a date field
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  13. #13
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by VoodooSteve
    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.
    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)
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  14. #14
    Join Date
    Feb 2006
    Posts
    91

    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....

    PHP Code:
    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 date sits in the date field like this 0000 00 00 and with the above code is displayed like so 1st January 2006

    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....

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles