How to transform a codebase to use modern DB-access practices?

[dennis-fedco]

My question is about moving my codebase to modern DB practices (with least work possible).

 

My codebase is currently using outdated MySQL extensions.  Like so:
 

<?
$sql = "
    SELECT description, quantity
    FROM sales.item
    WHERE id = {$orderid}
    ";

$result = db_query($sql);

for ($i = 0; $i < db_num_rows($result); $i++)
{
    $row = db_fetch_array($result);
    ?>
    <tr>
	<td><?=$row['description']?></td>
	<td><?=$row['quantity']?></td>
    </tr>
    <?
   }
?>

I have 1140 such statements in "SELECT" statements alone and not counting any DELETE/UPDATE or other SQL statements.  This means that updating and paying attention to each statement individually is going to be a huge effort.  That effort is what I want to avoid, or automate as much as possible.

 

Question:  other than leaving the codebase as-is, are there any reasonable ways to move it or nudge it to the current latest PHP DB practices?  (Be it, PDO, or mysqli, or Doctrine, or otherwise), without expending herculean amount of effort?

[rwhite35]

Here is an anonymous db query class I use for SELECT and DELETE actions.  Its algorithmic, it will take the query, optimize it, bind the tokens and return a result.  Note the protected property, this class was written to be called from within another class.  Its method querydb is only available to the class from which it was called.  To make dbmanager::querydb available to scripts outside another class, you'll want to change protected fucntion querydb to public.  Whether called from a class or from the script, you would query the database with something like:

include( "dbmanager.class.php" ); // path to dbmanager class
$query = "SELECT * FROM sales.item WHERE id=?";
$bind = $orderid;
$qryObj = new dbmanager();
$result = $qryObj->querydb( $query, $bind );
print_r($result); 

$result will be the query result, either an array or boolean(true/false).  Using this pattern, you can probably use most of your SELECT and DELETE statements without a lot of refactoring.  Just instance a new class each time you want a data pull.  Just instantiate another $results variable each time you want another query:

$qryObj = new dbmanager();
$result1 = $qryObj->querydb( $query1, $bind1 );
$result2 = $qryObj->querydb( $query2, $bind2 );

Here is the class:

class dbmanager {
	protected $query;
	protected $result;
	protected $bind;
	/* 
        * connection method. return object $DB, db connection
        * requires PDO driver 
        */
	protected function conn() {
	  require '../DB_CONF.inc';  // text file with db credential constants
	  
	  try {
	    $DB = new PDO(DB_HOST, DB_UNAME, DB_UPWORD);
	    $DB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	  } catch( Exception $e ) {
	    $mes = "Error:dbmanager Caught Exception ---"."\n";
	    $mes .= $e->getMessage()."\n";
	    error_log($mes);  
	  }
	  return $DB;
   }
   /*
   * protected querydb method
   * param string $query, query statement passed from calling class
   * param mixed $bind, indexed array or scalar variable contains WHERE tokens(?), 
   * !!important, the field order and placeholders must match up!!
   * return mixed $result, array or boolean  
   */
   protected function querydb($query,$bind) {
	$DB = (!isset($DB)) ? $this->conn() : $DB;
	try {
	    $stmt = $DB->prepare($query);
	    if ($bind!=null) {
               $cnt = count($bind);
	       if ($cnt>1) { // mulitple tokens
		 $t=1; // binding starts with 1
		 for($i=0;$i<$cnt;$i++) {
		     $stmt->bindParam($t,$bind[$i]);
	             $t++;
		 }
	       } else { //single binder
		     $stmt->bindParam(1,$bind);
	       }
	     }
		
	     if($stmt->execute()) { // run the query
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	        $this->result[] = $row;
	     }
		return $this->result;
	     } else { 
		throw new Exception("Error on dbmanage::query execution."); 
	     }
	} catch ( Exception $e ) {
	     error_log("Error on query method: ".$e->getMessage());
	}
	unset($stmt);
	unset($this->result);
   }
} 

Edited June 18, 2015 by rwhite35

[Muddy_Funster]

Yes and No.

 

I assume you have a modular connection design in so much as all 1000+ queries are run through the one same piece of transaction code.  If that is the case, then you can alter that transaction code to launch a PDO->query() which will accept the same format of sql strings that you are using.  This is circumventing half of what makes the modern transaction layers better than the original mysql connector, but the same sanitisation that you have been using should be just as effective as it always was.  This means that you will continue to function once mysql is removed from PHP and buy you time to build up those muscles and take on Hercules.

 

You could always look into writing your own automation script for updating query strings and transaction layers. swapping in placeholders based on a preg_replace on $'s in the query string and building statements based around these $ sub strings and their data types.