SQL syntax error or server error?

Question

Hey guys I am new to the whole database scene and trying to perform a relatively simple task but apparently I am doing something wrong. Every time I try to execute this statement I get a 1064 error telling me either my syntax is wrong or the server version is too old. the SQL server version is 5.1.x and I am running PHP5.

Here is my code:

$query = "INSERT INTO `cut_log` (`driver`, `date1`, `time`, `cut`, `flood`, `notes`) VALUES ($driver, $date, $time, $cut, $flood, $notes)";
$result = $mysqli->query($query);
if($result) {
echo "success";
} else {
echo "" . $mysqli->errno . $mysqli->error;
}

You need to quote `($driver, $date, $time, $cut, $flood, $notes)` do `('$driver', '$date', '$time', '$cut', '$flood', '$notes')` — Funk Forty Niner, Aug 02 '14 at 16:12
You should echo out the query that gives the error. And you better use prepared statements with parameters to bind the input values to, to avoid sql injection and avoid escaping problems. — VMai, Aug 02 '14 at 16:14
If you're going to forget quotes around strings in SQL statements, do yourself a favour and learn to use bind variables — Mark Baker, Aug 02 '14 at 16:14

score 6 · Accepted Answer · edited Aug 02 '14 at 16:20

6

You're missing quotes around your string values:

$query = "INSERT INTO `cut_log` (`driver`, `date1`, `time`, `cut`, `flood`, `notes`) VALUES ('$driver', '$date', '$time', '$cut', '$flood', '$notes')";

edited Aug 02 '14 at 16:20

Funk Forty Niner

74,450
15
68
141

answered Aug 02 '14 at 16:13

John Conde

217,595
99
455
496

Thanks guys! I didn't realize that you needed quotes for the values. However on all the other documentation and examples I have seen, they have also been binding the variables. Thanks for the explanation. – sanch Aug 02 '14 at 17:27

score 2 · Answer 2 · edited May 23 '17 at 12:20

2

Like John said, the problem is that it's missing quotes.
What you should have done is prepare the query to avoid SQL injection attacks:

$query = "INSERT INTO `cut_log` (`driver`, `date1`, `time`, `cut`, `flood`, `notes`) 
          VALUES (?, ?, ?, ?, ?, ?)";
if ($stmt = $mysqli->prepare($query)) {

    $stmt->bind_param("ssssss", $driver, $date, $time, $cut, $flood, $notes);

    if($stmt->execute()) {
      echo "success";
    } else {
      echo "" . $mysqli->errno . $mysqli->error;
    }

}

edited May 23 '17 at 12:20

Community

1
1

answered Aug 02 '14 at 16:16

meda

45,103
14
92
122

1

Thanks for the edit @Fred You always great with the details ! :D – meda Aug 02 '14 at 16:28
Thanks guys! I tried running that as-is and although the query executed, it threw an error on run...there aren't any other variables that would account for the error. thoughts? Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement in /Users/Matt/path/to/zam/cut.php on line 21 – sanch Aug 02 '14 at 17:24
@MattSanford I counted 6 fields, if any of them is an int type then change the `s` to `i` – meda Aug 02 '14 at 17:28
@meda Four of the data types are VARCHAR, one is TIME the other is DATE. In order it would be VC-D-T-VC-VC-VC. I changed the bind params to siisss but still got the same error. – sanch Aug 02 '14 at 17:34
@MattSanford no so `ssssss` is correct because dates are strings, is it inserting everything correctly? – meda Aug 02 '14 at 17:41
I cant see the error did you copy paste my code, there should be no quotes around the question mark – meda Aug 02 '14 at 18:05

SQL syntax error or server error?

2 Answers2