How To Add A Decimal Shipping_Type Variable To Orders Table In Ex2?

[Bill]

My aim is to extend the effortless ecommerce application in the second example by adding a shipping_type variable in the orders table to enable overseas purchases. I've created an array in the form_functions.Inc script with parameters for a drop down list in billing.html which allows a user to select from three shipping type variables: RD, RG, OS. This is straight forward enough. Here is the code from form.functions.inc

 

elseif (($name == 'shipping_type') || ($name == 'cc_shipping_type')){  $data = array('RD' => 'Shipping - Registered Delivery - $7.00', 'RG' => 'Registered - $10.00', 'OS' => 'Overseas - $35.00');

 

As a result this array a shipping_type variable can be stored in the mysql database as a char(2) such RD in the customers table. See below where I have inserted a shipping_type field char(2) into the customer table.

 

id   customer_id    total shipping   shipping  shipping_type     credit_card_number    order_date
7        3                  11.75      4.75          RD                 8888            2011-0614

 

The orders table in this exercise however, ought to have a decimal (5,2) variable inserted which involoves a cc_shipping_type that is submitted with credit card information. Here is the validation in the form submission that is contained in billing.php. This works okay.

 

// Check for shipping type:
if (preg_match ('/^[A-Z]{2}$/', $_POST['cc_shipping_type'])) {
	$cc_shipping_type = $_POST['cc_shipping_type'];
} else {
	$billing_errors['cc_shipping_type'] = 'Please enter your shipping type!';
}

 

 

While the form input validation can pass the drop down selection list variables with either of the three rates for the cc_shipping_type variable i.e., $7.00, $10.00 or $35, I don't know how to put a numeric variable into shipping_type column of the orders table. The code would probably requre a function of sorts that should select the approprate rate and insert it into the database - excuse the lack of clarity here.

 

Any suggestions would be greatly appreciated

 

Many thanks

 

System information: xampp 1.74 running on Windows Vista

[HartleySan]

Do you mean you don't know how to make a numbers field in the table, or you don't know how to do the actual INSERT query?

[Bill]

I want to incorporate a drop down list with three options that enables someone from overseas to make a purchase people from different countries such as Japan, Uk, Madgascar - all over the world. Therefore an option for overseas mail is required.

[HartleySan]

So, you're asking how to do that, right?

 

If I were you, I'd make a new table for the three types of shipping, and then use the ID as a foreign key in the orders table.

 

Does that make sense? Is that what you're asking?

[Bill]

Yes I think I understand. What I had in mind was that when the submit buttom on the form is pressed the shipping_type whether it is OS -overseas or RG - registered would be converted into a decimal value and placed in the orders table. If I understand you correctly then on your view this can only be done if it is in a seperate table so that the detials can be manipulated independently?

[HartleySan]

Well, I think our ideas are one and the same. You seem to be saying that you'd assign a value to the shipping type (i.e., 1, 2, 3) from the PHP side, whereas I was saying do the same thing from the database side. For example, you shipping_type table might look like the following:

 

1   Registered delivery
2   Registered
3   Overseas

 

And then the ID column (the 1, 2, 3) would be used as a foreign key in the orders table.

 

Anyway, either approach would do the same thing, but for better separation and the ability to modularize things, I think having that logic on the database side is better.

[Bill]

When the form is submitted the value of the shipping type is a char ie RD, or RG or OS but I need a decimal(5,2) in the database as in the following.

id  shipping_type
1   7.00
2   10.00
3   35.00

 

HOw would I do this?

[Bill]

If the logic is on the database side then I would have to select the value say 'OS' from the database and then change it to a specfic rate such as $35.00 using php. This is what you mean, correct?

[HartleySan]

Or you could design the database table as follows:

 

1   7.00    Registered delivery
2   10.00   Registered
3   35.00   Overseas

[Bill]

Okay let's assume we now have a database with an orders table and a shipping_types table and the primary key from the orders table is linked as a foreign key in the shipping-types table. Further, the values in the shipping_types table remain the same and never chage therefore these values do not have to be updated. Here are the two tables:-

 

 orders table
id   customer_id  total  shipping  credit_card_number    order_date
3        3        46.75   35.00          8888            2011-0614

 

Shipping_types table
id   shipping_type         shipping
1    Registered_Delivery    7.00
2    Registered             10.00
3    Overseas               35.00

 

Please correct me if I'm wrong but now it follows that the add_orders stored procedure() will have to add a new order by updatng the total column in the orders tables by getting the shipping values from the shipping_types table as well as assigning a value to the total variable as part of the total order.

[HartleySan]

I think your shipping_type table is okay, but instead of using the order ID as a foreign key in the shipping_type table, I think it would be better to have a shipping_type foreign key in the orders table. So basically, your shipping field would become the foreign key from the shipping_type table, and the value would become 1, 2 or 3, not a dollar value.

 

Then, when you generate the order form, you'd assign a value of 1, 2 or 3 to the various shipping options in the drop-down list, and when a user submits an order, that 1, 2 or 3 value would be stored in the orders table along with the other order info.

 

After that, when the cart is displayed, the stored procedure that calls all the information from the orders table would have to add one more join to the shipping_type table, in order to get the actual dollar value, and display that for the customer, and add it to the total.

 

Does that make sense?

[Bill]

Thanks...Yes, it makes sense. Yeah, the shipping_type_id as a foreign key in the orders table.

But I'd like to use a char(2) like 'OS' which refers to 'overseas' as the shipping_type_id in the orders table, because I've already created a drop down list with these three refereces to shipping_types. Is this okay? Here are the two tables again with your suggestions.

 

Order Table
id customer_id  shipping_type_id    total      shipping   credit-card_number    order_date
7         2          OS                          35.00           8888           2011-06-12

 

 

Shipping_types table
shipping_id  shipping_type_id               shipping_type                        shipping
1                      RD                     Registered_Delivery                  7.00
2                      RG                     Registered                          10.00
3                      OS                     Overseas                            35.00

 

Here is what you said regarding the regarding the stored procedure() for updating the orders table:

 

After that, when the cart is displayed, the stored procedure that calls all the information from the orders table would have to add one more join to the shipping_type table, in order to get the actual dollar value, and display that for the customer, and add it to the total.

 

I'll need a bit of time to think about how to do this. For the time being, regards

[HartleySan]

All right. I'll leave you to think about the stored procedures for a bit. Certainly, when you get into making complex queries with lots of joins, it can get...well, complex.

 

As for the shipping_type table, you can just take out the shipping_id field and just use the shipping_type_id field as your primary key. There is no rule that every table has to have incrementing integer values starting from 1.

 

Anyway, I think you're starting to get the idea down, so I'll leave you be. Lemme know if you need any additional help. Thanks.

[Bill]

I have therefore reached a stalemate with my attempt to modify the code in ex2 of effortless ecommerce to accomodate a shipping_type varable for overseas purchases.

 

Here are the shipping_contents table and the orders table:

shipping_contents table
shipping_type_id           category               shipping_fee
     RD                   Registered Delivery        7.00
     RG                   Registered                10.00
     OS                   Overseas                  35.00

 

Order Table
id customer_id  shipping_type_id     total     shipping   credit-card_number    order_date
7         2          OS              91.00       35.00           8888           2011-06-12

 

Here is a piece of the code from the stored procedure that adds stuff. It is only a fragment that contains what I think is important for understanding the problem. I added the select oid, o.customer_id, o.shipping_type_id etc...plus the shipping_type_id and shipping_fee in the insert part. Note my attempted to write an inner join to the right of the select statement. It is probably wrong!

 
DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `add_order`(cid INT, uid CHAR(32), ship_type_id CHAR(2), ship DECIMAL(5,2), ship_fee DECIMAL(5,2), cc MEDIUMINT, OUT total DECIMAL(7,2), OUT oid INT)
BEGIN
DECLARE subtotal DECIMAL(7,2);
           SELECT oid, o.customer_id, o.shipping_type_id, o.shipping, o.shipping_fee,  o.credit_card_number, o.order_date FROM orders AS o INNER JOIN shipping_contents AS sc ON o.shipping_type_id=sc.shipping_type_id;
INSERT INTO orders (customer_id, shipping_type_id,  shipping, shipping_fee, shipping_type, credit_card_number, order_date) VALUES (cid, ship_type_id, ship, ship_fee,  cc, NOW());
SELECT LAST_INSERT_ID() INTO oid;
=========missing code here==============see page p.265 for complete listing in effortless ecommerce
SELECT SUM(quantity*price_per) INTO subtotal FROM order_contents WHERE order_id=oid;
UPDATE orders SET total = (subtotal + ship) WHERE id=oid;
SELECT (subtotal + ship) INTO total;

END $$

 

Here is the code refered to in billing.php (p.300 effortless ecomerce) that lays a vital part in the payment process.

// Call the stored procedure:
		$r = mysqli_query($dbc, "CALL add_order({$_SESSION['customer_id']}, '$uid', '$shipping_type_id', {$_SESSION['shipping']}, $shipping_fee,  $cc_last_four, @total, @oid)");

 

 

 

Yet it does not run? Here is the error message:

 

An error occurred in script 'C:\xampp\htdocs\ecom_book\ex2.4\html\billing.php' on line 182:

[HartleySan]

Well, I'll need to more closely analyze your code, but right off the bat, I can tell you that "oid" needs to be "o.id".

 

If that doesn't immediately fix the problem, I'll look more closely at things later.

[HartleySan]

Also, your order table says "credit-card_number", but your query says "o.credit_card_number". Also, there is no "o.shipping_fee".

[HartleySan]

And another thing is that you do not need to perform a join at all if you are only grabbing data from one table. Sorry for all the posts, but I'm posting as I see things.

 

Hopefully that all gives you some things to think about. Try rewriting everything, and then coming back.

 

Feel free to ask questions along the way.

[Bill]

Sorry about the errors and confusion but I am not used to stored procedures and only have the one reference book.

 

the add procedure at the beginning defines oid as an OUT INT so I thought it would be okay as other select statements in the add procedure use oid.

 

The use of o.columnname such as o.ship_type_id appears to be jsutified by the use of AS o

 

Anyway I will try to make a few more changes.

 

Regards

[HartleySan]

Ah, good point on "oid". I didn't notice that. Sorry. I'm not too familiar with declaring variables in stored procedures, so without looking into things a bit more, I have to plead ignorance there.