Converting from mysqli to PDO

PHP
1

I have inherited an app and am attempting to convert mysqli calls to PDO calls.

I have a couple of simple queries like the following

  $query = "SELECT * FROM client where client_id = '{$id}'";
  $client = mysqli_query($dblink,$query);
  $r = mysqli_fetch_assoc($client);

I have got thus far but I’m not sure I’m quite on the right track

  $query = "SELECT * FROM hall where hall_id = :id";
  $stmt = $dblink->prepare($query);
  $stmt->bindParam('id', $id, PDO::PARAM_INT);
  $stmt->execute();
  $r = $stmt->fetchAll();

Am I anywhere close yet? :wink:

2

That will work, but -

  1. Name the connection variable as to what type of object it contains, such as $pdo, so that you can search your code to find what has and what has not been updated.
  2. Use positional ? place holders, to save on typing and typo mistakes.
  3. Use implicit binding and supply an array of values to the ->execute([…]) call (this causes the data type of the value to be carried through to the database.)
  4. If you didn’t already do so, when you make the connection, set the character set to match your database tables, set the error mode to use exceptions, set emulated prepared queries to false, and set the default fetch mode to assoc.
  5. While you are going through the database specific code, list out the columns you are selecting.
$query = "SELECT list of columns... FROM hall where hall_id = ?";
$stmt = $pdo->prepare($query);
$stmt->execute([$id]);
$r = $stmt->fetchAll();
3 Likes
3 
$sql = 'SELECT * FROM hall WHERE id=:id'; // or name the individual column names individually ?
$stmt = $dblink->prepare($sql); // assuming $dblink is you PDO connection string?
$stmt->execute(['id' => $id]); // Execute the query with the supplied data:
$r = $stmt->fetchAll(PDO::FETCH_ASSOC);

This is one way of doing it, but I would look at https://phpdelusions.net/pdo as it is a good online resource. I even still use that website, plus I would try your own script to see if it works as the best way to do it is to test it yourself. If you get errors and I’m assuming you have error reporting on then you will know if it works or not.

1 Like
4

Thanks chaps. I am aware of most of the points made, thanks. I am getting errors, but I wanted to check they were not as a result of that code. I am still trying to fathom what various parts of the scripts actually do.

Onwards and upwards…

5

By setting the pdo error mode to exceptions, and letting php catch and handle the any pdo exception, php will display/log the actual sql error information, via an uncaught exception, using your current php error related settings - error_reporting, display_errors, and log_errors.

2 Likes
6

Something to consider:

What I really find confusing when using PDO is the return types of failed functions.

From memory a PDO failed function would return a boolean instead of an empty string, array or object.

For simple queries I created functions that returned the same types regardless of success or failure.

My “Blackbox” PDO functions would be something like:

$iResult = getPdoInteger( $sql );
$sResult = getPdoString( $sql );
$aResult = getPdoArray( $sql );
$oResult = getPdoObject( $sql ):

I find testing for empty return values far better than delving into scripts which may return conflicting results.

…just my two Satang :slight_smile:

1 Like
7

I’m making good progress with my conversion but have run into a problem with one script. I have a couple of other similar scrips that work fine, but this one comes up with a fatal error. I’m not sure how much of the code I need to share…

  $q = 'UPDATE hall SET name=:name, phone=:phone, address=:address, rent=:rent, size=:size, manager_id=:manager_id WHERE hall_id = :$hall_id';
  $data = ['hall_id' => $hall_id, 'name' => $name, 'phone' => $phone, 'address' => $address, 'rent' => $rent, 'size' => $size, 'manager_id' => $manager_id];
  $stmt = $pdo->prepare($q);
  $stmt->execute($data);

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 25 column index out of range in C:.…\edit_hall.php:26 Stack trace: #0 C:.…\edit_hall.php(26): PDOStatement->execute(Array) #1 {main} thrown in C:.…\edit_hall.php on line 26

Line 26 is the execute()

This is the schema:

CREATE TABLE IF NOT EXISTS "hall" (
	"hall_id"	INTEGER,
	"name"	TEXT,
	"phone"	TEXT,
	"address"	TEXT,
	"rent"	INTEGER,
	"size"	TEXT,
	"manager_id"	INTEGER,
	PRIMARY KEY("hall_id")
);

and this is the test data:

INSERT INTO "hall" ("hall_id","name","phone","address","rent","size","manager_id") VALUES (1,'Gec Community Hall','018xxxxxx','GEC circle',400,'1200',8);
INSERT INTO "hall" ("hall_id","name","phone","address","rent","size","manager_id") VALUES (2,'agrabad Community','-','ctg',1200,'1200',7);
INSERT INTO "hall" ("hall_id","name","phone","address","rent","size","manager_id") VALUES (4,'CDA Community','-','Halishahar.ctg',1200,'1200',7);
INSERT INTO "hall" ("hall_id","name","phone","address","rent","size","manager_id") VALUES (11,'Pavillion','none','Recreation field',10,'200',6);
INSERT INTO "hall" ("hall_id","name","phone","address","rent","size","manager_id") VALUES (12,'Village Hall','none','Church Lane',10,'100',8);
9

Your WHERE clause has a colon and the $. Should just have a colon. Also, I believe all of your index keys should begin with colons as well and not just the name.

3 Likes
10

Oh der! I knew it had to be something silly but my eyes had gone square!

Thanks, @spaceshiptrooper

1 Like
11

I seem to recall reading on here that isn’t the case - the name in the index key or in bindParam() does not need to have the starting colon, only in the query itself. I don’t have anything set up to test it, though.

1 Like
13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.