PHP - 在mysql varchar中插入小时的字符串末尾添加NULL

Question

I have a really strange (for me) problem. I have a Mysql db where i store opening hours of a store: This is the table stucture

#   Nome        Tipo            Codifica caratteri  </br>
1   id          int(11)         No</br>
2   store_slug  varchar(50)     utf8_unicode_ci </br>
3   dow         tinyint(4)      No</br>
4   type        varchar(2)      utf8_unicode_ci </br>
5   open        varchar(10)     utf8_unicode_ci</br>    
6   close       varchar(10)     utf8_unicode_ci </br>
7   extra_text  varchar(50)     utf8_unicode_ci</br>

I get time as input in a one php file and another php file insert that input data to the database.The problem is here:

<?php $open_d = $hours[$i]["d"]["o"];
      $close_d =  $hours[$i]["d"]["c"];
      $type = "d";
      $sql = "INSERT INTO `".MLS_PREFIX."store_hours`(store_slug,dow,type,open,close) VALUE('$store_slug',$i,'$type','$open_d','$close_d')";
      echo $sql ;
      echo "</br>";
      if($db->query($sql)){ echo "Store saved !"; } else { echo "Some error came up ! "; }

?>

For example Resulting SQL insert for 1 row is :

INSERT INTO `aCr_store_hours`(store_slug,dow,type,open,close) VALUE('haki',1,'d','13:01','15:00')

When I copy and execute this SQL in phpmyadmin all works fine but if i use the PHP script with the same SQL the time in 'open' and 'close' columns will be something like this

enter image description here

Can, please, someone help me with this really strange problem?

Thanks and have a nice day!

Regards

Michele

Answer 1

You need to escape the colon in your query. Try prefixing all colons with backslashes.