While loop and PHP/MYSQL insert statement

Question

Am I able or how can I create a while loop to run through some insert statemtns. I have some code below that I have tried to use using i++ but I keep getting errors in relation to where I have put $i such as:

Object of class PDOStatement could not be converted to string

Its hard to explain the previous code that amounts to this insert setup, but basically I decalre some variables and then they get bind and inserted with the following code but I have like 30 thins that could be inserted hence the while loop. So the outcome should be supplier_name2 - supplier_name30 and same for address etc etc.

   while ($i <= 30):
      //LINE TWO
      $suppliername = $this->supplier_name.$i;
      if(!empty($suppliername)){
          $sql0.$i = "INSERT INTO suppliers (supplier_name, supplier_address) 
                          VALUES (:supplier_name.$i, :supplier_address.$i) ";   
          $st0.$i = $conn->prepare ( $sql0.$i );
          $st0.$i->bindValue( ":supplier_name".$i, $this->supplier_name.$i, PDO::PARAM_STR );
          $st0.$i->bindValue( ":supplier_address".$i, $this->supplier_address.$i, PDO::PARAM_STR);
          $st0.$i->execute();
          $this->id = $conn->lastInsertId();
          $supplierid.$i = $this->id = $conn->lastInsertId();
        }  
    $i++; 
    endwhile;

Any help appreciated. Ian

Elon Than · Accepted Answer · 2013-11-18 14:05:25Z

1

Expression $sql0.$i will be interpreted as string so PHP will try to use your PDOStatement as string. It's not the way you should use prepared statements.

Move your prepared statement before loop with some modifications.

$sql = "INSERT INTO suppliers (supplier_name, supplier_address) 
                      VALUES (:supplier_name, :supplier_address) ";   
$statement = $conn->prepare ( $sql );

Then perform bindValue() and execute() on $statement variable. Idea is to declared once and use few times. Also don't use $i in attributes names, it's unnecessary.

answered Nov 18, 2013 at 14:05

Elon Than

9,8154 gold badges29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

snookian Over a year ago

Yes I get an "Object of class PDOStatement could not be converted to string" on the line that contains " $st0.$i = $conn->prepare ( $sql0.$i );". Not really sure how to put the statement outside the loop while making it loop the require amounts of times

Elon Than Over a year ago

@snookian Put only lines from my answer outside of loop, anything else stays in loop.

snookian Over a year ago

This add the same line 30 times to the db, I have a table with thrity fields in it, all supplier_name and supplier_address with $i++ so for example supplier_name1, supplier_name2 etc, Now rather than write the statement for each I wanted to create a loop so If they add only the first 10 name and address its adds the first 10, if they add thrity names it adds 30

Elon Than Over a year ago

@snookian If you assign result of preapre() to lets say $prepared and then assign values and execute from this variable, then you'll add different rows. You're probably overwriting $statement just like you did in your code from question.

snookian Over a year ago

OK Im actually a bit lost

TabLand · Accepted Answer · 2013-11-18 14:08:58Z

0

In > "supplier_name.$i" the dot is literal. So it becomes "supplier_name.1", "supplier_name.2" etc

In ":supplier_address".$i the dot is not literal, it is used as the concatenation operator. Hence you get "supplier_name1" and "supplier_name2" etc. Hence no replacement occurs I assume.

answered Nov 18, 2013 at 14:08

TabLand

264 bronze badges

Comments

Krish R · Accepted Answer · 2013-11-18 14:22:09Z

Can you try this, below one insert supplier_name1 - supplier_name30 in tables.

          while ($i <= 30):
              //LINE TWO
              $suppliername = $this->supplier_name.$i;
              $supplier_address = $this->supplier_address.$i;
              if(!empty($suppliername)){
                $sql0 = "INSERT INTO suppliers (supplier_name, supplier_address)
                VALUES (:supplier_name, :supplier_address) ";
                $st0 = $conn->prepare ( $sql0 );
                $st0->bindValue( ":supplier_name", $suppliername, PDO::PARAM_STR );
                $st0->bindValue( ":supplier_address", $supplier_address, PDO::PARAM_STR);
                        $st0.$i->execute();
                        $this->id = $conn->lastInsertId();
                $supplierid = $this->id = $conn->lastInsertId();
              }
              $i++;
          endwhile;

Collectives™ on Stack Overflow

While loop and PHP/MYSQL insert statement

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related