How to change mysql to mysqli

Question

Based on this code below I use for regular mysql  how could I convert it to use mysqli   Is it as simple as changing mysql query  sql   to mysqli query  sql      lt  PHP    in my header file that is included on every page I have this  DB  dbName      emails    DB  host      localhost    DB  user      root    DB  pass          link   mysql connect  DB  host     DB  user     DB  pass    or die   lt center gt An Internal Error has Occured  Please report following error to the webmaster  lt br gt  lt br gt   mysql error      lt  center gt     mysql select db  DB  dbName        end header connection part     function from a functions file that I run a mysql query through in any page  function executeQuery  sql         result   mysql query  sql       if  mysql error               error     lt BR gt  lt center gt  lt font size   1  face  arial  color  red  gt An Internal Error has Occured  lt BR gt  The error has been recorded for review lt  font gt  lt  center gt  lt br gt            if    SESSION  auto id      1                 sql formatted   highlight string stripslashes  sql   true                error      lt b gt The MySQL Syntax Used lt  b gt  lt br gt      sql formatted     lt br gt  lt br gt  lt b gt The MySQL Error Returned lt  b gt  lt br gt     mysql error                      die  error             return  result        example query ran on anypage of the site using executeQuery function  sql  SELECT auto id FROM friend reg user WHERE auto id     info  auto id     result member executequery  sql   if  line member mysql fetch array  result member        extract  line member     else       header  location  index php        exit      gt

User · Accepted Answer

The first thing to do would probably be to replace every mysql_* function call with its equivalent mysqli_*, at least if you are willing to use the procedural API -- which would be the easier way, considering you already have some code based on the MySQL API, which is a procedural one.

To help with that, the MySQLi Extension Function Summary is definitely something that will prove helpful.

For instance:

mysql_connect will be replaced by mysqli_connect
mysql_error will be replaced by mysqli_error and/or mysqli_connect_error, depending on the context
mysql_query will be replaced by mysqli_query
and so on

Note: For some functions, you may need to check the parameters carefully: Maybe there are some differences here and there -- but not that many, I'd say: both mysql and mysqli are based on the same library (libmysql ; at least for PHP <= 5.2)

For instance:

with mysql, you have to use the mysql_select_db once connected, to indicate on which database you want to do your queries
mysqli, on the other side, allows you to specify that database name as the fourth parameter to mysqli_connect.
Still, there is also a mysqli_select_db function that you can use, if you prefer.

Once you are done with that, try to execute the new version of your script... And check if everything works ; if not... Time for bug hunting ;-)

User · Answer

2020  Answer I ve created a tool called Rector  that handles instant upgrades  There is also mysql   mysqli set  It handles   function renaming  constant renaming  switched arguments  non-1 1 function calls changes  e g     data   mysql db name  result   row        mysqli data seek  result   row      fetch   mysql fetch row  result      data    fetch 0      How to use Rector  1  Install it via Composer composer require rector rector --dev     or in case of composer conflicts composer require rector rector-prefixed --dev  2  Create rector php in project root directory with the Mysql to Mysqli set  lt  php  use Rector Core Configuration Option  use Rector Set ValueObject SetList  use Symfony Component DependencyInjection Loader Configurator ContainerConfigurator   return static function  ContainerConfigurator  containerConfigurator   void         parameters- gt set Option  SETS            SetList  MYSQL TO MYSQLI              3  Let Rector run on e g   src directory to only show the diffs vendor bin rector process src --dry-run  4  Let Rector change the code vendor bin rector process src   I ve already run it on 2 big PHP projects and it works perfectly

User · Answer

similar to dhw s answer but you don t have to worry about setting the link as global in all the function because that is kind of difficult  just use this code in your config file   sv connection   mysqli connect  dbhost   dbuser   dbpass   dbname    db connection   mysqli select db   sv connection   dbname   mysqli set charset  sv connection   utf8      optional     Check connection if  mysqli connect errno          echo  quot Failed to connect to MySQL   quot    mysqli connect error        exit        function mysqljx query  q       global  sv connection      return mysqli query  sv connection   q      function mysqljx fetch array  r       return mysqli fetch array  r      function mysqljx fetch assoc  r       return mysqli fetch assoc  r      function mysqljx num rows  r       return mysqli num rows  r      function mysqljx insert id        global  sv connection      return mysqli insert id  sv connection      function mysqljx real escape string  string       global  sv connection      return mysqli real escape string  sv connection   string      -now do a search for php files that contain  quot mysql  quot   i used total commander for that - Alt F7  search for  quot   php quot   find text  quot mysql  quot   Start search  Feed to listbox  -drag amp drop them all in Notepad    there u press CTRL H  Find what   quot mysql  quot   Replace with  quot mysqljx  quot    quot Replace All in All Opened Documents quot  if you are worried that you have other functions than the ones listed above just replace one by one   quot mysql query quot  with  quot mysqljx query quot   then mysql fetch array with  quot mysqljx fetch array quot  etc    and then search again for  quot mysql  quot  and if its still there its a uncovered function and you can just add it same as the rest   that is it

