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

[nerf]

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 ?

[r937]

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

[nerf]

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:

$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>"; } 

[r937]

& 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

 where time_start <= current_timestamp
   and time_finish > current_timestamp
   and mech = 1

[nerf]

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

[r937]

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

[nerf]

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.

[nerf]

also are there better methods for wanting to loop this much ?
theres alot of querys to the database...

[r937]

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

[nerf]

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 ?

[r937]

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

[nerf]

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