Fausto Filho Fausto Filho -4 years ago 107
MySQL Question

keep getting a syntax error (php / mysql)

php/mysql

I keep getting 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 '1' at line 1".

I'm trying hard to make this query to happen. It works, it inserts into the mysql database but this error appears every time. I've tried to use everything in the same line, changed double quotes to single quotes, removed all the whitespaces inserting everything in the samen line, changing the way I pass the variables({$variable} to '.$variable.') and everything else. I've seen a couple of stackoverflow questions related to this but with different solutions.
I know that we can't pass '' in a numeric fields.

I think I'm out of options now. Need help!
This error keeps showing but the data is correctly inserted in my table

here is the code:

$user_id = get_current_user_id();
$prescription_name = $_POST['prescription_name'];
$date_created = date('Y-m-d');
$last_updated = date('Y-m-d');
$right_eye_sphere = $_POST['right_eye_sphere'];
$left_eye_sphere = $_POST['left_eye_sphere'];
$right_eye_cylinder = $_POST['right_eye_cylinder'];
$left_eye_cylinder = $_POST['left_eye_cylinder'];
$right_eye_axis = $_POST['right_eye_axis'];
$left_eye_axis = $_POST['left_eye_axis'];
$pd = $_POST['pd'];
$date_of_birth = $_POST['date_of_birth'];
$file_path = $_POST['file_path'];

$add_query = "INSERT INTO wew_prescription (
prescription_id,
user_id,
prescription_name,
date_created,
last_updated,
right_eye_sphere,
left_eye_sphere,
right_eye_cylinder,
left_eye_cylinder,
right_eye_axis,
left_eye_axis,
pd,
date_of_birth,
file_path
) Values (
NULL,
{$user_id},
'{$prescription_name}',
'{$date_created}',
'{$last_updated}',
'{$right_eye_sphere}',
'{$left_eye_sphere}',
'{$right_eye_cylinder}',
'{$left_eye_cylinder}',
'{$right_eye_axis}',
'{$left_eye_axis}',
'{$pd}',
'{$date_of_birth}',
'{$file_path}'
)";

$sql = $dbCon->query($add_query);

if (!mysqli_query($dbCon,$sql)){
die('Error: ' . mysqli_error($dbCon));
}else{
mysqli_query($dbCon,$sql);
echo "dados atualizados!";
}

Answer Source

The error is coming from this line:

if (!mysqli_query($dbCon,$sql)){

$sql contains the result of

$dbCon->query($add_query);

Since that query was successful, $sql contains TRUE. mysqli_query() requires the second argument to be a string, so TRUE becomes "1", so you're effectively doing:

if (!mysqli_query($dbCon, "1")) {

That's not a valid query, so you get an error.

I think what you really meant to do was:

if (!$sql) {
    die('Error: ' . $dbCon->error);
} else {
    echo "dados atualizados!";
}

You don't need to keep calling mysqli_query() repeatedly.

You should also learn to code using prepared statements instead of substituting variables into the query, to prevent SQL injection.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download