Bill
-
Posts
45 -
Joined
-
Last visited
-
Days Won
1
Posts posted by Bill
-
-
Think I'll revist this task later and go through the Larry's Advanced Oop book beacuse this task requires more study.
Cheers
-
How does one rewrite the stored procedure called select_products to display the new size table as defined above in the drop down menu? Please note that the query ought to run using the program list_coffees.html. Presently the existing query displays two different sizes such as 25 grams and 100 grams in the drop down menu but not every size in the sizes table i.e., 25, 50, 75, 100, 125 grams.
Here again is the stored procedure select_products I want to rewrite and change so as to retrieve all the various sizes from the database into a drop down list:
SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku,CONCAT_WS(" - ", s.id, s.size) AS name,
sc.stock, sc.price
FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id
INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id
WHERE general_coffee_id=cat
-
Thanks for the tips, guys. I am refering to the second example in Larry's book. Basically, in general terms my purpose is to create a drop down list in list_coffees.html with the new sizes table as defined above. Furthermore, I imagine coffee is available in different sizes from 25 grams to 1.25 grams. Each product also has a unique id. So therefore one product has the option of selecting from many sizes.
Cheers
-
1
-
-
Hello
System Information: MYSQL 5.5.16, PHP Version 5.3.8, Windows NT, XAMPP 1.7.7
I am trying to extend the coffee site and would like to know what I should do to alter the drop down list to display size information from the sizes table. I have changed the sizes table so that size has various weights. Here is the new sizes table:
id size
1 25 grams
2 50 grams
3 75 grams.
4 100 grams
5 1.25 grams
The main problem in my oopinion is probably the query which probably needs to be recast to reflect the size attributes in the coffee site. Here is the query in question that is a stored procedure (p.196-7 and p.202):
SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku, CONCAT_WS(" - ", s.id, s.size) AS name, sc.stock, sc.price FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE general_coffee_id=catYou might be interested to know that I have broken the problem down to a simple query in another program for testing purposes such that $q = "SELECT id, size FROM sizes"; this query does in fact produce the right result with the various sizes displaying in the drop down list. Perhaps I also need to alter the option code, too!
Thank you for any help in advance!
-
Hi Larry
A topic I would like to see in your JavaScript book is multidimensional arrays. As I want to know how to add, update and delete an array of JavaScript custom objects. It would therefore be good to see a specific example of this in your book. Why you may well ask?
Recently, I have been attempting to modify a nutrition program that I found on the internet to this end but to no avail. The JavaScript nutrition program I am referring to adds up all the various totals for eight nutrient values including protein, fats and carbohydrates from the various food groups - very useful for the health conscious. This also involves a user selecting the amount for each food item from a drop down list. Anyway, here is part of the list of array objects:
foodgroup[1]=new food("Apple Juice",15,0,0,59,0,125,1,3); foodgroup[2]=new food("Prune Juice",24,0,0,99,3,301,1,4); foodgroup[3]=new food("Grapefruit Juice",11,0,0,44,0,208,1,2); foodgroup[4]=new food("Cranberry Juice",19,0,0,76,0,15,1,3); - - foodgroup[101]=new food("Vegetable Oil 8.1 cal/cc",0,0,27,2430,0,0,0,0);The problem that I have discovered occurs when you want to change the data structures in the list of array objects in anyway. If you want to add a food group, delete a food group that you don’t eat anymore or change the nutirent data values themselves with more accurate data, it can become a very cumbersome and tedious process. It can take a fair while, even hours and days especially if you have 29 nutrients or more! This could be expedited in my opinion with the inclusion of add, update and delete buttons within the user interface itself. Nevertheless, this nutrition program uses JavaScript coding like document.write to output results. You mentioned in one of your articles that this is “bad code, bad JavaScript“ but I have found it to be a very useful program despite it limitations in terms of changing the code to increase the number of nutrients etc.
You can see this JavaScript program I am referring to hereby just pasting the following link in your browser: http://www.nafwa.org...d-software.html and then download the file NUTRIENT.ZIP
Regards
-
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
-
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.00Order 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: -
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
-
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.
-
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?
-
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?
-
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?
-
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.
-
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
-
You could contact your own bank or any other of the major banks where you are? I know in Australia all the major banks use payment gateways but for a fee...Alternatively, you could just use paypal or just the cart and put your bank account number on your site with relevant postal prices and information instead of incorporating a payment gateway with credit card processing.
Andrew
-
Hi there,
Thanks Paul for noticing the error in my coding - mysqli_affected_rows is the right command to use for inserting information into the mysql database.
However, my Mysql data structure definitions were unique for user_id and page_id. This only allowed one row to be inserted in the database. Changing those data structures to index allowed more than one row.
While the inclusion of the hidden form field containing a ternary operator enabled the passing of page_id to be sent with the post request, I kept getting an UNDEFINED INDEX error message regarding the $_GET['id']; variable even after the script worked.
This error appeared because of my PHP error reporting settings. As the variable was not properly set, I got an UNDEFINED INDEX error. This issue was handled by including the following piece of code:
Check if $_GET['id'] is set before using it. For example: if (!isset($_GET['id'])) { //If not isset -> set with dumy value $_GET['id'] = "undefine"; }It's good to get it going anyway. I hope to rewrite the script using coding similar to what is contained in selling physical products on p.256 and p.75 in Larry's book Effortless E-commerce.
Thanks again,
Andrew
-
Thanks Antonio for the suggestion.
I will have to make a few changes here and there to get it to run.
-
Thank HartleySan for your help. And thank you as well Paul. I used that line of code you suggested. You were right. Now the post array now contains the page_id variable
[_POST] => Array
(
[message] => hi
[submit_button] => Add comment
[page_id] => 2
[user_id] => 1
)
The script is still not running however. Perhaps it's a syntax error in the isset validation. I don't know whether to use $_POST['id'] or $_GET['id'] for the isset() validation procedure. If I remove the isset() validaiton procedure from the script completely I get an undefined index error. If the isset validation procedure is present in the coding, it appears to be executing okay. The problem probably lies elsewhere.
The major concern is an error message relating to the mysqli insert query that appears saying the page has been accessed in error. This could be an indication that the syntax of the query is wrong though there are no errors being reported. The code that is probably causing the error maybe the way the page_id is expressed in the query i.e., {$_POST['id']}. I have used {$_GET['id']}as well but the mysql table remains empty after attempting to insert these variables into the database. Perhaps the insert command requires special coding as with the form where ternary operators are used.
I am thinking about breaking up the mysqli insert command and the isset validation procedure by placing it on another webpage page.php instead of sending the contents of the form submission to the same page. However, using multiple pages would only be an alternative unless everything cannot be done on the same page as this reduces the clutter by having it as an include file.
-
Hi Larry,
Thanks for writing effortless e-commerece. It's a demanding at times but hopefully it will pave the way to a better understanding of web programming.
Currently, I'm trying to create an add comment box on the page.php script for the first ecommerce site: selling virtual products.
Problem: I cannot pass a page's id from a form submission via server request method == post to insert information into a mysql database? I have tested this script on firefox and windows explorer with similar results.
My computer configuration is based on a Windows Vista OS using a localhost Apache server version 2.2.17 with PHP/5.2.10 and MySQLI Client API library version 5.0.51a.
Eventhough $_GET can get the page_id, a $_GET variable cannot be passed in a post submission with a hidden field. The is evident when the contents of the post array is displayed with:
'<pre>';
print_r($_POST); as no page_id is listed
Array
(
[message] => hi there
[submit_button] => Add comment
[page_id] =>
[user_id] => 1
)
Here is the script that does everything except pass a hidden variable to insert the page_id into the mysql database:
include_once('../mysql.inc.php');
//require(MYSQL);
// For storing errors:
$add_page_errors = array();
// Check for a form submission:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
echo '<pre>';
print_r($_POST); // this displays the contents of the array. no page_id listed
// Check for the message:
if (!empty($_POST['message'])) {
$allowed = '<div><p><span><br><a><img><h1><h2><h3><h4><ul><ol><li> <blockquote>';
$m = mysqli_real_escape_string($dbc, strip_tags($_POST['message'], $allowed));
}
else {$add_page_errors['message'] = 'Please enter a comment!'; }
if (empty($add_page_errors)) { // If everything's OK.
// validate variables
if (isset($_SESSION['user_id'], $_POST['id'])
&& filter_var($_SESSION['user_id'], FILTER_VALIDATE_INT, array('min_range' => 1))
&& filter_var($_POST['id'], FILTER_VALIDATE_INT, array('min_range' => 1))) { // Okay!
// Add the message to the database:
$r = mysqli_query($dbc, "INSERT INTO page_posts (user_id, page_id, message) VALUES ({$_SESSION['user_id']}, {$_GET['id']}, '$m')");
if (mysqli_num_rows($r) != 1) { // Problem!
$page_title = 'Error!';
include ('./includes/header.html');
echo '<p class="error">This page has been accessed in error.</p>';
include ('./includes/footer.html');
exit();
}
} //end of validation for user session and page id
} // End of $add_page_errors IF.
} // End of the main form submission conditional.
// Need the form functions script, which defines create_form_input():
require ('includes/form_functions.inc.php');
?>
<h3>Add Comment</h3>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" accept-charset="utf-8">
<fieldset><legend>Please leave a comment:</legend>
<p><label for="message"><strong>Message</strong></label><br /><?php create_form_input('message', 'textarea', $add_page_errors); ?></p>
<p><input type="submit" name="submit_button" value="Add comment" id="submit_button" class="formbutton" /></p>
<input type="hidden" name="page_id" value="<?php if (isset($_POST['id'])) echo $_POST['id']; ?>" id="page_id" /> //This is the problem code in my opinion
<input type="hidden" name="user_id" value="<?php if (isset($_SESSION['user_id'])) echo $_SESSION['user_id']; ?>" id="user_id" />
</fieldset>
</form>
I've managed to get the script to insert one row of data into the mysql database but I don't remember how!
Regards,
Andrew
Selecting Products With Dropdown List Using Size Table
in Effortless E-Commerce with PHP and MySQL
Posted
Yes, I thought the inner join on the sizes table could be the reason why me new sizes table does not completely display the five various sizes in the drop-down list. I've already tried left or right inner join to no avail. The specific_coffees table listed beiow shows the data for testing purposes. . A foreign key-primary key relation exists I believe between s.id and sc.size_id.
Id general_coffe_id size_id coffeename image price stock date_created
3 3 4 defcaf - 50.00 10 2012-09-19
1 4 5 instant - 15.00 90 2012-09-19
4 3 5 blend_43 - 22.00 4 2012-09-19
2 4 3 dark_coffee - 70.00 10 2012-09-19
14 3 4 ground_whole - 12.00 10 2012-10-10
Hope this explanation is clear and distinct.