Understanding PDO

[Drummin]

I'm attempting to convert a huge project I made with mysql to PDO.  I have many cases where I would use a WHILE statement to return a query array.

while($row= mysql_fetch_array($result)){

From what I've seen so far, it looks as though I need to use a foreach statement to do the same task.

foreach ($dbh->query($sql) as $row){

 

Is that correct?

[Drummin]

OK I did find this which is close to what I was doing.

while ($row = $sql->fetch(PDO::FETCH_BOTH)){

[xyph]

I'm attempting to convert a huge project I made with mysql to PDO.  I have many cases where I would use a WHILE statement to return a query array.

while($row= mysql_fetch_array($result)){

From what I've seen so far, it looks as though I need to use a foreach statement to do the same task.

foreach ($dbh->query($sql) as $row){

 

Is that correct?

 

It works, and won't throw syntax errors.

 

Whether it's 'correct' or not is subjective. If you're using PDO::ERRMODE_EXCEPTION, there's no need to check if $dbh->query returns FALSE or not. If you aren't, PHP will throw a foreach invalid argument error if an invalid query is entered.

 

OK I did find this which is close to what I was doing.

while ($row = $sql->fetch(PDO::FETCH_BOTH)){

 

Works as well, except now you can check if ($sql == FALSE) before trying to call a method it might not have

 

Keep in mind, JUST using PDO won't make your script compatible with other SQL servers. You have to avoid the use of engine-specific functionality, which can be really tricky.

 

Here's a little user comment debate in a PDO article.

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/comment-page-3/#comment-400404

[Drummin]

Do you think it's a good Idea to wrap all results in a check like this adding a second wrapper?

if ($sql == FALSE){
while ($row = $sql->fetch(PDO::FETCH_BOTH)){

}
}

 

I guess that would be NOT false.

if ($sql != FALSE){
while ($row = $sql->fetch(PDO::FETCH_BOTH)){

}
}

[Drummin]

Can I ask your opinion?  This project has 100+ pages that would need to be converted?  Do you think it's worth the effort to convert to PDO?  Do you think at some time, mysql will not be available?

[xyph]

Of course. If there's a chance a function will return a non-object/non-array, you should check for that before using the result in a manner that requires it to be an object/array... and handle it appropriately.

 

Can I ask your opinion?  This project has 100+ pages that would need to be converted?  Do you think it's worth the effort to convert to PDO?  Do you think at some time, mysql will not be available?

 

If you don't plan on using a database other than MySQL, or going through and making sure your queries don't contain any MySQL-specific syntax, then no.

 

If you're optimizing your queries to best use the MySQL engine, PDO doesn't have any major advantage over MySQLi

 

Even if they stop development of MySQL, it will still be available. It's open-source. Anyone who wants to can take over active development, if they want.

[Drummin]

OK, thank you.

[Drummin]

Which is a better way to make queries?

$sql=$dbh->prepare("SELECT settings_value from `settings` WHERE settings_name='sitename'"); 
$row = $sql->fetch(PDO::FETCH_ASSOC);

OR

$sql = "SELECT settings_value from `settings` WHERE settings_name='sitename'";
$result = $dbh->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);

[Drummin]

OK.  After some testing option two.

$sql = "SELECT settings_value from `settings` WHERE settings_name='sitename'";
$result = $dbh->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);

Sorry, just trying to get my head around this.