Updating deprecated code for MySQL 5.5.+

[kuau]

I would highly recommend using MySQLi instead on the mysql extension - mysql is deprecated and should not be used in new projects. MySQLi (or PDO) has been the recommended extension since 2004.

--------------------------------------------------
# If at all possible, you should avoid using the mysql_* functions. #
Existing code should be updated to avoid performance and security problems.

I haven't been on the forum for awhile. I had been checking something on php.net and noticed the warning about mysql_* functions being deprecated, so I came to DD & found traq's post. I am dumbfounded that I could not have heard anything about this until now. I didn't even start using php until about 2008. The host for all my client sites uses MySQL 5.5.32 so I fear that any day all my sites could suddenly stop working.

I am far from a php expert, but it appears I have to rewrite the code for all my sites... a HUGE undertaking. I am wondering if it is just a case of doing a search-and-replace substituting mysqli for mysql everywhere it occurs. For example, here are the typical ways I use mysql...

$connection = mysql_connect($server,$user,$pass) or die("Couldn't connect to $db_name database.");
$db = mysql_select_db($db_name, $connection) or die("Couldn't find $db_name database.");

$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());

while($var = mysql_fetch_assoc($result)){ ?>

If I change to mysqli in the connection string, does that mean the code will break anywhere I used mysql within that code set? ie. I will have to change ALL code all at once for an entire site or it won't work?

Can I just add the "i" to mysql or are there other changes required to effect the update? Is mysql the only thing being deprecated? I might as well do everything at once. Thanks very much.

BTW it took me about an hour to get on the forum. First it told me my password was bad so I tried the lost password routine. I couldn't get past the CAPTCHA even though I was entering the right characters. So then I tried to re-register, but it said my email was in use, so I tried lost password again and this time was able to change the pw. I logged in but got logged out again before I could submit my question. What is going on??!

[traq]

I haven't been on the forum for awhile. I had been checking something on php.net and noticed the warning about mysql_* functions being deprecated, so I came to DD & found traq's post. I am dumbfounded that I could not have heard anything about this until now. I didn't even start using php until about 2008. The host for all my client sites uses MySQL 5.5.32 so I fear that any day all my sites could suddenly stop working.

It's not happening immediately. Version 5.6 at the earliest (probably 5.7, or it might even wait until 6 because of how heavily it is used). And, regardless of *when* it is removed, you won't feel it until you upgrade PHP - e.g., ext/mysql will always be in version 5.5, no matter what.

The funny part is that ext/mysqli has been around since 2004, and has been the "recommended" (especially from within the mysql community) extension since its release.
But you're not alone in never having heard anything about it until now.

Other things to consider:

Moving away from ext/mysql is not only about security but also about having access to all features of the MySQL database.

ext/mysql was built for MySQL 3.23 and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain. From top of my head missing features not support be ext/mysql include:

* Stored Procedures (can't handle multiple result sets)
* Prepared Statements
* Encryption (SSL)
* Compression
* Full Charset support
* ...

So moving away from ext/mysql is a good thing.

I am far from a php expert, but it appears I have to rewrite the code for all my sites... a HUGE undertaking. I am wondering if it is just a case of doing a search-and-replace substituting mysqli for mysql everywhere it occurs. For example, here are the typical ways I use mysql...

You're right; it is a huge undertaking, especially if you have a large or diverse codebase. And while ext/mysqli is designed to be as familiar as possible to ext/mysql users, no, it's not a simple "search and replace" operation. Take your code, for example:

<?php
/*
 * $connection = mysql_connect($server,$user,$pass) or die("Couldn't connect to $db_name database.");
 * $db = mysql_select_db($db_name, $connection) or die("Couldn't find $db_name database.");
 */

// setting up the initial connection is not that different.
// the main difference is that you'll be able to select the initial DB when you connect:
$connection = mysqli_connect( $server,$user,$pass,$db_name );

// error handling with die() will still work, obviously, but is not a preferred method (especially on "live" sites).
// but that's not really what we're talking about here.  I won't harp on it.

/*
 * $result = mysql_query($sql,$connection) or die("Couldn't execute $sql query. <br> mysql error: ".mysql_error());
 */

// very similar, but the order of the arguments is reversed...
$result = mysqli_query( $connection,$sql );
// ... and **specifying the connection is no longer optional**
// (i.e., with ext/mysql, if you left out $connection it would assume you want the last-used connection.
//  ext/mysqli _does not allow that_:
// mysqli_query( $sql );  // this emits a WARNING and then FAILS!)

/*
 *while($var = mysql_fetch_assoc($result)){
 *    #  . . .
 */

// very much the same.
while( $var = mysqli_fetch_assoc( $result ) ){
    #  . . .

If I change to mysqli in the connection string, does that mean the code will break anywhere I used mysql within that code set? ie. I will have to change ALL code all at once for an entire site or it won't work?

You can use both extensions in the same script, but they are not interoperable: mysqli can't use mysql connections, you can't switch extensions mid-code, and so forth. They are different functions and don't talk to each other.

Can I just add the "i" to mysql or are there other changes required to effect the update?

See above; there's more to it than just adding "i". All in all, however, it's not difficult: just tedious.

If everything had worked out as planned, then coders would have been trying out mysqli on new projects for some time now, and would be wholly comfortable with what changes would need to be made on their older projects. But we all know how that works (even assuming everyone had been successfully informed).

Of course, there is a lot of functionality that ext/mysqli offers that ext/mysql doesn't: prepared statements (**VERY** good for efficiency _and_ security), bound parameters (VERY good for efficiency/convenience), and object-oriented api* (plus a procedural api, very similar to the old ext/mysql)... lots of stuff. Check it out as you have the time.

Is mysql the only thing being deprecated?

See here. Short answer, no, but it's the only thing most people will notice.

I might as well do everything at once.

Recommendations:

• Start with a single project.
• Duplicate it somewhere (e.g., locally) so you can experiment without screwing anything up.
• Change only one statement at a time and test that the change was successful (your script should FAIL after that).
• If it works, change the next statement.
• When it seems to be going smoothly, change a whole sequence of statements at once.
• Repeat.

When you can do it comfortably, continue with your other projects.

---------------------------------------------------------------------------
* an object-oriented version of your example code: much easier/cleaner, IMO, but there's nothing wrong with the procedural approach at all.

<?php
$connection = new mysqli( $server,$user,$pass,$db_name );
$result = $connection->query( $sql );
if( $result ){
    while( $var = $result->fetch_assoc() ){
        #  . . .
    }
}

[kuau]

Dear Adrian: Wow, what a great response! Thank you so much. It definitely answered all my concerns and made perfect sense. Thanks also for making me not feel like such a dolt for not knowing about mysqli.

I spent all afternoon going through the site I am currently building and it wasn't nearly as much work as I thought. My new versions of the code were very close to yours, except the one for $result. The only replacement code I could find had that funny arrow. Daniel tried to explain that to me at some point but it didn't stick. I do remember how you guys told me not to use "or die" but I'm the only one who sees the error when I am coding, so it is easier for me.

$connection = mysqli_connect($server,$user,$pass,$db_name) or die("Error ". mysqli_error($connection));

$result = $connection->query($sql) or die("MySQL Error: " . mysqli_error($connection));
// which is better the one above or the one below?
$result = mysqli_query( $connection,$sql );

I just realized that what I was using for $result is like your OO version. If you think that is better, I might as well use it seeing as I have to change to something new anyway. Please explain what the arrow means again. I think

if( $result ){

might mean if the result set is not an error, or if the result of the query exists or is != '' then do the code (?).

When the result is one value, should you use

while($row = mysqli_fetch_row($result)){

rather than

while($row = mysqli_fetch_assoc($result)){

Oh, I guess for OO I use

while( $var = $result->fetch_assoc() ){

or

while( $var = $result->fetch_array() ){

or

while( $var = $result->fetch_row() ){

I'm still not entirely sure what the difference is between fetch_row or fetch_array. Thanks so much for helping me through this transition. I really felt lost earlier when I thought all my sites were going to crash & burn. I wouldn't be able to sleep nights.

Mahalo plenty!!! e

[traq]

I just realized that what I was using for $result is like your OO version. If you think that is better, I might as well use it seeing as I have to change to something new anyway. Please explain what the arrow means again.

The arrow points at a property (≈ variable) or method (≈ function) of an object: $connection is a mysqli object, which has a query() method. Hence, $connection->query().

As to whether or not you should use the object-oriented approach, just because I like it shouldn't be the deciding factor for you. It's a whole different way of coding. I'd certainly encourage you to give it a try (but not on anything critical). You might love it, or it might be incredibly frustrating (or both). OOP is not a prerequisite for good coding.

I think

if( $result ){

might mean if the result set is not an error, or if the result of the query exists or is != '' then do the code (?).

If mysqli_query() fails, it returns FALSE (if( $result ) is similar to if( $result != false )). If mysqli_query is successful, then the condition will be true, and your code to get the results will execute.

When the result is one value, should you use

while($row = mysqli_fetch_row($result)){

rather than

while($row = mysqli_fetch_assoc($result)){

No; the difference between mysqli_fetch_row() and mysqli_fetch_assoc() is in how the results are retrieved: fetch_row returns a list (numerically indexed array; i.e., [0=>value_1,1=>value_2]) of the values in a result row, while fetch_assoc returns a map (associative array; i.e., [col_1=>value_1,col_2=>value_2]) of the row.

[kuau]

Thanks, Adrian. I still have a few questions if you don't mind.

1. So you use "if( $result ){" instead of "or die" in order to control the error message display?

2. I use the following format on a page of statistics to keep the code compact. I can't figure out how to successfully convert this code to mysqli:

<?php $result = mysqli_query("SELECT COUNT(*) FROM client WHERE country = 'Japan'"); echo number_format(mysqli_result($result,0,0));?>

3. I prefer associative arrays. Is there any advantage to using associative or numerically indexed arrays, or is it just personal preference?

4. Can you mix procedural style code with OOP style? I prefer $result->close(); to mysqli_free_result($result); because it is shorter.

5. I used to know how to use that yellow highlight you use in the posts, but can't find it anymore (?).

Mahalo!

[djr33]

Adrian knows this better than I do, so I haven't replied yet. But I can reply for a few of those questions:

1. So you use "if( $result ){" instead of "or die" in order to control the error message display?

I use die only when I believe that someone is trying to break the rules-- usually when I have an admin control panel and want to stop others from accessing it without a password. Otherwise, using if/else will be better so that you don't serve invalid half-pages.

3. I prefer associative arrays. Is there any advantage to using associative or numerically indexed arrays, or is it just personal preference?

I don't think there's anything wrong with either one. I also prefer associative arrays because it's clearly what's going on. On the other hand, numerical indices are shorter, so they could possibly make the code more compact, or even generalized (something like "look through all results/columns and echo the values"). Basically, I wouldn't use numerical indices unless I didn't care about what the columns were, which rarely happens. I can imagine some might do it to save space. It also might take a little bit less memory to use just numbers-- those are by default part of the array, while string indices are extra to process/store. So in some cases, you might do that for efficiency, but only when it becomes a problem.
One simple example would be when you only get one value from the database-- then you really don't care about the name of the index because it's always the same thing.

4. Can you mix procedural style code with OOP style? I prefer $result->close(); to mysqli_free_result($result); because it is shorter.

I'm not entirely sure about any possible conflicts that might come up, but in theory there's nothing wrong with mixing coding styles, except that it could become confusing. If it works for you, that's fine. But from what you said, I think you actually like OOP better, but you just don't know how to use it yet for everything (which isn't surprising-- it's confusing when you first start, and takes a long time to become intuitive-- I still in a way prefer the "logic" of function-based code, rather than OOP.)

5. I used to know how to use that yellow highlight you use in the posts, but can't find it anymore (?).

It's [icode] tags. They're no longer part of the "quick reply" box (I think they were a while ago) but you can type them by hand.

[traq]

So you use "if( $result ){" instead of "or die" in order to control the error message display?

among other things, yes. I've been throwing exceptions for error handling lately. But however you do it, the idea is to treat it just like any other "choice" in your program logic: if it worked, do this; if there was an error, do that.

I use the following format on a page of statistics to keep the code compact. I can't figure out how to successfully convert this code to mysqli:

<?php $result = mysqli_query("SELECT COUNT(*) FROM client WHERE country = 'Japan'"); echo number_format(mysqli_result($result,0,0));?>

aside: "compact" code is attractive to programmers, and sometimes is just fine. But there are things you need to consider:
1) compact code can sometimes make it impossible to have error handling (your example illustrates this: because mysqli_result* is called inside another function, there's no opportunity to catch possible errors).
* mysqli_result is not a function. There is no equivalent to mysql_result in ext/mysql. See my example below.

2) compact code is sometimes harder to "read" than longer, more explicit code, which can make debugging/modifications/adding new features difficult in the future.

3) compact code doesn't always equate to better performance (as some expect); in fact, the opposite is often the case.

rewriting your example with mysqli:

<?php
// assuming you already have a connection in $mysqli
$result = mysqli_query( $mysqli,"SELECT COUNT(*) FROM client WHERE country='Japan'");
if( $result ){
    $row = mysqli_fetch_row( $result );
    echo $row[0];
}

###  alternatively (you said you're using php 5.5, correct?  ###
$result = mysqli_query( $mysqli,"SELECT COUNT(*) FROM client WHERE country='Japan'");
if( $result ){
    // this is called _array dereferencing_
    // see: http://php.net/manual/en/language.types.array.php#example-88
    echo mysqli_fetch_row( $result )[0];
}

I prefer associative arrays. Is there any advantage to using associative or numerically indexed arrays, or is it just personal preference?

You can't use list with associative arrays (though you can use mysqli_fetch_array() to get both associative and numbered indexes).

Can you mix procedural style code with OOP style? I prefer $result->close(); to mysqli_free_result($result); because it is shorter.

sometimes. sometimes not. It's not intended to be mixed. much simpler not to.

rewriting your example in OO style:

<?php
$mysqli = new mysqli( 'server','user','pass','db_name' );

/* ... snip ... */

$result = $mysqli->query( "SELECT COUNT(*) FROM client WHERE country='Japan'" );
if( $result ){ 
    echo $result->fetch_row()[0]; 
}

If you're using this query repeatedly (e.g., different countries), you might look into prepared statements.

[kuau]

Dear Daniel: Great to hear from you! I hope all your projects are going well. Thanks for answering my questions. I'm not entirely sure I like OOP better - only where it is more compact. Haven't understood enough of it yet. Seems the benefit is mostly for portability and my sites will never be used with anything but MySQL. Do you use OOP for all your php?

Now I am testing the yellow highlight

[kuau]

Thanks, Adrian. All my sites are on shared servers at Bluehost/Hostmonster so I have no control over the versions of MySQL or PHP. One of the good things about Bluehost is they keep their software very up-to-date; however, that means I have to be ever vigilant about changes to defaults in php.ini and deprecated code (ha... I failed with mysqli!) lest my sites suddenly crash & burn.

PHP version 5.3.26
MySQL version 5.5.32-log

I'd like to see what you snipped out above because I am trying to redo my connection include in OOP style. This is what I have... (not that I know what %s means or why this is any more secure than or die)

$connection = new mysqli( $server,$user,$pass,$db_name );
if($mysqli->connect_errno){ printf("Connection failed: %s\n", $mysqli->connect_error); exit(); }

I tried the code

$result = $mysqli->query( "SELECT COUNT(*) FROM client WHERE country='Japan'" );
if( $result ){ 
    echo $result->fetch_row()[0]; 
}

and it gives the error PHP Parse error: syntax error, unexpected '[', expecting ',' or ';'. Not sure what I am doing wrong, but all I get is a blank page.

Oh, I read the php.net page on Prepared Statements and couldn't understand a single word. This is ALL it has under Introduction: Represents a prepared statement. I kid you not.

[traq]

I'd like to see what you snipped out above because I am trying to redo my connection include in OOP style.

I didn't "snip" anything, really, just indicating that there might be other code in between those two parts.

This is what I have... (not that I know what %s means or why this is any more secure than or die)

it's not more secure, really - in fact, exit() is simply an alias for die(). die() is just an "Easy" way to write an example. Just be careful of letting it survive to your production code. The "danger" here is that the error message you're outputting is likely to contain sensitive information about your server and/or database. On a "live" site, you wouldn't output the mysql error at all (you'd log it, for your own reference) and show the user a generic "oops! try again please!" message.

In general, die() is bad because it just stops everything in its tracks: usually resulting in the user looking at a cryptic, half-finished, and probably unusable page.

the %s is a string placeholder for the sprintf() function.

I tried the code

echo $result->fetch_row()[0];

and it gives the error PHP Parse error: syntax error, unexpected '[', expecting ',' or ';'. Not sure what I am doing wrong, but all I get is a blank page.

I misread your original post. I though you'd said that you were using PHP 5.5 (when you really said mysql 5.5). No, the function()['result_index'] trick won't work for you (it was introduced in PHP 5.4).

As a side note, PHP 5.3 is not really "up to date" - it's already entered its "end-of-life" cycle (it receives only critical security fixes). It's not "outdated," though. I would say running 5.2- is cause for concern, but 5.3+ is fine.

Oh, I read the php.net page on Prepared Statements and couldn't understand a single word. This is ALL it has under Introduction: Represents a prepared statement. I kid you not.

yeah, it's something you have to wrap your head around. It's helpful to know what a prepared statement is (in MySQL) before trying to figure out what those functions are doing.

- - - EDIT - - -

mysqli object-oriented api: prepared statement example

<?php
// connect
$mysqli = new mysqli( 'db_server','username','password','db_name' );

// prepare statement
// the ? is where your param will be inserted.  
//  It will be sanitized automatically (no need for real_escape_string / similar).
$stmt = $mysqli->prepare( "SELECT COUNT(*) FROM client WHERE country=?" );

// let's get counts for a few countries
$countries = array( 'Japan','USA','Australia','Brazil' );

// bind param lets you choose a variable that's value will be inserted into the ?
// it's a reference, so it doesn't need to exist yet (it's created now).
// "s" means MySQL sould treat this param as a string (it has nothing to do with the data type in PHP).
$stmt->bind_param( 's',$country );

// loop!
// note that I'm using $country - the variable we used for bind_param!
foreach( $countries as $country ){
    // do the query, using the current value of $country
    $stmt->execute();
    
    // bind_result lets us choose a variable for the column(s) in the result
    $stmt->bind_result( $count );

    // fetch the results (into the bound result variables)
    $stmt->fetch();

    // and print something
    print "There are $count clients in $country.";
}