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

mysql is deprecated, it only makes sense they wouldn't work together. Why are you trying to do so..? — Sterling Archer, Jul 05 '13 at 23:50
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. — fvu, Jul 06 '13 at 00:06
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — John Conde, Jul 06 '13 at 04:38
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]:http://stackoverflow.com/a/12860046/1055295 — Andrei Bârsan, Jul 05 '13 at 23:52
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. — Your Common Sense, Jul 06 '13 at 06:47
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? — Andrei Bârsan, Jul 06 '13 at 08:22
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? — Your Common Sense, Jul 06 '13 at 08:43
That wasn't the main point I was trying to make. The lack of prepared statement support and transactions is the biggest problem. — Andrei Bârsan, Jul 06 '13 at 09:23
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? — Your Common Sense, Jul 06 '13 at 09:25
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. — Andrei Bârsan, Jul 06 '13 at 09:44
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](http://stackoverflow.com/q/17501590/285587) I shiver with pain seeing this approach. Do you *really* think mysqli have to be used this way? — Your Common Sense, Jul 06 '13 at 09:49
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? — Andrei Bârsan, Jul 06 '13 at 09:54
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. — Your Common Sense, Jul 06 '13 at 10:05
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. — Andrei Bârsan, Jul 06 '13 at 10:12
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. — Your Common Sense, Jul 06 '13 at 10:24
I am now convinced by your arguments; you obviously have more experience than me. But then why are the `mysql_*` extensions marked as deprecated? — Andrei Bârsan, Jul 06 '13 at 12:02
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. — Your Common Sense, Jul 06 '13 at 12:28
@AndreiBârsan this should shed some light: https://wiki.php.net/rfc/mysql_deprecation — Gordon, Jul 07 '13 at 09:44
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. — Andrei Bârsan, Jul 07 '13 at 10:04

score 73 · Accepted Answer · answered Jul 05 '13 at 23:53

73

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.

answered Jul 05 '13 at 23:53

Explosion Pills

188,624
52
326
405

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.) – cHao Jul 05 '13 at 23:54
@cHao not only that, but PHP will close any open MySQL connections when the script exits – Explosion Pills Jul 05 '13 at 23:56
1

But not always, to my experience, so we always place a close at the bottom of the file. – RationalRabbit Dec 31 '17 at 21:23

score 17 · Answer 2 · edited Jul 30 '15 at 23:08

17

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

edited Jul 30 '15 at 23:08

Funk Forty Niner

74,450
15
68
141

answered Jul 09 '15 at 13:59

Rizier123

58,877
16
101
156

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 http://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? – Funk Forty Niner Dec 10 '15 at 19:05
1

@Fred-ii- You are right :) Reading the [manual](http://php.net/manual/en/function.mysql-real-escape-string.php#refsect1-function.mysql-real-escape-string-errors) shows that you are [correct](https://3v4l.org/vrFT0#v432). What probably happened is, that [`mysql_real_escape_string()`](http://lxr.php.net/xref/PHP_5_2/ext/mysql/php_mysql.c#1684) will [silently *try* make a connection with the default parameters](http://lxr.php.net/xref/PHP_5_2/ext/mysql/php_mysql.c#php_mysql_get_default_link) which then worked for OP. So it just made the connection to get the character set. So OP has 2 connections – Rizier123 Dec 10 '15 at 19:49
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. – Funk Forty Niner Dec 10 '15 at 19:53
@Fred-ii- See: [link_identifier](http://php.net/manual/en/function.mysql-real-escape-string.php#refsect1-function.mysql-real-escape-string-parameters) 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 (☕☕☕). – Rizier123 Dec 10 '15 at 19:56
You might like to add something about `sqlsrv_query()`. I just closed a question here http://stackoverflow.com/q/41263771/ – Funk Forty Niner Dec 21 '16 at 13:40
@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. – hanshenrik Apr 04 '19 at 08:27

Your Common Sense · Answer 3 · 2015-07-09T14:37:11.503

2

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.

edited Jul 09 '15 at 14:37

answered Jul 09 '15 at 14:08

Your Common Sense

156,878
40
214
345

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. – Doug Jul 09 '15 at 14:43

score -3 · Answer 4 · 2018-04-04T18:59:29.320

-3

MySQLi is a lot more secure than MySQL which is anyway now deprecated. That's why you should stick with MySQLi and also you can't mix them as they are both different.

edited Apr 04 '18 at 18:59

answered Jul 05 '13 at 23:55

Can I mix MySQL APIs in PHP?

4 Answers4

Linked

Related