Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Bill

Selecting Products With Dropdown List Using Size Table

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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

  • Upvote 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

  • Upvote 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

  • Upvote 1

Share this post


Link to post
Share on other sites

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>



Share this post


Link to post
Share on other sites

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.

  • Upvote 1

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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";
}
?>

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...