Converting from mysqli to PDO

[jacobpressures]

This is my first time converting code in php. I really have no idea what I'm doing and can't tell if i'm doing it right or not.

 

I would appreciate it very much if you could help me. Thanks!

 

From process_new_user.php

 

mysqli

if ($insert_stmt = $mysqli-> prepare("INSERT INTO members (username, email, password, salt) VALUES (?, ?, ?, ?)")) {
       $insert_stmt-> bind_param('ssss', $username, $email, $password, $random_salt);
       $insert_stmt-> execute(); // Execute the prepared query.
       echo "<script>";
       echo "alert(\"New User Successfully Added!\");";
       echo "window.location = \"users.php#current_user\";";
       echo "</script>";
   } else {
       echo "alert(\"Uh Oh! Something went terribly wrong.\");";
       echo "</script>";
   }   	

pdo (attempt)

if ($params = array(':username' => $username, ':email' => $email, ':password' => $password, ':random_salt' => $random_salt);
$insert_stmt->prepare("INSERT INTO members (username, email, password, salt) VALUES (:username, :email, :password, :random_salt)")) {

       $insert_stmt-> execute($params); // Execute the prepared query.
       echo "<script>";
       echo "alert(\"New User Successfully Added!\");";
       echo "window.location = \"users.php#current_user\";";
       echo "</script>";
   } else {
       echo "alert(\"Uh Oh! Something went terribly wrong.\");";
       echo "</script>";
   }   	

from process_delete_user.php (i converted as much of this as i could. But the last part I have no idea.)

   

original mysqli

foreach($id as $check) 
       {
   
      $conn = new mysqli(host, user, password, database);
	  if($conn == false){
        echo "connection has failed";    
       }
      $sql=("DELETE FROM $table WHERE id ='$check'");
	  $res=mysqli_query($conn, $sql);
	  if($res == true){            
        echo "<script>";
        echo "alert(\"User Successfully Deleted!\");";
        echo "window.location = \"users.php#delete_user\";";
	    echo "</script>";
      } else {
        echo "<script>";
        echo "DELETE failed".mysqli_error($conn);
       // echo "window.location = \"users.php#delete_user\";";
	    echo "</script>";
      }
	  mysqli_close ($conn);
    } 	

   

PDO (attempt)

foreach($id as $check) 
       {
   
      $conn = new PDO(host, database, user, password);
 if($conn == false){
        echo "connection has failed";    
       }
      $sql->prepare("DELETE FROM :table WHERE id = :check");
 $sql->exectute(array(':username' => $table, ':check' => $check);
 


$res=mysqli_query($conn, $sql);
 if($res == true){            
        echo "<script>";
        echo "alert(\"User Successfully Deleted!\");";
        echo "window.location = \"users.php#delete_user\";";
   echo "</script>";
      } else {
        echo "<script>";
        echo "DELETE failed".mysqli_error($conn);
       // echo "window.location = \"users.php#delete_user\";";
   echo "</script>";
      }
 mysqli_close ($conn);
    }  

 

[mac_gyver]

1) you should bind each input variable/value to its placeholder. this allows you to specify the correct data type for type checking. all data put into the pdo ->execute() statement is treated as a string.

 

2) you should not open and close a database connection inside of a loop (never do this) and you should not run a query inside of a loop. you also cannot use placeholders for table (and column) names. only literal data (numbers, strings) can use placeholders in a prepared query.

 

running a prepared query (mysqli or pdo) in a loop takes almost the same amount of time as running a non-prepared query in a loop (the time to prepare most queries is small compared to the time to run the query.) so with a prepared query or not, it is usually best to make one query to operate on all the data at once.

 

to make one delete query operate on all the id's, the where term needs to be WHERE id in(?,?,?,...). there must be a placeholder for each id value. you can make the list of place holders to put into the query by counting the number of id's and you would run a pdo stmt bindValue statement inside of a loop to bind each id value to its placeholder after you prepare the query.