A few notes from my experience with PDO.
For the bind parameter, you can do this once and then run a loop and not have to re-bind it. When the execute runs, it will use what is stored in the variable at that time...
Code: Select all
$aryData = array(1=>"Greg", 3=>"Max", 8=>"Ricky");
$key = 0;
$value = '';
$pdoUpdate = $db->prepare('UPDATE tblWhatever SET myField=:fieldvalue WHERE keyField=:keyfield');
$pdoUpdate->bindParam(':fieldvalue',$key);
$pdoUpdate->bindParam(':keyfield',$val);
foreach($aryData as $key=>$val) {
$pdoUpdate->execute();
}
If you are doing several PDO oobjects, make sure after you are done with any $pdoObject->fetch(); section, do a $pdoObject->closeCursos(); This was a tough one to figure out when we switching to using PDO, we didn't know and left them all out there hanging so to speak, so pages would start giving us WSOD (white screen of death, what we called it cause you just got a empty browser window)
Trying to trace those down... a bt of a pain, cause it would say the fault would happen on say line 1032, which was a PDO execute. However the issue was that OTHER pdo objects were open and you had to back trace through the code to find previous ones left open. (On top of this, the way the Database handler was written, they mistakenly created a NEW connection for every build of PDO object. Brought a brand new production web server to a crawl once a high traffic site was taken live on it. Was a mess to learn the hard way for sure.
Another area (well 2) that threw us off:
SELECT * FROM tblWhatever WHERE Key IN (3,6,7) can't be done via PDO (unless you know HOW many options in advance, pretty darn messy. For these still ended up putting the values in the prepare:
Code: Select all
$aryKeys = array(3,6,7);
$pdoValues = $db->prepare('SELECT * FROM tblWhatever WHERE Key IN ('.implode(',',$aryKey).')');
Another area where we had to code the variables into the prepare was the use for
LIMIT 3,5 .... Cannot do:
Code: Select all
$pdoValues = $db->prepare('SELECT * FROM tblWhatever LIMIT :start, :len ')';
Both these examples the worry about injection were not much of issues as the they ended up being integers, so you could type cast the value as an int, and no injection issues two examples in the same code:
Code: Select all
$aryKeys = array(3,6,7);
$intStart = (isset($_GET['start'] && (int)$_GET['start'] > 0) ? (int)$_GET['start'] : 0;
$intLength = (isset($_GET['perpage'] && (int)$_GET['perpage'] >= 5) ? (int)$_GET['perpage'] : 10;
// NOTE, the above says per page needs to be at least 5, and if not set (or attempted injection) default to 10
$pdoValues = $db->prepare('SELECT * FROM tblWhatever WHERE Key IN ('.implode(',',(int)$aryKey).') LIMIT '.$intStart.','.$intLength.);
Just some tips.
-Greg