Getting a PHP Pdo Connection from a MySQL_Connect()

Getting a PHP PDO connection from a mysql_connect()?

If you are using two different APIs (i.e. mysql_* and PDO), PHP will generate two different connections.

And, as a "proof", consider this portion of code :

$db = mysql_connect('localhost', 'USER', 'PASSWORD');
$pdo = new PDO('mysql://@localhost/astralblog', 'USER', 'PASSWORD');
sleep(5);

Running this will cause two distinct connections, on the MySQL server -- which will sleep for 5 seconds :

mysql> show processlist;
+----+------------+-----------------+------------+---------+------+-------+------------------+
| Id | User       | Host            | db         | Command | Time | State | Info             |
+----+------------+-----------------+------------+---------+------+-------+------------------+
| 41 | astralblog | localhost:46551 | astralblog | Sleep   |  188 |       | NULL             |
| 42 | astralblog | localhost:46552 | astralblog | Sleep   |  188 |       | NULL             |
| 43 | astralblog | localhost       | astralblog | Query   |    0 | NULL  | show processlist |
| 64 | astralblog | localhost       | NULL       | Sleep   |    4 |       | NULL             |
| 65 | astralblog | localhost       | NULL       | Sleep   |    4 |       | NULL             |
+----+------------+-----------------+------------+---------+------+-------+------------------+
5 rows in set (0,00 sec)

(The connections in question are the two last one, which appeared when I started the PHP script, and disappeared after 5 seconds)

mysql_connect to PDO connection

You cant use PDO and then exepect to use mysql_* functions they arent related.

Theres no need to select a db like that with pdo because its included in the DSN which is the contructors first argument:

$db = new PDO('mysql:host=localhost;dbname=jdlferreira', DB_USER, DB_PASS);

Then you need to use the PDO interface to interact with the DB, not the mysql ones:

    $stmt = $db->prepare("SELECT COUNT(*) FROM blog");
    $stmt->execute();
    $num_rows = $stmt->fetchColumn();
    $stmt->closeCursor();

    $pages = new Paginator;
    $pages->items_total = $num_rows;
    $pages->mid_range = 9; // Number of pages to display. Must be odd and > 3
    $pages->paginate();

    $query = "SELECT id, title, resume, date
        FROM blog
        ORDER BY date DESC $pages->limit";
    $stmt = $db->prepare($query);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      // do stuff
    }

how to change mysql_connect() to PDO

class DB {
   function DB() {
       $this->host = "localhost";
       $this->db = "dbtest";
       $this->user = "root" ;
       $this->pass = "password";
       $this->link = new PDO("mysql:host=$this->host;dbname=$this->db", $this->user, $this->pass);
   }
   function query($query) {
       $result = $this->link->query($query);
       return $result;
   }
   function thislink() {
       return $this->link;
   }
   function close() {
       $this->link = NULL;
   }
}

mysql_connect() works PDO doesn't

It appears to be the hosting. I put the exact same script on a different shared hosting : 'Combell' , i made a database on that hosting , i changed the credentials and the script works like a charm. So the problem is 'Blacknight'. Thanks for the help everybody.

PHP: Reuse Open MySQL Connection from mysql_connect() PDO?

I am just curious to see if there is a way to transfer a connection opened by mysql_connect() to PDO.

NO

I would like for the class's queries to work regardless on the user opening a connection to the database via mysql_* or PDO...

It's quite hard to understand your reasons.

If you want to keep both drivers, you have to keep 2 sets of functions as well. And completely change the class structure. You have to have an abstract class with common methods and 2 implementations for them. No connections passed to the class but class itself have to manage connection. I doubt it worth such a labor though.

If you just want to allow use of your class along with some legacy mysql_* code - just open another connection from PDO. It's not such an evil thing to be avoided at any cost. It's just a connection. It will burden your server slightly, yes, but it will be least problem for the application that uses such a sandwiched spaghetti.

PHP PDO vs normal mysql_connect

PDO is a bit slower than the mysql_*
But it has great portability. PDO provides single interface across multiple databases. That means you can use multiple DB without using mysql_query for mysql, mssql_query for MS sql etc. Just use something like $db->query("INSERT INTO...") always. No matter what DB driver you are using.

So, for larger or portable project PDO is preferable. Even zend framework use PDO.

How to force the creation of a NEW DATABASE CONNECTION using PHP's PDO to connect to MySQL

Yes it's a new instances of the connection using the credentials given. You can see this with MySQL SHOW FULL PROCESSLIST via MySQL command-line, as it creates each new connection and query, just have to be fast about it or run some slow queries.

On a side note; running the insert again is a bit dirty workaround to an unoptimized process with the database design and mysql config, I recommend revisiting the items in question and find a better method to the process. Best

Do SQL connections opened with PDO in PHP have to be closed

Use $link = null to let PDO know it can close the connection.

PHP: PDO Connections & Connection Management

Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

---

Related Topics