Store many items in the database

2 tables:
ORDERS:
OrderID,customerID,date_order

ORDER_ITEMS:
OrderID,itemID,amount,quantity

So when the user buy two items it will store to the table Orders for example
OrderId:1 CustomerID:1,date_order:23/05/2006
OrderID:2, CustomerID:2, date_order:25/05/2006 (the order for another customer)

and the the order_items:

OrderID:1 ItemID:23, Amount: 23, Quantity:2 (the first item)
OrderID:1 ItemID:24, Amount: 25, Quantity:1 (the second item)
OrderID:2 ItemID:30, Amount:80, Quantity:1 (the item of a different order)

Did I understoond correct?
The field amount will be the price for the specific item all the total item?

Thank you,
Xenia

yes, perfectly understood

First I wil make an insert statement for storing the customerId and the date of order in the table Orders and then
a select statement that will give me the orderID depending from the customer and then a second insert statement
that will store the orderID taken from the select statement and the itemId,amount and quantity?

How I will do in order to save to the database if the user has two orders?Do I have to do a loop?

Could you please provode an example?

Thanks,
Xenia

The problem I still have is that the id's of the items will be stored in an array. So when I tryied to insert the second item in the Order_items put a second orderId in the table Orders. So it create two orders actually.      THe orderID is an auto_increament

 $queryOrders="Insert into Orders(`customerID`,`order_date`) values ('$customerID','$date')";
             $rsOrders=mysql_query($queryOrders);
             
             $queryID="select * from Orders where customerID='$customerID' order by order_date desc
limit 1";
             $rsID=mysql_query($queryID);
             $rowID=mysql_fetch_array($rsID);
             $ID=$rowID['orderID'];
             echo "the order id is :" . $ID;
             
             //I beleive somewhere here I might need to put a loop
             $query="INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`)
                                           values ('$ID','$id','$amount','1')";
                                                                                         
                              
                              $rs=mysql_query($query);
                              if ($rs==1){
                                                echo " The order was succesfully added" ;
                                                                              
                                          }else {
                                                      echo " Your submission could not be processed due to a system error" . mysql_error();
                                                      }
             

In the HTML form, make sure you add [] to the end of the field names.
NOT

<input type="text" name="item">
<input type="text" name="item">
<input type="text" name="item">

but

<input type="text" name="item[]">
<input type="text" name="item[]">
<input type="text" name="item[]">


This way you get an array if requested items back.

Ok so whenI will have the array with the ID's

I am going to insert            $query="INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`)
                                    values ('$ID','$id','$amount','1')";

If I do that I will save to the database something like

OrderID:1, itemID:23,45,67, amount:34, quantity:23

this also will cause problem is that right?

Can you show us the form you are using? HTML that is.

The form that the user add items to the basket or the form that displays the items in the basket?
The insert statements are prformed when the user click on the checkout button.

How do you control the content of the "basket"?

Is this in a session? or a cookie? or what.

What does it look like?

Ok I am storing the items in a session and this is my code for the viewbasket.php

<? include "session.php";
 require_once "dbconn.php";
 $q=$_SESSION['quantitymenu'];//stores the session quantity menu
 
 
//Check if the form has been submitted

if (isset($_POST['submit'])){
      
      
      foreach ($_POST['qtybasket'] as $key => $value){
      
//if the user writes in the quantity textfield 0 it will remove from the session the specific product            
            if (($value==0) AND (is_numeric($value))) {
                  unset($_SESSION['cart'][$key]);
                  
            }
            elseif (is_numeric($value) AND ($value>0)){ // else it will increase the quantity
            $_SESSION['cart'][$key]=$value;
            
            
            }
      }
}
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<link href="test.css" rel="stylesheet" type="text/css" />
</head>
<body BGCOLOR="#CCCCCC">

        <?

//Check if the basket is empty

$empty = TRUE;
if (isset($_SESSION['cart'])){
      foreach ($_SESSION['cart'] as $key => $value){
      if (isset($value)){
                                     $empty=FALSE;
                                    }
                        }
}


