Page 1 of 1
two values from one select list
Posted: Thu Jul 18, 2013 4:31 am
by jonnyfortis
Hello, i have a product page that has a select list that is showing sizes of some clothes. when the page is sent to the cart i need to send two value from the select list. these values are put into an array to be sent to the cart. currently it is just sending
Code: Select all
<?php echo $row_rsProduct['Size']?>
but i also need it to send
Code: Select all
<?php echo $row_rsProduct['StockID']; ?>
this is what i have so far
Code: Select all
<select name="SelectSize" id="SelectSize">
<option value="Select Size">Select Size</option>
<?php
do {
?>
<option value="<?php echo $row_rsProduct['Size']?>"><?php echo $row_rsProduct['Size']?></option>
<?php
} while ($row_rsProduct = mysql_fetch_assoc($rsProduct));
$rows = mysql_num_rows($rsProduct);
if($rows > 0) {
mysql_data_seek($rsProduct, 0);
$row_rsProduct = mysql_fetch_assoc($rsProduct);
}
?>
</select>
this is how the information is being sent to the cart
Code: Select all
// *** Add item to Shopping Cart via form ***
$XC_editAction1 = $_SERVER["PHP_SELF"];
if (isset($_SERVER["QUERY_STRING"])) $XC_editAction1 = $XC_editAction1 . "?" . $_SERVER["QUERY_STRING"];
if (isset($_POST["XC_addToCart"]) && $_POST["XC_addToCart"] == "form1") {
$NewRS=mysql_query($query_rsProduct, $beau) or die(mysql_error());
$XC_rsName="rsProduct"; // identification
$XC_uniqueCol="ProductID";
$XC_redirectTo = "";
$XC_redirectPage = "../cart.php";
$XC_BindingTypes=array("RS","RS","FORM","FORM","RS","RS","RS","NONE");
$XC_BindingValues=array("StockID","ProductID","SelectSize","Quantity","Product","Price","Stock","");
$XC_BindingLimits=array("","","","","","","","");
$XC_BindingSources=array("","","","","","","","");
$XC_BindingOpers=array("","","","","","","","");
require_once('XCInc/AddToXCartViaForm.inc');
}
and this is the form
Code: Select all
<form id="form1" name="form1" method="post" action="<?php echo $XC_editAction1; ?>">
<select name="SelectSize" id="SelectSize">
<option value="Select Size">Select Size</option>
<?php /* note about passing the StockID Both the size needs to be passed using the select list and the stockID, the size chosen in the size and the stockID in the stock on the shopping cart array*/?>
<?php
do {
?>
<option value="<?php echo $row_rsProduct['Size']?>"><?php echo $row_rsProduct['Size']?><?php echo $row_rsProduct['StockID']; ?></option>
<?php
} while ($row_rsProduct = mysql_fetch_assoc($rsProduct));
$rows = mysql_num_rows($rsProduct);
if($rows > 0) {
mysql_data_seek($rsProduct, 0);
$row_rsProduct = mysql_fetch_assoc($rsProduct);
}
?>
</select>
<input type="image" src="../images/SS13AddToCart.jpg" border="0" name="submit"/>
<input type="hidden" name="XC_recordId" value="<?php echo $row_rsProduct['ProductID']; ?>" />
<input type="hidden" name="XC_addToCart" value="form1" />
<input name="NewPrice" type="hidden" value="<?php echo $discountThirtyResult; ?>" />
</form>
i am not sure i am going about this the correct way but i need to the stockID to be sent as the stock array as this stockID needs to be passed to the database later as this is the ID that is needed to update the product stock at a later date
here are the tables i have built
table 1 - beauSS13_products
ProductID
CatID
table 2 - beauSS13_Cat
catID
table3 - beauSS13_Stock
StockID
ID (this was named like this incorrectly) but this joins with ProductID
SizeID
Stock
beauSS13_SizeList
SizeID
Size
Re: two values from one select list
Posted: Thu Jul 18, 2013 12:20 pm
by requinix
You're putting it in the wrong place: goes in the option value, not in the text.
But you can't really make a select submit two values. You could combine them into one string like "large,123" but that's inadvisable.
Use just an ID for the row, then on the receiving end look up the corresponding size and stock ID.
Re: two values from one select list
Posted: Wed Jul 31, 2013 2:09 pm
by jonnyfortis
hello what you say i sort of get the logic but am unsure how to go about it. so i have started again as this is for a new part of the site and didnt want to use data from the live site.
i have cleaned the database and am working with just 2 products for now
StockID | ProductID | SizeID | Stock
-------------------------------------------
353 | 582 | 14 | 5
-------------------------------------------
352 | 577 | 14 | 5
StockID is auto increment
ProductID 582 is t-shirt red
ProductID 577 is t-shirt blue
SizeID14 is one size
Stock of 5 is 5 in stock
in the select list i need to show the 'size' but need to send the value of StockID to the database
currently the select list looks like:
Code: Select all
<select name="select" id="select">
<?php
do {
?>
<option value="<?php echo $row_rsProduct['StockID']; ?>"><?php echo $row_rsProduct['SizeID']; ?><?php echo $row_rsProduct['Size']?><?php echo $row_rsProduct['StockID']; ?></option>
<?php
} while ($row_rsProduct = mysql_fetch_assoc($rsProduct));
$rows = mysql_num_rows($rsProduct);
if($rows > 0) {
mysql_data_seek($rsProduct, 0);
$row_rsProduct = mysql_fetch_assoc($rsProduct);
}
?>
</select>
the sql joining all the relevant tables is
Code: Select all
$var1_rsProduct = "-1";
if (isset($_GET['productID'])) {
$var1_rsProduct = $_GET['productID'];
}
mysql_select_db($database_beau, $beau);
$query_rsProduct = sprintf("SELECT * FROM beauAW13_Cat, beauAW13_products, beauAW13_Stock, beauAW13_SizeList WHERE beauAW13_products.catID = beauAW13_Cat.catID AND beauAW13_products.ProductID = beauAW13_Stock.ProductID AND beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND beauAW13_products.ProductID = %s AND beauAW13_Stock.Stock != 0 ", GetSQLValueString($var1_rsProduct, "int"));
$rsProduct = mysql_query($query_rsProduct, $beau) or die(mysql_error());
$row_rsProduct = mysql_fetch_assoc($rsProduct);
$totalRows_rsProduct = mysql_num_rows($rsProduct);
what i am currently getting at the moment is the "one size" on the select list and when i get to send the details to the database i will only be sending the size and i need to send the StockID.
send the Use just an ID for the row, then on the receiving end look up the corresponding size and stock ID.
how do i do this id i send the sizeID this will just be 14 and how can i match this up to the stockID?
really could do with some help on this
thanks
Re: two values from one select list
Posted: Wed Jul 31, 2013 3:51 pm
by mecha_godzilla
Hi,
I think what requinix was suggesting is that you populate the selection menu using either the StockID or ProductID value, and then run a query after the form has been submitted to retrieve the other values.
So, when you generate your menu you could use the value of StockID as the submitted value (because this is guaranteed to be unique) and the option "label" would be the product name derived from the value of ProductID - by which I mean that you not only need to retrieve a list of all your products from the main table, but in the same query you would then need to JOIN it with a look-up table to retrieve the product name. I'm going to have to guess the names of your tables here, but your query might look something like this:
Code: Select all
SELECT StockID, ProductID, Size, Stock, ProductName
FROM Products
JOIN ProductDescriptions ON Products.ProductID = ProductDescriptions.ProductDescriptionID
WHERE Stock > 0
ORDER BY ProductName ASC
Your menu generation code would then look something like this
Code: Select all
<option value="<?php echo $row_rsProduct['StockID']; ?>"><?php echo $row_rsProduct['ProductName']; ?></option>
and when the form is submitted you would capture the selected option in a variable - such as $StockID - and then run this query:
Code: Select all
SELECT Size
FROM Products
WHERE StockID = '$StockID'
LIMIT 1
Once you've retrieved the result, you then have all the values you need to update the cart.
HTH,
Mecha Godzilla
Re: two values from one select list
Posted: Thu Aug 01, 2013 3:34 am
by jonnyfortis
hello so are you saying build a new query for the select list on the product description page (this is where the current select list is) as use similar to your suggestion of
Code: Select all
SELECT StockID, ProductID, Size, Stock, ProductName
FROM Products
JOIN ProductDescriptions ON Products.ProductID = ProductDescriptions.ProductDescriptionID
WHERE Stock > 0
ORDER BY ProductName ASC
or add a join to the current query which looks like this
Code: Select all
$var1_rsProduct = "-1";
if (isset($_GET['productID'])) {
$var1_rsProduct = $_GET['productID'];
}
mysql_select_db($database_beau, $beau);
$query_rsProduct = sprintf("SELECT * FROM beauAW13_Cat, beauAW13_products, beauAW13_Stock, beauAW13_SizeList WHERE beauAW13_products.catID = beauAW13_Cat.catID AND beauAW13_products.ProductID = beauAW13_Stock.ProductID AND beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND beauAW13_products.ProductID = %s AND beauAW13_Stock.Stock != 0 ", GetSQLValueString($var1_rsProduct, "int"));
$rsProduct = mysql_query($query_rsProduct, $beau) or die(mysql_error());
$row_rsProduct = mysql_fetch_assoc($rsProduct);
$totalRows_rsProduct = mysql_num_rows($rsProduct);
this would be just for the select list?
I'm going to have to guess the names of your tables here
the tables that are relevant are listed in the query above
then in the cart page use the following suggestion
Code: Select all
SELECT Size
FROM Products
WHERE StockID = '$StockID'
LIMIT 1
thanks
Re: two values from one select list
Posted: Thu Aug 01, 2013 8:17 am
by jonnyfortis
i passed the stock id as suggested
using stock is as 355 for example
and now in the cart when i echo out the stockID it shows the 355 <?php echo $XCart_StockID = ${$XCName}["contents"][0][$XCart__i]; ?> and when I echo out the size this also shows 355 <?php echo $XCart_Size = ${$XCName}["contents"][2][$XCart__i]; ?>
i then added the following
Code: Select all
mysql_select_db($database_beau, $beau);
$query_rsSize = "SELECT * FROM beauAW13_Stock, beauAW13_SizeList WHERE beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND beauAW13_Stock.StockID = '$XCart_StockID'";
$rsSize = mysql_query($query_rsSize, $beau) or die(mysql_error());
$row_rsSize = mysql_fetch_assoc($rsSize);
$totalRows_rsSize = mysql_num_rows($rsSize);
and tried ehoeing out <?php echo $row_rsSize['Size']; ?> to show the size but am getting an error
Notice: Undefined variable: XCart_StockID in E:\Domains\b\beau\user\htdocs\cart1.php on line 43
line 43 is
$query_rsSize = "SELECT * FROM beauAW13_Stock, beauAW13_SizeList WHERE beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND
Re: two values from one select list
Posted: Thu Aug 01, 2013 2:36 pm
by mecha_godzilla
Hi again,
I don't quite understand how your tables all interact, so it would probably be better if I explain what I was trying to demonstrate with the example code I posted:
1. In order to populate your selection menu you just need to retrieve two values - the stock IDs and names of every product in the database. In this query you will also need to include whichever other query parameters are important - in the example I gave, the selection menu wouldn't be populated with a product that wasn't in stock.
2. You then need to loop through these results to build your selection menu code - the value for the menu option will be the stock ID and the label for the menu option will be the name of the product.
3. When your site visitor selects an option from the menu, the form is submitted and a single value is sent back to your script - the stock ID. Based on this value alone, you have the required amount of information you need to retrieve all the other details about the associated product so you then just need to run a query that retrieves all the product information based on that stock ID (i.e. you are only retrieving one record, using the stock ID as a query parameter).
4. You then update your cart with the information retrieved from the last query.
HTH,
M_G
Re: two values from one select list
Posted: Fri Aug 02, 2013 4:08 am
by jonnyfortis
1. In order to populate your selection menu you just need to retrieve two values - the stock IDs and names of every product in the database. In this query you will also need to include whichever other query parameters are important - in the example I gave, the selection menu wouldn't be populated with a product that wasn't in stock.
in order to populate the select list i need to retrieve the information from the property table, stock table and the size table as demonstrated below
the product table
beauAW13_products
-----------------------------
ProductID
the stock table
beauAW13_Stock
-----------------------------
StockID
ProductID
SizeID
and the size table
beauAW13_SizeList
-----------------------------
SizeID
Size
with your explanation i understand the workflow and how i need to do it but just not HOw it should be done. With your sql i am uncertain if this is a new query just for the select list
im sorry for all the question but getting a bit desperate. thanks so much for you help
Re: two values from one select list
Posted: Fri Aug 02, 2013 4:05 pm
by mecha_godzilla
Hi again,
You will need two queries:
1. The first query will be used to populate the selection menu and will retrieve the details of
all of the products in the database.
2. The second query will be used to retrieve the details of
one product in the database. This query is only run after the form has been submitted.
The starting point for both of these queries is the beauAW13_Stock table - when you need to retrieve the product name or product size, you will be using the other two tables as "look-up" tables and your query would look something like this:
Code: Select all
SELECT beauAW13_Stock.StockID, beauAW13_products.ProductName, beauAW13_SizeList.Size
FROM beauAW13_Stock
JOIN beauAW13_products ON beauAW13_Stock.ProductID = beauAW13_products.ProductID
JOIN beauAW13_SizeList ON beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID
ORDER BY ProductName ASC
This query will then return the stock ID, product name and product size for all products in the database. At this point you have the information you need to display your selection menu:
Code: Select all
<select id="ProductSelector">
<option value="$StockID">$ProductName ($Size)</option>
<option value="$StockID">$ProductName ($Size)</option>
<option value="$StockID">$ProductName ($Size)</option>
<option value="$StockID">$ProductName ($Size)</option>
...
</select>
The site visitor then selects the appropriate product from the selection menu, and when the form is submitted $StockID will be sent back to your script.
You can then use this value to retrieve all the product information:
Code: Select all
SELECT beauAW13_Stock.StockID, beauAW13_products.ProductName, beauAW13_SizeList.Size
FROM beauAW13_Stock
JOIN beauAW13_products ON beauAW13_Stock.ProductID = beauAW13_products.ProductID
JOIN beauAW13_SizeList ON beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID
WHERE beauAW13_Stock.StockID = '$StockID'
LIMIT 1
As you can see, the queries are similar but not identical - the second query will pick only one product from the database, not all of them. Note that (technically) in the second query we do not need to retrieve the stock ID because this information has already been sent back from the form.
HTH,
M_G
Re: two values from one select list
Posted: Mon Aug 05, 2013 6:18 am
by jonnyfortis
Hello
thanks so much for your help so far.
you say there will be two query's i just wanted to clarify. i already have one query and I will to keep this one as this query is the one that identifies to product from the product list page
Code: Select all
$var1_rsProduct = "-1";
if (isset($_GET['productID'])) {
$var1_rsProduct = $_GET['productID'];
}
mysql_select_db($database_beau, $beau);
$query_rsProduct = sprintf("SELECT * FROM beauAW13_Cat, beauAW13_products, beauAW13_Stock, beauAW13_SizeList WHERE beauAW13_products.catID = beauAW13_Cat.catID AND beauAW13_products.ProductID = beauAW13_Stock.ProductID AND beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND beauAW13_products.ProductID = %s AND beauAW13_Stock.Stock != 0 ", GetSQLValueString($var1_rsProduct, "int"));
$rsProduct = mysql_query($query_rsProduct, $beau) or die(mysql_error());
$row_rsProduct = mysql_fetch_assoc($rsProduct);
$totalRows_rsProduct = mysql_num_rows($rsProduct);
and then i add your two querys? As these are just used for the select list?
Re: two values from one select list
Posted: Mon Aug 05, 2013 5:04 pm
by Christopher
I am confused about the "receiving two values" and this <select> code:
Code: Select all
<select name="select" id="select">
<?php do { ?>
<option value="<?php echo $row_rsProduct['StockID']; ?>"><?php echo $row_rsProduct['SizeID']; ?><?php echo $row_rsProduct['Size']?><?php echo $row_rsProduct['StockID']; ?></option>
It passed the value of $row_rsProduct['StockID'], but I thought you needed two values? Should't the option value have both StockID and SizeID, maybe delimited? Or is the StockID a one-to-one relationship with the SizeID?
Re: two values from one select list
Posted: Tue Aug 06, 2013 3:57 am
by jonnyfortis
Christopher wrote:I am confused about the "receiving two values" and this <select> code:
Code: Select all
<select name="select" id="select">
<?php do { ?>
<option value="<?php echo $row_rsProduct['StockID']; ?>"><?php echo $row_rsProduct['SizeID']; ?><?php echo $row_rsProduct['Size']?><?php echo $row_rsProduct['StockID']; ?></option>
It passed the value of $row_rsProduct['StockID'], but I thought you needed two values? Should't the option value have both StockID and SizeID, maybe delimited? Or is the StockID a one-to-one relationship with the SizeID?
well it needs to pass the stockID to do stock updates when the page is returned but it needs also send the size so this can be displayed on the cart page and have the size as reference ( so we know what size it is ). So the most important one is the stockID because this has the unique number. passing both variables is one way of achieving what i need i presume.
Re: two values from one select list
Posted: Tue Aug 06, 2013 2:34 pm
by Christopher
jonnyfortis wrote:well it needs to pass the stockID to do stock updates when the page is returned but it needs also send the size so this can be displayed on the cart page and have the size as reference ( so we know what size it is ). So the most important one is the stockID because this has the unique number. passing both variables is one way of achieving what i need i presume.
Yeah, but it is only sending the StockID and not the Size. I would expect to make the value delimited if you really wanted to send both:
Code: Select all
<select name="stockandsize" id="stockandsize">
<?php do { ?>
<option value="<?php echo $row_rsProduct['StockID']; ?>|<?php echo $row_rsProduct['SizeID']; ?>"><?php echo $row_rsProduct['SizeID']; ?><?php echo $row_rsProduct['Size']?><?php echo $row_rsProduct['StockID']; ?></option>
Also, why is the name of the <select> set to "select"? It should be more meaningful like I show above. Then when you get the form submissions, you would do something like this:
Code: Select all
list($StockID, $SizeID) = explode('|', $_POST['stockandsize']);
Re: two values from one select list
Posted: Wed Aug 07, 2013 6:59 am
by jonnyfortis
Thanks for everyone advise. I managed to get it working
I sent the stockID on;y to the cart page, made a new variable based on the stockID the made a query and got all the information based on the new stockID variable
Code: Select all
<?php
$newStockID = $XCart_StockID = ${$XCName}["contents"][0][$XCart__i];
mysql_select_db($database_beau, $beau);
$query_rsSize = "SELECT * FROM beauAW13_SizeList, beauAW13_Stock WHERE beauAW13_Stock.SizeID = beauAW13_SizeList.SizeID AND beauAW13_Stock.StockID = '$newStockID'";
$rsSize = mysql_query($query_rsSize, $beau) or die(mysql_error());
$row_rsSize = mysql_fetch_assoc($rsSize);
$totalRows_rsSize = mysql_num_rows($rsSize);
?>
and the select list
Code: Select all
<option value="<?php echo $row_rsProduct['StockID']; ?>"><?php echo $row_rsProduct['Size']?></option>