User · Answer

If you have a lot files to change in your projects you can create functions with the same names like mysql functions  and in the functions make the convert like this code    sql host        your host          sql username    username        sql password    password           sql database    database              mysqli   new mysqli  sql host    sql username    sql password    sql database         check connection    if   mysqli- gt connect errno        printf  Connect failed   s n    mysqli- gt connect error       exit        function mysql query  query        result    mysqli- gt query  query       return  result      function mysql fetch array  result       if  result            row     result- gt fetch assoc             return  row              function mysql num rows  result       if  result             row cnt    result- gt num rows            return  row cnt

User · Answer

Although this topic is a decade old  I still often require to  backpatch  existing applications which relied upon the mysql extension     the original programmers were too lazy to refactor all their code  and just tell customers to make sure that they run the latest PHP 5 6 version available   PHP 5 6 is now officially deprecated  in other words  developers had a decade to get rid of their dependencies upon mysql and move to PDO  or  well  mysqli      But    changing so much legacy code is expensive  and not every manager is willing to pay for the uncountable hours to  fix  projects with dozens of thousands of lines   I ve searched for many solutions  and  in my case  I often used the solution presented by  esty-shlomovitz     but in the meantime  I ve found something even better   https   www phpclasses org package 9199-PHP-Replace-mysql-functions-using-the-mysqli-extension html   you need to register to download it  but that just takes a minute   These are just two files which act as drop-in replacements for the whole mysql extension and very cleverly emulate pretty much everything  using mysqli  without the need to worry much about it  Of course  it s not a perfect solution  but very likely it will work in 99  of all cases out there   Also  a good tutorial for dealing with the chores of migration  listing many of the common pitfalls when migrating  can also be found here  https   www phpclasses org blog package 9199 post 3-Smoothly-Migrate-your-PHP-Code-using-the-Old-MySQL-extension-to-MySQLi html   if you re reading this in 2030 and the PHPclasses website is down  well  you can always try archive org  -   Update   crashwap noted on the comments below that you can also get the same code directly from GitHub  Thanks for the tip   crashwap  -

User · Answer

I realise this is old  but it still comes up      If you do replace mysql   with mysqli   then bear in mind that a whole load of mysqli   functions need the database link to be passed   E g    mysql query  query    becomes  mysqli query  link   query    I e   lots of checking required

User · Answer

The easiest way i always handle this Where    con   mysqli connect  serverName  dbusername  dbpassword     3 steps replacement in the following order   All  mysql select db   with  mysqli select db  con   All  mysql query   with  mysqli query  con   and  All  mysql   with  mysqli      This works for me everytime

User · Answer

I would tentatively recommend using PDO for your SQL access   Then it is only a case of changing the driver and ensuring the SQL works on the new backend  In theory  Data migration is a different issue   Abstract database access is great

User · Answer

