Fist failure with migration my MySql code to PDO

I’ve decided to change my huge website based on php and MySql to PDO. I have read some stuff and about the pain-staking task to change lots of code, but hopefully once done, there will be no regrets. I am optimistic the changes done are only in the querying, so hopefully, the list of changes is not huge.

I am following the tutorial here:
[URL=“http://www.sitepoint.com/migrate-from-the-mysql-extension-to-pdo/”]

I have the PDO extension installed well (at least it shows on phpinfo) I have not installed PDO_myaqlite.

So I am stuck immediately in a very basic query sending code with the error:
Fatal error: Call to a member function quote() on a non-object in E:\WEBSITE\pa.php on line 20



<?php

function conn()
{ 
$mwp_20c = "user"
$mwp_71c = "password"

$db = new PDO("mysql:host=localhost;dbname=mwp", $mwp_20c, $mwp_71c);
}

conn();

$code="PTRAQ";

$Qpln = "SELECT * FROM sppindex WHERE ID=" . $db->quote($code); // line 20
$Rpln = $db->query($Qpln);
$Nrow = $Rpln->fetch(PDO::FETCH_ASSOC);
$Rpln->closeCursor();


$faml = $Nrow['faml'];

$Qpln = "SELECT * FROM faml_index WHERE faml=" . $db->quote($faml);
$Rpln = $db->query($Qpln);
$Frow = $Rpln->fetch(PDO::FETCH_ASSOC);
$Rpln->closeCursor();
$db = null;

end;

// more code below...
?>

It wasn’t a good start

Maybe the first question I should have asked is how much work is there to convert a huge site based with mysql to mysqli or PDO??? As I see there is a lot of changes in the syntax not only in mysql queries, but practically in most other code handling. I am self-taught hobbyist coder and learnt mysql/php at the age of 30. I remember those endless nights trial and error. I code maybe a day every month. Now in my 40s and a family, I dont think that I have the time to learn an almost “new” language (PDO/mysqli) which means my site (and ! !!) shall die when mysql is no further supported and completely depreciated.

Hi Steve,

The first thing I notice is that you’re not returning the PDO object from your conn() class. Variables you create within functions aren’t available to outside code, so anything you want to use elsewhere you need to return from the function:


function conn()
{
    $mwp_20c = "user"
    $mwp_71c = "password"

    return new PDO("mysql:host=localhost;dbname=mwp", $mwp_20c, $mwp_71c);
}

// In use
$db = conn();

You also might want to have a read of this tutorial, as it gives some advice on handling connection errors, and also using prepared statements which avoids the need for manually quoting data for your queries.

Hi folks, I felt miserable of giving up on this migration, so I am back with another attempt. Obviosly I need to do lots of reading, so tutorials such as that sugegsted for a rather baginner are welcomed. The correction of the code worked… so thanks. I need to know one basic thing - the migration deals with the querying and fetching data only, then the rest of the php code with the data fetched in arrays 9or variables) will remain the same… Hope this is correct because then it means that the migration and code change is within my time-frame and limit.

PDO::quote is susceptible to the same injection attacks as mysql_escape_string. They are essentially the same thing. In order to gain you primary advantages of “PDO” you need to convert all your existing database queries that embed variables controlled via user input to variable binding/prepared statements. PDO itself offers no security enhancements. However, PDO provides a way to easily create prepared statements and use variable binding which does eliminate injection attacks. In theory you could do the same with mysql_* functions but you would essentially be replicating what already exists in PDO. Eliminating injection vulnerability is all about sending the variables separate from the query which has nothing to do with PHP. PDO just provides an interface to make it easier. It is all about slightly changing the workflow of interacting with the database not “PDO” itself. On which involves sending application data in separate queries from the query itself which includes placeholders that reference the application data/variables.

Thanks Oddz, I’ve done more reading and experementation. So hope users will help me with my questions and code fixing if i get stuck. While my website has a huge number of queries of the databses and simply display the data, it has little risk of getting attacked because it does not contain any sensitive (valuable) data and very little user input. But still I will do my best to convert it to PDO using prepared statements. Nevertheless I am yet very low down in the learning curve !

