PDO Conversion Question(s)

PHP
1

(Should I post this on the PHP forum instead?)

People keep telling me I have to change my database queries to PDO, so I decided to make that project part of a huge, multi-site upgrade I’m working on.

Here’s the first query I want to convert:


$res = mysql_query ("SELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Site, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Alpha, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, PC.URL, PC.Class, Brf.URL, Brf.Site, Brf.Brief, ART.Article PXA
 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 ART ON ART.URL = P.URL
 WHERE P.Site = 'PX' AND Brf.Site  = 'PX' AND PB.Alpha LIKE '$MyURL%' AND Brf.Brief !='' AND PC.URL = P.URL AND P.Live = 1
 GROUP BY P.URL ORDER BY P.N") or die (mysql_error());

Of course, it’s probably pointless to tackle something that big and complex in PDO 101, so let me condense it to something simpler:


$res = mysql_query ("SELECT P.URL, P.Site, P.Live, Brf.URL, Brf.Site, Brf.Content
 FROM people P
 LEFT JOIN people_briefs Brf ON Brf.URL = P.URL
 WHERE P.Site = 'PX' AND Brf.Site  = 'PX' AND Brf.Brief !='' AND P.Live = 1
 GROUP BY P.URL ORDER BY P.N") or die (mysql_error());

I’m following a tutorial @ http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers and I’m focusing on this example:


$stmt = $db->prepare("SELECT * FROM table WHERE id=? AND name=?");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

This is how I began translating it to fit my query:


$stmt = $db->prepare("SELECT * FROM people WHERE P.Site = 'PX' AND Brf.Site = 'PX' AND Brf.Brief !='' AND P.Live = 1");
$stmt->bindValue(1, $id, PDO::PARAM_INT);
$stmt->bindValue(2, $name, PDO::PARAM_STR);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

But I was immediately lost. I assume that if a value is an integer, then I use a bindValue statement ending in PARAM_INT. Otherwise, I use a bindValue statement ending in PARAM_STR.

But I don’t understand how I’m supposed to translate my PHP query into a PDO query. Thanks for any tips.

2

I personally find bindValue way too much hassle. I prefer passing parameters to the execute function.

Your query would then be:


$stmt = $db->prepare("ELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Site, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Alpha, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, PC.URL, PC.Class, Brf.URL, Brf.Site, Brf.Brief, ART.Article PXA
 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 ART ON ART.URL = P.URL
 WHERE P.Site = 'PX' AND Brf.Site  = 'PX' AND PB.Alpha LIKE ? AND Brf.Brief !='' AND PC.URL = P.URL AND P.Live = 1
 GROUP BY P.URL ORDER BY P.N");
$stmt->execute(array($MyURL.'%'));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
3

Whew, that’s a relief; it’s so much easier to understand than some of the examples I’ve been perusing. Thanks. :wink:

On Edit: However, I’m getting the following error messages:

Notice: Undefined variable: db in /Users/… on line 118

Fatal error: Call to a member function prepare() on a non-object in /Users/… on line 118

It corresponds to this line:

$stmt = $db->prepare("ELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Site, P.Live,
4

You do of course need to create a PDO instance. That should be covered by your tutorial :slight_smile:

5

OK, I’ve picked up a few more tips from tutorials, but it still isn’t working. Can anyone tell me what’s wrong?

It turned out that my original database connection apparently didn’t work. It gave an error message if there was something wrong with my DB connection, but I couldn’t get even a simple PDO row(count) query to work with it. So I switched to a different DB connection which actually works with queries - but I now have no error reporting at all, so I have no way of knowing what’s wrong.

Below is my entire code, with some more notes.


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

// Is this next line what's known as an "instance"?
$pdo = new PDO($dsn,'USERNAME','PASSWORD', $opt);

$stmt = $db->prepare("SELECT P.URL, P.Title, P.Subtitle, P.MetaTitle, P.MetaDesc, P.KW, P.Site, P.Live,
 PB.Common, PB.First, PB.Middle, PB.Last, PB.Prefix, PB.Suffix, PB.Alpha, PB.Born, PB.Died, PB.Birth_Place, PB.Death_Place, PB.Nationality, PC.URL, PC.Class, Brf.URL, Brf.Site, Brf.Brief, ART.Article PXA
 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 ART ON ART.URL = P.URL
 WHERE P.Site = 'PX' AND Brf.Site  = 'PX' AND PB.Alpha LIKE ? AND Brf.Brief !='' AND PC.URL = P.URL AND P.Live = 1
 GROUP BY P.URL ORDER BY P.N");
$stmt->execute(array($MyURL.'%'));
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// If I understand correctly, PDO does away with while loops, which are replaced with the following, right?
foreach ($data as $row);

// Once I get my query working, I should be able to echo the page title on a separate downstream file, like this...
echo $row['Title'];

Thanks.

6

Imagine a PHP/MySQL query followed by the following while loop…


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

After converting to a PDO query, I can no longer use my while loop. Someone told me all I need to do is change it to a foreach function. But how do I do that?

My research suggests the following solution…


foreach ($array as $value)
{
 $URL = $row['URL'];
 $Title = $row['Title'];
 $Class = $row['Class'];
 $ClassList[] = $row['Class'];
 $ClassL = str_replace(' ', '_', $Class);
 $ClassLinked[] = '<a href="/People/'.$ClassL.'" title="'.$Class.'">'.$row['Class'].'</a>';
}

But I don’t know what values to substitute for $array and $value. My query’s below:


$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);

$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();

Thanks.

7

This is untrue. Using a while loop is standard practice for retrieving rows from the database query, regardless of the driver/library you are using.

If you use a foreach loop on a fetch statement, it will loop through the columns of a single row.

The only case where a foreach would probably work is if you fetch the entire result set as an array.

8

Well, that confirms that my query isn’t working. ):

But that’s good to know. Am I correct in assuming that a foreach loop would therefore replace an array (e.g. $Class = $row[‘Class’])? That’s one of the many things I’m trying to figure out.

On Edit: Here’s the question I should be asking. If my original while loop looked like this…

In the meantime, I’ll have to go back and troubleshoot my query. Thanks.


while ($row = mysql_fetch_array ($res))

How should I change it to match my new PDO query?

9

Yes. Generally speaking, any time you use a class (in this case, PDO is the class), it is an Instance of that Class of Object. The keyword here is “new”. Whenever you see that, you’re instantiating (creating) an instance of the object class. Think of the class definition as a blueprint for a car; the instance is the car produced from the blueprint. (which lives for the lifespan of your page execution, then crashes into a tree, gets scraped off the road, and thrown into a metal recycler to be cubed up.)

// If I understand correctly, PDO does away with while loops, which are replaced with the following, right?
foreach ($data as $row);

Not… -quite-. You saved the results of the fetchAll into $rows, not $data. You should be walking the $rows array here instead. Also, a foreach is a statement-block level element, so you should probably be ending this line with a { (and adding the corresponding } where you want your loop to end). You could also have done a while loop invoking while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

// Once I get my query working, I should be able to echo the page title on a separate downstream file, like this…
echo $row[‘Title’];

As long as you’re still inside your loop. Otherwise $row loses its relevance.