difference between mysqli_query and mysqli_real_query

Question

What is the difference between mysqli::query and mysqli::real_query?

OR

What is the difference between mysqli_query and mysqli_real_query?

Not sure why this question was closed. It probably *should* be answered by the manual, but it's not very clear there, and the text of the "not constructive" close reason doesn't seem to fit it at all. — IMSoP, Aug 17 '14 at 16:14
Yeah, this should definitely not be closed and especially for the reasons listed to close this. I just came here for this exact question and glad I'm did. Moderators need to seriously stop being so click happy when they see a "Close" link on a post. — The Duke Of Marshall שלום, Sep 19 '14 at 17:25
@SazzadHossainKhan I already did, but apparently was outvoted 3 to 2 when it was reviewed by other users ([there's a report here, but you can probably only see it with a high-rep account](http://stackoverflow.com/review/reopen/5560191)). I don't think The Duke of Marshall has the reputation to cast such a vote. — IMSoP, Sep 19 '14 at 20:05
I also request that this question be re-opened. I believe the question is perfectly valid, and well formed. I viewed the PHP manual before coming here, and was also stymied by the explanation. Now I understand that, in the (undocumented) code I was reviewing, the error check on the _$mySQLi->real_query()_ function tests for a failed SQL command, while the error check on the **separate** _$mySQLi->store_result()_ function tests for a non-null result set. — DUHdley d'Urite, Jan 26 '15 at 06:40

score 20 · Answer 1 · edited Nov 20 '12 at 13:48

20

mysqli::query will return a result if there is any.

mysql::real_query will return true on success or false if not

You could have seen this in the php doc:

query,
real_query.

edited Nov 20 '12 at 13:48

Bruno Vieira

3,884
1
23
35

answered Nov 20 '12 at 09:06

Thomas

689
3
8
14

11

help me writing better answers by describing me why you downvoted it – Thomas Nov 20 '12 at 09:55

score 19 · Accepted Answer · answered Nov 20 '12 at 09:04

19

Look at the documentation of mysqli_query():

Functionally, using this function is identical to calling mysqli_real_query() followed either by mysqli_use_result() or mysqli_store_result().

From what I understand real_query actually executes the query, and use/store_result initiates the process of retrieving a result set for the query. query() does both.

answered Nov 20 '12 at 09:04

Bgi

2,513
13
12

4

The documentation is different now: "**For non-DML queries** (not INSERT, UPDATE or DELETE), this function is **similar** to calling mysqli_real_query() followed by either mysqli_use_result() or mysqli_store_result()." – Beat Feb 18 '16 at 14:44

score 5 · Answer 3 · edited Oct 01 '19 at 08:14

5

Bit late, but the biggest advance for me is the RAM usage when these functions are called with default settings: with mysqli_real_query() you do not copy the whole result into the RAM, which mysqli_query() by default does (although it can be changed by using the $resultmode parameter).

edited Oct 01 '19 at 08:14

Your Common Sense

156,878
40
214
345

answered Sep 19 '19 at 10:35

Marco

3,470
4
23
35

1

you can have the same with mysqli_query(), just tell it to make an unbuffered call – Your Common Sense Oct 01 '19 at 05:58

Leopoldo Sanczyk · Answer 4 · 2019-10-03T05:14:36.910

3

In practice there is another difference I don't see in other answers. You could need to use mysqli_real_query() for a CALL statement.

If you are calling a stored procedure, then it can return more than one result. mysqli_query() will fetch the first result, but could be more results that needs to be fetched, and that would cause an error. You need to use mysqli_real_query(or mysqli_multi_query()) to fetch those result sets.

Sadly that explanation is in the stored procedures section of the PHP docs, so is hard to reach.

I let you a code example, just in case, calling a procedure that suppose to return several result sets using mysqli::real_query:

$query="CALL storedProcedure()";
if($conn->real_query($query)){              
    do{
        if($result=$conn->store_result()) {
            while($row=$result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
    }while($conn->more_results() && $conn->next_result());          
}

edited Oct 03 '19 at 05:14

answered Oct 01 '19 at 05:51

Leopoldo Sanczyk

1,529
1
26
28

you can do that with mysqli_query() query as well. check your premises – Your Common Sense Oct 01 '19 at 05:57
@YourCommonSense Manual says explicity: "Result sets returned from a stored procedure **cannot be fetched correctly using mysqli_query()**. The mysqli_query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli_query() to fail returning the user expected result sets." – Leopoldo Sanczyk Oct 01 '19 at 06:05
Well, the manual is wrong. Just call the usual routine with next_result() and store_result() and get those additional result sets as well – Your Common Sense Oct 01 '19 at 06:27
@YourCommonSense I learned it the hard way years ago, and had to change my code, but maybe new versions of PHP allow it. Sounds rare the Manual is wrong, but is not impossible. Will try it if I can, meawhile seems fair to give an advice extracted from the official documentation. – Leopoldo Sanczyk Oct 01 '19 at 06:42
That's only a minor algorithmical inconvenience, not a some inherent feature. you just don't call store_result() on the first pass, that's all. in your loop you can just move it in the while clause and then you can change real_query to query() without any problem.basically, which function you are using is not related to stored procedures. – Your Common Sense Oct 01 '19 at 07:11

difference between mysqli_query and mysqli_real_query

4 Answers4

Linked