PDO Conversion Question II

PHP
1

I got some great tips for converting a database query to PDO here. Unfortunately, my query isn’t working, and I’m getting more confused. I think the problem is I don’t understand the whole process; for example, I’m still trying to figure out if I need to use a while loop, foreach or both.

Anyway, I want to give it another try, but this time let me first explain my goal. First, I have a working database connection and page display script…


$dsn = "mysql:host=localhost;dbname=DATABASE;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'USERNAME','PASSWORD, $opt);

$sql= "SELECT COUNT(URL) AS num FROM people
WHERE URL = :url";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':url',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

Now that my page displays, I want to pull additional data from several joined tables. I would also like to be able to echo these values outside the while loop or foreach. I’m working with a series of includes, and if I have to put the closing bracket of a while loop on some downstream file, it just gets too confusing.

Most of the values are “static.” For example, echo $Title should display the page title and echo $BirthDate should display a person’s birthday.

However, there’s also some data that needs to be in arrays. For example, the table people_1_class includes rows like this:

URL | CLASS
Carl_Sagan | scientist
Carl_Sagan | writer
Carl_Sagan | political activist

There may be situations where I’ll want to echo $Class to get something like this…

scientist | writer | political activist

This is my original query, which I need to convert to PDO:


$SeaURL = str_replace('Washington/', '', $MyURL);

$res = mysql_query ("SELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, Brf.Site, Brf.Brief, PC.Class,
 PX.Article PXA, PX.Pagedex PXP, IVR.Article IVRA, IVR.Pagedex IVRP, SM.Article SMA, SM.Pagedex SMP
 FROM people P
 LEFT JOIN people_1_bio PB ON PB.URL = P.URL
 LEFT JOIN people_1_class PC ON PC.URL = P.URL
 LEFT JOIN people_briefs Brf ON Brf.URL = P.URL
 LEFT JOIN people_articles_px PX ON PX.URL = P.URL
 LEFT JOIN people_articles_ivr IVR ON IVR.URL = P.URL
 LEFT JOIN people_articles_sm SM ON SM.URL = P.URL
 WHERE P.URL LIKE '$MyURL' AND P.Site = '$MySiteID'
 GROUP BY Class") or die (mysql_error());

This is the PDO version I’m working with…


$SeaURL = str_replace('Washington/', '', $MyURL);

$stm = $pdo->prepare("SELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, Brf.Site, Brf.Brief, PC.Class,
 PX.Article PXA, PX.Pagedex PXP, IVR.Article IVRA, IVR.Pagedex IVRP, SM.Article SMA, SM.Pagedex SMP
 FROM people P
 LEFT JOIN people_1_bio PB ON PB.URL = P.URL
 LEFT JOIN people_1_class PC ON PC.URL = P.URL
 LEFT JOIN people_briefs Brf ON Brf.URL = P.URL
 LEFT JOIN people_articles_px PX ON PX.URL = P.URL
 LEFT JOIN people_articles_ivr IVR ON IVR.URL = P.URL
 LEFT JOIN people_articles_sm SM ON SM.URL = P.URL
 WHERE P.URL LIKE '$MyURL' AND P.Site = '$MySiteID'
 GROUP BY Class");
$stm->execute();
$data = $stm->fetchAll();

I’m not sure if everything is OK up to here or not. I don’t see any error messages, and there are no fatal errors, but I’m unable to display any data.

Here’s a condensed version of my original while loop…


while ($row = mysql_fetch_array ($res))
{
 $URL = $row['URL'];
 $Title = $row['Title'];
 $Subtitle = $row['Subtitle'];
 $Class = $row['Class'];
 $ClassList[] = $row['Class'];
 $ClassL = str_replace(' ', '_', $Class);
 $ClassLinked[] = '<a href="/People/'.$ClassL.'" title="'.$Class.'">'.$row['Class'].'</a>';
}

I can echo values outside the while loop, like echo $Title. I also recently learned how to echo arrays:


echo join( $Class, '' );
// or...
echo join( $Class, ' | ' );

So what should I use to accomplish the above - a while loop or foreach?

If I keep my while loop, how do I change it from this…


while ($row = mysql_fetch_array ($res))

…to match my PDO query. I think that’s all I need to do, unless I need to go back to the beginning and change FETCH_ASSOC to something else…


$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);

Thanks.

2

In your while loop, you just need to switch from the mysql fetch function to the PDO fetch function. In the other thread, we were trying to explain some of the alternative options available since it wasn’t clear what you were trying to do.

When you prepare the PDO statement, don’t add the variables directly within the SQL statement–use bindings. This helps prevent SQL injections.



$stm = $pdo->prepare("SELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, Brf.Site, Brf.Brief, PC.Class,
 PX.Article PXA, PX.Pagedex PXP, IVR.Article IVRA, IVR.Pagedex IVRP, SM.Article SMA, SM.Pagedex SMP
 FROM people P
 LEFT JOIN people_1_bio PB ON PB.URL = P.URL
 LEFT JOIN people_1_class PC ON PC.URL = P.URL
 LEFT JOIN people_briefs Brf ON Brf.URL = P.URL
 LEFT JOIN people_articles_px PX ON PX.URL = P.URL
 LEFT JOIN people_articles_ivr IVR ON IVR.URL = P.URL
 LEFT JOIN people_articles_sm SM ON SM.URL = P.URL
 WHERE P.URL LIKE :MyURL AND P.Site = :MySiteId
 GROUP BY Class");
$stm->execute(array(
     'MySiteId'=>$MySiteId, 
     'MyURL'=>'%'.$MyURL.'%'
));

while ($row = $stm->fetch())
{
 $URL = $row['URL'];
 $Title = $row['Title'];
 $Subtitle = $row['Subtitle'];
 $Class = $row['Class'];
 $ClassList[] = $row['Class'];
 $ClassL = str_replace(' ', '_', $Class);
 $ClassLinked[] = '<a href="/People/'.$ClassL.'" title="'.$Class.'">'.$row['Class'].'</a>';
}

Here’s a tutorial on PDO that you might find useful: http://net.tutsplus.com/tutorials/php/php-database-access-are-you-doing-it-correctly/

3

Awesome! My page now appears to be working OK.

PDO really isn’t user friendly, though. I wonder how many people are going to be left out in the cold when PHP stops supporting the original method. To be honest, I wouldn’t have even attempted it if I wasn’t forced to. :wink:

The examples you’ve given make - including the tips on bindings - make it a lot easier to understand, though. I’ll add the tutorial to my list of PDO links. Thanks!

4

Actually, I switched from the mysql driver to PDO a while back, and I find that I actually like PDO better.

Since it’s an object-oriented approach, I suspect that it’s simply not in the format you are accustomed to, as apposed to the procedural approach that the mysql driver takes. I’ve found that I prefer the PDO approach, as it’s simpler and less cumbersome since you don’t need to use as many functions and variables to execute and process a query.

After you spend some time with it and start to get familiar with it, I’m sure it will seem easier to work with. Jumping in feet first on something new always appears daunting initially.