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>