Things to consider while converting mysql_query to PDO in Php

Hello friends,

My website is running on PHP 5.6 and I want to make it compatible with latest PHP 7.
As I have numerous mysql_query in many php pages which are needed to be converted in PDO as well as wants to make whole website compatible with PHP 7, I need to know below:

1.As my website has many mysql_queries, is there any easy way to convert it? Do I need to convert all the mysql_query manually?
2. I have studied PDO way of running queries, I want to know which is the preferable method in writing statements e.g. email = ? or email = :email. Which is more recommended.
3. List of functions which are deprecated and needed to be converted to new style. What are the new styles of writing that particular function?
4. Does 5.6 supports PDO?
5. What other precautions are required to take care of?
6. Are SQL injections only possible through pages which are accessible to website users or by any other means too?
7. Any link which easily describes of PDO conversion.

Please help.

Thanks in anticipation

A lot of php based web sites were produced like web sites were done back in the 1990’s, with many repetitive actual pages, rather than to take advantage of what a server-side language and a database can do for you, and that is to dynamically produce the pages using a database driven content management system on a single page web site.

Rather than to spend the time converting numerous mysql_query(s) in many php pages to use a different database extension, spend the time simplifying all the code by switching to use a content management system, then you will have far fewer database statements that will need to be converted.

When you do convert the code to use a different database extension, you want the application programming interface to be as simple as possible, so that the amount of application code you end up with is the least.

The simplest way of doing this is to write a static database class (the mysql_ connection broke function scope, so the database class needs to do so too), that has methods for connecting to the database server, executing a query, executing a query and fetching a single row of data, and executing a query and fetching a result set of rows of data. The last three methods accept the sql query statement and an optional array of input data for prepared queries. The first of the last three methods is used when executing INSERT, UPDATE, and DELETE queries and is used internally in the class by the last two methods. The last two methods are used with SELECT/SHOW queries and both execute the query and fetch either a single row of data or all the rows of data from the query. The optional array of input data for prepared queries causes the query method code to either just execute the query (when there is no input data) or to prepare and then execute the query (when there is input data.) The query method returns a PDOStatement object to the calling code.

As to your specific questions -

  1. You will need to go through and convert each sql query to use the new database class methods and for queries that have external/unknown data being put directly into them, you will need to change the sql statement to use prepared query place-holders and then supply the external/unknown data as the optional array to the method calls. You will also need to convert the code using the result from SELECT/SHOW queries, since the mysql_ result resource no longer exists. This is where the last two class methods that execute the query and return either a single row or a result set of rows come in. You can use these to replace a query call followed by a single mysql_fetch statement or a query call followed by loop using a mysql_fetch statement. For the typical case of using a while(){} loop with a mysql_fetch statement, you would instead use a foreach(){} loop.

  2. The ? place-holder is the simplest syntax. The method I have describe in this post to convert to use the PDO extension will work with either type of place-holder.

  3. If this question is just about database statements, see what I have written above. If this is a general question for all php version changes, there are migration appendixes in the php.net documentation that list what has been deprecated and removed from php.

  4. Yes. PDO is available in php5+.

  5. Too vague of a question. Do you have examples of what you were thinking when you wrote that question?

  6. What? Any sql query that has external/unknown data being put directly into it is open to sql injection.

  7. I have only seen one fairly usable conversion/emulation class, which I don’t have the link to, which was more complicated (used process ids to manage multiple different connections and had class method wrappers to emulate all the mysql_ statements.) My description above in this reply results in the simplest application programming interface (gets the class methods to do useful work for you, rather than to just emulate mysql_ statements), which will result in the least amount of application calling code.

Here is a simple class as described above, that will work for code containing a single database connection -

<?php

// simple db class using PDO
class db
{
	public static $db;

	public static function connect($dsn,$username=null,$password=null,$driver_options=null)
	{
		$pdo = new pdo($dsn, $username, $password, $driver_options);
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the error mode to exceptions
		$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); // turn off emulated prepared queries, run real prepared queries
		$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); // set default fetch mode to assoc
		self::$db = $pdo;
	}

	// general purpose query method. accepts an optional array of input parameters and returns a pdostatement object
	public static function query($sql,$params=[])
	{
		if($params)
		{
			// prepared query
			$stmt = self::$db->prepare($sql);
			$stmt->execute($params);
			// note: to execute a prepared query more than once, you would call the ->execute() method on the returned stmt object, supplying an array of new input values for each new execution
		} else {
			// non-prepared query
			$stmt = self::$db->query($sql);
		}
		// return the pdo statement object
		return $stmt;
	}

	public static function fetch_one($sql,$params=[])
	{
		$stmt = self::query($sql,$params);
		$row = $stmt->fetch();
		return $row;
	}

	public static function fetch_all($sql,$params=[])
	{
		$stmt = self::query($sql,$params);
		$rows = $stmt->fetchAll();
		return $rows;
	}
}


// example usage

// define connection credentials
$db_servername = ""; // hostname or ip address
$db_username = ""; // connection username
$db_password = ""; // connection password
$db_name = ""; // database name
$db_charset = "utf8mb4"; // character set for connection/your database tables - typical value shown

//define PDO dsn
$dsn = "mysql:host=$db_servername;dbname=$db_name;charset=$db_charset";

//create connection
db::connect($dsn,$db_username,$db_password);


// form a prepared sql query, execute, and fetch one row
$sql = "SELECT * FROM your_table WHERE id = ?";
$row = db::fetch_one($sql,[1]);

// examine data
echo '<pre>';
print_r($row);


// form a non-prepared sql query, execute, and fetch all rows
$sql = "SELECT * FROM your_table";
$rows = db::fetch_all($sql);

// examine data
echo '<pre>';
print_r($rows);

If you need to do something different with the result from a query, either add method(s) to the class, or just use the query() method to execute the query. Since the query() method returns the PDOStatement object, you can use any of the PDOStatement methods to access the data.

The connection property is publicly available, so you can reference it when you need to get the last insert id from an INSERT/UPDATE query, reference/modify any of the connection attributes, or any of the other PDO object methods.

1 Like

Thanks phdr for your valuable reply. I am trying to grasp all what you explained above, hopefully.

Also, if you need a tutorial on PDO, check out this site: PHP PDO Tutorial

Thank you very much Mr ErnieAlex…:expressionless:

Sponsor our Newsletter | Privacy Policy | Terms of Service