MYSQL While Loop DropDown

Question

I have a while loop that display data from table student and an extra column that displays a drop down selection from employee table id which when selected, updates the id in student table. I have the following codes but on displaying it shows only 1 data from the employer table each line.

Here is the while loop. Any help would be appreciated, Thanks in advance.

while(($row = mysql_fetch_array($search_result)) && ($row1 =mysql_fetch_array($search_result2))){
echo"<form action=manualallocation.php method=post>";
echo"<tr>";
echo "<td>" . $row['stud_ID']."</td>";
echo "<td>" . $row['stud_NAME']."</td>";
echo "<td>"."<Select name='ex1'>"."<option value='" .$row1['emp_id'] ."'>" .$row1['emp_id'] ."</option>"."</select>";
echo "<td>" . "<input type='char' name='emp_location' value='" .$row1['emp_location'] . "'/> </td>";
echo "<td>" . "<input type='char' name='stud_FIELDOFSTUDY' value='" .$row['stud_FIELDOFSTUDY'] . "'/> </td>";
echo "<td>" . "<input type='char' name='student_yearCompleted' value='" .$row['student_yearCompleted'] . "'/> </td>";
echo "<input type='hidden' name=hidden value=" . $row['stud_ID'] . ">";
echo "<td>" . "<input type='submit' name='submit' value=update". "></td>";
echo "</tr>";
echo "</form>";
}

The query:

<?php
    $connect=mysql_connect('127.0.0.1', 'root', 'root');
    if(!$connect)
    { die("Can't Connect " . mysql_error()); } 
    mysql_select_db("web",$connect); 
    if (isset($_POST['submit']))
    {
        $updatequery = "UPDATE student SET emp_id=emp_id WHERE emp_ID='$_POST[submit]'"; 
        mysql_query($updatequery,$connect); 
    }; 
    $sql="SELECT * FROM student WHERE emp_id IS NULL"; 
    $search_result=mysql_query($sql,$connect); 
    $sql2="SELECT * FROM employer "; 
    $search_result2=mysql_query($sql2,$connect); 
?>

What's in our queries? Probably... they are different rows number? — fusion3k, Mar 07 '16 at 13:28
Show your db queries. It seems like you're trying to loop the results in a strange way. Also, I would separate the html from the php code if possible, it will make it more readable and easier to manage in the future. — purpleninja, Mar 07 '16 at 13:30
There's so many mistakes here. And you're using an ancient, deprecated API. — Strawberry, Mar 07 '16 at 13:43
If 've 3 lines in the loop it is showing the 1st data in the 1st dropdown and 2nd data in 2nd dropdown and 3rd data in 3rd dropdown. What i want is to display all data in all drop down. If i extract the code outside the loop it works well but inside the loop there is something wrong which i can't detect. Thanks — Oliver, Mar 07 '16 at 13:44
@Oliver next time, please add your query to original question. In the comment it is hard to read the code. Thank you — fusion3k, Mar 07 '16 at 13:44
Is it possible to correct the mistakes that i know none please. — Oliver, Mar 07 '16 at 13:45
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Mar 07 '16 at 13:50
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Mar 07 '16 at 13:50
Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Mar 07 '16 at 13:52

score 0 · Answer 1 · answered Mar 07 '16 at 14:12

Your while loop fails because the two queries returns different rows number. Also, you don't understand the fetch mechanism: on each loop, $row and $row1 values are one query row: how do you can build the <option> with only one value?

Basically, you have to separate the while() loops:

First, fill a variable with the complete <option> code for employers (replace field names with your own correct names);

$employers = '';
while( ( $row = mysql_fetch_array( $search_result2 ) )
{
    $employers .= "<option value=\"{$row['emp_id']}\">{$row['emp_name']}</option>";
}
$employers = "<select>$employers</select>";

Then, perform the while() loop for the students:

while( ( $row = mysql_fetch_array( $search_result ) )
{
    (...)
    echo "<td>" . $row['stud_NAME']."</td>";
    echo "<td>$employers</td>";
    (...)
}

Generally, you have to figure it how you code can work before writing it.

Also, please note that UPDATE student SET emp_id=emp_id WHERE emp_ID='$_POST[submit]' sounds like an unnecessary query: it means that emp_id field must be set to their own value.

The above code is only an example to help you modifying your code. It is not tested and I don't know if in your code there are other errors. You absolutely have to check for errors writing code: follow Jay's suggestion in comments for that.

Please note:

mysql_ syntax was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

(from PHP Official site)

By separating the while loops i can not update a specific line details . e.g a line consists of student id 113321 and column emp_id when selected in the row will it update the respective student details? — Oliver, Mar 07 '16 at 14:53

MYSQL While Loop DropDown

1 Answers1

Please note: