I'm trying to create a time table using PHP and mysql.
Basically I have A for loop that goes from 600 to 2200, and inside that loop another loop that goes from 1 to 7 for each day of the week.
Inside my second for loop, I search my database for an event where day
is equal to my iteration inside my second loop, and the start and end is within the range of my first for loop. I have two events that should be returned. However it skips over the first one and only returns the second.
Here is my code:
$week = 1;
for($i = 600; $i <=2200; $i += 25){
echo $i .'</br>';
for($j = 1; $j < 8; $j++){
echo $j .'</br>';
$query = 'SELECT * FROM `event` WHERE `week` =:week AND `day` =:day AND (`start` <=:start AND `end` >=:start)';
$stmt = $dbh->prepare($query);
$stmt->bindParam(':week', $week);
$stmt->bindParam(':day', $j);
$stmt->bindParam(':start', $i);
if($stmt->execute()){
if($stmt->rowCount() > 0){
$row = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($row);
}
}
}
I also tried
SELECT *
FROM `event`
WHERE `week` =:week AND `day` =:day AND (:start BETWEEN `start` AND `end`)
and in both cases I get the same outcome.
Here is what is inside my events table
as soon as $i is equal to 900 it should return row of id 9, however it doesnt, but when $i is 1875, it prints out that event.
Here is a picture when I try to put it directly in phpmyadmin
Can anyone see why this is?
Problem was the 'start' and 'end' datatype, just realized they were varchar and not int.