Populate dropdown from Another MySQL table - php

Question

I want to populate course list inside a dropdown but use the id from the student table to select its value in the edit page.

Database

course
+----+-------------+
| id | course_name |
+----+-------------+
| 1  | English     |
| 2  | Math        |
| 3  | Social      |
| 4  | Arts        |
| 5  | History     |
+----+-------------+

student
+----+--------+-----+--------+
| id | name   | age | course |
+----+--------+-----+--------+
| 1  | John   | 25  | 1      | /* English */
| 2  | Robert | 24  | 3      | /* Social */
| 3  | Nancy  | 21  | 4      | /* Arts */
+----+--------+-----+--------+

I am populating the course table database inside a dropdown like this in the create new student page.

Create New student

$dropdown_query = "SELECT * FROM course";

// other codes

echo "<select name='course' value='' required/>";
    echo "<option value=''> Select the Course </option>";
    foreach (mysqli_query($con, $dropdown_query) as $row){
        echo "<option value='".$row['id']. "'> $row[course_name] </option>";
    }
echo "</select>";   

// other codes

HTML output

┌────┬────────┬─────┬─────────┬────────┐
│ id │  name  │ age │ course  │  Edit  │
├────┼────────┼─────┼─────────┼────────┤
│  1 │ John   │  25 │ English │ [Edit] │
│  2 │ Robert │  24 │ Social  │ [Edit] │
│  3 │ Nancy  │  21 │ Arts    │ [Edit] │
└────┴────────┴─────┴─────────┴────────┘

Edit Student page

I am stuck here

$id = '';
if( isset( $_GET['id'])) {
    $id = $_GET['id'];
}

$dropdown_query = "SELECT * FROM course";
$result = mysqli_query($con, $dropdown_query);

echo "<select name=course value=''> Course </option>";
    while ($row = mysqli_fetch_array($result)) {
        if($row['id'] == $id){
            echo "<option value='".$row['id']. "' selected> $row[course_name] </option>";
        } // if
        else{
            echo "<option value='" .$row['id']. "' >$row[course_name]</option>";
        } // else
    } // while
echo "</select>";

the code above generates the course inside the dropdown list, but the value is not selected. I want to combine two tables into one inside the dropdown.

Please help me!

So for example you want your options to have like Student Name - Course Name? — jerome
– jerome, Commented Feb 24, 2018 at 7:09
try to put 1 2 or 3 in url parameter id, so that you get one option selected — user5189304
– user5189304, Commented Feb 24, 2018 at 7:11
@TarangP Yes it is showing. The problem is, it is not selected the value based on the id. — Dexter
– Dexter, Commented Feb 24, 2018 at 8:06

sorak · Accepted Answer · 2018-02-24 08:54:32Z

1

This is assuming you have an object $student which is the row from students corresponding to the current user.

$dropdown_query = "SELECT * FROM course";
$courses = mysqli_query($con, $dropdown_query);

echo '<select name="course">';
    while ($course = mysqli_fetch_array($courses)) {
        echo "<option value='{$course['id']}'".($student['course']==$course['id'] ? ' selected="selected"' : '').">{$course['course_name']}</option>";
    } // while
echo '</select>';

answered Feb 24, 2018 at 8:54

sorak

2,6332 gold badges18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dexter Over a year ago

Thanks for the help, the value is selected based on the id. But when i edit and save to new course it is not accepting the changes. Any idea.

Dexter Over a year ago

Sorry, for the question above. it is fixed. It was the value issue.

TarangP · Accepted Answer · 2018-02-24 08:24:07Z

0

Try This

echo "<select name=course value=''> Course </option>";
//ADD EMPTY ELEMENT SO YOU CAN SEE SELECTED ELement
echo "<option value=''> Your Course </option>";
    while ($row = mysqli_fetch_array($result)) {
        if($row['id'] == $id){
            echo "<option value='".$row['id']. "' selected> $row[course_name] </option>";
        } // if
        else{
            echo "<option value='" .$row['id']. "' >$row[course_name]</option>";
        } // else
    } // while
echo "</select>";

answered Feb 24, 2018 at 8:24

TarangP

2,7385 gold badges24 silver badges42 bronze badges

4 Comments

Dexter Over a year ago

This will populate the course list dropdown but how does this select the correct value.

TarangP Over a year ago

echo "<option value='".$row['id']. "' selected> $row[course_name] </option>" will select the correct value.

Dexter Over a year ago

It does not. i am calling $dropdown = "SELECT * FROM course"; $result = mysqli_query($con, $dropdown); this looks at the course table but how does it know the proper id. Do i need to add a col in the course table as well?

TarangP Over a year ago

Use` If($id == $row['id'] ) $var = "selected";`

Collectives™ on Stack Overflow

Populate dropdown from Another MySQL table - php

Database

Create New student

HTML output

Edit Student page

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Database

Create New student

HTML output

Edit Student page

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related