//Display the cart if it's not empty
if (!$empty){

//Retrieve all the details for the items in the cart
$queryProducts='select * from Items,Categories,Brands where
Items.categoryID=Categories.categoryID and
Brands.brandID=Items.brandID and
Items.itemID IN (';

foreach ($_SESSION['cart'] as $key => $value ){
 $queryProducts .= $key .',';
}
$queryProducts=substr($queryProducts, 0, -1).') order by Items.itemID';

$rsProducts=mysql_query($queryProducts);

echo '<table border="0" align="center">



<tr>
 <th width="150" align="center" ><span class="style33">Brand</span></th>
  <th width="150" align="center" ><span class="style33">Colour</span></th>
  <th width="150" align="center"><span class="style33">Qty</span></th>
  <th width="150" align="center" ><span class="style33">Size</span></th>
  <th width="150" align="center" ><span class="style33">Image</span></th>
  <th width="150" align="center" ><span class="style33">Description</span></th>
  <th width="150" align="center" ><span class="style33">Price</span></th>
 

 
</tr>

<form action="" method="post">';

//print each items
$total=0;// total cost of the orders
$_SESSION['id'] = Array(); //the Session['id'] is an array
while ($row=mysql_fetch_array($rsProducts, MYSQL_ASSOC)){ //diplays error
  $_SESSION['id'][] = $row['itemID']; //I assign the id's in the array SESSIO['id']
;  


//Calculate the total and subtotals
$subtotal=$_SESSION['cart'][$row['itemID']] * $row['price'] ;
$total +=$subtotal;

//Print the row
echo "<tr >
<td  width=\"150\" align=\"center\" class=\"generalfont\"> {$row['brand_name']}</td>

  <td width=\"150\" align=\"center\" class=\"generalfont\">{$row['colour']}</td>
  <td width=\"150\" align=\"center\" class=\"generalfont\"><input type=\"text\" size=\"3\" name=\"qtybasket[{$row['itemID']}]\"  value=\"{$_SESSION['cart'][$row['itemID']]}\" class=\"generalfont\"/></td>
   <td width=\"150\" align=\"center\" class=\"generalfont\">{$row['size']}</td>  
  <td width=\"150\" align=\"center\"><img src=\"images/../small_{$row['image_small']}\"</td>
  <td width=\"150\" align=\"center\" class=\"generalfont\">{$row['description']}</td>
 
  <td width=\"150\" align=\"center\" class=\"generalfont\"> &pound; {$row['price']} </td>
 
 
 
</tr>\n";
 
}//end the while loop

echo '<br><tr>
<td align="right"> <span class="generalfont"> Total: </span></td>
<td> <span class="generalfont"> $' . number_format($total,2). '<span></td>
</tr>
</table><br>


<input name="submit" type="submit" value="Update your basket" />
</form>'; ?>

<form action="checkout.php" method="post" name="checkout" >
<input name="brand" type="hidden" value="<? echo $brand;?>">

<input name="checkout" type="submit" value="CHECKOUT">
</form>
<?




mysql_close();
} else {echo '<p> Your basket is currently empty. </p>';
}

?>
</td>
  </tr>
</table>
</body>
</html>

In the checkout page (which has the insert statements) I am using the following code in order to display the id's of the items

             for($i = 0; $i < count($_SESSION['id']); $i++) {
  // This will echo each id in the array
  echo 'Item #' . $i + 1 . ': ' . $_SESSION['id'][$i] . "<br />\n";
}

THe above code will print in the screen

 1: 60
1: 63
1: 64

So I was asking for these id's how they can be stored separatley in the table Order_items.

I hope what I post to help you undertand in order to help me.

Thanks

foreach($_SESSION['id'] as $i_count => $i_item_id)
 {
 // INSERT into order table using item number $i_item_id
 }

Use foreach rather than counting and going from 0 to x - 1 as PHP does not automatically renumber indicies if you remove an item.

e.g.

<?php
$a_array = array(1 => 'one', 3 => 'three', 5 => 'five');

echo count($a_array);
for ($i_count = 0 ; $i_count < count($a_array) ; $i_count++)
 {
 echo "$i_count and {$a_array[$i_count]}\n";
 }
?>

will fail as even though there are 3 elements (from count), index 2 does not exist.

Also, some indexes are text based. You cannot therefore address them numerically.

With the foreach command you get the key and the value.

<?php
foreach($a_array as $key => $value)
 {
 echo "The key is $key and the value is $value and can also be accessed as {$a_array[$key]}\n";
 }
?>

Ok now is working ok.I would like to ask you a last question.I want to insert in the database the quantity of the items taht the
the user select.How I can get for every item the quantity?

Thanks again,
Xenia

foreach($_SESSION['id'] as $i_count => $i_item_id)
 {
 // INSERT into order table using item number $i_item_id
 }
You should be able to rely on the $i_count variable be the same in the quantity array.

So

foreach($_SESSION['id'] as $i_sale_line => $i_item_id)
 {
 // INSERT into order table using item number $i_item_id and $_SESSION['qtybasket'][$i_sale_line] as the quantity.
 }

Oh. All that \" going on!!!!

//Print the row
echo <<< END_HTML
<tr>
      <td width="150" align="center" class="generalfont">{$row['brand_name']}</td>
      <td width="150" align="center" class="generalfont">{$row['colour']}</td>
      <td width="150" align="center" class="generalfont"><input type="text" size="3" name="qtybasket[{$row['itemID']}]" value="{$_SESSION['cart'][$row['itemID']]}" class="generalfont" /></td>
      <td width="150" align="center" class="generalfont">{$row['size']}</td>
      <td width="150" align="center"><img src="images/../small_{$row['image_small']}"</td>
      <td width="150" align="center" class="generalfont">{$row['description']}</td>
      <td width="150" align="center" class="generalfont"> &pound; {$row['price']} </td>
</tr>

END_HTML;

Is that a little easier to see? You can use the heredoc to include text that requires quotes without having to escape them.

You are already bracing your PHP variables, so this is just a small step further!

The marker names are irrelevant

echo <<< END_SOMETHING
blah
blah
blah
END_SOMETHING;

If you leave a blank line BEFORE the end of the marker, it will be included in the final output.

The end marker MUST be left justified. No leading whitespace or indenting.

You can also assign to a variable the same way ...

$s_some_output = <<< END_SOMETHING
blah
blah
blah
END_SOMETHING;

Hope this helps a bit.

Ok I tryied as you told me but actually in both price and quantity insert 0 in the database

             foreach($_SESSION['id'] as $i_sale_line => $i_item_id)
 
{

 
             
             
             
             
             $query="INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`)
                                           values ('$ID','$i_item_id','$price','$_SESSION[qtybasket][$i_sale_line]')";
                                                                                         
                              //echo $query;
                              $rs=mysql_query($query);
                              if ($rs==1){
                                                echo " The order was succesfully added to the Order_items table" ;

What error you think I did again?

Thanks again

Did you not get any actual errors with that code?

First of all add ...

error_reporting(E_ALL);

to the top of the PHP code (just under the <?php)

Next, after ...

session_start();

add ...

print_r($_SESSION);

Run the program again and tell us all the errors/warnings/notices and the values in the session.

Just cut and paste the view-source would be best!


None of the data you are inserting are strings so why do you put quotes around them?
Also you only normally need to `tick` the column names if the column name is in some way a reserved word.

So try this too.
$query=<<< END_SQL
INSERT INTO
      Order_items
            (
            orderID,
            itemID,
            amount,
            quantity
            )
      VALUES
            (
            $ID,
            $i_item_id,
            $price,
            {$_SESSION['qtybasket'][$i_sale_line]}
            )
END_SQL;

echo $query;



You removed the echo. I would always recommend keeping the debugging in until the code is completed and then comment it out with a define().

Here is the outpit from the sessions

Array ( [cart] => Array ( [39] => 1 [57] => 2 [59] => 1 [68] => 1 ) [quantitymenu] => 1 [id] => Array ( [0] => 39 [1] => 57 [2] => 59 [3] => 68 ) [username] => user [password] => password [price] => Array ( [0] => 150 [1] => 34 [2] => 34 [3] => 23 ) )

It didn't display any error when I did the insert ,it just insert 0 in the fields amount and quantity

Ah. As you can see $_SESSION['cart'], $_SESSION['id'] and $_SESSION['price'] are all arrays.
39,57,59,68
39,57,59,68
150,34,34,23

But $_SESSION['quantitymenu'] = 1

NOT an array.

You need to put the quantities being asked for in the forms into the basket.

Also you need to absolutely use the same name

qtybasket is NOT quantitymenu

I have put in the page basket.php the following code for the itemID, the price and the qty  

$_SESSION['id'] = Array();
$_SESSION[price]=Array();
$_SESSION[qty]=Array();

while ($row=mysql_fetch_array($rsProducts, MYSQL_ASSOC)){ //diplays error
  $_SESSION['id'][] = $row['itemID'];
 $_SESSION[price][]=$row['price'];
 $_SESSION[qty][]=$_SESSION['cart'];
...
..

...

}

I think that the $_SESSION[qty][]=$_SESSION['cart']; is not correct ,but what to assign to the $_session[qty][]?

It depends what the user entered.

Also even thought it displays the price again it doesn't save it.

You assign the quantites from the data entered into the form and posted to the add to basket code.

How do you get the quantities currently?

Forget the DB that all happens in the cart -> DB pages.

How do you remember the quantities?

And quote the index names. The coding suggests constants when in fact they are strings.

$_SESSION['id'] = Array();
$_SESSION['price']=Array();
$_SESSION['qty']=Array();

Do you currently have the system working for a quantity of 1?

Actually it still displays the $_SESSION['quantitymenu'] even though I have change it and I call it now $_SESSION[qty]

Actually it is 2

The session $_SESSION['quantitymenu'] displays the quantity that the user selected from the drop down menu in the page that adds the item in the basket. The user can increase or decrease the quantity of the product in the basket.php page and by clicking on the update button it displays the final quiantity that the user selected. This quantity I need to insert in the database.

Could you tell me please how I can take this value and save it in a session?

Even though the price for the specific item I keep it correctly in the session variable,when I try to insert in the database it takes 0 instead of the correct value.

Any suggestions please?

Thanks,
xenia

$_SESSION['quantitymenu'] IS in the session!!!!

But you will need to use an array as this is only 1 quantity. Not 1 quantity PER order line!

Could you please provide an example in order to uderstand?

Thanks

In the basket page I am using these sessions:
$_SESSION[id] = Array();
$_SESSION[price]=Array();
$_SESSION[qty]=Array();


//$_SESSION['price']=Array();
while ($row=mysql_fetch_array($rsProducts, MYSQL_ASSOC)){ //diplays error
  $_SESSION[id][] = $row['itemID'];
 $_SESSION[price][]=$row['price'];
$_SESSION[qty][]=$_SESSION['cart'][$row['itemID']];

When I print the session it displays the folowing results:
Array ( [user] => username [pass] => pass [cart] => Array ( [76] => 6 [60] => 7 [64] => 1 ) [quantitymenu] => 1 [id] => Array ( [0] => 60 [1] => 64 [2] => 76 ) [price] => [qty] => Array ( [0] => 7 [1] => 1 [2] => 6 ) )

Here is what it displays for specific the price and qty
[price] => [qty] => Array ( [0] => 7 [1] => 1 [2] => 6 )

Can you put a <pre> tag around the output for the session as this will give you a better visual understanding of the storage.

The strange thing I get is the following:
 [price] => Array ( [0] => 45 [1] => 56 [2] => 10 ) [qty] => Array ( [0] => 7 [1] => 1 [2] => 6 )

 but when I print the

$price=$_SESSION[price][$i_sale_line];
echo "price : ". $price;
echo "i_sale_line : ". $i_sale_line ;

it displays 45 , 0
                5,1
               nothing,2

           
Here is how I insert in the database:

 $query="INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`)
                                           values ('$ID','$i_item_id','$_SESSION[price][$i_sale_line]','$_SESSION[qty][$i_sale_line]')";

Here I print the insert query

price 34,i_sale_line :0
INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`) values ('85','57','34[0]','Array[0]')
price : 4,i_sale_line : 1
INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`) values ('85','76','4[1]','Array[1]')

The values for the price and qty are wrong

INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`) values ('92','57','3','7')
INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`) values ('92','76','','3')

The actual values that are printed are:
    [price] => Array
        (
            [0] => 34
            [1] => 10
        )

    [qty] => Array
        (
            [0] => 7
            [1] => 3
        )

As I can understand it takes half of the values for the price and correct the quantity

Can you think anything?

Out of interest why not simply write all the data directly to the DB? The session is useful but I think you are overwriting something.

I don't know what methodology you are using. You obviously understand enough to see the problem. It is a fairly simple debugging exercise to find where you "lose" your data.

In EVERY page dealing with the session, put ...

<?php
session_start();
echo 'Before:<pre>' . var_export($_SESSION) . '</pre><br />';
....

// at the end of the script
echo 'After:<pre>' . var_export($_SESSION) . '</pre><br />';
?>

This will allow you to see all the work that is being done to the session. This should lead you to what code is losing the data. Without full access to the code/data trying to fix this sort of bug is really outside of the realms of experts help. We can help you find it. But finding it ourselves is a lot harder. Most of us would rewrite it to use OOP or some framework. We all do things differently.

I am saving only in session the items that the user put in the basket because is not sure that he will buy them and also I want to remeber what he put in his basket if he comes to the shop again.

At the beginning of every page I am including the session.php that has the following code:

<? ob_start();
session_start();
?>

I am going to include the var_export and to try and fix the bug but I would appreciate if I could ask you for any things that i might not understand in order to  help me also.

Thanks,
xenia

Yes.

Ok.

Good luck.

Split