Tutorial for converting mysql code to mysqli code?

8 posts by 3 authors in: Forums > CMS Builder
Last Post: September 14, 2017   (RSS)

By gkornbluth - September 2, 2017

Hi All,

Converting mysql code to mysqli code for a PHP7 upgrade is making my head ache (with little success), as it is I’m sure for many other CMSB users.

Any chance that we can start a tutorial on how to convert the code we have.

If we post a code snippet I’m hoping that someone will take a look as well.

Thanks,

Jerry Kornbluth




The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By gkornbluth - September 4, 2017

Hi all,

I’ve been using the following to populate and update a double opt in email sign up database, and I’m having trouble converting the code to work in PHP7.

Any thoughts appreciated

Thanks,

Jerry Kornbluth


INSERT ORIGINAL RECORD INTO DATABASE

  mysql_query("INSERT INTO `{$TABLE_PREFIX}email_signup` SET
                      last_name            = '".mysql_real_escape_string( $_REQUEST['last_name'] )."',
                      first_name          = '".mysql_real_escape_string( $_REQUEST['first_name'] )."',
              email                  = '".mysql_real_escape_string( $_REQUEST['email'] )."',
     hidden           = '1',
    createdDate      = NOW(),
            updatedDate      = NOW(),
            createdByUserNum = '0',
            updatedByUserNum = '0'")
    or die("MySQL Error Creating Record:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
    $recordNum = mysql_insert_id();



UPDATE DATABASE RECORD (SECOND OPT IN)

 $where = "email='".mysql_escape($_REQUEST['email'])."'";
 if(mysql_select_count_from('email_signup',$where)){ //check to ensure that email exists in the table
    $where.=" AND confirmed='1'";
    if(!mysql_select_count_from('email_signup',$where)){ //email exists and has not been confirmed yet
        $query = "UPDATE `{$TABLE_PREFIX}email_signup` SET
              hidden           = '0',
              confirmed          = '1',
              updatedDate      = NOW()
               
             WHERE email = '".mysql_escape( $_REQUEST['email'] )."'";
        mysql_query($query) or die("MySQL Error:<br/>\n". htmlspecialchars(mysql_error()) . "\n");
        $userNum = mysql_insert_id();



 

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Dave - September 5, 2017

Hi Jerry, 

I've attached a plugin we use internally that scans for old mysql code and suggests replacements.  You can set the directory it's scanning at the top of the plugin.

Also, I rewrote some code on your other thread.

Let me know if you need anything else.

Dave Edis - Senior Developer
interactivetools.com
Attachments:

legacyMysqlScanner.php 6K

By gkornbluth - September 6, 2017

Wow, this is great.

Thank you Dave.

It will sure save a lot of headaches going down the road.

Jerry

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By rez - September 14, 2017

Hi. What's this all about? Is our usual CMSB code / viewers and such, depreciating? If our hosts update to PHP7 will we have problems on existing sites?

By gkornbluth - September 14, 2017 - edited: September 14, 2017

Hi Rez,

In PHP7, some commands have been removed and replaced with others because of security and other factors.

Most larger web hosts (like Bluehost) don't just upgrade the PHP versions. They install the latest version on their server and  give you the option of using them.

If you've upgraded to the latest version of CMSB, (3.10) the core code for your installation should be fine with PHP7.

If you've got older custom code on your site there's a check box in the latest version of CMSB under Admin < General settings, called "Legacy MySQL Support" which will allow you to "Connect to legacy PHP MySQL library to support old code (doubles required MySQL connections)" until you can update all of your custom code.

If you're using official plugins you should update them to the latest versions. Some user supplied plugins have been updated, some have not, you'll have to see if they work correctly or update them accordingly. (Post issues on the forum and you'll probably get help solving them)

If you're using the website membership plugin, once you've updated it to the latest version (1.12), you'll have to update the code on your generated code pages like the sign up, log in, password request, update profile, and other forms to reflect the newest generated code. There may be other official plugins that will require updating of the code on some generated pages.

If your sites are complex the process may take some time to work through, but you can always contact consulting to help you (use the "Hire Us" tab on the Nav Menu at the top of this page) .

Hope that helps,

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By rez - September 14, 2017

I forgot, yes, my Cpanel has options to upgrade PHP only when desired. Thanks for this entire explanation. Very helpful!