Inserting multiple database rows with one form

Question

I'm trying to insert multiple rows into a database, each with the same user_id and order_id, but a different product_id, the error I'm getting is:

SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'product_id' cannot be null

button function:

if(isset($_POST['confirm_order']))
{
        $product_id = array($_POST['productid']);
        $user_id    = $_POST['userid'];
        $user->confirm_order($product_id,$user_id);
}

The Form:

<?php 
    $stmt = $DB_con->prepare("SELECT * FROM products as p INNER JOIN basket as b ON p.product_id=b.product_id WHERE user_id = :user_id"); 
    $stmt->bindParam(":user_id",$user_id);   
    $stmt->execute();
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($row as $p)
        {
                echo '<form method="POST" enctype="multipart/form-data">
                <tr>
                <td><img id="img" class="img-fluid" src="images/',$p['product_image'],'" style="height:100px;width:100px;"></td>
                <td>',$p['product_name'],'</td>
                <td>£',$p['product_price'],'</td>
                <td>
                <input type="hidden" id="productid" name="productid[]" value="',array($p['product_id']),'">
                <input type="hidden" id="userid" name="userid" value="',$user_id,'">
                </td>
                </tr>';
             }  
            ?>
                <button type="submit" class="btn btn-primary" name="confirm_order"> Confirm Order</button>
                </form>

The Function:

    public function confirm_order($product_id,$user_id)
{
   try
   {
                $order_id = substr(str_shuffle(MD5(microtime())), 0, 10);
                $stmt1 = $this->db->prepare("INSERT INTO orders (order_id, product_id, user_id) VALUES (:order_id, :product_id, :user_id)");
                $stmt1->bindparam(":order_id", $order_id);
                $stmt1->bindparam(":product_id", $product_id[]);
                $stmt1->bindparam(":user_id", $user_id);        
                $stmt1->execute();

                $stmt2 = $this->db->prepare("DELETE * FROM basket WHERE product_id = :product_id AND user_id = :user_id");
                $stmt2->bindparam(":product_id", $product_id[]);
                $stmt2->bindparam(":user_id", $user_id);        
                $stmt2->execute();
   }
   catch(PDOException $e)
   {
       echo $e->getMessage();
   }    
}

cOle2 cOle2 · Accepted Answer · 2016-10-07T16:27:24

You either need to loop through the $product_id array in your confirm_order() function or change your queries to accept a variable number of product ID's.

As it stands you are not providing a valid array index or element in your bindparam() calls, hence the mysql error.

Inserting multiple database rows with one form

1 Answers