What is the best way to add array into Mysql

Question

I am making a simple order form with 5 products and they can be select with a checkbox from the user and then I want to add the products that the visitors will select into mysql database, but as you know for example INSERT array[] doest work!

I made an FOR stamtment with php but it adds only the last product of an array!

I know a way the serialize and unserialize php functions but I dont really like it!

Thank you for your help.

Here is the code:

$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$products = $_POST['products'];
$email = $_POST['email'];

echo '<h3>Sehr geehrte/er '.$firstname.' '.$lastname.'</h3>';


  if(empty($products)) 
  {
    echo("Keine bestellung.");
  } 
  else 
  {
    $N = count($products);

    echo("Sie haben $N produkte bestellt:<br /> ");
    for($i=0; $i < $N; $i++)
    {
      echo $order = ($products[$i]);    
    }
    $query = "INSERT INTO salt_orders (products, vorname, nachname, date)
        VALUES ( '$order', '$firstname', '$lastname', NOW())";
    $result = mysql_query($query);
  }

Normalize your database.... create an order_line for each product — Mark Baker, Apr 16 '11 at 22:52
Do you want to insert the 5 products into one row of the `salt_orders` table? Or into 5 rows? — ypercubeᵀᴹ, Apr 16 '11 at 23:17
For a quick read on normalization see: http://stackoverflow.com/questions/5620476/i-keep-messing-up-1nf — Johan, Apr 16 '11 at 23:36

score 1 · Answer 1 · answered Apr 16 '11 at 23:05

Create a seperate table which references your products and their details. Then create another table to hold order line items.

When a customer places and order, insert a new line referencing which product they bought and which order number it applies to in the line items table. Create a record in salt_orders which holds the order number.

Then you can get it all back out by joining them together to retrieve the full order details.

Adding multiple products to one cell in a row will make it very hard on you, so I suggest normalizing your data like stated above.

This just a simple example, it can be much more complex depending on what you need it to do.

score 1 · Answer 2 · answered Apr 16 '11 at 23:14

You can use this to concat the products in one field:

$order = "";
for($i=0; $i < $N; $i++)
{
  echo $order .= $products[$i] . "," ;    
}
$order = substr($order ,0 ,strlen($order )-1 ) ;

But you should really consider normalization of your orders table, as all others have suggested.

score 1 · Answer 3 · answered Apr 16 '11 at 23:24

1

Yes, normalization is what you are probably missing. When you have finished that, add functions to your database that handle the inserting to your normalized tables. This way the logic for the data stays with the data.

answered Apr 16 '11 at 23:24

chris polzer

3,219
3
28
44

What is the best way to add array into Mysql

3 Answers3