prevent duplicate entries in select menu

[SeenGee]

i'm populating a select menu from mySQL using this code:

$sql = "SELECT * FROM products order by printer"; 

$result = mysql_query($sql) or die(mysql_error());
// Counts the number of rows 
$num_rows=mysql_num_rows($result); 

// Tests if the database is empty 
if ($num_rows == 0) { 
    //if select menu is empty
} 

// If there are records.... 
else { 
    // Form to pick the product to be ammended 
	echo "<div align=\"center\">";
    echo "<FORM method=post action=\"\">"; 
    echo "Please select a printer to check for matching cartridges<BR>"; 
    echo "<select name=\"printer\">"; 

// Loop to populate the menu 
while ($row=mysql_fetch_array($result)) { 
    $printer=$row["printer"]; 
    $brand=$row["brand"]; 
    $code=$row["code"]; 
    $productid=$row["productid"]; 
    // Display options in the pulldown box 
    echo "<option value=\"$productid\">$printer</option>"; 
    } // end while 

echo "</select>"; 
} // end else 

echo "<BR><BR>"; 
echo "<input type=\"submit\" value=\"View the item\"></FORM>";
echo "</div>"; 

The problem is that the var that i am retrieving, the name of the printer the cartridge fits, occurs multiple times in the table. How can i make it so that each printer is only entered into the select menu once?

Thanks.

[nlhowell]

$sql = "SELECT * FROM products order by printer";  

$result = mysql_query($sql) or die(mysql_error()); 
// Counts the number of rows 
$num_rows=mysql_num_rows($result);  

// Tests if the database is empty 
if ($num_rows == 0) {  

    //if select menu is empty 
}  

// If there are records.... 
else {  

    // Form to pick the product to be ammended 
    echo "<div align=\"center\">"; 
    echo "<FORM method=post action=\"\">";  

    echo "Please select a printer to check for matching cartridges<BR>";  

    echo "<select name=\"printer\">";  

// this keeps track of what tables have been used.
$printers = array();
// Loop to populate the menu 
while ($row=mysql_fetch_array($result)) {  
    // if we've already looked at this one, skip it
    if(in_array($printers,$row["printer"])) continue;
    $printer=$row["printer"];  
    $brand=$row["brand"];  
    $code=$row["code"];  
    $productid=$row["productid"];  
    // Display options in the pulldown box 
    echo "<option value=\"$productid\">$printer</option>";  
    } // end while 

echo "</select>";  
} // end else 

echo "<BR><BR>";  

echo "<input type=\"submit\" value=\"View the item\"></FORM>"; 
echo "</div>";

[nlhowell]

or (probably better)

$sql = "SELECT * FROM products ORDER BY printer GROUP BY printer";

Makes SQL only retrieve one record per value in products.printer.

[SeenGee]

$sql = "SELECT * FROM products ORDER BY printer GROUP BY printer";

gives me an error:

You have an error in your SQL syntax near 'GROUP BY printer' at line 1

[SeenGee]

thanks for the help by the way, i couldnt get your first solution to work either although it seems to make perfect sense.

[SeenGee]

solved it, swapped group by with order by and it works a treat

[nlhowell]

Found the bug in my first solution:

$sql = "SELECT * FROM products order by printer";   

$result = mysql_query($sql) or die(mysql_error());  

// Counts the number of rows 
$num_rows=mysql_num_rows($result);   

// Tests if the database is empty 
if ($num_rows == 0) {   

    //if select menu is empty 
}   

// If there are records.... 
else {   

    // Form to pick the product to be ammended 
    echo "<div align=\"center\">";  

    echo "<FORM method=post action=\"\">";   

    echo "Please select a printer to check for matching cartridges<BR>";   

    echo "<select name=\"printer\">";   

// this keeps track of what tables have been used. 
$printers = array(); 
// Loop to populate the menu 
while ($row=mysql_fetch_array($result)) {   
    // if we've already looked at this one, skip it 
    if(in_array($printers,$row["printer"])) continue;
    $printers[] = $row["printer"]; // a certain idiot forgot to populate the $printers array...
    $printer=$row["printer"];   
    $brand=$row["brand"];   
    $code=$row["code"];   
    $productid=$row["productid"];   
    // Display options in the pulldown box 
    echo "<option value=\"$productid\">$printer</option>";   
    } // end while 

echo "</select>";   
} // end else 

echo "<BR><BR>";   

echo "<input type=\"submit\" value=\"View the item\"></FORM>";  

echo "</div>";

[SeenGee]

i stuck to the sql solution, makes more sense really. been havin a weird problem with the code, for some reason the variable productid is not being found which means i cant pass the vars to the next page. I've never really used select menus before, is there something i'm missing or can i not just call them with $var = $_POST['var'];?

[nlhowell]

$_POST['var'] should refer to the selected item; your select entity is named "printer", though...

Nick

[SeenGee]

thanks, i sussed it earlier anyway. cant work it out, too much coffee or not enough coffee??

nice one.