CODEWITHSUNDEEP
phpmysql
Jakob
Jakob

Reputation: 3546

Proper insert of date in MySql date field

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.

Upvotes: 0

Views: 48

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

repeat is a MySQL reserved word

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.

Upvotes: 1

Related Questions