CODEWITHSUNDEEP
phpmysqlvariables
Rida Helbawi
Rida Helbawi

Reputation: 11

using variable inside mysql update query in php

<?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

Upvotes: 1

Views: 869

Answers (2)

SaltyPotato
SaltyPotato

Reputation: 322

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();
?>

Upvotes: 0

Chris J
Chris J

Reputation: 1447

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.

Upvotes: 2

Related Questions