SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT query, '>' on a DATETIME field...

    I have a few loops in my PHP program and in the last one i have a mysql_query which checks 2 DATETIME fields, the date im checkin it against doesnt even seem to matter. Even if i typed something like "...WHERE date1 = 'abc';" it still returns the query as TRUE.

    Is mySQL supposed to ignore it if the queryed data is incorrectly formatted ?
    And if it is correct can you query a DATETIME field with dates that are '>' larger than so on ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, and yes

    for further assistance, we'd need to actually see your query

    and could you explain why you are querying inside a loop? there might be a more efficient method
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well first of the code is creating a schedule matrix, with what is being booked accross top and time going down, the hours are in a database table, so in writing, the loop is supposed to go as follows:
    Start loop through hours
    >Loop through all people that can be booked (accross)
    >>then check with current persons ID and check with Time. (bookings code below)
    >moves to next person and checks the current time against all
    Moves to next time to check and loops

    The table gets created although here ill put the code and the query that seems to be having trouble:
    PHP Code:
    $bk "SELECT * FROM bookings WHERE ";
    $bookings mysql_query($bk."time_start <= '$curdate' & time_finish > '$curdate' & mech = 1");//.$mech["ID"]);
    if ($bookings) {
        echo 
    "<td width='100' bgcolor='#FF0000'></td>";
    }else{
        echo 
    "<td width='100' bgcolor='#FF00FF'><font size=1></font></td>"; } 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    & is not a valid mysql logical connector, use the AND keyword instead

    for slightly faster execution, if time_start and time_finish are datetimes, use mysql's reserved word CURRENT_TIMESTAMP instead of passing in a string that mysql would first need to convert into a datetime value
    Code:
     where time_start <= current_timestamp
       and time_finish > current_timestamp
       and mech = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is the value for current_timestamp ? the server date and time ?
    the date that is used is created defaultly using server time if it is not selected from the calender which sends the new values for the date through querystrings, and as said before the times are listed in database, from 0800->1600 in 15 min increments.

    i changed the &'s and used current_timestamp but still it only creates the table with all cells red, which if u look in the code means that the query was succesful... ?

  6. #6
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also are there better methods for wanting to loop this much ?
    theres alot of querys to the database...

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by nerf
    what is the value for current_timestamp ? the server date and time ?
    yes

    i'm afraid i cannot help you with your looping, because i don't know what you're trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ...make sense ?

    Code is to create a table. Start first with "<table...>"

    Loop1: Get list of hours from database
    "<tr>" Create first row (put time in a cell on far left)

    Loop2: Get list of staff who can be booked
    Now we have a current time and a staff ID number and

    Loop3: Check in bookings table for entry with datetime and staff ID values
    and if the query is TRUE, then "<td></td>" with a red bgcolor.
    or if its FALSE, then the same with a diff color. End loop 3.

    Next entry in Loop2 -> until at end and then "</tr>"
    Next entry in Loop 1 -> until all times are done

    and then "</table>"

    the database table for bookings has the following fields for times.
    time_start [DATETIME]
    time_finish [DATETIME] where the date will always be the same in both fields only the time will be different (no multiple day bookings) then only seperated by staffID if times are also the same.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would move the query out of the loop, change it to get all the data that you want to display in one call to the database, make sure the results are returned from the database in a particular sort order, and then loop over the results to produce the matrix, rather than call the database from inside the loop over and over
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so perhaps, i should query the database and put into an array(?) all of the data where the datetime fields are LIKE the %date% (to attain entries only from the current day)

    I dont entirely understand how to query once outside the loop.
    Is it bad coding to query databases inside all loops ?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, i cannot comment on putting database results into an array, that would be a php technique and i don't do php

    you would not use LIKE on datetime columns, you would use date expressions and/or date functions

    yes, it is bad coding to run queries inside a loop
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Enthusiast nerf's Avatar
    Join Date
    May 2004
    Location
    Sunshine Coast, Australia
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well if u dont really do PHP then this might not help, ill go back to the PHP section aswell. But this is what im trying to acheive: http://www.zebratec.net/nerf/workingfor.html


Bookmarks

Posting Permissions

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