mysql select distinct query in PHP

问题:

$sql = "SELECT DISTINCT Branch FROM student_main";
    $result = mysql_query($sql);
    $row_num = mysql_num_rows($result);
    $rows = mysql_fetch_array($result);
    echo "<select name='Branch'>";
    for($i=0;$i<=$row_num-1;$i++){
        echo "<option value='".$rows[$i]."'>".$rows[$i]."</option>";

    }
    echo "</select>";
    echo "<input type='submit' Value='submit' />";
    echo "</form>";

I am trying to create a dropdown using the above code for my form. But its not working. There are 3 distinct values in the Branch column but in the dropdown, it shows only one value(the first one) and the next two as blank values.

However when in echo $row_num, its shows 3.
Thats means its fetching the three rows, but then why its not showing in the dropdown list.

If I run the same query in phpmyadmin it shows the correct answer i.r it returns 3 distinct Branch values.

回答1:

You should do something like this:

$sql = "SELECT DISTINCT Branch FROM student_main";
$result = mysql_query($sql);

echo "<select name='Branch'>";
while ($row = mysql_fetch_array($result)) {
    echo "<option value='".$row[0]."'>".$row[0]."</option>";
}
echo "</select>";

echo "<input type='submit' Value='submit' />";
echo "</form>";

回答2:

You need to loop through your query using the following:

    $sql = "SELECT DISTINCT Branch FROM student_main";
    $result = mysql_query($sql);
    echo "<select name='Branch'>";
    while($rows = mysql_fetch_array($result)){ // should probably use mysql_fetch_assoc()
        echo "<option value='".$rows['Branch']."'>".$rows['Branch']."</option>";
    }
    echo "</select>";
    echo "<input type='submit' Value='submit' />";
    echo "</form>";

回答3:

you need to mysql_fetch_array() for each row. That function returns an associative array for one row only. just include it inside your for loop just above your echo statement.

edit: mysql_fetch_array() actually returns an array (by default) that has associative indices and numbered indices. You can continue using it the same way, though.

回答4:

mysql_fetch_array only returns the current dataset as an array, and moves the internal pointer ahead. You need to repeatedly call mysql_fetch_array to get all results.

while ($row = mysql_fetch_array($result)) {
    echo "<option value='".$row['Branch']."'>".$row['Branch']."</option>";
}

回答5:

There is a problem in the loop using a while loop:

while($rows=mysql_fetch_array($result)){ 

echo "<option value='".$rows[$i]."'>".$rows[$i]."</option>";

}

Try this

回答6:

What you really need is to learn how to use templates.
But it seems Stackoverflow is definitely not the place where one can learn professional ways of website developing.

get your data first

$select = $array();
$sql = "SELECT DISTINCT Branch FROM student_main";
$res = mysql_query($sql) or trigger_error(mysql_error().$sql);
while($row = mysql_fetch_array($res)) $select = $row[];

And then use it in the template

<form>
<select name='Branch'>
<? foreach($select as $row): ?>    
  <option value="<?=htmlspecialchars($row['Branch'])?>">
    <?=htmlspecialchars($row['Branch'])?>
  </option>
<? endforeach ?>    
</select>
<input type='submit' Value='submit' />
</form>

回答7:

mysql_fetch_array will only return the first row...

see here for full details :)