Jump to content
Sign in to follow this  
dennis-fedco

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

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 by rwhite35

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.