mysql select distinct query in PHP

Question

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

Answer 1

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>

Answer 2

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

Answer 3

mysql_fetch_array will only return the first row...

see here for full details :)

Answer 4

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

Answer 5

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

Answer 6

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.