I could not update status row in my data using PDO procedure

Question

I'm having troubling updating status row in my mysql table using PDO procedure. The status row is defined as 0 in the table, so i want it to change to 1 whenever i hit the confirm button but it couldn't and i have been trying to fix it all night but i could not.

Here is my db connection

<?php
class Database
{   
    private $host = "localhost";
    private $db_name = "jerrydb";
    private $username = "root";
    private $password = "";
    public $conn;





    public function dbConnection()
    {

        $this->conn = null;    
        try
        {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   
        }
        catch(PDOException $exception)
        {
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }
}

?>

here is my script, i want the confirm to disappear whenever i clicked on it and at the same time change status row to 1.

<?php


if (isset($_GET['entity']) && isset ($_GET['action']) && isset($_GET['user_id'])){

  $entity = mysqli_real_escape_string($db, $_GET['entity']);
   $action = mysqli_real_escape_string($db, $_GET['action']);
    $user_id = mysqli_real_escape_string($db, $_GET['user_id']);



$query = "UPDATE jerrydb set status = '1' WHERE user_id='$user_id'";

    $db->query($query);
}




$query = "SELECT * FROM jerrydb WHERE status='0' ORDER BY user_id DESC";

$jerrydb = $db->query($query);

?>

 <?php if($row = $jerrydb->fetch_assoc())  { ?>


                  <tr> 
                  <td><a href="index.php?entity=go&action=approve&id=<?php echo $row['id'];?>" class="btn btn-success">Confirm</a> </td>



                </tr>

                  <?php } ?>

I have viewed all the answers of sql injection and i could not figure it out, i will be glad if my CODES would be modified. I'm new in web development. Thank you — Joe, Feb 17 '17 at 12:59
[YCS](http://stackoverflow.com/users/285587/your-common-sense) closed yet another question with the wrong link. This has nothing to do with sql injection but mixing different mysql apis and not using the right connection variable. When will this guy ever learn? I reopened the question. — Funk Forty Niner, Feb 17 '17 at 13:07
if using pdo, you need to go with bindParam and execute step. directly running query is not recommended. And can you post the result of var_dump($query) of your update so you can know what query is generated. — Alaksandar Jesus Gene, Feb 17 '17 at 13:12
Thank you sir, I have figured it out with this tips "different mysql apis and not using the right connection variable" Thank you very much. I appreciate — Joe, Feb 18 '17 at 13:12

score 0 · Answer 1 · answered Feb 17 '17 at 13:25

You are actually using PDO, therefore there's no need for you to use these mysqli_real_escape_string When you using PDO its best to prepare bind, execute statement.

<?php
if (isset($_GET['entity']) && isset($_GET['action']) && isset($_GET['user_id'])) {
    $entity  = $_GET['entity'];
    $action  = $_GET['action'];
    $user_id = $_GET['user_id'];
    $query   = $conn->prepare("UPDATE jerrydb SET status = '1' where user_id = ? ");
    if ($query->execute([$user_id])) {
        echo "status updated";
    } else {
        echo "not";
    }
}
$query = $conn->prepare("SELECT * FROM jerrydb WHERE status='0' ORDER BY user_id DESC");
$query->execute();
$jerrydb = $query->fetchall(PDO::FETCH_ASSOC);
if (count($jerrydb) > 0) { // we have results
    foreach ($jerrydb as $key => $row) {
    }
?>

             <tr> 
                  <td><a href="index.php?entity=go&action=approve&id=<?php
    echo $row['id'];
?>" class="btn btn-success">Confirm</a> </td>



                </tr>

<?php
}
?>

To learn more about PDO please visit this site, it explains well and make everything easy and simple https://phpdelusions.net/pdo

I could not update status row in my data using PDO procedure

1 Answers1