For instance, I am currently trying to understand what is held in variable $Nrow in the code below since it will not print anything but I have to rebuild the array with a foreach loop using $Nrow as source.

$db = conn(); // function above to connect with database

$Qpln = "SELECT * FROM sppindex WHERE ID='" . $code ."'"; 	 // my query
$Nrow = $db->query($Qpln);  // What is in $Nrow  here ???

print_r ($Nrow) ;  // nothing is printed out (print $nrow gives an error as expected)

So I am now working how to get data in an array as in old mysql $Nrow = mysql_fetch_array ($Result), without having to run a loop for every query data…

Many thanks

A bit of success!

Old mysql:


mysql_conn();
$Qpln = "SELECT * FROM sppindex WHERE ID='" . $code ."'"; 	
$Rpln = mysql_query($Qpln)  or die ('Error in query: ' . $Qpln .  mysql_error());
$Nrow = mysql_fetch_array ($Rpln);

$faml = $Nrow['faml'];

$Qpln = "SELECT * FROM faml_index WHERE faml='" . $faml ."'"; 	
$Rpln = mysql_query($Qpln)  or die ('Error in query: ' . $Qpln .  mysql_error());
$Frow = mysql_fetch_array ($Rpln);

Converted to PDO:

$db = conn();

$stm = $db->prepare("SELECT * FROM sppindex WHERE ID=?");
$stm->execute(array($code));
$Nrow = $stm->fetch(PDO::FETCH_BOTH);

$faml = $Nrow['faml'];

$stm = $db->prepare("SELECT * FROM faml_index WHERE faml=?");
$stm->execute(array($faml));
$Frow  = $stm->fetch(PDO::FETCH_BOTH);

print_r ($Frow);

I am missing the error handling to cover the old:

or die ('Error in query: ' . $Qpln .  mysql_error());

To be honest I can’t recall what query returns either since I’ve been using Drupal, Magento, and Symfony which all have an abstraction around the database layer themselve. However, this is why the documentation exists.

http://php.net/manual/en/book.pdo.php

On that page all the methods of PDO are listed out. You can click on a method and see what the signature (parameters and return value) are.

http://php.net/manual/en/pdo.query.php

Based on the documentation PDO::query returns an instance (object) of PDOStatement or FALSE on failure. So you can use that knowledge to now look up what the interface (methods) for the PDOStatement class. Though that isn’t really necessary because the first example demonstrates how to use PDO::query to run a query and iterate (loop) over the result set. PDOStatement implements an interface called transversable which allows any instance of the class itself to be used in a loop via foreach.


<?php
function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\	";
        print $row['color'] . "\	";
        print $row['calories'] . "\
";
    }
}
?>

When it comes to variable binding/prepared statements the primary method of focus is called prepare.

http://php.net/manual/en/pdo.prepare.php

The method signatures of PDO::query and PDO:: prepare are very similar. The primary difference between the two is that prepare allows or placeholders in the form of :variablename. These placeholders are used as a replacement for embedding variables directly in the query. Instead of embedding variables directly in the query the variables are send separately. This can be done in a variety of ways but the simplest is to call PDOStatement::execute and pass an associative array of placeholders whose keys pair up with the placeholders. That is exactly what is being done with the first example on the PDO:: prepare doc page.


/* Execute a prepared statement by passing an array of values */
$sql = 'SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
$sth->execute(array(':calories' => 175, ':colour' => 'yellow'));
$yellow = $sth->fetchAll();

Now shown here is once a statement is successfully executed it can be iterated over to retrieve the result rows in a variety of ways. fetchAll() shown in the above example. Though there are several methods that provide more control. merely look to the PDOStatement::fetch* methods to get an understanding of what is possible.


$db = conn();
 
$stm = $db->prepare("SELECT * FROM sppindex WHERE ID=?");
$stm->execute(array($code));
$Nrow = $stm->fetch(PDO::FETCH_BOTH);
 
$faml = $Nrow['faml'];
 
