Page 1 of 2

understanding this PHP code

Posted: Fri Feb 15, 2013 11:39 am
by jonnyfortis
Hello let me explain what i have. i have a webpage that displays individual products. when one is selected it takes the user to the product description page. Here they can see the product details. straight forward so far.
however each product is part of a category. For example product = red trousers category - trousers.
each product comes in a variety of sizes.

when you land on the description page you have the product ID of the item you selected on the first page
but you can select all the products from the category from a dropdown menu then submit this and this returns to the same page and gives you the value of the new selected product.

this is all working so far but i have some code for the product size menu so that when the product is selected is calls the sizes for this product.

below is the code from the menu

Code: Select all

<select name="os0" class="text" id="selectSize">
      <option value="Select Size">Select Size</option>
      <?php
                $query2 = sprintf("
				SELECT DISTINCT
					stock.StockID, size.Size 
				FROM 
					beauSS13_products AS prod 
					LEFT JOIN beauSS13_Stock AS stock ON prod.ID = stock.ID 
					LEFT JOIN beauSS13_SizeList AS size ON stock.SizeID = size.SizeID
				WHERE
					prod.ID = '%s' AND stock.Stock > 0
				ORDER BY
					size.SizeID ASC", GetSQLValueString($var1_Recordset1, "int"));
				$results2 = mysql_query($query2);
				while($row2 = mysql_fetch_array($results2)){
					?>
      <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
      <?php
				}
				
                ?>
    </select>
This is on a working site but i am having to make the latest product range and cant figure out what is going on with the code

this is also the other relevant code

Code: Select all

$var1_Recordset1 = "-1";
if (isset($_GET['ID'])) {
  $var1_Recordset1 = $_GET['ID'];
}
mysql_select_db($database_beau, $beau);
$query_Recordset1 = sprintf("SELECT * FROM beauSS13_products, beauSS13_Stock, beauSS13_SizeList WHERE beauSS13_products.ProductID = %s AND beauSS13_Stock.SizeID = beauSS13_SizeList.SizeID", GetSQLValueString($var1_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $beau) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
im not sure if there is another way of doing this or if i have provided enough information. I did just try changing the table names to the new table e,g beauSS13_Stock is the new table and beauStock was the old one. All table have the same columns so thought it was just this.

i think it will be easier if i understand what is going on with

Code: Select all

<select name="os0" class="text" id="selectSize">
      <option value="Select Size">Select Size</option>
      <?php
                $query2 = sprintf("
				SELECT DISTINCT
					stock.StockID, size.Size 
				FROM 
					beauSS13_products AS prod 
					LEFT JOIN beauSS13_Stock AS stock ON prod.ID = stock.ID 
					LEFT JOIN beauSS13_SizeList AS size ON stock.SizeID = size.SizeID
				WHERE
					prod.ID = '%s' AND stock.Stock > 0
				ORDER BY
					size.SizeID ASC", GetSQLValueString($var1_Recordset1, "int"));
				$results2 = mysql_query($query2);
				while($row2 = mysql_fetch_array($results2)){
					?>
      <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
      <?php
				}
				
                ?>
    </select>
thanks

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:03 pm
by requinix
I can't tell what you're asking. Are you trying to add (fix?) something to do with picking sizes? That's the only thing that seems to be missing.

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:04 pm
by jonnyfortis
yes im trying to get that code to work. i need to select from the menu the sizes that are available based on the product selected

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:15 pm
by requinix

Code: Select all

SELECT * FROM beauSS13_products, beauSS13_Stock, beauSS13_SizeList WHERE beauSS13_products.ProductID = %s AND beauSS13_Stock.SizeID = beauSS13_SizeList.SizeID
That query has a problem: you've defined a relationship between Stock and SizeList, but nothing with products. I'm not sure you even need the SizeList at all because it looks like this query is just for getting generic information about a product. The list of sizes needs to come separately.

So maybe

Code: Select all

SELECT
	*
FROM
	beauSS13_products AS prod
	JOIN beauSS13_Stock stock ON prod.ID = stock.ID
WHERE
	beauSS13_products.ProductID = %s
As for the other query, I doubt you should be using LEFT JOINs: neither the stock nor size information is optional. Use a regular JOIN for both.

Except I'm still not sure what's wrong. What is it doing now and what do you want it to do?

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:20 pm
by jonnyfortis
i have used the left join because this worked on the previous page. so just wanted to keep it like that, but if it can be done an easier way that would be better
Except I'm still not sure what's wrong. What is it doing now and what do you want it to do?
basically the rest of the page is working. the user can select the products but there are no sizes being displayed in the menu

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:26 pm
by requinix
Is prod.ID the right column for the ID number? The other query uses ProductID.

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:30 pm
by jonnyfortis
Is prod.ID the right column for the ID number? The other query uses ProductID.
that the thing prod.ID i dont know where this comes from . its not on the rest of the page nor is it in the db

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 12:39 pm
by jonnyfortis
actually below is the one that works

Code: Select all

$var1_Recordset1 = "-1";
if (isset($_GET['ID'])) {
  $var1_Recordset1 = $_GET['ID'];
}

mysql_select_db($database_beau, $beau);
$query_Recordset1 = sprintf("SELECT * FROM beauProd, beauStock, beauSizeList WHERE beauProd.ID = %s AND beauStock.SizeID = beauSizeList.SizeID", GetSQLValueString($var1_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $beau) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

$var2_Recordset2 = "-1";
if (isset($_GET['ID'])) {
  $var2_Recordset2 = $_GET['ID'];
}
mysql_select_db($database_beau, $beau);
$query_Recordset2 = sprintf("SELECT * FROM beauProd, beauCat WHERE beauCat.catID = beauProd.CatID AND beauProd.ID = %s", GetSQLValueString($var2_Recordset2, "int"));
$Recordset2 = mysql_query($query_Recordset2, $beau) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

Code: Select all

<?php
                $query2 = sprintf("
				SELECT DISTINCT
					stock.StockID, size.Size 
				FROM 
					beauProd AS prod 
					LEFT JOIN beauStock AS stock ON prod.ID = stock.ID 
					LEFT JOIN beauSizeList AS size ON stock.SizeID = size.SizeID
				WHERE
					prod.ID = '%s' AND stock.Stock > 0
				ORDER BY
					size.SizeID ASC", GetSQLValueString($var1_Recordset1, "int"));
				$results2 = mysql_query($query2);
				while($row2 = mysql_fetch_array($results2)){
					?>
      <option value="<?php echo $row2['Size']; ?>"><?php echo $row2['Size']; ?></option>
      <?php
				}
				
                ?>
there are a couple more querys on the page but i dont think they are anything to do with this

Code: Select all

<?php
				mysql_select_db($database_beau, $beau);
				$query = sprintf("SELECT * FROM beauProd WHERE CatID = '%s'", GetSQLValueString($row_Recordset1['CatID'], "int"));
				$results = mysql_query($query, $beau) or die(mysql_error());
				$productsInCategory = array();
				while($row = mysql_fetch_array($results)){
					$productsInCategory[] = $row['ID'];
					?>

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 1:14 pm
by requinix
So to be sure,

Code: Select all

SELECT DISTINCT
	stock.StockID, size.Size 
FROM 
	beauProd AS prod 
	LEFT JOIN beauStock AS stock ON prod.ID = stock.ID 
	LEFT JOIN beauSizeList AS size ON stock.SizeID = size.SizeID
WHERE
	prod.ID = '%s' AND stock.Stock > 0
ORDER BY
	size.SizeID ASC
is the problem? It's not returning any data?

As long as the product exists there will be a result. The stock and size data may be empty, thus creating an empty <option>, but there will be rows.
Look at the HTML source of the page. What is the entire HTML for the outputted <select>?

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 2:37 pm
by jonnyfortis
What is the entire HTML for the outputted <select>?
you mean load the page then view html and post it?

this is the html for the select but not sure if this is what you mean?

Code: Select all

<option value="Select Size">Select Size</option>
            <option value="0-3 months">0-3 months</option>
            <option value="3-6 months">3-6 months</option>
            <option value="18 - 24 months">18 - 24 months</option>
          </select>

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 2:38 pm
by jonnyfortis
as i said before i have this working if you want a URL?

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 2:38 pm
by requinix
jonnyfortis wrote:basically the rest of the page is working. the user can select the products but there are no sizes being displayed in the menu

Code: Select all

<option value="Select Size">Select Size</option>
            <option value="0-3 months">0-3 months</option>
            <option value="3-6 months">3-6 months</option>
            <option value="18 - 24 months">18 - 24 months</option>
          </select>
That sure looks like sizes are being displayed in the menu.

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 3:06 pm
by Christopher
jonnyfortis wrote:as i said before i have this working if you want a URL?
No. One essential skill for programmers is to be able to communicate clearly where the problem is. You need to find the common language that programmers use when describing design and code. (See DDD's ubiquitous language)

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 3:42 pm
by jonnyfortis
That sure looks like sizes are being displayed in the menu.
thats the working one that i posted

Code: Select all

<option value="Select Size">Select Size</option>
      <br />
<b>Warning</b>:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in <b>E:\Domains\b\beau.com\user\htdocs\SS13\product-description.php</b> on line <b>338</b><br />
    </select>
is the failing one

and line 338 is

Code: Select all

while($row2 = mysql_fetch_array($results2)){
No. One essential skill for programmers is to be able to communicate clearly where the problem is. You need to find the common language that programmers use when describing design and code. (See DDD's ubiquitous language)
i agree

Re: understanding this PHP code

Posted: Fri Feb 15, 2013 4:32 pm
by requinix
Ah ha, an error message! :drunk:

That $query2 has some sort of big error with it. I don't see any syntax errors so I think you're using the wrong table and/or column names.