insert query using foreach loop receiving Uncaught Error: Call to a member function execute() on boolean

Question

I am trying to insert multiple rows at once to my db, I posted a question here the other day see here. Regarding my insert query only insert one row into the db, even if it was necessary for a 2 or more rows of data to be inserted into the DB. Due to the fact this insert query is inserting shopping cart data i am unaware of the number of items a user may insert into the db.

After spending a few days researching i found the method i am now using may be the best way to resolve the issue, this is my first time using a foreach loop, and creating a insert query this way, I am receiving the follow error:

Fatal error: Uncaught Error: Call to a member function execute() on boolean

Which i am aware means my prepare statement failed, but i was hoping someone could explain or demonstrate where i have gone wrong, as i am trying to learn from my mistakes. I am not sure if it is the way i have created my query or even the way i have executed it.

Code

$_query = "INSERT INTO ord_dets(Order_ID,custmer_ip,Resturant_ID,Resturant_name,City_name,
        Product_Id,Product_Name,Product_Price,item_sub) 
VALUES ";
        $_query_parts  = array();
        for($x=0;$x<count($OI); $x++){
        $_query_parts  = "('" . $OI[$x] . "', '" . $ip[$x] . "','" . $_SESSION['rest_id'][$x] . "','" . $rest_name[$x] . "',
            '" . $City_name[$x] . "','" . $Product_Id[$x] . "','" . $product_name[$x] . "','" . $prod_price[$x] . "','" . $item_sub[$x] . "')";
        }
       $query_run = $dbc->prepare($_query);
       echo $_query;

        if (!$query_run->execute()) {
            $insertError = "There was an error inserting data: " . $query_run->error;
      print "affected rows:" . $query_run->affected_rows; //how many records affected?         

    }
  }

The result is a boolean (`false`) because the query is failing. You need to check for errors before continuing, never assume that a database operation was successful. (Note: The query fails because it's incomplete, resulting in a syntax error. You forgot everything after the `VALUES` keyword. You *do* build a horribly SQL-injectable string that you may have *meant* to use. But don't do that. Because it's horribly SQL-injectable. Use query parameters.) — David, Jun 24 '16 at 12:55
Possible duplicate of [mysqli\_fetch\_array()/mysqli\_fetch\_assoc()/mysqli\_fetch\_row() expects parameter 1 to be resource or mysqli\_result, boolean given](http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1) — David, Jun 24 '16 at 12:57
@David i had values, but the post on here which i am following suggested taking them out, Oh and i am aware of the sql injections. I am one to make sure everything working then go back on myself and tidy up — user6456767, Jun 24 '16 at 13:01
I don't know what suggestion you're referring to, but attempting to execute an invalid SQL statement clearly isn't going to work. It seems highly likely that you misunderstood or misinterpreted that suggestion. A SQL query has to be complete and syntactically valid in order for it to execute. Databases can't read your mind. — David, Jun 24 '16 at 13:03

maxpovver · Answer 1 · 2016-06-24T13:06:35.043

Your SQL is generated incorrectly: INSERT INTO ord_dets(Order_ID,custmer_ip,Resturant_ID,Resturant_name,City_name, Product_Id,Product_Name,Product_Price,item_sub) VALUES (...,...,....)(...,...,....)(...,...,....)(...,...,....)(...,...,....)

This is how it looks like. Fix it ad there will be no error

Solution here is to add commas betweeen values: VALUES (...,...,....),(...,...,....),(...,...,....),(...,...,....),(...,...,....)

UPD: Actually I can't see where you add $query_parts to your query string

UPD2: this is how it should look to avoid sql injections:

$_query = "INSERT INTO ord_dets(Order_ID,custmer_ip,Resturant_ID,Resturant_name,City_name, Product_Id,Product_Name,Product_Price,item_sub) VALUES (?,?,?,?,?,?,?,?,?)";
$stmt = $dbc->prepare($_query);

for($x=0;$x<count($OI); $x++){
    if (!$query_run->execute([$OI[$x] , $ip[$x], $_SESSION['rest_id'][$x] ,
          $rest_name[$x] , $City_name[$x] , $Product_Id[$x] ,
          $product_name[$x] , $prod_price[$x] ,$item_sub[$x]])) )
    {
        insertError = "There was an error inserting data: " . $query_run->error; 
          print "affected rows:" . $query_run->affected_rows;
    }
}

Hi. Thank you for your answer, i had "VALUES (?,?,?,?,?,?,?,?,?)"; before, but the post i am following suggests to take them out, do i put the question marks back and then keep the rest of the query as it is? — user6456767, Jun 24 '16 at 12:59
in that case you would have to run that query for every part of data — maxpovver, Jun 24 '16 at 13:01

score 0 · Answer 2 · answered Jun 24 '16 at 13:00

change it like this to separated it with coma.

$_query = "INSERT INTO ord_dets(Order_ID,custmer_ip,Resturant_ID,Resturant_name,City_name,
        Product_Id,Product_Name,Product_Price,item_sub) 
VALUES ";
        $_query_parts  = array();
        $sep ="";
        for($x=0;$x<count($OI); $x++){
        $_query_parts  = "$sep('" . $OI[$x] . "', '" . $ip[$x] . "','" . $_SESSION['rest_id'][$x] . "','" . $rest_name[$x] . "',
            '" . $City_name[$x] . "','" . $Product_Id[$x] . "','" . $product_name[$x] . "','" . $prod_price[$x] . "','" . $item_sub[$x] . "')";
        $sep=",";
        }
      $query_run = $dbc->prepare($_query);
       echo $_query;

        if (!$query_run->execute()) {
            $insertError = "There was an error inserting data: " . $query_run->error;
      print "affected rows:" . $query_run->affected_rows; //how many records affected?         

    }
  }

@user6456767 - can you please post the output from the **echo $_query;** statement, so than i can see what i am doin wrong — Bernd Buffen, Jun 24 '16 at 14:30
INSERT INTO ord_dets(Order_ID,custmer_ip,Resturant_ID,Resturant_name,City_name, Product_Id,Product_Name,Product_Price,item_sub) VALUES. The above answer suggests have no value i.e question marks is the issue, but even that didn't work — user6456767, Jun 25 '16 at 13:43

insert query using foreach loop receiving Uncaught Error: Call to a member function execute() on boolean

2 Answers2

Linked

Related