$stm = $db->prepare("SELECT * FROM faml_index WHERE faml=?");
$stm->execute(array($faml));
$Frow  = $stm->fetch(PDO::FETCH_BOTH);
 
print_r ($Frow);

Two primary things with this. The first is introduction of proper exception and error handling. That could be done in a variety of ways but the simplest is probably to wrap the whole thing in a try catch block unless the entire application can continue to run with or without either query executing successfully. The second is it is very rare to see FETCH_BOTH being used. Probably better to just get an associative array of the row using FETCH_ASSOC instead.

The last thing to point out which isn’t necessarily an error is that as the code currently exists it is assumed that there will only ever be a single result row for each query. Not knowing the actual architecture I’m not sure if that is a correct assumption.

OK, but I learn more quickly to solve problems on the fly while working on my code rather reading a lot…

Am i right in these statements?

To get multiple data in a single row of a db into -> an array, we use fetch(PDO::FETCH_BOTH) or fetch(PDO::FETCH_ASSOC)

To get multiple data through all rows in a db into -> an array, we use fetch() or fetchAll() and loop through the database row by row?#
(of fetchAll does not need looping?) Still trying to figure in simple words the diff between Fetch and FetchAll (btw is FetchAll it case sensitive in coding?)

We all learn differently but realistically you’re not going to learn through osmosis either. So the documentation is a good thing to reference. Sitting there just throwing crap at the wall until it sticks it a brittle and inefficient way to solve problems. Especially when there is such a large ecosystem of resources like documentation.

In the simplest terms, yes. Though I would avoid using FETCH_BOTH because that duplicates values when there is no real reason to.

Well fetch is different from fetchAll. PDOStatement::fetch returns a SINGLE row in the result set and internally moves the result pointer to the next row. So that the next time PDOStatement::fetch is called the next row is returned. Whereas PDOStatement::fetchAll returns ALL rows in a nested numeric array where each value represents a row as an array and/or object of data dependent on the fetch mode ie. PDO::FETCH_ASSOC, FETCH_BOTH, FETCH_OBJ, etc.

As I stated above the difference between fetchAll and fetch is fetch returns a SINGLE row and fetchAll returns ALL rows. From software architecture aspect what that translates to is fetchAll pulls all rows into memory immediately. On the other hand, fetch pulls a single row into memory one by one. So it can be said fetching a single row by row using fetch is less memory intensive than fetchAll. Though to that point it really depends on what you’re doing with the data afterwards.

Thank you, yes I got the lesson about Fetch/Fetch all. I have just converted a bulk of mysql pages to PDO but so far they are only fetch data from database pages. We see how we go when it comes to save data to database in my csv -> MySql converor code => another day.

My next problem is implimrnting a fetch into a function. Outside works fine, but within the function I get the error:
Call to a member function prepare() on a non-object.

Maybe I have to global the $db connection?

I will always be a proponent of avoiding globals – all day and everyday. However, if the code is structured in a way which that is impossible without rewriting the entire thing than practicality takes the forefront. making an assumption that most the code is procedural I would recommend a single function get and set the db.


function db($db = NULL) {
  static $conn;

  if($db !== NULL) {
     $conn = $db;
  }

  return $conn;
}

Than after you set-up the connection simple pass it to the function.


...
db($pdo);

Once that is done the connection can be accessed by simply calling the function without any arguments.


$db = db();

$db->prepare(...);

...

By far not the best method but the simplest avoiding globals and rewritting large segments of the code base.

Thanks again ODDZ - your way worked and I am using it, although for completness, the simple Global $db in the beg. of the function dod also work. Now I am stuck in this code for some onscure reason?!?!. I just want to build an array of countries in a visitor comments table store in row ‘flag’



$stm = $db->query("SELECT flag FROM comments");

// Building Country list array

$countrylist= array();

while ($country = $stm->fetch());
{
 	$country = ucwords(strtolower($country[0])); print "<br> C: $country";
	if (!in_array($country, $countrylist)) {array_push($countrylist, $country); }

}

The result is that nothing is fetched while no error is reported ?!?

Aargh! I had a semicolon in the end of the while line (and escaped the all the while looping ) - phew :sick: