MYSQLI INSERT MULTIPLE ROWS

this is my first post, and hopefully someone can help me with this problem I'm having.

I'm trying to take an item from a shopping cart table, and insert each row into the orders table. After they submit order, it should perform the action. Here is my code.

if(isset($_GET['submitOrder'])) {
   $timedate = $currentTime." at ".$currentDate;
   $order_number = rand(10,100);
   $cartOrder=$mysqli->query("SELECT * FROM cart WHERE members_id='".$members_id."'");
   $cartOrder_total = $cartOrder->num_rows;
   while($row=mysqli_fetch_assoc($cartOrder)) {
         $stmt = $mysqli->prepare("INSERT INTO orders(order_number, members_id, product_id, quantity, date_submitted) VALUES (?, ?, ?, ?, ?)");
         $stmt->bind_param('siiis', $order_number, $members_id, $row['product_id'], $row['quantity'], $timedate);
         $stmt->execute(); 
         $stmt->close();
   }
   header('Location: cart.php#orderplaced');
}

My problem is that it's only inserting one entry into the orders table. Even if there are 4 items in the cart, it still only enters the one.

Please if anyone can point me in the right direction, I'd appreciate it! It only enters one row right now, and I'm not sure why.

Here are my tables:

CREATE TABLE IF NOT EXISTS cart ( cart_id int(11) NOT NULL AUTO_INCREMENT, product_id int(11) NOT NULL, members_id int(11) NOT NULL, quantity int(11) NOT NULL, date_added varchar(255) NOT NULL, date_submitted varchar(250) NOT NULL, PRIMARY KEY ( cart_id ), KEY cart_id ( cart_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=153 ;

CREATE TABLE IF NOT EXISTS orders ( order_id int(11) NOT NULL AUTO_INCREMENT, order_number int(11) NOT NULL, members_id int(11) NOT NULL, product_id int(11) NOT NULL, quantity int(11) NOT NULL, date_submitted varchar(256) NOT NULL, PRIMARY KEY ( order_id ), UNIQUE KEY order_number ( order_number ) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=422 ;`

---

Without seeing the database schema I can't be sure, but I suspect this is to do with you using the for loop inside the while loop. This will attempt to do the exact same INSERT with the exact same values $cartOrder_total times.

Simply remove the for loop, leaving you with:

while($row=mysqli_fetch_assoc($cartOrder)) {
     $stmt = $mysqli->prepare("INSERT INTO orders(order_number, members_id, product_id, quantity, date_submitted) VALUES (?, ?, ?, ?, ?)");
     $stmt->bind_param('siiis', $order_number, $members_id, $row['product_id'], $row['quantity'], $timedate);
     $stmt->execute(); 
     $stmt->close();        
}

---

Which row you are seeing arrive through the INSERT is a clue to your iteration problem. Alternatively, you could just add in an ECHO and just see what data you are moving.

If you see only the first row of data submitted, that is a clue you are not iterating through the outer loop's data.

If you see only the last row of data submitted, then that is a clue you have jumped to the last row of the outer data instead of iterating through each one.

链接地址: http://www.djcxy.com/p/59696.html

上一篇: mysql关系慢

下一篇: MYSQLI INSERT MULTIPLE ROWS