asked on

tips for upgrading large site to mysqli V2

Hello,

I posted this before and got a good answer, which ended up not being the answer I needed, so i am re-posting.. it was suggested to use phpcs + phpcbf but they will not upgrade your mysql to mysqli, it will only fix minor syntax and spacing issues..

I am looking to upgrade a website. My main goal is to remove all mysql connections and change them to mysqli. I have 100s of pages and 1000s of queries. Many of my queries look like so. Is there any easy ways to find and replace large amounts of queries at once?

$query_Recordset_getclientrep = "SELECT * FROM client_rep WHERE client_rep_id = '$the_client_rep'";
$Recordset_getclientrep = mysql_query($query_Recordset_getclientrep, $mine) or die(mysql_error());
$row_Recordset_getclientrep = mysql_fetch_assoc($Recordset_getclientrep);
$totalRows_Recordset_getclientrep = mysql_num_rows($Recordset_getclientrep); 

Select all
 
Open in new window

Open in new window

(I know it looks like old dreamweaver code, but it isn't, I just got comfortable with the format and used it long after I stopped using dreamweaver).

If there any ways I can do massive search and replaces to help save time.

I also have this code, any way to swap this out easy as removing it with breaking 100s of queries

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

Select all
 
Open in new window

Open in new window

Also, if you can think of any other functions that wee commonly used but be depreciated soon, please let me know as I can teach them out as well.

Chris Stanyon

Hmmm. Depending on your codebase, this could be a nightmare to sort out. Running search and replace is not going to be easy. As a quick example, the mysql_query() function takes 2 arguments - the sql and the connection in that order. The mysqli version of it takes the same 2 arguments, but in a different order:

mysql_query($sql, $conn);
mysqli_query($conn, $sql);

You might be able to run search and replace using regular expression, but I guarantee you it will turn your head inside out !

With regard to swapping out your GetSQLValueString() function - it depends. If you have that function in only one file, which you then include or require throughout your application, then it should be easy. However, if you have that function scattered around, then it's going to be a problem.

To figure out what else is deprecated, you were on the right track with phpcs. Run that with the PHPCompatibility standard and output the result to a log file. Specify the version of PHP you're migrating to and will attempt to hi-light all deprecate function automatically. You could also check the PHP docs to see what was deprecated in the various version of PHP:

https://www.php.net/manual/en/migration70.deprecated.php
https://www.php.net/manual/en/migration71.deprecated.php
https://www.php.net/manual/en/migration72.deprecated.php

You can see from those links that different function were deprecated in different releases of PHP7, so it may make more sense to migrate your site to 7.0 to start with, and then to 7.1, 7.2, 7.3. Not ideal as you really want to be aiming for 7.3, but it might break down the work that needs to be done into more manageable chunks.

Unfortunately, the bottom line is that if your codebase is a jumble of spaghetti code, then you've got a task on your hands. The benefits of writing structured, modular code will very quickly become apparent, and given your use of DreamWeaver style code, a complete re-write would be the 'best' option - although I completely understand that this probably isn't practical :(

David Favor

Provide a text attachment of one of the files phpcbf failed to convert.

I'll test the conversion process.

jblayney

ASKER

@ Chris Stanton I do have a centralized functions file which takes care of most of the heavy lifting and contains anything that was re-usable... but when i had a page which would have a page specific one-off query, I tended to just write the query on that page so my functions file didn't get completely out of control

@ David Favour, i went to the phpcs + phpcbf Github and was told there that it wont upgrade from mysql to mysqli. I would rather not post a full page here, can I email it?

jblayney

ASKER

@Chris Stanton Also, Im not sure if I will be doing web design forever, this is a fully customized accounting/reporting system for a company, so my goal was to bring it up to 7.3 so they can get at least 10 more years out of it in case i switch careers, the site is so customized and specific to their business, it wont be fun for someone else to rebuild or them to try to explain it.

David Favor

Generally phpcbf will do all mysql_* conversions to mysqli_*, including any parameter swapping.

I'll PM you might email address.

Rewriting the guard logic, looking for mysql_* in quotes might fail.

I'll let you know, when I run phpcbf on your file.

jblayney

ASKER

It looks like the basics include if my query looks like this

$query_Recordset_one = "SELECT * FROM artist ORDER BY artist_name ASC";
$Recordset_one = mysql_query($query_Recordset_one, $link) or die(header("Location:reporting_page.php?the_error=".mysql_error()."&the_page=".basename($_SERVER["PHP_SELF"]).""));
$row_Recordset_one = mysql_fetch_assoc($Recordset_one );
$totalRows_Recordset_one = mysql_num_rows($Recordset_one );

do {

}while ($row_Recordset_one = mysqli_fetch_assoc($Recordset_one));

1. find and replace all mysql_ to mysqli_
2a. find and replace all mysqli_query($query_ to mysqli_query($link, $query_
2b. find and replace all , $link) to )
3a. Change all "do {" to "while($row_ Recordset_one = mysqli_fetch_assoc($Recordset_one))" loops
3b. and remove all $row_ Recordset_one = mysqli_fetch_assoc($Recordset_one); where a loop is used later in page
4. change mysql_error(). to mysqli_error($link).

this seems to take care of most of it and not as slowly as I thought, luckily all my queries start with $query_ which makes that easy to find and replace. I did have to rewrite my login page from scratch though

Can anyone confirm, i only need to remove this part of the query
$row_Recordset_one = mysqli_fetch_assoc($Recordset_one);
when I am using the query later for a while loop, otherwise I end up losing the first record.. I don't need to do this if I am just mining a single record?

Chris Stanyon

When calling mysqli_fetch_assoc(), it retrieves a record from the resultset and moves the pointer forward ready for retrieval of the next record.

In your code, you seem to be retrieving one record and then running a do/while loop to retrieve the rest. The alternative to this is to just run a while loop:

while ($row_ Recordset_one = mysqli_fetch_assoc($Recordset_one)) {
    ...
}

Open in new window

Obviously if you're only grabbing one record, then you don't need the while loop at all:

$row_ Recordset_one = mysqli_fetch_assoc($Recordset_one)

Open in new window

If you need to grab the first record before looping over all records, then you'll need to reset the pointer, otherwise your while loop will start at the second record:

$row_ Recordset_one = mysqli_fetch_assoc($Recordset_one);
echo $row_ Recordset_one['someColumn']

...

mysqli_data_seek($Recordset_one, 0); // reset the pointer back to the start

while ($row_ Recordset_one = mysqli_fetch_assoc($Recordset_one)) {
    ...
}

Open in new window

ASKER CERTIFIED SOLUTION

David Favor

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jblayney

ASKER

So i should run line 4 in above code completely?

David Favor

Correct.

Then to run across many files...

phpcbf --parallel=16 -w --standard=PSR2 --encoding=utf-8 --extensions=php,inc,lib,js,css,html --runtime-set testVersion 7.3 /path-to-files

Open in new window

David Favor

Where --parallel=cores (on your hardware).

skullnobrains

If you have a central functions file, it is also feasible to declare a bunch of mysql_× functions and map them to their mysqli_× counterparts.

This can be wrapped in an if block so you only declare missing functions

You can hack the compat library so it logs calls and tell you where in the code they were called

jblayney

ASKER

hello David,

So i ran your code and got the same problem..

$ phpcbf -w --standard=PSR2 --encoding=utf-8 --extensions=php,inc,lib,js,css,html --runtime-set testVersion 7.3 ./artist.php

Changing into directory /chroot/html/administration
Processing artist.php [PHP => 1184 tokens in 240 lines]... DONE in 98ms (77 fixable violations)
=> Fixing file: 1/77 violations remaining [made 50 passes]... ERROR in 3.6 secs
No fixable errors were found
Time: 3.76 secs; Memory: 14Mb

David Favor

You'll have to run with higher verbosity then, like phpcbf -vvv or something similar.

Try providing output of following commands...

net16 # phpcs -i
The installed coding standards are PSR12, PSR2, MySource, PEAR, Zend, Squiz, PSR1 and PHPCompatibility

net16 # phpcbf -i
The installed coding standards are PSR12, PSR2, MySource, PEAR, Zend, Squiz, PSR1 and PHPCompatibility

net16 # id
uid=0(root) gid=0(root) groups=0(root)

net16 # ls -l artist.php

net16 # touch artist.php

Open in new window

jblayney

ASKER

Hello David,

[]$ phpcs -i
The installed coding standards are Zend, MySource, PHPCS, PSR2, Squiz, PSR1 and PEAR

[]$ phpcbf -i
The installed coding standards are Zend, MySource, PHPCS, PSR2, Squiz, PSR1 and PEAR

[]$ id
uid=10171(user) gid=634(user) groups=634(user),502(user)

[]$ ls -l artist.php
-rw-r--r-- 1 user user 9196 Aug 13 10:01 artist.php

[]$ touch artist.php

[]$

David Favor

Everything looks good, except PHPCompatibility is missing, so likely best to fix this... and... I don't think this is the problem.

Now provide the output from...

phpcbf -w --standard=PSR2 --encoding=utf-8 --extensions=php,inc,lib,js,css,html --runtime-set testVersion 7.3 ./artist.php

Open in new window

jblayney

ASKER

how do I fix that?

David Favor

If by "that" you mean missing PHPCompatibility, follow https://www.experts-exchange.com/questions/29150594/Updating-website-page-to-PHP-7.html instructions, step by step.

And provide the output from the above command to determine exact problem.

Just issue the command, then cut + paste (in text) the result.

jblayney

ASKER

This is the output of that command

Changing into directory administration
Processing artist.php [PHP => 1184 tokens in 240 lines]... DONE in 113ms (77 fixable violations)
=> Fixing file: 1/77 violations remaining [made 50 passes]... ERROR in 4.06 secs
No fixable errors were found
Time: 4.47 secs; Memory: 14Mb

David Favor

Suggestion: Best to open another question for upgrading PHP version using phpcs.

Tip: You can also use phpcbf to fix some/all errors.

In your case the error output shows 77 errors, all of which are flagged as mechanically fixable by running phpcbf.