[Resolved] Quick drop box population question

m_tCh

Hi,
I am still fairly new to php so my questions are rather simple in nature. I wrote this script for my database. It populates a drop box with all of the requestor names from the database. The user selects one and it moves on to the next page. The small problem i have is that I don't want it to display duplicate names. Right now if a Requestor appears more than once in the db its lists his or her name mulitple times in the drop box. I assume I need some kind of if/else startment. I'm just can't figure it out.
Thanks in advance for your help.

<?

			$conn = mysql_connect("") or die(mysql_error());
			$db = mysql_select_db("", $conn) or die(mysql_error());
			$sql = "SELECT ID, REQUESTOR FROM PROJECT_LIST ORDER BY REQUESTOR ASC";
			$sql_result = mysql_query($sql) or die (mysql_error());
	if (!$sql_result) {
		echo "Something has gone wrong!";
	} else {
		echo "
		<HTML>
		<HEAD>
		<TITLE>Select a Requestor</TITLE>
		</HEAD>
		<BODY>
		<h1>Select a Requestor</h1>
		<FORM method=\"POST\" action=\"whiteboard_frequestor.php\">
		<p><strong>Select a Product:</strong><br>
		<select name=\"sel_record\">
		<option value=\"\"> -- Select an Item -- </option>";
		while ($row = mysql_fetch_array($sql_result)) {
				$id = $row["ID"];
				$requestor = $row["REQUESTOR"];
				echo "<option value=\"$requestor\">$requestor</option>";
		}
		echo "
		</select>
		<P align=center><INPUT type=\"submit\" value=\"Show records\"></p>
		</FORM>
		</BODY>
		</HTML>";
	}

?>

bastien

use the distinct keyword

SELECT distinct(REQUESTOR), ID FROM PROJECT_LIST ORDER BY REQUESTOR ASC";

m_tCh

Ok, I tried that and it didn't do anything. It still has repeats of requestors. Do I have to replace anyother code lines to get your suggestion to work?
Thanks for your help!

 <? 

            $conn = mysql_connect("") or die(mysql_error()); 
            $db = mysql_select_db("", $conn) or die(mysql_error()); 
            $sql = "SELECT distinct(REQUESTOR), ID FROM PROJECT_LIST ORDER BY REQUESTOR ASC"; 
            $sql_result = mysql_query($sql) or die (mysql_error()); 
    if (!$sql_result) { 
        echo "Something has gone wrong!"; 
    } else { 
        echo " 
        <HTML> 
        <HEAD> 
        <TITLE>Select a Requestor</TITLE> 
        </HEAD> 
        <BODY> 
        <h1>Select a Requestor</h1> 
        <FORM method=\"POST\" action=\"whiteboard_frequestor.php\"> 
        <p><strong>Select a Product:</strong><br> 
        <select name=\"sel_record\"> 
        <option value=\"\"> -- Select an Item -- </option>"; 
        while ($row = mysql_fetch_array($sql_result)) { 
                $id = $row["ID"]; 
                $requestor = $row["REQUESTOR"]; 
                echo "<option value=\"$requestor\">$requestor</option>"; 
        } 
        echo " 
        </select> 
        <P align=center><INPUT type=\"submit\" value=\"Show records\"></p> 
        </FORM> 
        </BODY> 
        </HTML>"; 
    } 

?>

m_tCh

anyone? please help.

Roger_Ramjet

It is selecting the ID field that is producing the multiple REQUESTOR listings, it is unique for each row and so any select will return all rows. If you just want a list of requestors then:

SELECT distinct(REQUESTOR) FROM PROJECT_LIST ORDER BY REQUESTOR ASC

will work, as will

SELECT requestor FROM PROJECT_LIST GROUP BY requestor ORDER BY requestor

m_tCh

excellent!

Thank you!