Proper insert of date in MySql date field

Question

I am posting data for event in this format via ajax:

doctor=100&date=2015-04-30&rCheck=1&rDate=2015-05-12&fromTime=21%3A35&toTime=22%3A40&status=open

I am getting it like this with php

$date = $_POST['date'];
$fromHours = $_POST['fromHours'];
$fromMinutes = $_POST['fromMinutes'];
$toHours = $_POST['toHours'];
$toMinutes = $_POST['toMinutes'];
$fromTime = $_POST['fromTime'];
$toTime = $_POST['toTime'];
$start = $date.' '.$fromTime.':00';
$end = $date.' '.$toTime.':00';
$status = $_POST['status'];
$doctor = $_POST['doctor'];

if($_POST['rCheck']==1){
    $repeat = $_POST['rDate'];
}else{
    $repeat = '0000-00-00';
}

When I echo any of that variables I get correct result.

I am inserting data in database like this:

$query = 'INSERT INTO events (start,end,doctor,status,repeat) VALUES ("'.$start.'","'.$end.'","'.$doctor.'","'.$status.'","'.$repeat.'")';
$result = mysqli_query($db, $query) or die (mysqli_error($db));

Now main problem is $repeat because without it everything is inserted without problem but with $repeat I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'repeat) VALUES ("2015-04-30 21:35:00","2015-04-30 22:40:00","100","offen","2015-' at line 1

Insert stops after fifth '-' character. Even if $repeat is 0000-00-00
Field for that in database is date field in format 0000-00-00
I really don't know where problem is.
Thank you for helping.

Have you tried to output the contents of your query to make sure your expected interpolated variables are kicking out the correct value? Output $query to see the actual (full) statement being sent to the DB instead of what you are being shown in the error. — Crackertastic
– Crackertastic, Commented Apr 29, 2015 at 20:52

Community · Accepted Answer · 2017-05-23 11:58:27Z

repeat is a MySQL reserved word

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Either rename it to something else; for example "repeats", or use ticks around it

(start,end,doctor,status,`repeat`)

Look at what MySQL is telling you; it's pointing it out where it starts:

...for the right syntax to use near 'repeat  
                                    ^

Sidenote:

If you're still having problems inserting data into your table, you can sanitize your input(s) using mysqli_real_escape_string() or using prepared statements as stated below.

I need to point out though, that your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements, they're much safer.

Collectives™ on Stack Overflow

Proper insert of date in MySql date field

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related