Error updating data in mysql using php

Question

I want to update data in database using php the error which is diplayed in logcat is

Error: UPDATE usersSET Question1=null2null,Question3=nullnull,Question4=nullnullnullnullWHERE email=bb<br>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 '=null2null,Question3=nullnull,Question4=nullnullnullnullWHERE email=bb' at line 1{"error":false,"uid":"56bd5f88afb7b3.99372648","user":{"name":"Bb","email":"bb","created_at":"2016-02-12 09:58:56","updated_at":null,"Question1":"","Question3":"","Question4":""}

the value for Question1, Question3 and Question4 column is not getting updated , it should be null2null,nullnull,nullnullnullnull respectively. it is written check the syntax for your version , i am running 5.5.12 php version and 5.6.17 mysql version on localhost . i have checked the syntax and different sites show different show different syntasx how to get the right one , below is my php code

PHP

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "android_api";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
require_once 'include/DB_Functions.php';
$db = new DB_Functions();

// json response array
$response = array("error" => FALSE);

if (isset($_POST['Question1']) && isset($_POST['Question3']) && isset($_POST['Question4'])) {

    // receiving the post params
    $email = $_POST['email'];
    $password = $_POST['password'];

     $Question1 = $_POST['Question1'];
    $Question3 = $_POST['Question3'];
    $Question4 = $_POST['Question4'];
 /*$sql = "INSERT INTO users (Question1, Question2, Question4)
    VALUES ('$Question1', '$Question3', '$Question4')"; */
    $user = $db->getUserByEmailAndPassword($email, $password);
   // $result = mysql_query("UPDATE users"."SET Question1='$Question1',Question3='$Question3',Question4='$Question4'"."WHERE email=$email";
    $sql="UPDATE users"."SET Question1=$Question1,Question3=$Question3,Question4=$Question4"."WHERE email=$email";
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    // get the user by email and password
    $user = $db->getUserByEmailAndPassword($email, $password);

    if ($user != false) {
        // use is found
        $response["error"] = FALSE;
        $response["uid"] = $user["unique_id"];
        $response["user"]["name"] = $user["name"];
        $response["user"]["email"] = $user["email"];
        $response["user"]["created_at"] = $user["created_at"];
        $response["user"]["updated_at"] = $user["updated_at"];
         $response["user"]["Question1"] = $user["Question1"];
          $response["user"]["Question3"] = $user["Question3"];
           $response["user"]["Question4"] = $user["Question4"];
        echo json_encode($response);
    } else {
        // user is not found with the credentials
        $response["error"] = TRUE;
        $response["error_msg"] = "ABCD";
        echo json_encode($response);
    }
} else {
    // required post params is missing
    $response["error"] = TRUE;
    $response["error_msg"] = "abcd";
    echo json_encode($response);
}
?>

EDIT

after reading answers and changing the syntax now the error is changed to

 Error: UPDATE users SET Question1=null2null,Question3=nullnull,Question4=nullnullnullnull WHERE email=bb<br>Unknown column 'bb' in 'where clause'{"error":false,"uid":"56bd5f88afb7b3.99372648","user":{"name":"Bb","email":"bb","created_at":"2016-02-12 09:58:56","updated_at":null,"Question1":"","Question3":"","Question4":""}}

No space in $sql between users and SET

Ali Zia
– Ali Zia

2016-02-12 08:00:36 +00:00
Commented Feb 12, 2016 at 8:00 — Ali Zia
– Ali Zia, Commented Feb 12, 2016 at 8:00

Rohit5k2 · Accepted Answer · 2016-02-12 08:08:33Z

1

Missed few white spaces and qutoes(') here and there. Change your SQL statement to this

 $sql ="UPDATE users" . " SET Question1 = '".$Question1."', Question3 = '".$Question3."', Question4= '".$Question4."' WHERE email = '".$email."'";

Also, values in these variables don't seem right like, see why you are not getting right values (or is it what you are passing?)

Question1 = null2null
Question3 = nullnull
Question4 = nullnullnullnull
email = bb

edited Feb 12, 2016 at 8:08

answered Feb 12, 2016 at 8:00

Rohit5k2

18.1k8 gold badges48 silver badges58 bronze badges

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

7 Comments

JSONParser Over a year ago

thanks, but now the error is changed to Error: UPDATE users SET Question1=null2null,Question3=nullnull,Question4=nullnullnullnull WHERE email=bb<br>Unknown column 'bb' in 'where clause'{"error":false,"uid":"56bd5f88afb7b3.99372648","user":{"name":"Bb","email":"bb","created_at":"2016-02-12 09:58:56","updated_at":null,"Question1":"","Question3":"","Question4":""}}

JSONParser Over a year ago

and i am passing empty strings that's why it is reading null there

Rohit5k2 Over a year ago

Quote the values for each column.

Rohit5k2 Over a year ago

Use this

$sql  ="UPDATE users" . " SET Question1 = '".$Question1."', Question3 = '".$Question3."', Question4= '".$Question4."' WHERE email = '".$email."'";

JSONParser Over a year ago

thanks but is these white spaces much significant and is there any editor which take care of these by itself?

|

Vijay Sai Chaudary · Accepted Answer · 2016-02-12 08:03:22Z

1

Here you missed 3 things:
1. Space after usersSET
2. Quotes for value assignment (Question1=null2null)
3. Space before WHERE keyword (Question4=nullnullnullnullWHERE)
The correct version of your query is below:

 UPDATE users SET Question1='null2null', Question3='nullnull' ,
    Question4='nullnullnullnull' WHERE email='b'

So replace below line in your script:

$sql    ="UPDATE users"."SET Question1=$Question1,Question3=$Question3,Question4=$Question4"."WHERE email=$email";

with

$sql    =" UPDATE users SET Question1 = '".$Question1."', Question3 = '".$Question3."', Question4= '".$Question4."' WHERE email = '".$email."'";

answered Feb 12, 2016 at 8:03

Vijay Sai Chaudary

1773 bronze badges

Comments

Lalit Parkash · Accepted Answer · 2016-02-12 08:12:26Z

Hi you need to properly save the detail the issue comes due to data is containing null as string.

you need to prepare query as below:

Question1 = null2null

You have to quoted all parameter before saving into database. like this

Question1 = 'null2null'

Here is below sample updated code for your reference, make a habit to clean your query while creating with proper quoted strings for better readability.

$email = $_POST['email'];
$password = $_POST['password'];

$Question1 = $_POST['Question1'];
$Question3 = $_POST['Question3'];
$Question4 = $_POST['Question4'];

$user = $db->getUserByEmailAndPassword($email, $password);

$sql="UPDATE users SET Question1 = '$Question1', Question3 = '$Question3', Question4 = '$Question4' WHERE email = '$email'";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

Collectives™ on Stack Overflow

Error updating data in mysql using php

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related