How to insert multiple records to MySQL

Question

i have small page with cart. All things is storage in session. Now i need to save this data to mysql databse.

I think, that i need use foreach cycle to do this, but i cant construct it. Does anybody know the solution?

This is the function that displays cart.

function showCart() {
    global $db;
    $cart = $_SESSION['cart'];
    if ($cart) {
        $items = explode(',',$cart);
        $contents = array();
        foreach ($items as $item) {
            $contents[$item] = (isset($contents[$item])) ? $contents[$item] + 1 : 1;
        }
        $output[] = '<form action="cart.php?action=update" method="post" id="cart">';
        $total=0;
        $output[] = '<table>';
        foreach ($contents as $id_menu=>$qty) {
            $sql = 'SELECT * FROM menu WHERE id_menu = '.$id_menu;
            $result = $db->query($sql);
            $row = $result->fetch();
            extract($row);
            $output[] = '<tr>';
            $output[] = '<td><a href="cart.php?action=delete&id_menu='.$id_menu.'" class="r">Delete</a></td>';
            $output[] = '<td>' .$name. '</td>';
            $output[] = '<td>' .$price.' Kč</td>';
            $output[] = '<td><input type="text" name="qty'.$id_menu.'" value="'.$qty.'" size="5" maxlength="5" /></td>';
            $output[] = '<td>' .($price * $qty).' Kč</td>';
            $total += $price * $qty;

            $output[] = '</tr>';
        }
        $output[] = '</table>';
        $output[] = '<p>Total: <strong>'.$total.' EUR</strong></p>';
        $output[] = '<div><button type="submit">Update</button></div>';
        $output[] = '</form>';
    } else {
        $output[] = '<p>Cart is empty.</p>';
    }
    return join('',$output);
}

what you exactly want to store in database?? $name $price $qty $total ??? or something else???? — CyberBoy, Mar 15 '14 at 16:38
You are right, it's probably a loop over whatever variable that's holding the items to insert. So first have a look at where and how the data that's going to be inserted is stored. Then head over to one of the existing questions here on SO that talk about inserting multiple rows in MySQL, like [this one](http://stackoverflow.com/questions/779986/insert-multiple-rows-via-a-php-array-into-mysql) and if you still have problems, please ask a more specific question with *relevant* code. — fvu, Mar 15 '14 at 16:38

Cedric Simon · Answer 1 · 2014-03-15T18:30:21.117

0

You can use:

Insert into mytable SELECT * FROM menu WHERE id_menu = ...

To construct the multiple insert and insert all in one statement, do the following:

Insert into mytable (col1, col2, col3) values
('Val1a','val2a','val3a'),
('Val1b','val2b','val3b'),
('Val1f','val2d','val3c'),
Etc...
;

Each iteration will concatenate one line of values. Insert is then done after the loop.

edited Mar 15 '14 at 18:30

answered Mar 15 '14 at 16:53

Cedric Simon

4,571
4
40
52

Thanks for your reply, I know how to insert data to table, but I want insert all records using foreach. – Imrich Jebal Mar 15 '14 at 16:57
Added bulk insert in my answer – Cedric Simon Mar 15 '14 at 18:30

score 0 · Accepted Answer · answered Mar 15 '14 at 17:04

0

i think you need something like this....

    foreach ($contents as $id_menu=>$qty) 
    {
    $sql1 = 'INSERT INTO tablename (colum1, colum2, column3, ... ) SELECT * FROM menu WHERE id_menu = '.$id_menu;
    //rest  of your program
    }

answered Mar 15 '14 at 17:04

CyberBoy

745
1
7
31

How to insert multiple records to MySQL

2 Answers2