Member Avatar for showman13

Wow.. I guess I must have been living with my head in the sand for a few years.

I write everything using PHP/MySql, and now it seems that eveything I am reading is referencing MySqli...

So I started researching that and found that it has been around for years and I didn't know about it... DUH...

Anyway, my question is this:

How urgently should I be looking at changing over to MySqli?

I have a system in place that entails hundreds of scripts and multiple thousands of lines of code

It seems to me that converting that would be a major project.. Would it be advisable to do it anyway?

I know I'm asking for opinions, but some feedback would be greatly appreciated.

I'm assuming that all projects going forward should use MySqli, so hopefully the conversion / learning curve won't be to bad.

Thanks in advance for your feedback.

Douglas

  • 3 Contributors
  • 4 Replies
  • 251 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by drjohn

Dani AI

Generated

Short, practical update and timeline: the old ext/mysql API was officially deprecated in PHP 5.5.0 (June 20, 2013) and removed in PHP 7.0.0 (December 3, 2015), so code that still calls mysql_* will fail on modern PHP builds and is running on unsupported tooling. Treat migration as required if you plan to move to current hosts or upgrade PHP; if the site holds sensitive data, make it high priority rather than “someday”. (php.net)

A pragmatic migration plan that fits a large codebase (hundreds of scripts) is: (1) inventory every mysql_ use (for example, run grep -R --include="*.php" -n "mysql_" .), (2) centralize the DB entry point if you haven’t already (echoing ’s idea) so you only change one file for connection/config, (3) create a staging site and write simple tests for critical flows (auth, payments, uploads), (4) use a temporary compatibility shim only to buy time, and (5) run automated refactors then manual review. There are community shims you can drop in for short-term compatibility and automated refactor tools (e.g., Rector) that will translate many function calls — but neither will turn concatenated SQL into safe prepared statements for you, so plan for manual fixes and testing. (gist.github.com)

Longer-term choice: move to parameterized queries and a modern API. PDO is a portable, well supported option (good defaults: set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, disable emulation when appropriate, and include charset=utf8mb4 in the DSN). Example pattern (illustrative):

try {
    $pdo = new PDO('mysql:host=host;dbname=db;charset=utf8mb4', 'user', 'pass', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
    $stmt->execute([':email' => $email]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    // log and fail safely
}

Use PDO docs and charset guidance when implementing. (php.net)

Prioritize by risk: convert authentication, input-handling, and admin-facing scripts first, then public read-only pages. For a codebase your size, combine automated tools, short-term shims, and staged manual fixes; plan for testing and a staged rollout rather than a single big-bang rewrite.

Recommended Answers

All 4 Replies

Member Avatar for Octet

Hello showman,

I too only released about MySQLi a couple of months ago however once you get the hang of it you can easily continue as if you've been using it for years.

The question about how urgently you change is down to what you do. Any future projects should use the newer MySQLi for the simple reason that it is more secure if you use things like prepared statements. These limit SQL injection risks to an absolute minimum because nothing is executed as a query.

In terms of swapping out your other project, technically I would say yes because of the security benefits however it depends on what it is. If this project is a large website, which recieves a lot of traffic then I would say yes, if it handles sensetive or critical data then I would say yes however if it is only a low traffic project which isn't handling anything of value then I would say to just swap things over in stages, public side first and then private side.

It is all about risk analysis, is it likely to be attacked, and if it is then what will they be able to get, is it worth the effort?

Good luck and I hope this helps!

Edited by Octet
Member Avatar for showman13

Thank you for that reaponse AHarrisGsy...

Is there any sort of a process that you could suggest for converting from one to the other?

If everything is done with procedural code, is it a simple matter of changing the mysql_ with the mysqli_ or is there a lot more to that story?

I'm curious as to the implementation, and if I could do a little bit at a time and keep the site functioning, or if it is something that I need to do on a ghost site and upload it to the live site all at once after testing it thoroughly..

I'm inclined to change over the site that I'm currently dealing with because of the nature of the site and the data that is on it.

Thanks again,

Douglas

Member Avatar for Octet

Hello again Douglas,

I'm not an expert on MySQLi however from experience it would probably be best to work on a ghost site and either do it in stages such as the registration system and then upload or do it all and then upload the final.

A lot of it you can swap relatively easily with MySQL to MySQLi however when you first begin you might get some unexpected errors which suprise you, although there is obviously this forum to help.

As a tip, create master files and then call upon the functions in these. Take for example your connection piece of code, in MySQLi it would be:

<?php

    $Connect = mysqli_connect('localhost', 'root', '***', '***');

    if(mysqli_connect_errno())
    {
        die("MySQLi Connection Error, Couldn't Connect to the Database Server");
    }

?>

So that when you come to make modifications you aren't changing hundreds of files, and instead your are editing a central file.

commented: Very Clear and understandable response +2
Member Avatar for drjohn

You might just want to check that your host actually supports mysqli - I too recently decided it was time to expand my skillset and do things in what appeared to be the modern way. Pity that the web host I tried my new project on hadn't set the server up for mysqli (client already had a site, so no choice in host), so I had to rewrite things the way I was used to.

If you do decide to switch and the host does support it, watch that in some cases the order of variables is swapped round - why, god only knows!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.