Solved

multi_query usage

Posted on 2009-07-09
6
866 Views
Last Modified: 2013-12-13
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

0
Comment
Question by:jet-black
6 Comments
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 125 total points
ID: 24812613
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
 
LVL 5

Expert Comment

by:kingofnines
ID: 24812625
try this...

while($row = fetch_row($d))

instead of this...

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



Alan
0
 
LVL 5

Expert Comment

by:kingofnines
ID: 24812635
opps, sorry crx. I didnt see your post before I posted.

Alan
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 9

Expert Comment

by:xBellox
ID: 24812650
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
 
LVL 12

Author Closing Comment

by:jet-black
ID: 31601518
Thank you for the true solution.
INSERT statements were just for demonstration :)
0
 
LVL 12

Author Comment

by:jet-black
ID: 24812874
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question