Updating deprecated code for MySQL 5.5.+

Quote:

---

Originally Posted by traq

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...

Code:

---

$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??!

Quote:

---

Originally Posted by kuau

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:

Quote:

---

Originally Posted by http://article.gmane.org/gmane.comp.php.devel/66894

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.

---

Quote:

---

Originally Posted by kuau

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 Code:

---

<?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 ) ){
    #  . . .

---

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

Is mysql the only thing being deprecated?

---

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

Quote:

---

Originally Posted by kuau

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 Code:

---

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

---

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.

Code:

---

$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

Code:

---

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

Code:

---

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

---

rather than

Code:

---

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

---

Oh, I guess for OO I use

Code:

---

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

---

or

Code:

---

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

---

or

Code:

---

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 :)

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

I think

Code:

---

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.

Quote:

---

Originally Posted by kuau

When the result is one value, should you use

Code:

---

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

---

rather than

Code:

---

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.

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:

Code:

---

<?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!

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.)

Quote:

---

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.

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

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 Code:

---

<?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 Code:

---

<?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];
}

---

Quote:

---

Originally Posted by kuau

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).

Quote:

---

Originally Posted by kuau

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 Code:

---

<?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.

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 :)

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)

Code:

---

$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

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.

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

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.

Quote:

---

Originally Posted by kuau

I tried the code

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.

Quote:

---

Originally Posted by kuau

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 Code:

---

<?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.";
}

---

Quote:

---

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?

---

OOP is in a sense a different perspective on coding-- it's about things, and manipulating properties of those things, rather than step-by-step procedures.
I should add that I agree with Adrian-- mixing OOP and other code would lead to problems in general. In the case of mysqli where there are equivalent OOP and non-OOP options, though, it's probably fine to switch, since they do exactly the same thing (as long as you check that they really do exactly the same thing!). In general, such equivalent functions won't exist (although in theory you could create them for most operations).

OOP isn't necessarily about using anything other than MySQL. It's a way to manipulate things ("objects") such as a MySQL connection. I do use OOP when it seems useful; I know others who use it for almost everything. I use it when I want to think about my code in terms of things-- for example, if I want to create a template, I might make that an object (a class called "template") and then manipulate things like $template->footer('some text'), and so forth. Rather than a series of unconnected functions, the result is all tied together with reference to an object like $template.

I think of OOP as a third level. You can draw a parallel to math sort of like this:
simple addition = line-by-line procedural code, just the basics; limited and slow
multiplication = functional programming, loops, and so forth; more powerful, faster
exponents = OOP: functions within functions, functions with properties, objects you can modify, etc.; abstract and most powerful

So in the same way that multiplication is more powerful than addition, exponents allow you to manipulate multiplication (2^4 = 2*2*2*2). In the same sense, you can get to that "next level" of programming with OOP and do some very complicated things. Or, in another way, it's just a nice way to think about certain kinds of code.

Thanks, Daniel. Sounds as if you consider OOP to be a more evolved mode of programming. I guess I haven't seen enough of it to see its power. The concept of manipulating objects sounds a bit like modular programming. Any similarity?

Are most people moving toward OOP? ie. is the procedural method being phased out? What percentage of programmers are using OOP as opposed to procedural style? Is procedural code considered old-fashioned?

Mahalo, e :)

[ I'm actually writing a database for feral cat colony caretakers... the code calculates the numbers in the colonies... might as well use the actual code so I don't have to change it. ]

It took me a while, but I have upgraded this site to PHP version 5.4.16. So now when I run this code...

Code:

---

$result = $mysqli->query("SELECT COUNT(*) FROM cat"); if($result){ echo $result->fetch_row()[0]; } $result->close();

---

I get this error: PHP Fatal error: Call to a member function query() on a non-object

Thanks for the example of prepared statements. I'll see if I can adapt it to this script as soon as it starts giving a count instead of an error. Thanks. :)

Daniel's right about OO: it's not just a coding "style," it's an approach. The example in my last post, for instance, shows mysqli's OO api, but it's still more-or-less functional programming. "Do this, then this, then this."

As for the "more evolved" comment, no; programming styles aren't really arranged like that. OO allows you to do some pretty cool things, but you have to know when it's "overkill." Procedural, functional, and other styles all have their places, too.

Quote:

---

Originally Posted by kuau

PHP Fatal error: Call to a member function query() on a non-object

---

That means $mysqli is not a mysqli object (most likely, your connection failed).

I have decided to abandon OOP. The connection was working for the other pages, but I just couldn't get that code to work no matter what. I wasted an entire day on it & got zero done except upgrading to PHP 5.4. So I redid the code in procedural style and it is now working. Many thanks to both you and Daniel for explaining OOP to me. It helped me decide that it is not for me. I'm sure if it had worked I would have felt differently, but I just see no point in going through that time-consuming torture. Thanks again for your patience and expert tutoring.

I hope they never phase out procedural style. :)

For some, OOP is seen simply as better. So they use it all the time.
But for many, it's just one tool among others.
I'd say that all of the best programmers are able to use OOP and do sometimes, but it hasn't replaced anything. It has just added to it.
In the case of MySQL, there may be a bit of logic behind thinking of the connection as an object, but beyond that, I see no reason that it needs to be OOP. Other times you might find that OOP really does the fit the project in a way that other approaches don't.

Well, maybe when the commands work, I'll take another look at it. Until then it is POOP to me. :)