How to create PDO correectly

Question

I have following existing code which is vulnerable for SQL injections.

<?php  
 //fetch.php  
 $connect = mysqli_connect("localhost", "root", "", "project");  
 if(isset($_POST["row_id"]))  
 {  
      $query = "SELECT id,username,usertype,division,mobnum,userstatus,date(created_at) as created_at FROM users WHERE id = '".$_POST["row_id"]."'";  

      $result = mysqli_query($connect, $query);  
      $row = mysqli_fetch_array($result);  
      echo json_encode($row);  
 }  
 ?>

SO I need to change it to PDO. Here what I tried. But this is not working. Can anyone help me?

    <?php  
     //fetch.php  
     $connect = mysqli_connect("localhost", "root", "", "project");  
     if(isset($_POST["row_id"]))  
     {  
          $query = "SELECT id,username,usertype,division,mobnum,userstatus,date(created_at) as created_at FROM users WHERE id =?";  
          $stmt = $connect->prepare($sql);  
          $stmt->bind_param('".$_POST["row_id"]."', $id);
          $stmt->execute();
          $result = $stmt->get_result(); 
          $row = mysqli_fetch_array($result);  
          echo json_encode($row);  
     }  
     ?>

Answer 1

You are probably confusing PDO with prepared statements. And one can use them with mysqli as well. You even almost nailed it, only messing up a bit with bind_param. Here is how it has to be done

<?php  
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connect = mysqli_connect("localhost", "root", "", "project");  
if(isset($_POST["row_id"]))  
{  
    $query = "SELECT id,username,usertype,division,mobnum,userstatus,date(created_at) as created_at FROM users WHERE id = ?";  
    $stmt = $connect->prepare($query);  
    $stmt->bind_param("s", $_POST["row_id"]);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = mysqli_fetch_array($result);  
    echo json_encode($row);  
}  

In case you still need PDO, the code is:

$connect = new PDO('mysql:host=localhost;dbname=project', 'root', '', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$query = "SELECT id,username,usertype,division,mobnum,userstatus,date(created_at) as created_at FROM users WHERE id = :id";  
$stmt = $connect->prepare($sql);
$stmt->bindParam(':id', $_POST['row_id']);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo json_encode($row);