[SOLVED] Adding time into MySql

[Democreous]

I'm trying to add a time into a mySQL time table.  It's in the format 00:00:00, but it never is inserted into it.

The reason for this code is to sort through events by time.

 

the insertion code is as follows with variables $mm = minute, $ampm= am or pm, and $hh = hour

 

if ($ampm == pm) // Sets To a 24 hour format
{
$hh = ($hh+12);
}
$time=("$hh:$mm:00"); // Puts it into the MySql time format... or so I think
echo ("$time"); // I test to see if it diplays in the correct mysql format, which it does ex. 02:30:00

$con = mysql_connect($HOST, $USER, $PASS); 
if (!$con) 
{ 
  // Since the entire script depends on connection, die if connection fails 
  die("Error connecting to MySQL database!"); 
} 
mysql_select_db($T, $con); 

$sql = "UPDATE calendar_events SET event_title='$title',event_desc='$description',event_date='$idate',event_time='$time' WHERE event_id='$id'"; 
         $result = mysql_query($sql); 
	 echo "<div class=\"ttl\">Event Updated, <a href=\"calendar.php\">Return to main page</a></div>";

}

[marcus]

Well it could be your database. Is the event_time field marked as INT(#) ? or VARCHAR(#)?

 

If the field is an integer it won't allow the colons to be adding in.

[Democreous]

I have it in a Time field, I want to be able to sort through it by event time, so I'm assuming it has to be in a time field.

[marcus]

try

 

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

 

See if you get any errors.

[Democreous]

ugg.  I accidently posted the edit script, the add script is suppose to be

 

	// SET TIME ACCURATE
if ($ampm == pm)
{
$hh = ($hh+12);
}
$time=("$hh:$mm:00");

echo ($time);

// Connect to MySQL database 
$con = mysql_connect($HOST, $USER, $PASS); 
if (!$con) 
{ 
  // Since the entire script depends on connection, die if connection fails 
  die("Error connecting to MySQL database!"); 
} 
mysql_select_db($T, $con); 

$query = "INSERT INTO calendar_events(event_title, event_desc, event_date, event_time)
VALUES('".$_POST['title']."','".$_POST['desc']."','".$_POST['idate']."','".$_POST['time']."')";
mysql_query($query);

  echo "<div class=\"ttl\">Event Added <a href=\"calendar.php\">Return to main page</a></div>";

  mysql_close($con);

[chronister]

Have you though about the timestamp data type.

 

If you are simply inserting the current time into the database, then you can set the field as a timestamp, and make it insert the current stamp automatically.

 

If you are inserting future, or past dates, then this will not work so well.

[Democreous]

yea, it is a future time

[chronister]

echo your query and see if the time is being passed into it properly.

 

If so, try copying and pasting the echo'd query into phpmyadmin and see if it gives you errors.

 

 

and also, you should sanitize this query before inserting it. It is not good practice to insert directly from the $_POST fields as mysql injection attack could be possible.

 

You should pass your $_POST vars through mysql_real_escape_string() before sending to the query.

 

$query = "INSERT INTO calendar_events(event_title, event_desc, event_date, event_time)

VALUES('".$_POST['title']."','".$_POST['desc']."','".$_POST['idate']."','".$_POST['time']."')";

mysql_query($query);

[Democreous]

I fiexed it, just took out

 

VALUES('".$_POST['title']."','".$_POST['desc']."','".$_POST['idate']."','".$_POST['time']."')";

 

and replaced it with

 

VALUES('$title','$desc','$idate','$etime')";

 

weird...