help with mysql dropdown list

[adammc]

Hi Folks,

I was hoping someone could help me with this.
Im trying to automatically fill a dropdown list with data from MYSQL table.

Sizes Table
--------

ProductId ¦ size1 ¦ size2 ¦ size3 ¦ size4 ¦ size4 ¦ size6 ¦

* the 'size' rows hold a numeric value for the quantity I have of each

I was hoping to only list the sizes that have a greater number than 0.
I have used an auto fill select code on another project but I'm just not sure how to fill in the blanks.

$query = mysql_query("SELECT * FROM sizes WHERE `productCode`='$productCode' AND? size1 23456 >= 1 ");  // autofill dropdown list 'select' with options from DB
$r = mysql_fetch_array($query); 
echo "<select name=option1>"; 
 
for(?) 
{ 
echo "<option value=?>?</option>"; 
}  
echo "</select>"; 

Any help would be GREATLY apppreciated. :)

[dreamcatcher]

Hi adammc,

$query = mysql_query("SELECT * FROM sizes WHERE `productCode`='$productCode'");
$r = mysql_fetch_array($query);

echo "<select name=option1>";

for ($i=1; $i<7; $i++)
{

if ($r['size'.$i]>0)
{
echo "<option value='".$r['size'.$i]."'>$r['size'.$i]</option>";
}

}

echo "</select>";

Not tested, but should work ok I think.

dc

[adammc]

Hi Dreamcatcher,
Thanks for the response :)

I got a parse error on that one, somewhere here:
echo "<option value='".$r['size'.$i]."'>$r['size'.$i]</option>";

Will your code fill my select box if it finds a matching productId, listing the data held in each size column if the numeric value is over 0?

ProductId ¦ size1 ¦ size2 ¦ size3 ¦ size4 ¦ size4 ¦ size6 ¦

[grandpa]

This is bugging me.. can you perform a numeric value test for size5, since it doesn't exist?

if ($r['size'.$i]>0) // size5

adammc, you might want to consider using error reporting until you get it worked out.
error_reporting(E_ALL);

[adammc]

I wasnt quite sure how to structure my database, I am open to suggestions as I am sure it can be done better.

I am using a content management system (custom built) to add products to the website. I am using another table called 'products' this contains rows like:

Id (auto inc.)
Product id
name
price
description
date
etc

On the page / script that I use to add the products to the DB, I have options for sizes and quantity i have in stock for each size:

Size 1 - (input field for quantity)
Size 2 - ("")
Size 3 - ("")
Size 4 - ("")
Size 5 - ("")
Size 6 - "")

My script currently adds the size/qty data above into the sizes table and the rest of the data into the products table.

Any ideas on how I should best do this?

[adammc]

Hi Grandpa,

Am still unable to see the result of your idea due to the parse error somewhere here:
echo "<option value='".$r['size'.$i]."'>$r['size'.$i]</option>";

[grandpa]

Could you show the specific error? That might help someone. DC's solution ought to work.. I would create a variable if I thought it might fix a parse error. Note that I also tend to make use of the double quote, so it's necessary to escape one.

if ($r['size'.$i]>0)
{
$current = $r['size'].$i;
echo "<option value=\"".$current."\">$current</option>";
}

Maybe that will help.

[dreamcatcher]

Thanks Grandpa,

I think this line though:
$current = $r['size'].$i;

should be:
$current = $r['size'.$i];

or try this instead:
$current = $r["size".$i""];

dc

[adammc]

sorry grandpa,

The error message was:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/kimrob/public_html/CMS2/test.php on line 86

[eelixduppy]

I would stick to this one:
$current = $r['size'.$i];

The other suggestion is missing a period (otherwise the quote is in the wrong place) ;)

[adammc]

Thank you sooooo much for your help everyone.
Its now working a treat :)

[adammc]

Hmmm...

I think I may have structured my table the wrong way for what I need :(

What I wanted it to output was the size names into the dropdown list.

The names of the sizes arent listed anywhere except in the name of the rows:
size1 ¦ size2 ¦ size3 ¦ size4 ¦ size4 ¦ size6 ¦

The code you helped me with outputted the QTY of each size I have available.

[adammc]

Could i do something like this?

if size 1 is greater than '0' then echo 'size 1' into the dropdown list

if size 2 is greater than '0' then echo 'size 2' into the dropdown list

if size 3 is greater than '0' then echo 'size 3' into the dropdown list

etc ?

[adammc]

Well, I got it working (roughly though)

Is this the cleanest way I can do this?

[PHP]<select name=option1>

<?

$query = mysql_query("SELECT * FROM sizes WHERE `productCode`='$productCode'");
$r = mysql_fetch_array($query);

$size1 = $r["size1"];
$size2 = $r["size2"];
$size3 = $r["size3"];
$size4 = $r["size4"];
$size5 = $r["size5"];
$size6 = $r["size6"];

if ($size1 >= 1) {
echo "<option value='size1'>Size 1</option>";
}
else {
echo "";
}

if ($size2 >= 1) {
echo "<option value='size2'>Size 2</option>";
}
else {
echo "";
}

if ($size3 >= 1) {
echo "<option value='size3'>Size 3</option>";
}
else {
echo "";
}

if ($size4 >= 1) {
echo "<option value='size4'>Size 4</option>";
}
else {
echo "";
}

if ($size5 >= 1) {
echo "<option value='size5'>Size 5</option>";
}
else {
echo "";
}

if ($size6 >= 1) {
echo "<option value='size6'>Size 6</option>";
}
else {
echo "";
}

?>

</select>[/PHP]

[jatar_k]

what about some variable variables?

<?
$query = mysql_query("SELECT * FROM sizes WHERE `productCode`='$productCode'");
$r = mysql_fetch_array($query);
$size1 = $r["size1"];
$size2 = $r["size2"];
$size3 = $r["size3"];
$size4 = $r["size4"];
$size5 = $r["size5"];
$size6 = $r["size6"];

echo '<select name=option1>'; 
$counter = 1; 
while(isset(${'size' . $counter})) { 
 if (${'size' . $counter} >= 1) { 
 echo "<option value='size",$counter,"'>Size ",$counter,"</option>"; 
 } else { 
 echo ""; 
 } 
 $counter++; 
} 
echo '</select>'; 
?>

you could also set the while to be explicit

while($counter <= 6) {

that should work, didn't test it though