1

Am I able to use MySQL and PDO statement together on my website?

The reason being is that my website is currently using MySQL statement and it's quite a huge website and it will take me about a month to convert everything to PDO.

I would like to implement PDO slowly page by page as my website is still running.

Here are some code samples on my site now:

On page db.php

mysql_connect ( "localhost", "test", "test" ) or trigger_error ( mysql_error(),E_USER_ERROR );
mysql_select_db ( "test_db" );
mysql_query('set names utf8');

On other pages (every other pages is linked to that db.php file):

require_once('db.php');

$sql = "SELECT * FROM test_table WHERE id='$user_id' AND status='active'";
$row = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($row);

So, I was thinking inside the db.php file, can I add something like this below the old mysql connection?

$servername = "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();

And then on other pages, I will slowly update the old mysql to PDO like below:

$stmt = $conn->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Is this even possible? Because I need to update while the site is still running. Also, I might add more pages to the website also, so that I don't keep adding more pages with the old mysql connection string. The more pages I add, the tougher I need to change everything to PDO.

nodeffect
  • 1,830
  • 5
  • 25
  • 42
  • 1
    You could. But that means having two active connections. So should be a stop-gap measure at best. (If your legacy code is only using a few common mysql_ functions, then making some wrapper procedures is often feasible.) – mario Jul 03 '15 at 04:55
  • There's a lot mysql_functions in my code..... Is having two active connections a bad thing ? – nodeffect Jul 03 '15 at 07:23
  • 1
    The number of mysql_* calls doesn't matter. Two active connections could even be beneficial, if you were running asynchronous/concurrent queries. Otherwise they're a memory hog (in particular mysql_query buffers a lot), and could deplete available MySQL server sockets (for high-traffic spikes). – mario Jul 03 '15 at 07:27
  • 1
    See also: [whats more efficient and why: one db connection per page or one db connection per function?](http://stackoverflow.com/q/15621877) (opening sockets is expensive), [How do you connect to multiple MySQL databases on a single webpage?](http://stackoverflow.com/q/274892) (though mysql_ and PDO might not share a handle, except for mysqlnd as backend), [How many connections/s can I expect between PHP and MySQL on separate server?](http://stackoverflow.com/q/927491) (max 100 connections?) – mario Jul 03 '15 at 07:32
  • 1
    And you might give [`pdo_query()`](http://stackoverflow.com/a/20767765/345031) a try. It's a drop-in replacement for mysql_ functions (simple search and replace really, can be incrementally replaced by plain PDO). However it's not very well-tested, so be careful. – mario Jul 03 '15 at 07:34
  • Hi, thanks so much. However the pdo_query is prone to SQL injection attacks. So I guess it's not a really good solution. But thanks anyway. I guess I have to make 2 connections for now and then slowly change everything to pdo. – nodeffect Jul 03 '15 at 08:04
  • How is it prone to SQL injection? It allows for parameterized queries and uses actual/real prepared statements. – mario Jul 03 '15 at 08:33
  • @mario... most of my mysql statement is something like this -> SELECT * FROM TABLE WHERE id=$user I did not use any binding.... Correct me if im wrong.... same goes for my INSERT statement... – nodeffect Jul 03 '15 at 08:34

0 Answers0