0

I have a time (H:i:s format), but not a date, so I call:

 $dateFormat = 'Y-m-d H:i:s';
 $value4 = date($dateFormat, strtotime($_POST['start_time']));
 $value5 = date($dateFormat, strtotime($_POST['finish_time']));

Then I call the sql function:

$sql = "INSERT INTO times (site_id, crew_leader_id, service_number, start_time, finish_time,
                             deicer_quantity, salt_quantity)
VALUES ($value1, $value2, $value3, $value4, $value5, $value6, $value7)";

I get the following error:

Error: INSERT INTO times (site_id, crew_leader_id, service_number, start_time, finish_time, deicer_quantity, salt_quantity) VALUES (3, 1, 1, 2016-11-06 15:00:00, 2016-11-06 15:15:00, 0, 0)
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '15:00:00, 2016-11-06 15:15:00, 0, 0)' at line 3

The DateTime is in the correct mysql format, but won't go into the DB. Any ideas as to why? I'm running the latest php and mysql. Alternatively, if I try convert to a DateTime in mysql from a datetime string via UNIX_TIMESTAMP('datetimestring'), it fills the table with 0000-00-00 00:00:00. What the heck am I doing wrong??

-thanks in advance!

Aaron
  • 23
  • 7
  • you need to quote all non-numeric values like `'$value4'` – Paul Spiegel Nov 06 '16 at 00:55
  • 2
    use PDO or mysqli's prepared statements, the problem will resolve itself. – Xorifelse Nov 06 '16 at 01:05
  • THANKYOU!!! It works – Aaron Nov 06 '16 at 01:06
  • @PaulSpiegel You need to **escape** all values. Adding quotes only creates problems. Prepared statements with placeholder values is the answer here. – tadman Nov 06 '16 at 01:55
  • 1
    **WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because arbitrary user data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Nov 06 '16 at 01:56
  • @tadman My goal was to explain the error message. But i agree that *prepared statements* would be the best solution. – Paul Spiegel Nov 06 '16 at 13:58

0 Answers0