using variable inside mysql update query in php

Question

<?php
    require 'functions/connection.php';
    $conn = Connect();
    $e_id = $conn->real_escape_string($_POST['e_id']);
    $first_name = $conn->real_escape_string($_POST['first_name']);
    $last_name = $conn->real_escape_string($_POST['last_name']);
    $e_salary = $conn->real_escape_string($_POST['e_salary']);
    $e_startdate = $conn->real_escape_string($_POST['e_startdate']);
    $e_department = $conn->real_escape_string($_POST['e_department']);          
    $sql = "UPDATE employee SET firstname='$first_name' WHERE id=$e_id";
    if (mysqli_query($conn, $sql)) {
        echo "Record updated successfully";
    } else {
        echo "Error updating record: " . mysqli_error($conn);
    }
    mysqli_close($conn);
?>

I'm trying to use the first_name variable inside the update query.

I tried echo the variable and its working... this is my connection code that im using.

<?php


function Connect()
{
 $dbhost = "localhost";
 $dbuser = "root";
 $dbpass = "";
 $dbname = "company";

 // Create connection
 $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname) or die($conn->connect_error);

 return $conn;
}

?>

if i i replace the variable with anything between "" the database is getting updated

are you getting any error? Also you are mixing object-oriented and procedural way. show your connection.php file — B. Desai
– B. Desai, Commented Oct 5, 2017 at 9:11
Best practice is to use prepared statements. PDO is good for this, but if you are set on using mysqli, see: php.net/manual/en/mysqli.prepare.php — Chris J
– Chris J, Commented Oct 5, 2017 at 9:18
@B.Desai im getting the following error: Error updating record: Unknown column 'rida' in 'field list' — Rida Helbawi
– Rida Helbawi, Commented Oct 5, 2017 at 10:25

Chris J · Accepted Answer · 2017-10-05 09:33:18Z

2

I'd suggest making it more secure and using prepared statements. This is an example using mysqli, but I prefer PDO:

  <?php
        require 'functions/connection.php';
        $conn = Connect();

        // Prepare the query
        $myQuery = $conn->prepare("UPDATE employee SET firstname=? WHERE id=?");

        $e_id = $conn->real_escape_string($_POST['e_id']);
        $first_name = $conn->real_escape_string($_POST['first_name']);
        $last_name = $conn->real_escape_string($_POST['last_name']);
        $e_salary = $conn->real_escape_string($_POST['e_salary']);
        $e_startdate = $conn->real_escape_string($_POST['e_startdate']);
        $e_department = $conn->real_escape_string($_POST['e_department']);          

        // Bind your variables to the placemarkers (string, integer)
        $myQuery->bind_param('si', $first_name, $e_id);

        if ($myQuery->execute() == false) {
        echo 'Error updating record: ' . $mysqli->error;
        }
        else {  
        echo 'Record updated successfully';
        }
        $myQuery->close();

    ?>

Note: The 'cleansing' you're doing in the middle I have left, but it's not really necessary with prepared statements.

answered Oct 5, 2017 at 9:33

Chris J

1,44711 silver badges22 bronze badges

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

2 Comments

Toby Speight Over a year ago

Actually, you want to remove the 'cleansing', if you want to match existing records for people such as O'hara and the like...

Chris J Over a year ago

True, in this instance it looks like an integer ID lookup though. I wanted to leave it there for a talking point, as opposed to doing it for the OP and the issue going under the radar, but you're absolutely correct.

SaltyPotato · Accepted Answer · 2017-10-05 09:44:27Z

0

functions/connection.php (Now an object):

<?php
class Connect
{
 private $dbhost = "localhost";
 private $dbuser = "root";
 private $dbpass = "";
 private $dbname = "company";

 public $conn;

 public function __construct()
 {
    if($this->conn = new mysqli($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname))
    {
      //connection established
      //do whatever you want here
    }
    else
    {
      //Error occurred
      die($this->conn->error);
    }
 }

 //other functions here

}

?>

Change mysqli_query to: $conn->conn->query($sql);

Prepared statement: Avoid SQLI injection

if($stmt = $conn->conn->prepare("UPDATE employee SET firstname = ? WHERE id = ?"))
{
    $stmt->bind_param('si', $first_name, $e_id);
    $stmt->execute();
    echo $stmt->affected_rows;
}

Final code:

<?php
    require 'functions/connection.php';
    $conn = new Connect();
    $e_id = $conn->conn->real_escape_string($_POST['e_id']);
    $first_name = $conn->conn->real_escape_string($_POST['first_name']);
    $last_name = $conn->conn->real_escape_string($_POST['last_name']);
    $e_salary = $conn->conn->real_escape_string($_POST['e_salary']);
    $e_startdate = $conn->conn->real_escape_string($_POST['e_startdate']);
    $e_department = $conn->conn->real_escape_string($_POST['e_department']);          

    if($stmt = $conn->conn->prepare("UPDATE employee SET firstname = ? WHERE id = ?"))
    {
        $stmt->bind_param('si', $first_name, $e_id);
        $stmt->execute();
        echo $stmt->affected_rows;
    }
    $conn->conn->close();
?>

edited Oct 5, 2017 at 9:44

answered Oct 5, 2017 at 9:14

SaltyPotato

3223 silver badges16 bronze badges

2 Comments

Chris J Over a year ago

This isn't recommended - you should be using prepared statements for this kind of thing

SaltyPotato Over a year ago

Updated answer @ChrisJ

Collectives™ on Stack Overflow

using variable inside mysql update query in php

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related