mysql_* is being deprecated?!

[james438]

I had heard about mysqli before, but always thought of it as a type of MySQL lite. Does this mean that mysql_* functions will only be used with older versions of MySQL and limited functionality with the latest versions of MySQL? I'm just a bit surprised since I have been using mysql_* functions for years without much thought. When was this announced? Does dev.mysql.com have any announcements about this?

[djr33]

I think this is within PHP, not within MySQL. I could be wrong.

mysqli is "better". But I, like you, am in the bad habit of using mysql functions because that's what I learned with. If I get into any serious web design projects I may switch over, but at the moment I'm in a "use what works" situation with my coding, not doing any web-design-focused projects. (Eg, using databases to organize data for my unrelated-to-the-web research projects.)

There's a practical side to this, though: it may be deprecated, but it'll be around for a very long time. And so many scripts (eg, all of the forums out there) rely on the mysql_ functions that I'd be surprised to see them go in any permanent sense. We might start seeing more PHP deprecated warnings, but that's about it, at least for a while.


I think traq knows more about this. I've seen him mention it a few times, so I look forward to hearing what he says. I don't know much about it, except that it's a good idea to look into it soon.

[bernie1227]

Traq said a bit about it here

[traq]

Right, ext/mysql is not recommended for new development and will be deprecated in the future.

The announcement was made in 2004 (!) when PHP 5 was released, but there is no timeframe for its removal from the PHP core. Basically, it is supported only because it is in such heavy use - people should have been using this time to update their codebase (but, of course, we all know how well that strategy works).

ext/mysql uses the libmysql library, while ext/mysqli uses mysqlnd (mysql native driver). mysqlnd is completely integrated with the PHP core and offers better performance and security, along with fuller access to MySQL's capabilities, including prepared statements, bound parameters, transactions, and so forth...

mysqli has a dual object-oriented/procedural interface. Personally, I hate that they did it that way, but they kinda had to: for the most part, you can "lazy-upgrade" your old scripts by adding an " i " to all of your mysql_* function names (mysql_query() becomes mysqli_query(), etc.), with minimal trouble. But the OO interface is awesome, and much nicer and easier to use (IMO).

this is the Phrase Center snippet I use:

[COLOR="#808080"]--------------------------------------------------
[B]# If at all possible, you should avoid using the mysql_* functions. #[/B]
ext/mysql is outdated and scheduled for deprecation. It is no longer recommended for new projects, and existing code should be updated to avoid performance and security problems. Using [URL="http://php.net/mysqli"]ext/mysql[b][COLOR="#008000"]i[/COLOR][/b][/URL] or the [URL="http://php.net/pdo"]PDO class[/URL] is recommended. Read more about [URL="http://php.net/mysqlinfo.api.choosing"]choosing an API[/URL] here.[/COLOR]

Does this mean that mysql_* functions will only be used with older versions of MySQL and limited functionality with the latest versions of MySQL?

the mysql_* functions can still be used with current (5+) versions of MySQL, it's just less efficient (since it wasn't written specifically for PHP) and doesn't support all of the features that newer versions of MySQL offers.

[djr33]

for the most part, you can "lazy-upgrade" your old scripts by adding an " i " to all of your mysql_* function names (mysql_query() becomes mysqli_query(), etc.), with minimal trouble.

To what extent does this not work? Are there limitations?

[traq]

The biggest thing I can think of, off the top of my head, is in mysqli_connect():

<?php

# mysql style
$db = mysql_connect( 'host','user','pass' );
mysql_select_db( 'name',$db );
$result = mysql_query( "SELECT 'Hello!'",$db );
if( $result ){
    $row = mysql_fetch_row( $result );
    print $row[0];
}

# mysqli style
$db = mysqli_connect( 'host','user','pass','name' );
// hmm - no extra step selecting the DB!
$result = mysqli_query( "SELECT 'Hello!'",$db );
if( $result ){
    $row = mysqli_fetch_row( $result );
    print $row[0];
}

# mysqli object-oriented style
$db = new mysqli( 'host','user','pass','name' );
$result = $db->query( "SELECT 'Hello!'" );
if( $result ){
    $row = $result->fetch_row();
    print $row[0];
}

There's other stuff like that, but it's minor.

You need to take the time to learn it if you want to use the new features, however, and I say it's well worth it.

Edit:

going back to james' question,
Also, I don't know for sure, but I think you can use ext/mysqli with *older* versions of MySQL too.
Of course, if your webhost runs MySQL 4, they probably run PHP 4 also, and you should really just go find a new webhost.

[djr33]

Thanks for the overview on that. As I said, it's something I should do. But for the moment, I'm stuck in bad habits.

// hmm - no extra step selecting the DB!

Hmm... that seems a little odd though. Sure, it's convenient, but that's really two separate processes, like finding a bank and then making a transaction. But 90% of the time you're doing the same thing, so I guess it's a shortcut.

[bernie1227]

Does the 'i' stand for anything?

[traq]

Does the 'i' stand for anything?

"improved", supposedly.
("supposedly that's what it stands for," not "supposedly it's improved." )

that seems a little odd though. Sure, it's convenient, but that's really two separate processes, like finding a bank and then making a transaction. But 90% of the time you're doing the same thing, so I guess it's a shortcut.

yeah, it's a shortcut. If you need to change it (and want to reuse the same connection), you can:

<?php

$db = new mysqli( 'host','user','pass','myDatabase' );
$db->select_db( 'myOtherDatabase' ); // assuming it's accessible on the same connection, that is