The ultimate guide to upgrading mysql   functions to MySQLi API  The reason for the new mysqli extension was to take advantage of new features found in MySQL systems versions 4 1 3 and newer  When changing your existing code from mysql   to mysqli API you should avail of these improvements  otherwise your upgrade efforts could go in vain  The mysqli extension has a number of benefits  the key enhancements over the mysql extension being    Object-oriented interface Support for Prepared Statements Enhanced debugging capabilities   When upgrading from mysql   functions to MySQLi  it is important to take these features into consideration  as well as some changes in the way this API should be used    1  Object-oriented interface versus procedural functions   The new mysqli object-oriented interface is a big improvement over the older functions and it can make your code cleaner and less susceptible to typographical errors  There is also the procedural version of this API  but its use is discouraged as it leads to less readable code  which is more prone to errors     To open new connection to the database with MySQLi you need to create new instance of MySQLi class     mysqli   new  mysqli  host   user   password   dbName    mysqli- gt set charset  utf8mb4      Using procedural style it would look like this      mysqli   mysqli connect  host   user   password   dbName   mysqli set charset  mysqli   utf8mb4      Keep in mind that only the first 3 parameters are the same as in mysql connect  The same code in the old API would be      link   mysql connect  host   user   password   mysql select db  dbName   link   mysql query  SET NAMES utf8      If your PHP code relied on implicit connection with default parameters defined in php ini  you now have to open the MySQLi connection passing the parameters in your code  and then provide the connection link to all procedural functions or use the OOP style   For more information see the article  How to connect properly using mysqli  2  Support for Prepared Statements  This is a big one  MySQL has added support for native prepared statements in MySQL 4 1  2004   Prepared statements are the best way to prevent SQL injection  It was only logical that support for native prepared statements was added to PHP  Prepared statements should be used whenever data needs to be passed along with the SQL statement  i e  WHERE  INSERT or UPDATE are the usual use cases      The old MySQL API had a function to escape the strings used in SQL called mysql real escape string  but it was never intended for protection against SQL injections and naturally shouldn t be used for the purpose  The new MySQLi API offers a substitute function mysqli real escape string for backwards compatibility  which suffers from the same problems as the old one and therefore should not be used unless prepared statements are not available    The old mysql   way    login   mysql real escape string   POST  login      result   mysql query  SELECT   FROM users WHERE user   login       The prepared statement way     stmt    mysqli- gt prepare  SELECT   FROM users WHERE user       stmt- gt bind param  s     POST  login      stmt- gt execute     result    stmt- gt get result      Prepared statements in MySQLi can look a little off-putting to beginners  If you are starting a new project then deciding to use the more powerful and simpler PDO API might be a good idea    3  Enhanced debugging capabilities  Some old-school PHP developers are used to checking for SQL errors manually and displaying them directly in the browser as means of debugging  However  such practice turned out to be not only cumbersome  but also a security risk  Thankfully MySQLi has improved error reporting capabilities    MySQLi is able to report any errors it encounters as PHP exceptions  PHP exceptions will bubble up in the script and if unhandled will terminate it instantly  which means that no statement after the erroneous one will ever be executed  The exception will trigger PHP Fatal error and will behave as any error triggered from PHP core obeying the display errors and log errors settings  To enable MySQLi exceptions use the line mysqli report MYSQLI REPORT ERROR   MYSQLI REPORT STRICT  and insert it right before you open the DB connection   mysqli report MYSQLI REPORT ERROR   MYSQLI REPORT STRICT    mysqli   new  mysqli  host   user   password   dbName    mysqli- gt set charset  utf8mb4      If you were used to writing code such as     result   mysql query  SELECT   WHERE 1 1    if    result        die  Invalid query      mysql error         or   result   mysql query  SELECT   WHERE 1 1   or die mysql error       you no longer need to die   in your code     mysqli report MYSQLI REPORT ERROR   MYSQLI REPORT STRICT    mysqli   new  mysqli  host   user   password   dbName    mysqli- gt set charset  utf8mb4      result    mysqli- gt query  SELECT   FROM non existent table       The following line will never be executed due to the mysqli sql exception being thrown above foreach   result as  row                   If for some reason you can t use exceptions  MySQLi has equivalent functions for error retrieval  You can use mysqli connect error   to check for connection errors and mysqli error  mysqli  for any other errors  Pay attention to the mandatory argument in mysqli error  mysqli  or alternatively stick to OOP style and use  mysqli- gt error    result    mysqli- gt query  SELECT   FROM non existent table   or trigger error  mysqli- gt error  E USER ERROR     See these posts for more explanation  mysqli or die  does it have to die  How to get MySQLi error information in different environments   4  Other changes  Unfortunately not every function from mysql   has its counterpart in MySQLi only with an  i  added in the name and connection link as first parameter  Here is a list of some of them    mysql client encoding   has been replaced by mysqli character set name  mysqli  mysql create db has no counterpart  Use prepared statements or mysqli query instead mysql drop db has no counterpart  Use prepared statements or mysqli query instead  mysql db name  amp  mysql list dbs support has been dropped in favour of SQL s SHOW DATABASES mysql list tables support has been dropped in favour of SQL s SHOW TABLES FROM dbname mysql list fields support has been dropped in favour of SQL s SHOW COLUMNS FROM sometable mysql db query -  use mysqli select db   then the query or specify the DB name in the query  mysql fetch field  result  5  -  the second parameter  offset  is not present in mysqli fetch field  You can use mysqli fetch field direct keeping in mind the different results returned mysql field flags  mysql field len  mysql field name  mysql field table  amp  mysql field type -  has been replaced with mysqli fetch field direct mysql list processes has been removed  If you need thread ID use mysqli thread id mysql pconnect has been replaced with mysqli connect   with p  host prefix mysql result -  use mysqli data seek   in conjunction with mysqli field seek   and mysqli fetch field   mysql tablename support has been dropped in favour of SQL s SHOW TABLES mysql unbuffered query has been removed  See this article for more information Buffered and Unbuffered queries

