Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Store many items in the database

Hello everyone,

I have the following problem with an e-commerce site that I am building using php and database MySQL.
When the user has select items to buy then clicks on the button to checkout which are stores in the database in order the owner of the shop to send the specific items to the clients.

The tables that stores the information for the orders is called Orders with the following fields:
OrderID,customerID,itemID,amount,date_order and quantity.

The problem I have is that if the cutomer buy more than one item how they are going to be stored in the database,If for the same order of the customer I store two items in the itemID field it will cause problem with the normalisation.

Could please anyone suggest how I can store to the database many item for the same order?

Thank you,
Xenia
0
xenoula
Asked:
xenoula
  • 21
  • 18
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
2 tables:
ORDERS:
OrderID,customerID,date_order

ORDER_ITEMS:
OrderID,itemID,amount,quantity
0
 
xenoulaAuthor Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, perfectly understood
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
xenoulaAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the field OrderID should be aut_increment, and to find out the newly created order record, you use the function mysql_last_insert()
the more recent order in general can be identified using

select * from orders where customerid = <the customerid>
order by order_date desc
limit 1

0
 
xenoulaAuthor Commented:
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();
                                                      }
             
0
 
Richard QuadlingSenior Software DeverloperCommented:
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.
0
 
xenoulaAuthor Commented:
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?
0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you show us the form you are using? HTML that is.
0
 
xenoulaAuthor Commented:
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.
0
 
Richard QuadlingSenior Software DeverloperCommented:
How do you control the content of the "basket"?

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

What does it look like?
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
foreach($_SESSION['id'] as $i_count => $i_item_id)
 {
 // INSERT into order table using item number $i_item_id
 }
0
 
Richard QuadlingSenior Software DeverloperCommented:
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";
 }
?>

0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
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.
 }
0
 
Richard QuadlingSenior Software DeverloperCommented:
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!

0
 
Richard QuadlingSenior Software DeverloperCommented:
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.
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
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().
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
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



0
 
xenoulaAuthor Commented:
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.
0
 
Richard QuadlingSenior Software DeverloperCommented:
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();

0
 
Richard QuadlingSenior Software DeverloperCommented:
Do you currently have the system working for a quantity of 1?

0
 
xenoulaAuthor Commented:
Actually it still displays the $_SESSION['quantitymenu'] even though I have change it and I call it now $_SESSION[qty]
0
 
xenoulaAuthor Commented:
Actually it is 2
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
$_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!
0
 
xenoulaAuthor Commented:
Could you please provide an example in order to uderstand?

Thanks
0
 
xenoulaAuthor Commented:
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 )
0
 
Richard QuadlingSenior Software DeverloperCommented:
Can you put a <pre> tag around the output for the session as this will give you a better visual understanding of the storage.
0
 
xenoulaAuthor Commented:
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
0
 
xenoulaAuthor Commented:
           
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]')";
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
Try this ...


 $query="INSERT into Order_items (`orderID`,`itemID`,`amount`,`quantity`)
                                    values ('$ID','$i_item_id','{$_SESSION['price'][$i_sale_line]}','{$_SESSION['qty'][$i_sale_line]}')";
0
 
xenoulaAuthor Commented:
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
0
 
xenoulaAuthor Commented:
Can you think anything?
0
 
Richard QuadlingSenior Software DeverloperCommented:
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.
0
 
xenoulaAuthor Commented:
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
0
 
Richard QuadlingSenior Software DeverloperCommented:
Yes.

Ok.

Good luck.
0
 
Richard QuadlingSenior Software DeverloperCommented:
Split
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 21
  • 18
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now