How to enter more than one value into one database column

Question

I am trying to get a completed order to save to a table called orders in a database. In the database, the orders table only shows the last product, but I need it to show all the products on the order.

In the code below ($query2) it grabs all the ids of the products that are in the users cart.(There are two products in the cart)

I then use a while loop to display them, but if I try to INSERT them into the orders table($query3) using the $pro_id variable, it only grabs the last product in the cart and enters it into the database.

How do I get both products to enter into the orders table? Do I need to use an array somehow? Or is there a different way to do this?

Thanks!

        $ip = getIp();
        $query = "SELECT * FROM cart WHERE ip='$ip'";
        $result = mysqli_query($conn, $query); 

        if(mysqli_num_rows($result) > 0){
            while($row = mysqli_fetch_array($result)){
                $id     = $row['id']; 
                $pro_qty    = $row['quantity'];

                $query2 = "SELECT * FROM product WHERE id='$id'";
                $result2 = mysqli_query($conn, $query2);     

                if(mysqli_num_rows($result2) > 0){   
                    while ($row = mysqli_fetch_array($result2)){
                        $pro_id = $row['id'];
                    }
                }
            }
        } 

        if(isset($_POST['placeOrder'])){
            $query3 = "INSERT INTO orders (productId) VALUES ('$pro_id')";
            $result3 = mysqli_query($conn, $query3);
        }

    }

You don't enter multiple values into one column. You redesign it so that every column only has one value. See http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Mike, Mar 30 '16 at 16:43

score 1 · Accepted Answer · answered Apr 01 '16 at 17:10

I found someone online that showed me a different way to do what I was trying to do. Instead of using an array to save both products to one column; I created two tables in the database ("orders" and "orderproducts"). Using the code below, I made it so that if the "placeOrder" button was clicked it would INSERT the customer's order into the "orders" table (with a unique number that I created and saved in the variable "$orderId").

I then got the id of the products that were in the customers cart and created a while loop that would run through the products in the cart and using "$query3" INSERT each product individually into its own row in the "orderproducts" table with the "orderId='$orderId'" ($orderId is the unique number I created) and "productId='$productId'".

if(isset($_POST['placeOrder'])){
    $query = "INSERT INTO orders (orderId) VALUES ('$orderId')";
    $result = mysqli_query($conn, $query);

    $ip = getIp();
    $query1 = "SELECT * FROM cart WHERE ip='$ip'";
    $result1 = mysqli_query($conn, $query1); 

    if(mysqli_num_rows($result1) > 0){
        while($row = mysqli_fetch_array($result1)){
            $id      = $row['id']; 

            $query2 = "SELECT * FROM product WHERE id='$id'";
            $result2 = mysqli_query($conn, $query2);     

            if(mysqli_num_rows($result2) > 0){   
                while ($row = mysqli_fetch_array($result2)){
                    $productId = $row['id'];

                    $query3 = "INSERT INTO orderproducts (orderId, productId) VALUES ('$orderId', '$productId')";
                    $result3 = mysqli_query($conn, $query3);
                }
            }   
        }
    } 
}

Then to grab that order, I made a query like this:

    $orderNumber = 5578;(This is a fake order number)

    $query = "SELECT * FROM orders WHERE orderId='$orderNumber'"; 
    $result = mysqli_query($conn, $query);

    if($result){
        $query1 = "SELECT * FROM orderproducts WHERE orderId='$orderNumber'"; 
        $result1 = mysqli_query($conn, $query1);
    }

score -1 · Answer 2 · answered Mar 30 '16 at 18:46

-1

You can modify your variable $pro_id and make it an array type like this:
$pro_id = array();
and then use it to store your ids in this array.

answered Mar 30 '16 at 18:46

Aparna

255
1
8

How to enter more than one value into one database column

2 Answers2