Can I mix MySQL APIs in PHP?

Question

I have searched the net and so far what I have seen is that you can use mysql_ and mysqli_ together meaning:

<?php
$con=mysqli_connect("localhost", "root" ,"" ,"mysql");

if( mysqli_connect_errno( $con ) ) {
    echo "failed to connect";
}else{
    echo "connected";
}
mysql_close($con);
echo "Done";
?>

or

<?php
$con=mysql_connect("localhost", "root" ,"" ,"mysql");
if( mysqli_connect_errno( $con ) ) {
    echo "failed to connect";
}else{
    echo "connected";
}
mysqli_close($con);
echo "Done";
?>

Are valid but when I use this code what I get is:

Connected
Warning: mysql_close() expects parameter 1 to be resource, object given in D:\************.php on line 9
Done

For the first and the same except with mysqli_close(). For the second one.

What is the problem? Can't I use mysql_ and mysqli together? Or is it normal? Is the way I can check if the connections are valid at all? (the if(mysq...))

Solution 1

No, you can't use mysql and mysqli together. They are separate APIs and the resources they create are incompatible with one another.

There is a mysqli_close, though.

Solution 2

Just to give a general answer here about all three MYSQL API's with a reference:

You can't mix any of the three (mysql_*, mysqli_*, PDO) MYSQL API's from PHP together, it just doesn't work. It's even in the manual FAQ:

It is not possible to mix the extensions. So, for example, passing a mysqli connection to PDO_MySQL or ext/mysql will not work.

---

You need to use the same MySQL API and its related functions, from connection to querying.

• http://php.net/manual/en/mysqlinfo.api.choosing.php

Solution 3

Technically you can use as many separate connections as you want, while your problem is caused by a mere typo - you only cannot use resources from one extension with functions from another, which is quite obviously.

However, you should avoid multiple connections from the same script, no matter from single API or different ones. As it will burden your database server and exhaust its resources. So, although technically you can, you shouldn't mix different extensions in your code, save for the short period of refactoring.

Comments

[Sterling Archer]

mysql is deprecated, it only makes sense they wouldn't work together. Why are you trying to do so..?

[Andrei Bârsan]

You should avoid using mysql_* functions altogether. They're error-prone and unsafe, and they will be removed from PHP soon (they're marked as deprecated at the moment). [This great answer][0] goes into way more detail explaining why they are bad. [0]:stackoverflow.com/a/12860046/1055295

[fvu]

1) you insist on using an old shoddy interface (mysql) that's being marked as obsolete in the doc since eons 2) for some odd reason you want to mix it with its successor instead of doing the right thing and convert to the new one 3) you are so surprised it doesn't work that you ask on SO about it, although it should be pretty obvious that what you're doing is nonsense.

[John Conde]

possible duplicate of mysql_fetch_array() expects parameter 1 to be resource, boolean given in select

[Your Common Sense]

Yet another superstition to be added to thousands others. Everyone is so sure that it's honest function is "error prone", not a programmer, who had no idea how to use a function properly and scarcely would have that idea for the new one.

[Andrei Bârsan]

It's not a superstition. Of course you can write bad code with mysqli_* functions and good code with mysql_* ones. But the latter category is marked as deprecated since it's the inferior set of functions, not being able to support OO-style invocations or even prepared statements (to name just two examples). Given a choice of two tools to do the same job, one of which is clearly better in the long run and more flexible, isn't the correct answer obvious?

[Your Common Sense]

In PHP there is a helluvalot of functions not being able to support OO-style invocations which nobody is going to deprecate. What's wrong with them?

[Andrei Bârsan]

That wasn't the main point I was trying to make. The lack of prepared statement support and transactions is the biggest problem.

[Your Common Sense]

There is no lack of transactions. Are you really do believe that all those years PHP users were unable to use transactions? Can you prove it?

[Andrei Bârsan]

There is no direct support, such as mysqli::begin_transaction. All you could do is a mysql_query("START TRANSACTION"). And this makes error handling more cumbersome, while the mysqli variant throws an exception when something goes wrong during the transaction. This makes code much clearer and easier to read. Arguably, you could just emulate this functionality using a custom class and mysql_* calls, but that's a bit overkill, as it would just be manually implementing the mysqli_* ones.

[Your Common Sense]

Do you really believe that mysqli API have to be used as is, in the form of raw API calls right in the application code, without a custom class to be encapsulated in? Looking into recent question I shiver with pain seeing this approach. Do you really think mysqli have to be used this way?

[Andrei Bârsan]

Of course not. However, given the option of choosing between the deprecated functionality and the actively supported one for, say, rolling your own abstraction, why use the old functions?

