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

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. 🙂

thepeccavi

Here is some help (hopefully)

<select name="aname">
<?php
$query = mysql_query("SELECT * FROM sizes WHERE `productCode`='$productCode'");

// Process results
while($product = mysql_fetch_assoc($query)){
if($product['size1'] != 0 && $product['size2'] != 0 && $product['size4'] != 0 && $product['size5'] != 0 && $product['size6'] != 0){
echo '<option value="' . $product['ProductName'] . '">' . $product['ProductName'] . '</option>' . "\n"
}
}
// Make the rest of your form......
?>
</select>

adammc

Hi,
Thanks for the reply 🙂

Can you possibly explain what I should be putting in here?

$product['ProductName'

As I am not sure what your code is pulling out of the DB ?

cgraz

in the code provided, there is a line that says

while($product = mysql_fetch_assoc($query)){

What that is doing is taking the results from your query ($result) and building an associative array called $product. Associative means the array keys are not numerical, but rather correspond to the field names in your mysql database. So if you have the fields name, address, email, and phone in your database, after the line above, you could access them using

$product["name"]
$product["address"]
$product["email"]
$product["phone"]

So to answer your question, $product["ProductName"] would print the field ProductName in your mysql database. If that field doesn't exist, replace ProductName with the name of that field.

HTH.

thepeccavi

I thought that showing a dropdown list with product names would be more useful to users than product codes! I recommend that you add a ProductName field to your database (it should be NOT NULL and UNIQUE)... anyway, that is what ther scipt is doing, I will provide a more explained version:

<!-- Start the select field -->
<select name="aname">

<?php
// Run a query to get all fields and rows from the database table
$query = mysql_query('SELECT * FROM sizes');

// Process the results of that query
// An assosiative array of the query results
while($product = mysql_fetch_assoc($query)){

// If all sizes are not 0...
if($product['size1'] != 0 && $product['size2'] != 0 && $product['size4'] != 0 && $product['size5'] != 0 && $product['size6'] != 0){

// Echo a option with the value (what it returns into it's $_POST/$_GET bit) being the product ID and displaying the ProductName
echo '<option value="' . $product['ProductID'] . '">' . $product['ProductName'] . '</option>' . "\n"
}
}
// Now you can make the rest of your form!
?>
</select>

The Table should look something like this:

[code]

ProductID | ProductName | size1 | size2 | size3 | size4 | size5 | size6 |

VALUES......................................................................................

[/code]

Hope that helps!

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.

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?

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

thepeccavi

Yes. That should work, just remember to be connected to the db and have the right DB selected first!

If you have any problems with it, I'll be willing to help. One little thing, you may want to only use double quotes(") around stings with variables in. This is because PHP check strings within double quotes for variables, meaning that the script is slower... I normally just use single quotes and concantate variables like so:

$query = mysql_query('SELECT * FROM sizes WHERE productCode = \'' . $productCode . '\'')

Doing this may make big scripts run faster...

One more thing, you should try and use valid XHTML...

<select name="option1">

instead of

<select name=option1>

Hope this helps!

cgraz

your else statements where you echo "" aren't necessary. You really just need the if statement. If it evaluates to false, it won't do anything and will move onto the next one.

Also, you could save yourself a few lines of code using extract() and Variable variables.

Here's some code for you

<?php

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

extract($r);

echo "<select name=\"option1\">\n";

for($i=1; $i<7; $i++)
{
	$var = "size".$i;

if($$var >= 1)
{
	echo "<option value=\"size".$i."\">Size ".$i."</option>\n";
}
}

echo "</select>\n";

?>

adammc

That worked a treat guys, thank you 🙂

I have code for the 'add to cart' button following the code above....
How would I form my select statement to only show the button if the sizes table contained a size greater than 0 ?

Somthing like this ????

if ($size1, $size2, $size3, $size4, $size5, $size6 >= 1) { 
	echo "<input type='submit' value='add to cart' name='B1' STYLE='font-size:8pt; background-color:000000; color:CCCCCC'> "; 
	} 
	else { 
	echo "(not in stock)"; 
	}

adammc

Whoo Hooo !!
I must be starting to learn something 😃

I just worked it out :

if ($size1 && $size2 && $size3 && $size4 && $size5 && $size6 >= 1) {

adammc

Hmmm.. Maybe not 🙁

Its not showing the add to cart button of 1 or more of the size rows contain less than 1

I assume i need to say if

if ($size1 ???OR??? $size2 ???OR???$size3 ???OR???$size4 ???OR???$size5 ???OR???$size6 >= 1) {

Can anyone possibly tell me, how would I do the OR in the ablove code ?

cgraz

and is && as you have
or is ||