MySQL error 1064 while using PHP variables in query

Question

<?php
    session_start();
    $con = mysqli_connect("localhost","root","12369","medical");
    $data1 = $_SESSION["symp1"];
    $data2 = $_SESSION["symp2"];
    $data3 = $_SESSION["symp3"];
    $data4 = $_SESSION["symp4"];
    $finalData = implode(' ', array($data1, $data2, $data3, $data4));
    $userinput = $_REQUEST["answer"];
    $dname=$_SESSION["dname"];
    $dname = str_replace(' ', '_', $dname);
    echo $dname."  <br>";
    $sql = " UPDATE diseases SET UserInput = $finalData WHERE Name =   $dname ";
    if($userinput=='yes'){  
        if(mysqli_query($con,$sql)){
            echo "Values inserted";
            $_SESSION["info"] = "yes";
            header('Location: http://localhost/medical/last.php');
    }else{
            echo mysqli_errno($con);
            $_SESSION["info"] = "no";
            //header('Location: http://localhost/medical/last.php');
    }
   }
?>

I'm getting error 1064? I already read answers to similar question, but my code doesn't work. My table schema is:

CREATE TABLE IF NOT EXISTS `diseases` (
  `ID` int(50) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Symptoms` varchar(255) NOT NULL,
  `Medicines` varchar(255) NOT NULL,
  `Description` varchar(255) NOT NULL,
  `Tags` varchar(255) NOT NULL,
  `UserInput` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
)

What's wrong in my code? Thanks

$sql = " UPDATE diseases SET UserInput = '$finalData' WHERE Name = '$dname' "; — Reagan Gallant
– Reagan Gallant, Commented Aug 20, 2015 at 9:12
Name is actually a reserved word and should not be used really. If you do use it, then you must always wrap that column name in backticks — RiggsFolly
– RiggsFolly, Commented Aug 20, 2015 at 9:13

SuperDJ · Accepted Answer · 2015-08-20 09:19:19Z

3

Change:

$sql = " UPDATE diseases SET UserInput = $finalData WHERE Name =   $dname ";

to:

$sql = "UPDATE `diseases` SET `UserInput` = '$finalData' WHERE `Name` = '$dname'";

Add single quotes around variables that contain a string. Add backticks around columns and table to prevent mysql reserved words error

It would be even better to use mysqli_prepare do the following:

$stmt = mysqli_prepare($con, "UPDATE `diseases` SET `UserInput` = ? WHERE `Name` = ?");
mysqli_stmt_bind_param($stmt, "ss", $finalData, $dname);
mysqli_stmt_execute($stmt);

edited Aug 20, 2015 at 9:19

answered Aug 20, 2015 at 9:13

SuperDJ

7,69911 gold badges45 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user1438038 · Accepted Answer · 2015-08-20 09:31:30Z

1

As the error message should state, you have an error in your SQL syntax:

MySQL Error 1064: You have an error in your SQL syntax

Surround your data by single quotes and you are good to go. Furthermore, Name is a reserved keyword in MySQL. You can still use it in your query, though, but you should consider escaping table names with backticks:

$sql = " UPDATE diseases SET `UserInput` = '$finalData' WHERE `Name` = '$dname' ";

edited Aug 20, 2015 at 9:31

answered Aug 20, 2015 at 9:15

user1438038

6,1287 gold badges64 silver badges105 bronze badges

Comments

Jens · Accepted Answer · 2015-08-20 09:12:53Z

0

Add single qoutes around your data:

 $sql = " UPDATE diseases SET UserInput = '$finalData' WHERE Name =   '$dname' ";

or better use prepared statements

answered Aug 20, 2015 at 9:12

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Collectives™ on Stack Overflow

MySQL error 1064 while using PHP variables in query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related