SitePoint Sponsor |
|
User Tag List
Results 1 to 12 of 12
-
Jan 16, 2005, 07:09 #1
- 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 ?
-
Jan 16, 2005, 07:36 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jan 16, 2005, 18:20 #3
- 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>"; }
-
Jan 16, 2005, 18:31 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jan 16, 2005, 18:54 #5
- 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... ?
-
Jan 16, 2005, 18:55 #6
- 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...
-
Jan 16, 2005, 19:12 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by nerf
i'm afraid i cannot help you with your looping, because i don't know what you're trying to do
-
Jan 16, 2005, 19:31 #8
- 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.
-
Jan 16, 2005, 19:52 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jan 16, 2005, 20:04 #10
- 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 ?
-
Jan 16, 2005, 20:12 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Jan 16, 2005, 20:16 #12
- 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