#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1

    Switching from MySQL to PDO


    Hey, I was wondering for a while, how I can slowly switch my whole website's code from mysql queries to PDO queries. However I'm not sure how to go about it. I've done some test queries with PDO and a PDO connection on test pages and it works fine. When I switch the PDO connection to mysql connection to the database, the PDO queries don't work. So I changed the connection on my website from the mysql connection to the PDO connection to the database and none of the mysql queries on my website worked. All I'd like to do is just change a few queries at a time since I work alot and don't get too much time to sit infront of the computer. Is there a way to do this so both PDO and mysql queries can work until I can convert the whole website to PDO completely. Thanks for your time.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    Hi,

    you'd need a separate database connection for PDO. You cannot use a connection from the old MySQL extension for PDO or the other way round. Those are two completely different database extensions.

    However, I wouldn't do this "slow update" at all. What's the point of this? Do a complete update in your local test environment. Take all the time you need for converting the code and testing it. And when it's done, then you update your website in one go.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    I guess you mean tht you want to move from the mysql_* functions to their PDO equivalents?

    The queries themselves will remain the same because you are still using MySQL.

    It is not possible to just run a few queries through PDO and the rest through mysql_* because that would mean that you need two connections, which is bad.

    What you can do is forget PDO for the moment, and first sanitizy the way you run your queries. Create a class or a function that you can access like this:

    $arrResult = db_function("SELECT x FROM y WHERE foo=?", array('hello'));

    that would replace the ? with 'hello', and return an array of records found.

    That is pretty much what PDO does and by creating this function/method you can make that function use mysql_*, and therefor the same database connection, which is important.

    Then when once you have replaced all your mysql_* calls with a call to the new function, you can change that new function to use PDO., and you're done.

    Remember, there is no need to spam the mysql_* or PDO functions all over your scripts, just one location is enough and as long as that retuns dataa in a plain PHP variable you can do anything you want.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1
    Ok sounds good, I've never setup a test environment but should really think about doing that, I've just always edited the code in dreamweaver and uploaded the pages directly to the server. Thanks for all your help Jacques!
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Do a complete update in your local test environment. Take all the time you need for converting the code and testing it. And when it's done, then you update your website in one go.
    That's possible as long as you can keep the code synchonised between the test environment and the live environment.

    The last thing you need is to put the new code live and find out that it doesn't work on the live site because of some silly change.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    120
    Rep Power
    1
    True enough, thanks Vinny
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,921
    Rep Power
    1045
    As soon as your code exceeds, say, 1000 lines, you should always have a local test environment and a central code repository managed by version control software like Git or Subversion.

    Doing "open heart surgeries" with FTP on your live server may kinda sorta work for a while. But if anything goes wrong or you do a bigger change (like switching to PDO), you end up right in debugging hell, because you simply can't tell what you did when and why. And you cannot go back.

    I suggest you start with Subversion. It's a very simple version control tool with a nice GUI for Windows. Yeah, it may take some hours to get familiar with it. But it will save you many, many hours of painful debugging and backup searches.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    If the OP doesn't know Subversion yet then I'd recommend GIT instead, because GIT can work on your local filesystem while subversion requires a centralised server (wich can also be on your local machine but it's more compicated).

IMN logo majestic logo threadwatch logo seochat tools logo