Cart Checkout - Inserting to two dbs and arrays?

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
User avatar
apexonelove
Forum Newbie
Posts: 7
Joined: Tue Jul 17, 2007 2:52 pm
Location: USA

Cart Checkout - Inserting to two dbs and arrays?

Post by apexonelove »

So i've been building a shopping cart from scratch...probably not the best idea on the planet...but trying to learn. Basically, i've got my cart to function beautifully...got the checkout information and process all going as planned....except for the last step where i start working with the sql tables. As most carts go, I need to insert the record for the order as well as then have an order_details table updated, correct? I understand how i'd go about inserting two tables at the same time....but how do i insert multiple records to the order_details table? is this an array?

Thanks for any help!
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Post by aliasxneo »

You want to insert multiple rows into a table? Try using a loop?

Your description lost me a little so I might be misunderstanding.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Yes, insert the one order record and then either loop thorough the items and insert each into the order_details table. If you have a more recent version of many databases you can also use the multiple record syntax for INSERT that will be more efficient because there is only one query executed. It usually looks something like:

Code: Select all

INSERT INTO order_details
  (order_id, sku, price, quantity)
VALUES
  (100, 'A001', 24, 1),
  (100, 'B002', 21, 1),
  (100, 'C003', 22, 1),
  (100, 'D004', 25, 1),
  (100, 'E005', 29, 1);
(#10850)
User avatar
apexonelove
Forum Newbie
Posts: 7
Joined: Tue Jul 17, 2007 2:52 pm
Location: USA

Re: Cart Checkout - Inserting to two dbs and arrays?$$$$$$$$$$$$

Post by apexonelove »

arborint,

Thanks so much for the reply -- I've been working on it -- my noobness is outstanding!

So here's what I've got thus far:

Code: Select all

 
<?php 
 
$query = INSERT INTO simp (order_id,productid,itemid,qty,priceper) VALUES (<?php
            while($row = mysql_fetch_array($result))
            {
                // Increment the total cost of all items
                $totalCost += ($row["qty"] * $row["PRICE"]);
                ?>332,  <?php echo $row["NAME"]; ?>, <?php echo $row["ITEMNUM"]; ?>,  <?php echo $row["qty"] ?>, <?php echo number_format($row["PRICE"], 2, ".", ","); ?>),(<?php
            }
            
            ?>
It feels pretty close...I'm just very weary at this point. Here's what else I need it to do: :banghead:
-Create record for ORDERS_MASTER table (which would store the order_id)
-Multiple Row Insert (from cart session or POST to ORDER_DETAILS table with order_id from ORDERS_MASTER id)
-Multiple Row Delete (from the cart session table)

I'm willing to drop a little cash if someone out there is willing to help with this page of code!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Cart Checkout - Inserting to two dbs and arrays?

Post by Christopher »

Sounds like you are on track. Usually you create one record in the "orders" table that contains all the non-item information (e.g. user info, grand total, tax, shipping, credit card, etc. ). Then you create one record for each item in the cart in an "order_items" table. Use the unique id from the insert into "orders" to set the order_id field in each record inserted in "order_items".

And finally, you might find it simpler to store the Cart in the session rather than a data table. It makes it much simpler to destroy when checkout is completed that having to go back to the database again.
(#10850)
User avatar
apexonelove
Forum Newbie
Posts: 7
Joined: Tue Jul 17, 2007 2:52 pm
Location: USA

Re: Cart Checkout - Inserting to two dbs and arrays?

Post by apexonelove »

Well, it's good to hear that I'm moving in the right direction!

For the insert of non-item info to the "orders_master" table, I will just do this from the POST data from the form and then pull the item data from the session (i'm actually using sessions for this currently as well as storing to the db so they can do things like quantity and all of that -- now that you say that i could totally see how easy that would be just destroy the session....).

How do I pull the unique id from the insert of the "orders" table to the "order_items" table? The only way I could think of would be to run a query of that db and then "+ 1" it.

For the destroying...I suppose I'll just use the same "foreach" do take them out. I'll let you know how it goes.

Thanks again for all of your help!


ps: Again, I'm willing to pay anyone who's willing to just finish this up for me :)!!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Cart Checkout - Inserting to two dbs and arrays?

Post by Christopher »

apexonelove wrote:For the insert of non-item info to the "orders_master" table, I will just do this from the POST data from the form and then pull the item data from the session (i'm actually using sessions for this currently as well as storing to the db so they can do things like quantity and all of that -- now that you say that i could totally see how easy that would be just destroy the session....).
I usually save this checkout information in the session in a Checkout object. Sometimes there is a User object as well that contains some of the information. The you can have multi-page checkout which is friendlier for users.
apexonelove wrote:How do I pull the unique id from the insert of the "orders" table to the "order_items" table? The only way I could think of would be to run a query of that db and then "+ 1" it.
The insert on the "orders" table will provide you with the value. For example, in MySQL there is a function mysql_insert_id() that will give you this information.
(#10850)
Post Reply