CODEWITHSUNDEEP
phpmysqldatetimecalendar
enloz
enloz

Reputation: 5834

PHP / MySQL query - if current time/date is in schedule

I'm trying to develop a simple php based schedule ...

This is my database table: enter image description here

Let's say that current time/date is: 2012-03-21 02:00:00

PHP script should echo: Meeting 2

I have a part of the script, but no idea what to do next

<?php
$con = mysql_connect("localhost","root","");
if (!$con){die('Could not connect: ' . mysql_error());}
mysql_select_db("schedule", $con);

$current_time = date("Y-m-d H:i:s");

$result = mysql_query("SELECT * FROM events");

while($row = mysql_fetch_array($result)){

  echo $row['Subject'];
}
mysql_close($con);
?>

How to filter query, to mach current time and scheduled subject? Thank you!

(if current_time is between StartTime and EndTime - echo it's Subject)

Upvotes: 2

Views: 4166

Answers (7)

Luca Rainone
Luca Rainone

Reputation: 16468

Try this query

SELECT * FROM events WHERE NOW() BETWEEN StartTime AND EndTime

Upvotes: 7

Jubin Thomas
Jubin Thomas

Reputation: 1013

putenv("TZ=Asia/Calcutta");  // To Set TimeZone
$now = date("Y-m-d H:i:s");
$sql = "select * from events where '$now' between StartTime and EndTime";

Upvotes: 0

OneSneakyMofo
OneSneakyMofo

Reputation: 1289

Firstly, you're going to have to convert your StartTime and EndTime into a timestamp using strtotime so you can compare it to the current time (same thing goes for current time):

http://php.net/manual/en/function.strtotime.php

Then just say if current_time > StartTime AND current_time < EndTime echo Subject. Something like this should work:

while($row = mysql_fetch_array($result)){
  $startTime = strtotime($row['StartTime']);
  $endTime = strtotime($row['StartTime']);
  if (strtotime($current_time) > $startTime && strtotime($current_time) < $endTime
      echo $row['Subject'];
}

Upvotes: 0

rainecc
rainecc

Reputation: 1502

The mysql BETWEEN operator may be of some use.

$startDate = gmdate('Y/m/d/ H:i:s','2012-03-21 02:00:00');
$endDate   = gmdate('Y/m/d/ H:i:s','2012-03-21 02:23:59');

$sql = 'select subject from events 
  where startTime between "'.gmdate("Y/m/d H:i:s", $startDate) . 
  '" and "' . gmdate("Y/m/d H:i:s", $endDate).'") ';

Upvotes: 1

Ajay Patel
Ajay Patel

Reputation: 5418

Your query should like this

$result = mysql_query("SELECT * FROM events WHERE current_time between startTime and endTime);

Upvotes: 1

Chetter Hummin
Chetter Hummin

Reputation: 6817

Please try the following query

select * from events where current_time between startTime and endTime

Upvotes: 1

Milan Halada
Milan Halada

Reputation: 1934

Try select * from events where StartTime <= NOW() and EndTime >= NOW() NOW() will give you current time in mysql.

Upvotes: 1

Related Questions