Bill Posted September 26, 2012 Share Posted September 26, 2012 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=cat You 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! Link to comment Share on other sites More sharing options...
rob Posted September 26, 2012 Share Posted September 26, 2012 You haven't actually stated what the issue/error is. You 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"; That's not standard debugging if you think your issue is with the SQL: firstly you should print the query out in the browser to establish it is what you're expecting, then run the query directly against MySQL (using the CLI or other tool). If there are no issues with steps one and two, then inspect the construction of the html for the drop list. Link to comment Share on other sites More sharing options...
Edward Posted September 26, 2012 Share Posted September 26, 2012 Something like this? This is a drop down generated from your SQL statement it also holds its stickiness. $q = "SELECT id, size FROM sizes"; $r = $mysqli->query($q); // Run the query. while ($row = $r->fetch_object()) { echo "<option value=\"$row->id\" "; if (isset($_POST['size']) && ($_POST['size'] == $row->id)) echo 'selected="selected"'; echo ">$row->size</option>\n"; } $r->free(); // Free up the resources. unset($r); $mysqli->close(); // Close the database connection. unset($mysqli); Link to comment Share on other sites More sharing options...
Bill Posted September 26, 2012 Author Share Posted September 26, 2012 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 Link to comment Share on other sites More sharing options...
Larry Posted October 5, 2012 Share Posted October 5, 2012 Is there a specific question still here? Link to comment Share on other sites More sharing options...
Bill Posted October 6, 2012 Author Share Posted October 6, 2012 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 Link to comment Share on other sites More sharing options...
Bill Posted October 10, 2012 Author Share Posted October 10, 2012 Think I'll revist this task later and go through the Larry's Advanced Oop book beacuse this task requires more study. Cheers Link to comment Share on other sites More sharing options...
Antonio Conte Posted October 10, 2012 Share Posted October 10, 2012 Without knowing the question really good here, this sounds like a JOIN problem. Try variations of LEFT or RIGHT OUTER JOIN instead of the inner join on the size table. The main difference is that inner joins are exclusive (rows without joins are ignored) while outer will include those rows will null values instead. Try experimenting a bit. If you want help solving this, I need example data from the tables. (Paste two-three rows from each table) 1 Link to comment Share on other sites More sharing options...
Bill Posted October 12, 2012 Author Share Posted October 12, 2012 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. Link to comment Share on other sites More sharing options...
Bill Posted October 12, 2012 Author Share Posted October 12, 2012 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 OUTER 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 Here is the sizes table: id size 1 25 grams 2 50 grams 3 75 grams. 4 100 grams 5 1.25 grams Hope this explanation clarifies things. Link to comment Share on other sites More sharing options...
Larry Posted October 13, 2012 Share Posted October 13, 2012 If I understand you correctly, then it's working properly. The query would only should all the sizes for a given coffee if it's available in all the sizes. As you've presented, general coffee ID 3 is only in 3 sizes; 4 is only in 2. 1 Link to comment Share on other sites More sharing options...
Bill Posted October 15, 2012 Author Share Posted October 15, 2012 It's true that the existing specific_coffees page shows coffees by category and when you click on view kona coffee you get a drop down list with all the various products in it. This is probably why the data in my specific_coffees table has gc.id 3 showing only in 3 sizes and 4 is only in 2. My real intention is to create a specific products listing for all coffee products in a given category similar to the non-coffee products listing but with a drop-down listing every size from the sizes table. A user i.e., myself can then select a product from several rows of products on a page displaying, image, coffee_product, price, stock, a drop down displaying every size and an add to cart button. Here is the php and html coding with my changes to list_coffees2.html but please disgread any minor variations from the original coding as I am trying to write a food dietary program with different cattegories of vegetables etc: <?php // This page is included by browse.php. // This page displays the available coffee products. // This page will make use of the query result $r. // The query returns an array of: description, image, sku, name, and stock. echo '<table border="0" width="100%" cellspacing="1" cellpadding="4"> <tr> <th align="center">Image</th> <th align="center">Coffee_Name</th> <th align="center">Price</th> <th align="center">Stock</th> <th align="center">Size</th> <th align="center">Cart</th> </tr> '; // Only display the header once: $header = false; // Added later in Chapter 8: include ('./includes/product_functions.inc.php'); // Loop through the results: while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) { // If the header hasn't been shown, create it: if (!$header) { ?> <!-- box begin --> <h2><?php echo $category; ?></h2> < <p><img alt="<?php echo $category; ?>" src="[url="http://localhost/Sites/ecom_book/ex2.2/html/products/http://localhost/Sites/ecom_book/ex2.2/html/products/%20echo%20$row['image'];%20?>"] <?php echo $row['description']; ?></p> <p><small>All listed products are currently available.</small> <?php // The header has now been shown:[/url] [url="http://localhost/Sites/ecom_book/ex2.2/html/products/http://localhost/Sites/ecom_book/ex2.2/html/products/%20echo%20$row['image'];%20?>"] $header = true; } // End of $header IF.[/url] [url="http://localhost/Sites/ecom_book/ex2.2/html/products/http://localhost/Sites/ecom_book/ex2.2/html/products/%20echo%20$row['image'];%20?>"] [/url] [url="http://localhost/Sites/ecom_book/ex2.2/html/products/http://localhost/Sites/ecom_book/ex2.2/html/products/%20echo%20$row['image'];%20?>"] ?> <tr> <td width="21%"><div class="img-box"><p><img alt="<? echo $row['name']; ?>" src="[/url] [url="http://localhost/Sites/ecom_book/ex2.2/html/products/"]http://localhost//Sites/ecom_book/ex2.2/html/products/[/url]<? echo $row['image']; ?>" /> </p></td> <td align="center" width="6%" ><? echo $row['coffee_name']; ?></td> <td align="center" width="6%" ><? echo $row['price']; ?></td> <td align="center" width="6%" ><? echo $row['stock']; ?></td> <input type="hidden" name="action" value="add" /> <td align="center" width="6%" ><select name="sku"><option value="0">choose</option><?php echo "<option value=\"{$row['sku']}\">{$row['coffee_size']}</option>\n"; ?></td>; <td align="center" width="6%" ><form action="[url="http://localhost/Sites/ecom_book/ex2.2/html/cart.php"]http://localhost/Sites/ecom_book/ex2.2/html/cart.php[/url]" method="get"> <td align="center" width="7%" height="10"><input type="submit" value="Add to Cart" class="button" /></p></td></form> </select></tr> <?php } // End of WHILE loop.} ?> </tbody></table> Link to comment Share on other sites More sharing options...
Larry Posted October 19, 2012 Share Posted October 19, 2012 I don't see the query defined in that code. Also, keep in mind that if you do it this way, you have to have PHP still associate unique SKUs (product IDs) with the combined selection or else the cart system won't work. 1 Link to comment Share on other sites More sharing options...
Bill Posted October 26, 2012 Author Share Posted October 26, 2012 Here is the first part of the stored procedure select_products that retrieves coffee products from various categories. The only glich is that all the various sizes from the database do not display in a drop down list. Bugs do exist when info is transfered to cart such as categorie names appearing instead product names. but if I can can the dropdown displaying all the sizes then I can move onto the next thing. SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku, CONCAT_WS(" - ", s.id, s.size) AS coffee_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 The html code, tables and data appear in previous postings of the thread. Cheers! Link to comment Share on other sites More sharing options...
Bill Posted November 2, 2012 Author Share Posted November 2, 2012 Good news! I've decided to hardcode the select menu for sizes into the non-coffee product script with a little success. One of the probs I face now is to transfer the size information to the cart menu. Here is the peace of the code I wrote in list_products.html to display the select dropdon menu containing sizes: echo ' <td align="center" width="2%"><select name="sku"> <OPTION VALUE=' . $row['sku'] . '>25</option>\n"; <OPTION VALUE=' . $row['sku'] . '>50</option>\n" <OPTION VALUE=' . $row['sku'] . '>75</option>\n" <OPTION VALUE=' . $row['sku'] . '>100</option>\n" </td>; <td align="center" width="26%"><p><a href="http://localhost/larryullman/Sites/ecom_book/ex2.2/html/cart.php?sku=' . $row['sku'] . '&action=add" class="button">Add to Cart</a></p></td> I think I will need to probably store sizes infomation into the database? Link to comment Share on other sites More sharing options...
Bill Posted November 10, 2012 Author Share Posted November 10, 2012 I am now trying to develop a website that requires a dropdown select menu with a cart program based on the coffee cart. It has to display various sizes like the one in the admin section of add_other_products.php. Is it feasible or not given that add inventory for specific products has a while loop with bound variables and a server request method unlike cart.php and cart.html that has a form action validation method and relies on the database integration through a complex join? Here is is the select menu in add_other_products.php fyi. <div class="field"><label for="quantity"><strong>Quantity</strong></label><br /><select name="quantity"<?php if (array_key_exists('quantity', $add_product_errors)) echo ' class="error"'; ?>> <option>Select One</option> <?php // Retrieve all the categories and add to the pull-down menu: $q = 'SELECT id, size FROM sizes ORDER BY size ASC'; $r = mysqli_query ($dbc, $q); while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) { echo "<option value=\"$row[0]\""; // Check for stickyness: if (isset($_POST['quantity']) && ($_POST['quantity'] == $row[0]) ) echo ' selected="selected"'; echo ">$row[1]</option>\n"; } ?> Link to comment Share on other sites More sharing options...
Bill Posted November 11, 2012 Author Share Posted November 11, 2012 I think I know what to do now! The solution requires another procedure called call_select_sizes to show the various sizes in the pulldown list and another while loop withingin the existing cart.php loop. Two while loops could be impractical from a design perspective! Link to comment Share on other sites More sharing options...
Larry Posted November 12, 2012 Share Posted November 12, 2012 Yes, that would work. I still don't know how you're going to map all this to an actual, unique product ID, but I assume you've figured that part out already. Link to comment Share on other sites More sharing options...
Bill Posted May 28, 2013 Author Share Posted May 28, 2013 Hi - sorry for the delayed response but I have made a bit of progress. I have managed to get the dropdown selection list to appear in cart.html but I can't select and pass each size variable to the url: $sizes = array(0.25, 0.50, 0.75, 1.0, 1.25, 1.5, 1.75, 2.0); $size = 'size[' . $row['sku'] . ']'; echo '<td align="center"><select name="size[' . $row['sku'] . ']" value="size">'; echo '<option value="size[' . $row['size'] . ']">Choose</option>'; foreach ($sizes as $id => $size) { echo '<option value="$id ">' ; if ($size == $id) echo ' selected="selected"'; echo "$size</option>" ; } echo '</select>'; And here is the part that transfers the size variable: <td align="center" width="26%"><p><a href="http://localhost/larryullman/Sites/ecom_book/ex2.2/html/cart.php?sku=' . $row['sku'] . '&action=move&qty='.$row['quantity']. '&action=move&size=' . $size . '">Add to Cart</a></p></td> The only problem is that the last value in the array, that is, 2.0 is only passed. Nevertheless, size=2 does appear in the url but no other variable in the array can be selected or sent via the url? Thanks for your suggestions! Link to comment Share on other sites More sharing options...
Larry Posted June 1, 2013 Share Posted June 1, 2013 First, you need to get rid of the value attribute of your SELECT element. Second, your first OPTION value (for choose) should be blank. Third, you use $id in your foreach loop but there's no key for the array (no meaningful key), so that's not useful as far as I can tell. Shouldn't you just use the size for the value of each option? Link to comment Share on other sites More sharing options...
Recommended Posts