User · Answer

In case of big projects  many files to change and also if the previous project version of PHP was 5 6 and the new one is 7 1  you can create a new file sql php and include it in the header or somewhere you use it all the time and needs sql connection   For example     local  sql host        localhost          sql username    root        sql password               sql database    db       mysqli   new mysqli  sql host    sql username    sql password    sql database        check connection    if   mysqli- gt connect errno        printf  Connect failed   s n    mysqli- gt connect error       exit             change character set to utf8    if    mysqli- gt set charset  utf8          printf  Error loading character set utf8   s n    mysqli- gt error       exit      else          printf  Current character set   s n    mysqli- gt character set name       if   function exists  mysql real escape string          function mysql real escape string  string           global  mysqli          if  string                   mysqli   new mysqli  sql host    sql username    sql password    sql database                             newString     mysqli- gt real escape string  string               return  newString                        mysqli- gt close     conn   null  if   function exists  mysql query          function mysql query  query            global  mysqli             echo  DAAAAA           if  query                 result    mysqli- gt query  query               return  result                    else        conn mysql connect  sql host  sql username   sql password       mysql set charset  utf8    conn       mysql select db  sql database      if   function exists  mysql fetch array          function mysql fetch array  result           if  result                row     result- gt fetch assoc                return  row                     if   function exists  mysql num rows          function mysql num rows  result           if  result                row cnt    result- gt num rows               return  row cnt                     if   function exists  mysql free result          function mysql free result  result           if  result               global  mysqli               result- gt free                        if   function exists  mysql data seek          function mysql data seek  result   offset           if  result               global  mysqli              return  result- gt data seek  offset                       if   function exists  mysql close          function mysql close            global  mysqli          return  mysqli- gt close             if   function exists  mysql insert id          function mysql insert id                global  mysqli               lastInsertId    mysqli- gt insert id              return  lastInsertId           if   function exists  mysql error          function mysql error            global  mysqli           error    mysqli- gt error          return  error

User · Answer

Here is a complete tutorial how to make it quickly if you need to make worgking again a website after PHP upgrade  I used it after upgrading hosting for my customers from 5 4  OMG     to 7 x PHP version      This is a workaround and it is better to rewrite all code using   PDO or mysqli Class    1  Connection definition  First of all  you need to put the connection to a new variable  link or  con  or whatever you want   Example  Change the connection from     mysql connect  host   username   password  or die  Error message        mysql select db  db     or    mysql connect  host   username   password   db  or die  Error message         to     con   mysqli connect  host   username   password   db  or die  Error message         2  mysql   modification  With Notepad   I use  Find in files   Ctrl   Shift   f       in the following order I choose  Replace in Files      mysql query               -  mysqli query  con  mysql error               -  mysqli error  con  mysql close               -  mysqli close  con  mysql insert id           -  mysqli insert id  con  mysql real escape string  -  mysqli real escape string  con  mysql                     -  mysqli    3  adjustments  if you get errors it is maybe because your  con is not accessible from your functions   You need to add a global  con  in all your functions  for example    function my function            global  con              In SQL class  you will put connection to  this- gt con instead of  con   and replace it in each functions call  for example   mysqli query  con   query

User · Answer

I have just created the function with the same names to convert and overwrite to the new one php7    host        your host          un    username        pw    password           db    database      MYSQLI CONNECT   mysqli connect  host   un   pw   db    function mysql query  q        global  MYSQLI CONNECT      return mysqli query  MYSQLI CONNECT  q      function mysql fetch assoc  q        return mysqli fetch assoc  q      function mysql fetch array  q       return mysqli fetch array  q   MYSQLI BOTH      function mysql num rows  q       return mysqli num rows  q      function mysql insert id         global  MYSQLI CONNECT      return mysqli insert id  MYSQLI CONNECT      function mysql real escape string  q        global  MYSQLI CONNECT      return mysqli real escape string  MYSQLI CONNECT  q       It works for me   I hope it will work for you all   if I mistaken   correct me

[php] How to change mysql to mysqli?

Examples related to php

Examples related to mysql

Examples related to mysqli