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.

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

Solution 2

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.

Solution 3

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

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

Solution 5

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>

View more solutions

Comments

[Reiner Gerecke]

@Prisoner Thank you, that happens when you copy&paste :)

[hsinxh]

I am very new to programming, thats why my code is complete crap. But I am learning. Thanks for your advice though.

[Drewdin]

you recommend using <? ?> and not <?php ?>

[Your Common Sense]

@Drewdin yes, I do. It looks neat, isn't it?

[hsinxh]

I thought it fetches complete array at once. Am I wrong ? can you please explain a bit. Thanks

[Scott M.]

it fetches one row at a time. you need to call it again to get another row.

[Drewdin]

@Col. It does, are there any rules to using either or? Thanks!

[Your Common Sense]

@Drewdin no, it's just matter of taste. Most developers agree that such syntax especially suitable for templates.

[Ashish Augustine]

How can we modify the sql command here if we want to get multiple DISTINCT values where some 2 conditions are satisfied. (like mark=50 and grade= b)