Page 1 of 1

Compile data from multiple tables into 1 cell

Posted: Wed Feb 06, 2008 11:12 pm
by Newline2003
Ok, I have a shopping cart I made a few years ago. It stores each item added to a cart in a new table. That table holds the qty and all the other infromation. The problem is some people order 25 to 50 items. So I am ending up thosands of inserts in my cart.

Code: Select all

$SQL = "SELECT * FROM CART where goes_with = '$aidd' and sold = 'NO' ORDER by cart_id DESC";
$result = mysql_query( $SQL );
while( $row = mysql_fetch_array( $result ) ) {
$item_name = $row["item_name"];
$item_number = $row["item_number"];
$item_price = $row["item_price"];
$unit_price = $row["unit_price"];
$qty = $row["item_qty"];
What I want to do is build an array or something and submit all the information including the customers shipping into 1 table.
something like this. saving all the cart information in to $in_my_cart

Code: Select all

$sql = "INSERT INTO SAVE_ORDER SET 
save_order_id = '$new_order_id', goes_with = '$muser',
order_ip = '$order_ip' , [color=#BF0000][b]in_my_cart = '$in_my_cart' ,[/b][/color]
order_date = '$order_date' , account_firstname = '$account_firstname' ,
account_lastname = '$account_lastname' , account_email = '$account_email' ,
account_phone = '$account_phone' , account_address = '$account_address' ,
account_address_two = '$account_address_two' , account_city = '$account_city' ,
account_state = '$account_state' , account_zip = '$account_zip' ,
shipping_firstname = '$shipping_firstname' , shipping_lastname = '$shipping_lastname' ,
shipping_email = '$shipping_email' , shipping_phone = '$shipping_phone' ,
shipping_address = '$shipping_address' , shipping_address_two = '$shipping_address_two' ,
shipping_city = '$shipping_city' , 
shipping_state = '$shipping_state' , shipping_zip = '$shipping_zip' , 
order_total = '$order_total'  , 
order_sub_total = '$order_sub_total' , coupon_used = '$coupon_used' , 
coupon_percent = '$coupon_percent' , 
shipping_type = '$shipping_type' , 
shipping_price = '$shipping_price' ,
tax_percent = '$tax_percent' , 
tax_amount  = '$tax_amount'";
$query = mysql_query($sql) or die("Cannot query the database." . mysql_error());
 


Should I try an array ... I have tried everything I can think of

Re: Compile data from multiple tables into 1 cell

Posted: Thu Feb 07, 2008 9:04 pm
by Newline2003
Im sure someone has the anwser. Don't be afraid to help :lol:

Re: Compile data from multiple tables into 1 cell

Posted: Fri Feb 08, 2008 8:08 am
by dml
What about inserting all the order items in one INSERT statement... it would keep the data in better order than squashing it into a cell...

Code: Select all

 
insert into order_items(order_id, product_id, quantity)
values (12345, 2002, 1), (12345, 2341, 2);
 

Re: Compile data from multiple tables into 1 cell

Posted: Fri Feb 08, 2008 2:05 pm
by Newline2003
Well in the cart all items are listed in different. But I want to generate 1 table with all the information like an invoice setup on the final checkout. Each customer orders around 50 items, making the final cart a monster. So if I could get all the cart info in 1 cell it would work much better for me

Example Order:

- Customer_id = 123
- Customer_name = Mike Johnson
- Customer_Address = 555 road st
- Customer_City = Somewhere
- Customer_State = MD
- Customer_Zip = 54322

Same for shipping address

The shipping type and other deatils

But I would like to pull there cart information in at the end like this...

- Customer_order =
<b>$item_name</b> $item_number $item_price $item_qty<br>

/////// echo / array or something for the Customer_order to get it into one cell? Use basic HTML <br> to make a new line. This is the part I don't know.

The store gets around 25 orders a day with an adverage of 50 items per order. That works out to be around 1250 new tables a day. I would like to delete the cart tables once a week but save the final order table. Thats why I would like to do what I have listed above.

Can anyone help?

Re: Compile data from multiple tables into 1 cell

Posted: Fri Feb 08, 2008 2:52 pm
by Christopher
You should think about saving the cart in the session while the user is shopping. That way you don't have all of the database activity for what is really temporary data. Then if/when they checkout you can write order records for what was contained in the session.

If you would like to walk though implementing that we could give it a go.

Re: Compile data from multiple tables into 1 cell

Posted: Fri Feb 08, 2008 5:11 pm
by Newline2003
I have had problems with sessions in the past. I would like to keep sending the info into the cart for now. This way if a customer waits a few days to order they do not have to start over. Like I said, most people add 50 or so items to there cart. Most carts only deal with 1 or two items for each order.

I am sure it is possible to compile data into 1 cell. I just can figure it out.

Re: Compile data from multiple tables into 1 cell

Posted: Fri Feb 08, 2008 5:41 pm
by Christopher
You could achieve the same persistence by serializing the cart from the session and saving that in a single field in a table. It is really just a snapshot of the cart with a key for their user account that you can reload if it exists when they login. That way you could use the session cart for reading which would perform better and just write the whole thing to the DB when they change the contents of the cart.