Time comparisons

[posse2009]

I'd like to assign a database field to a group, depending on a set of criteria.

 

The field is $openingtime, which is a TIME formatted as 08:00:00 for example.

 

If $openingtime is between 08:00 and 12:00 it will need to be in Group 1

If $openingtime is between 13:00 and 17:00 it will need to be in Group 2

If $openingtime is between 17:00 and 07:00 it will need to be in Group 3

 

I understand i will have to convert the TIME to Unix timestamp using strtotime() however I am not having much luck with getting this to work.

 

Any pointers would be much appreciated.

 

Thanks

[Mchl]

UPDATE table SET group = 'Group1' WHERE openingTime BETWEEN 080000 AND 125959

UPDATE table SET group = 'Group2' WHERE openingTime BETWEEN 130000 AND 165959

UPDATE table SET group = 'Group3' WHERE openingTime BETWEEN 170000 AND 075959

[posse2009]

Thanks for this - I'm not sure where I would use it though.  Do you mean to use this with my upload form that adds my records to the database?

 

 

[Mchl]

These are just MySQL queries that will modify your existing records.

If you need to assign groups upon inserting rows to database, it'd be best to prepare data in PHP.

Unfortunately from you initial post it is hard to say what exactly you need. If you posted some code, you could probably get better help (and sooner)

[posse2009]

Thanks, here is some code I am working with to try and assign a Group, to an event time:

 

<?php

 

$query = "SELECT openingtimeFROM events";

$result = mysql_query($query) or die(mysql_error());

 

$starttime = $_POST['openingtime'];

 

$abc = strtotime($openingtime);

$hour = date('G',$abc);

$timezone = 0;

if (($hour >= 6) && ($hour < 12)) {

  $timezone = 1;

} elseif (($hour >= 12) && ($hour < 17)) {

  $timezone = 2;

} elseif ($hour > 17) {

  $timezone = 3;

}

$timebracket = 'Time zone '.$timezone;

 

echo "<table border='1'><tr>";

for($i = 0; $i < mysql_num_fields($result); $i++){

echo "<th>".mysql_field_name($result, $i)."</th>";

echo "<th>Time Bracket</th>";

}

echo "</tr>";

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

echo "<tr>";

for($i = 0; $i < mysql_num_fields($result); $i++){

echo "<td>". $row[$i] ."</td>";

echo "<td>". $timebracket ."</td>";

}

echo "</tr>";

}

 

echo "</table>";

?>

 

This however does not work - from what I can see the variable $openingtime isn't being recognised, therefore a comparison cannot be made.

[Mchl]

Please post you code in

tags.

 

You have an error in your query

$query = "SELECT openingtime FROM events";

[posse2009]

Thanks, that doesn't affect anything though - I'm still not getting a comparison returned. 

[Mchl]

$starttime = $_POST['openingtime'];

$abc = strtotime($openingtime);

 

I suppose you mean

 

$starttime = $_POST['openingtime'];

$abc = strtotime($starttime);