SQL/PHP INSERT INTO multiple rows with $row

Question

So pretty much my issue is that I need to send multiple SQL entries using information based on another SQL entry.

I've simplified the code down that I was using so it's easily understandable.

$sql = mysql_query("SELECT product FROM `cart` WHERE username = '".$user."' LIMIT 10");

while ($rowcart = mysql_fetch_row($sql)) {

    $sendorder = "INSERT INTO Orders (order_id, product) VALUES ('NULL', '".$rowcart[0]."')";
    mysql_query($sendorder);  

}

When I ran it, it had failed to work; so I tried to echo $sendorder to see exactly what was sending and it turns out it's copying the INSERT INTO part on each entry, instead of just copying the values.

Example output:

INSERT INTO Orders (order_id, product) VALUES ('NULL', 'Cakes')
INSERT INTO Orders (order_id, product) VALUES ('NULL', 'Sweets')
INSERT INTO Orders (order_id, product) VALUES ('NULL', 'Cakes')
INSERT INTO Orders (order_id, product) VALUES ('NULL', 'Brownies')
INSERT INTO Orders (order_id, product) VALUES ('NULL', 'Cakes')

enable errors by placing error_reporting(E_ALL), and then run it. Whatever error you see, share it. Also, share the columns details, so that someone can help you — Altaf Hussain
– Altaf Hussain, Commented Dec 22, 2014 at 13:26

Dan Bracuk · Accepted Answer · 2014-12-22 13:32:15Z

3

You said, "I need to send multiple SQL entries using information based on another SQL entry." The following approach is more efficient than what you are attempting. Note that I use neither php nor mysql so I might have some syntax errors.

insert into orders
(product)
select product
from cart 
where username = $user

As far as the limit 10 goes, if you want to restrict the person to 10 items, you should do something to ensure that only 10 rows go into the cart table.

answered Dec 22, 2014 at 13:32

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

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

Comments

Buse Gönen · Accepted Answer · 2014-12-22 13:41:57Z

Mysqli example

<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_database');

/* check connection */
if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
}

$stmt = $mysqli->prepare("SELECT product FROM `cart` WHERE username = ? LIMIT 10");
$stmt->bind_param('s', $user);
$stmt->execute();
$stmt->bind_result($product);

 while($stmt->fetch()) {
      $tvalue[] = $product;
 }


 $stmt = $mysqli->prepare("INSERT INTO Orders (product) VALUES (?)");
 $stmt->bind_param("s", $one);

foreach ($tvalue as $one) {
      $stmt->execute();
 }

 printf("%d Row inserted.\n", $stmt->affected_rows);

 /* close statement and connection */
 $stmt->close();

 /* close connection */
 $mysqli->close();
 ?>

kos · Accepted Answer · 2014-12-22 14:00:16Z

If i understand correctly, what you want to do is to send an unique query, you can do this by appending every value to be inserted at the end of a single query string:

<?php
    // code
    $sql=mysql_query("SELECT product FROM cart WHERE username='".$user."' LIMIT 10");
    $result=mysql_query($sql);
    if(mysql_num_rows($result)) {
        $rowcart=mysql_fetch_row("$result");
        $sendorder="INSERT INTO Orders (order_id, product) VALUES ('NULL', '".$rowcart[0]."')";
        while($rowcart=mysql_fetch_row($result))
            $sendorder.=", ('NULL', '".$rowcart[0]."')";
        mysql_query($sendorder);
    }
    // code
?>

vaso123 · Accepted Answer · 2014-12-22 13:22:40Z

0

I assume, your order_id is a primary key, and auto_increment. You can leave that:

INSERT INTO Orders (product) VALUES ('Cakes')

or if you really want to insert it, then use

INSERT INTO Orders (order_id, product) VALUES (NULL, 'Cakes')

if you add quotes ' around it, then it will be parsed as a string. And since, that is not a string, but integer, it will cause syntax error.

answered Dec 22, 2014 at 13:22

vaso123

12.4k4 gold badges37 silver badges68 bronze badges

Comments

Mike Miller · Accepted Answer · 2014-12-22 13:35:54Z

You should be able to do this in a single SQL statement something like this..

INSERT INTO Orders(order_id,product) 
SELECT null,product 
FROM cart 
WHERE username = $name    
LIMIT 0,10

To refactor even further I would suggest you probably dont need to insert the null value just do:

INSERT INTO Orders(product) 
SELECT product 
FROM cart 
WHERE username = $name    
LIMIT 0,10

If your table is structured to allow NULL in the order_id col then this will be populated as null by default.

And as Dan just said doesnt seem to be much point putting a limit on either

Collectives™ on Stack Overflow

SQL/PHP INSERT INTO multiple rows with $row

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Related