Compile data from multiple tables into 1 cell

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Newline2003
Forum Newbie
Posts: 7
Joined: Wed Feb 06, 2008 10:59 pm

Compile data from multiple tables into 1 cell

Post 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
Newline2003
Forum Newbie
Posts: 7
Joined: Wed Feb 06, 2008 10:59 pm

Re: Compile data from multiple tables into 1 cell

Post by Newline2003 »

Im sure someone has the anwser. Don't be afraid to help :lol:
dml
Forum Contributor
Posts: 133
Joined: Sat Jan 26, 2008 2:20 pm

Re: Compile data from multiple tables into 1 cell

Post 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);
 
Newline2003
Forum Newbie
Posts: 7
Joined: Wed Feb 06, 2008 10:59 pm

Re: Compile data from multiple tables into 1 cell

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compile data from multiple tables into 1 cell

Post 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.
(#10850)
Newline2003
Forum Newbie
Posts: 7
Joined: Wed Feb 06, 2008 10:59 pm

Re: Compile data from multiple tables into 1 cell

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Compile data from multiple tables into 1 cell

Post 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.
(#10850)
Post Reply