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

    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>
    

      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 ?

        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.

          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!

            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 |

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

                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>

                  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!

                    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";
                    
                    ?>

                      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)"; 
                      	} 

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

                        I just worked it out :

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

                          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 ?

                            and is && as you have
                            or is ||

                              Write a Reply...