How to successfully rewrite old mysql-php code with deprecated mysql_* functions?

Question

I am still learning mostly from books I buy, but today I leart that my book is old even though I bought it this year concerning programming in PHP. Now I know that mysql_* commands in PHP are deprecated and should be replaced with more secure and stable prepared statements and PDO. So I put myself to rewrite all my web according to it and maybe I will need some advices from you how to do it properly and working from you all more experienced guys :)

So I will start my rewrite with only main part (connect to db and choosing DB) in here (the rest I can do on my own with google and manuals). I will write here my old script and ask you if I am doing everything right and not missing anything and I hope this could be some good manual/answer for other people as well. So lets start.

So in config I have something like this:

$db = new dbConn('127.0.0.1', 'root', 'pass', 'people', 'animals');

Which should be like this:

$db = new PDO('mysql:host=127.0.0.1;dbname=people;charset=UTF-8', 'root', 'pass');

Right? But when I need to choose database later should i do it without dbname=people;? But how to choose database later?

Here is my one and only script to rewrite which is basic in most web projects and I hope it will bring not only me some understanding how new PDO system really works:

class dbConn
{
  public function __construct($server, $user, $pass, $db_people, $db_animals)
  {    
    if (!empty($server) && !empty($user) && !empty($pass) && !empty($db_people) && !empty($db_animals))
    {
      $this->server = $server;
      $this->user =  $user;
      $this->pass = $pass;
      $this->db_people = $db_people;  
      $this->db_animals = $db_animals;  
      $this->connect(); 
    }  
    else
    {
      die("Set up connection to db");
    }
  }

  public function connect()
  {
    $this->conn = mysql_connect($this->server, $this->user, $this->pass) or die ('cannot connect to MySQL');
  }

  public function selectDb($database)
  {
    switch($database)
    {
      case 'people':
        mysql_select_db($this->db_people, $this->conn) or die ('cannot connect to database '.$this->db_people.'.');
        mysql_query("SET NAMES 'utf8'");
        break;

      case 'animals':
        mysql_select_db($this->db_animals, $this->conn) or die ('cannot connect to database '.$this->db_animals.'.');
        mysql_query("SET NAMES 'utf8'"); 
    }
  }

  public function __destruct() 
  {
    if (!empty($this->conn))
    {
      mysql_close($this->conn); 
    }
  }  
}

So from what I know from Google and Wiki - functions like public function __construct and public function __destruct() should not be needed anymore, right? The same with functions like public function connect() SO only whats left is public function selectDb($database) but i have no idea how to do this correctly without damading all connection to database. Because in rest of my code (not mentioned here) I can easily choose database by this code: $this->db->selectDb("people"); But with prepared statements I do not know if this is even possible in easy way. I hope some advices around this from you will help me and other users understand this new code better. Other parts in code you may have are eplained in this PDO Tutorial for MySQL Developers. Thank you.

**Woah!** Super-duper-extra-special upvote for having the temerity to upgrade yourself from the tired old `mysql_*` dinosaur! — , Jun 06 '12 at 17:48

score 6 · Accepted Answer · edited May 23 '17 at 10:30

6

Actually, a simple, sweet and short: Yes, not necessary any longer.

Let's review the code not that we have lost something:

__construct - The constructor merely contained all the configuration. PDO has a much easier concept here, a connection string containing the most information:
```
 mysql:host=127.0.0.1;dbname=people;charset=UTF-8
```
Also PDO provides the constructor for use ready-made, so double not necessary.
connect - The connection function is not necessary any longer as well. This is done by instantiating PDO already. You can look for exceptions, the PHP manual has an example on it's constructor page.
selectDb - This complicated function is not needed any longer as well. Wow, the third function we can just drop because of the PDO connection string. Much power with so less characters. Cheers!
__destruct - The destructor. Let's be fair: MySQL did not need this as well. However with PDO we get it for free - without writing a single line of code.

Looks good! You managed to migrate from that obscure database class to PDO by removing outdated code! Congratulations:

$db = new PDO('mysql:host=127.0.0.1;dbname=people;charset=UTF-8', 'root', 'pass');

If you now think, what about if I want to have database class on my own? Well you can do that, because you can extend from PDO (yes that works!):

class DB extends PDO
{
   ... my super-new-shiny-code
}

Why you might want to do that? No idea, but maybe it's more fluent for your code. If you're looking for a better code-example, I have one at PHP/MySQL Table with Hyperlinks.

edited May 23 '17 at 10:30

Community

1
1

answered Jun 06 '12 at 18:48

hakre

193,403
52
435
836

No, that is just invalid PHP code. But maybe you want to describe what you *want* to do and I can give you the one or other hint. – hakre Jun 06 '12 at 19:46
I sometimes change pass in database for security reason so this code should check if the connection is valid and in false case return message :) or is that all really do the PDO code itself? – Byakugan Jun 06 '12 at 20:07
I suggest you just use exceptions for that. You will get the exception in case you have not configured it properly. You then fix it and done. No need to actually add code for that, it's build in in PDO via Exceptions. – hakre Jun 06 '12 at 22:00
Ah ok I thought this is needed but ok thank you for all the help :) – Byakugan Jun 06 '12 at 22:29
No, that's not needed. And the more simple you can keep your code, the better. You will know early enough if you "need" something. So sorry to irritate you with the `extends` example, that was more for completeness reasons in case you would have said: But my class is named `DB` and not `PDO` or something similar. ;) – hakre Jun 06 '12 at 22:36
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12223/discussion-between-byakugan-and-hakre) – Byakugan Jun 06 '12 at 23:09

score 1 · Answer 2 · answered Jun 06 '12 at 18:47

I think the easiest way to switch the database inside your application will be:

$pdo_instance->query("USE people");

and

$pdo_instance->query("USE animals");

or the maybe better (and cleaner) way might be

$db_people = new PDO('mysql:host=127.0.0.1;dbname=people;charset=UTF-8', 'root', 'pass');

and

$db_animals = new PDO('mysql:host=127.0.0.1;dbname=animals;charset=UTF-8', 'root', 'pass');

If you mark a database in your class active, you can access the data with $db_people->query() or $db_animals->query().

How to successfully rewrite old mysql-php code with deprecated mysql_* functions?

2 Answers2

Linked

Related