Php -MySQL query failing - error Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean

Question

I have following php code :

<?php
    $connect = mysqli_connect("localhost","root", "","archit") or die("Couldn't connect to database");
    $str = "SET @id := (SELECT ID FROM users where Name ='$u_name'); SELECT UID FROM useratt where ID = '@id';";
    echo $str;
    $query12 = mysqli_multi_query($connect,$str);
    echo $query12;
    while($row = $query12->fetch_assoc())
    {
        $str1 = "SET @UID := '".$row."'; 
         SELECT AttributeValue FROM att_value where UID=@UID; 
         SET @AID := (SELECT AID FROM att_value WHERE UID=@UID);
         SELECT AttributeName FROM att_name WHERE AID=@AID;";
         echo $str1;
        $query1= mysqli_multi_query($connect,$str1);
        echo $row[0];
    }
?>

I get the generic error but when I run the same code on MySQL then it works without error. Can someone please help me what am I missing .

I even tried exception handling but it didn't help.

NOTE : code fails in the while loop condition.

I know that .. Have done proper search before posting it. Have checked all the already answered posts but it didnt help. The connection is working properly. Other queries work properly. — Ria Sen, Mar 31 '17 at 05:15
if I run it in mysql console I get. But when I run through Php i get an error. When I print $query12 it prints "1" — Ria Sen, Mar 31 '17 at 05:18
Exactly..because it mysqli_multi_query returns boolean..I am posting right way to do this — Pramod Patil, Mar 31 '17 at 05:19
use new mysqli function for making connection, may fix the error — Sudhakar, Mar 31 '17 at 05:24

Pramod Patil · Accepted Answer · 2017-03-31T05:33:23.100

The mysqli_multi_query() executes one or multiple queries which are concatenated by a semicolon.To retrieve the resultset from the first query you can use mysqli_use_result() or mysqli_store_result(). All subsequent query results can be processed using mysqli_more_results() and mysqli_next_result().mysqli_multi_query() only returns FALSE if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.

if (mysqli_multi_query($connect,$str))
 {

     do
    {
        if ($result=mysqli_store_result($connect)) {

          while ($row=mysqli_fetch_row($result))
            {
                    $str1 = "SET @UID := '".$row."'; 
                    SELECT AttributeValue FROM att_value where UID=@UID; 
                    SET @AID := (SELECT AID FROM att_value WHERE UID=@UID);
                    SELECT AttributeName FROM att_name WHERE AID=@AID;";
                    echo $str1;
                    $query1= mysqli_multi_query($connect,$str1);
                    echo $row[0];
            }

          mysqli_free_result($result);
          }
    }
    while (mysqli_next_result($connect));
}

It will be great if you explain your answer so that it is easy for others to understand the reason. — Ria Sen, Mar 31 '17 at 05:29

score 1 · Answer 2 · answered Mar 31 '17 at 05:27

A. The first problem is your SELECT, you write:

SELECT UID FROM useratt where ID = '@id';

You must remove the quote at @id variable to this:

SELECT UID FROM useratt where ID = @id;

B. After mysqli_multi_query execute, you must add two others call to get result:

Your query will return two result, first result is for set variable. And the second is that your data query. Thus, we next to move next to second result to get data:

mysqli_next_result($connect);
After move next to second result, we must get result using mysqli_store_result:

$query12 = mysqli_store_result($connect);

Hope this can help you!

score -1 · Answer 3 · answered Mar 31 '17 at 05:26

<?php
    $connect = mysqli_connect("localhost","root", "","archit") or die("Couldn't connect to database");
    $str = "SET @id := (SELECT ID FROM users where Name ='$u_name'); SELECT UID FROM useratt where ID = '@id';";
    echo $str;
    $query12 = mysqli_multi_query($connect,$str);
    echo $query12;
    while($row = mysqli_fetch_assoc($query12))
    {
        $str1 = "SET @UID := '".$row."'; 
         SELECT AttributeValue FROM att_value where UID=@UID; 
         SET @AID := (SELECT AID FROM att_value WHERE UID=@UID);
         SELECT AttributeName FROM att_name WHERE AID=@AID;";
         echo $str1;
        $query1= mysqli_multi_query($connect,$str1);
        echo $row[0];
    }
?>

mysqli_multi_query returns boolean not resource id – Pramod Patil Mar 31 '17 at 05:27 — Pramod Patil, Mar 31 '17 at 05:27

Php -MySQL query failing - error Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean

3 Answers3