[Your Common Sense]

Why use is another question (though I was depraved from choice). But I was asking why old mysql ext is "error prone" and "unsafe". And you failed to provide a proof, feeding me with either plain false (doesn't support transactions and like) or irrelevant (procedural style) stuff. But the truth is that there is nothing wrong with old mysql ext save for its users. And these users won't become any good by just changing the function names they use.

[Andrei Bârsan]

Yes, I acknowledged that from the very beginning - ` It's not a superstition. Of course you can write bad code with mysqli_* functions and good code with mysql_* ones. `. And by error-prone, I was mostly referring to the lack of prepared statements. You can escape the data before concatenating it to the query, sure, but it's very easy to forget about a field thus introducing a vulnerability. With prepared statements this is impossible. Again, this is related more to the programmer than to the tools, so like you said - the programmer is what matters the most in the end.

[Your Common Sense]

In that custom class which we agreed that have to be used either way, one can use an emulated prepared statements all right - an example can be seen right in the answer next to one you linked to. So, nothing wrong with the API again. Saying that old mysql ext is error prone and unsafe is a mere superstition. Or - if you like it this way - a scary tale intended to excuse a deprecation. But there is nothing wrong with mysql ext itself and never have been. The very PHP user who is as helpless as to be unable to use anything but raw API calls in the application code is the only problem.

[Andrei Bârsan]

I am now convinced by your arguments; you obviously have more experience than me. But then why are the mysql_* extensions marked as deprecated?

[Your Common Sense]

As far as I know, it's just people who responsible for the extension support decided to drop it. PHP team is not only people who supports various extensions. Say, Microsoft is responsible for Windows support. And they dropped XP support as of recent 5.5. Same goes for mysql - it's Oracle folks who maintain the extension and they decided to drop it. As simple as this. Yet I suspect the intention was largely supported by community because of the very same wrong belief we discussed here.

[Gordon]

@AndreiBârsan this should shed some light: wiki.php.net/rfc/mysql_deprecation

[Andrei Bârsan]

I see. Thanks for the information. Well, I guess asynchronous queries could be another thing that's important for a high-performance web application. I haven't mentioned them, though, since I so far haven't used them personally.

[cHao]

Although you should never need to close the connection anyway; objects clean themselves up when they're no longer referenced anywhere. (Not sure whether plain old resources do that, but objects can actually take advantage of RAII to a not-insignificant degree.)

[Explosion Pills]

@cHao not only that, but PHP will close any open MySQL connections when the script exits

[Doug]

While this is probably a good idea, but connection pooling was developed for this reason. When you have multiple web requests hitting a web server, you cannot easily use the same connection, so you open a new connection. Connection pooling saves the overhead on the app server and the database.

[Funk Forty Niner]

Some guy tried to tell me today that they've no problem/errors when mixing mysql_real_escape_string() with what the rest of their code being PDO. Is there something I didn't get here in my time with working with these different APIs? Am I the ignorant one here? This being for the "now deleted" question stackoverflow.com/q/34209127 only viewable by 10K+ members should anyone wonder. This in relation to $stmt3->execute(array('classID' => $_POST['class'],'studentID' => mysql_real_escape_string($substr))) - Am I missing something here?

[Rizier123]

@Fred-ii- You are right :) Reading the manual shows that you are correct. What probably happened is, that mysql_real_escape_string() will silently try make a connection with the default parameters which then worked for OP. So it just made the connection to get the character set. So OP has 2 connections

[Funk Forty Niner]

If the OP had at least told me/us that they probably had 2 seperate connections, I'd of probably agreed; they decided otherwise. However, I still can't see how that would still work. If it does, I'm baffled.

[Rizier123]

@Fred-ii- See: link_identifier It will use the default connection settings, which it gets with ini_get(). So it probably just works for OP with the default settings. I would just leave it and get some new coffee (☕☕☕).

[Funk Forty Niner]

You might like to add something about sqlsrv_query(). I just closed a question here stackoverflow.com/q/41263771

[RationalRabbit]

But not always, to my experience, so we always place a close at the bottom of the file.

[hanshenrik]

@FunkFortyNiner you're correct, mysql_real_escape_string should absolutely not be mixed with PDO, and if you do then SQL injection vulnerabilities and incorrectly escaped strings are likely to occur. that said, the PDO-version of mysql_real_escape_string() is called PDO::quote(), but warning: quote() adds quotes around the string, mysql_real_escape_string does not, so $str="'".mysql_real_escape_string($str)."'"; translates to $str=$pdo->quote($str);, NOT $str="'".$pdo->quote($str)."'"; as one might assume when porting it.