Page 1 of 1

Cart Checkout - Inserting to two dbs and arrays?

Posted: Wed Dec 19, 2007 3:32 pm
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!

Posted: Wed Dec 19, 2007 5:17 pm
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.

Posted: Wed Dec 19, 2007 10:42 pm
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);

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

Posted: Wed Jan 23, 2008 12:23 pm
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!

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

Posted: Wed Jan 23, 2008 12:53 pm
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.

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

Posted: Wed Jan 23, 2008 1:09 pm
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 :)!!

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

Posted: Wed Jan 23, 2008 2:23 pm
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.