How to insert values using PHP MySQl UPDATE clause?

Question

Below is the HTML form which takes input subject & beginner(yes,no) from user and inserts it into database

<form action="coursesignup.php" method="post">
    Select Subject:<br>
     <select name="subject">
        <option value="select" selected>select</option>
        <option value="Database Management Systems">Database Management Systems</option>
        <option value="Advanced Microprocessors">Advanced Microprocessors</option>    </select><br><br>
        Are you a Beginner:<br>
        <select name="beginner">
        <option value="select" selected>select</option>
        <option value="yes">yes</option>
        <option value="no">No</option></select><br><br>

        <input type="submit" value="Submit" /></form>

Form submits the data into following php file. However, it is not inserting the values at the respective userid but putting them at the end of the table

<?php

session_start();

if($_SESSION["username"])
$_POST["username"]=$_SESSION["username"];
$user=$_POST["username"];

// Check connection
$con=mysqli_connect("localhost","root","","elearning");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security

$subject = mysqli_real_escape_string($con, $_POST['subject']);
$beginner= mysqli_real_escape_string($con, $_POST['beginner']);

$sql=" UPDATE student_info SET subject=$subject AND beginner=$beginner 
       WHERE username=$user " ;



if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}
echo "Successfuly entered";


mysqli_close($con);
?>

It gives following error:

Error: Erreur de syntaxe près de 'Management Systems AND ;beginner=yes WHERE username=gk4316' à la ligne 1

As the error message says, it seems that you have an extra (unnecessary) Semicolon in the query after the AND in : Management Systems AND **;** beginner=yes WHERE username=gk4316 , you also need to wrap the username value in quotes — Ormoz, Oct 12 '14 at 16:53
@Ormoz That is what SQL produced as the error, it's not the query itself. The OP's SQL is in French and I can understand that language. — Funk Forty Niner, Oct 12 '14 at 17:01

score 0 · Answer 1 · edited May 23 '17 at 11:52

There are a few things wrong with your query.

You need to use quotes around your variables, since you are passing those as strings.

Another problem is, you need to remove the AND and use a comma instead.

$sql=" UPDATE student_info SET subject='$subject', beginner='$beginner' 
       WHERE username='$user' " ;

That is why you're getting the error message.

For more information on SET visit: http://www.tutorialspoint.com/mysql/mysql-update-query.htm
and http://dev.mysql.com/doc/refman/5.1/en/set-statement.html

AND is where you wish to use multiple checks in a WHERE clause.

For example:

`WHERE column_1 = 'something' AND column_2 = 'something else'

Sidenote:

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

Footnotes:

Something else I spotted is if($_SESSION["username"]). You're better off using isset() if(isset($_SESSION["username"])) and using proper bracing:

if(isset($_SESSION["username"])){
  $_POST["username"]=$_SESSION["username"];
  $user=$_POST["username"];
}

It's just good coding practice.

A quick tutorial:

Here is an example of an UPDATE using mysqli with prepared statements:

Sidenote: i stands for integer and s stands for string.

$con = new mysqli('host','user','password','database');
$stmt = $con->prepare("UPDATE `table` SET `title`=?, `description`=?, `price`=?, `location`=?, `email`=?, `date`=? WHERE `id`=?");
$stmt->bind_param("ssisssi", $title_string, $description_string, $price_int, $location_string, $email_string, $date_string, $id_int);
stmt->execute();

score 0 · Answer 2 · answered Oct 13 '15 at 11:07

0

Remove the AND and replace it with a comma in your sql update statement.

$sql=" UPDATE student_info SET subject='$subject', beginner='$beginner' WHERE username='$user' " ;

answered Oct 13 '15 at 11:07

Kevin G

119
1
8

How to insert values using PHP MySQl UPDATE clause?

2 Answers2