multi_query usage

Hi.

I want to use mysqli multi_query function.
However I am facing with the following problem.

Can you explain why it gives an error and what is the solution for that.
$conn = new mysqli(host, user, pass, dbname) or die(mysqli_connect_error());
 
$d = $conn->query("SELECT COUNT(id) FROM table2");
while ($row = $d->fetch_row())
{
    echo $row[0];
}
exit();
 
 
 
The following throws an error:
 
$conn->multi_query("INSERT INTO table2(name) VALUES('a'); INSERT INTO table2(name) VALUES('b');");
$d = $conn->query("SELECT COUNT(id) FROM table2");
while ($row = $d->fetch_row())
{
    echo $row[0];
}
exit();
 
error:
 
Fatal error: Call to a member function fetch_row() on a non-object in .....

Open in new window

LVL 12
jet-blackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
First, you don't need a to send multiple queries when inserting multiple rows to the same table. You could insert both rows with one insert statement, like this:

INSERT INTO table2(name) VALUES('a'),('b');

You should allways test for errors:

$d = $conn->query("SELECT COUNT(id) FROM table2");
if(!$d) die($conn->error);

This would show you this error:

Commands out of sync; you can't run this command now

When using multi_query(), you need to handle the results before you try to send more queries on the same connection. In this case there is no rows to fetch, so you can just skip each result, like this:

$conn->multi_query("INSERT INTO table2(name) VALUES('a'); INSERT INTO table2(name) VALUES('b');");
while($conn->more_results()) $conn->next_result();

http://php.net/manual/en/mysqli.multi-query.php
http://php.net/manual/en/mysqli.more-results.php
http://php.net/manual/en/mysqli.next-result.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kingofninesCommented:
try this...

while($row = fetch_row($d))

instead of this...

while ($row = $d->fetch_row())



Alan
0
kingofninesCommented:
opps, sorry crx. I didnt see your post before I posted.

Alan
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

xBelloxCommented:
It appears that  this error isn't from "multi_query()" function, it occurs because the line:

$d = $conn->query("SELECT COUNT(id) FROM table2");

Do not create an object in $d variable, so when you call "$d->fetch_row()" it give you the error that fetch_row() must be called from an object which $d is not.

Try something like this:


if ($d = $conn->query("SELECT COUNT(id) FROM table2")) {
    while ($row = $d->fetch_row()) {
        echo $row[0];
    }
    $d->close();
    exit();
}

Open in new window

0
jet-blackAuthor Commented:
Thank you for the true solution.
INSERT statements were just for demonstration :)
0
jet-blackAuthor Commented:
xBellox, thank you for your message.
However,

if ($d = $conn->query("SELECT COUNT(id) FROM table2")) {
    while ($row = $d->fetch_row()) {
        echo $row[0];
    }
    $d->close();
    exit();
}

The code block within if statement doesn't run because $d = $conn->query("SELECT COUNT(id) FROM table2") gives an error which is not return TRUE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.