PHP/MYSQL - Fetch DB values from dropdown menu, then into table in same page

Question

I'm building an exam management website and one of the pages I'm working on is for adding students to a course. I have a dropdown menu for the student number (which fetches values from a table), however I'd like to make it so that when the teacher selects the student number from the dropdown menu, that student's name and major appear on a table below. I have pretty much all the code for it however I can't seem to make it work. The way it is right now it shows the head of the table but it doesn't show any lines.

The errors are always in the lines where I declare $sql1 and $sql2 and vary according to how I define the condition in the statement.

Code for my dropdown menu : (works fine)

<label class="control-label" for="number">Student Number</label>
            
<?php

$sql = "SELECT number FROM students";           
$result = $conn->query($sql);

echo "<select class=".'"form-control"'.'  id="number" name="number" for="number">';
while ($row = $result->fetch_assoc()) {
echo '<option value="' . $row['number'] . '">' . $row['number'] . "</option>";
}
echo "</select>";        

?>

Code for my table : (shows only head of table, which is the best I got after moving around the code and getting conversion errors and such)

The errors are always in the lines where I declare $sql1 and $sql2 and vary according to how I define the condition in the statement.

<table class="table"> 
  <thead> 
    <tr> 
      <th>Name</th> 
      <th>Major</th> 
    </tr>                                                     
  </thead> 
  <tbody>
    <?php
$sql1 = "SELECT name FROM students WHERE number='$row'";           
$result1 = $conn->query($sql1);           
$value = $result1->fetch_object();

$sql2 = "SELECT major FROM students where number='$row'";           
$result2 = $conn->query($sql2);
$value1 = $result2->fetch_object();

echo "<tr>
<td>".$value."</td>
<td>".$value1."</td>
</tr>";
?> 

  </tbody>
</table>

Thank you for all your help!!

"when the teacher selects the student number from the dropdown menu" - so, at this point a _new request_ is sent to the webserver to fetch the detail data corresponding with the currently selected student id?(!). Regardless of whether the request is handled by the same script or not, it's a new instance, all variables of the previous instance are gone and the script can only use what's sent by the client with _this_ new request (and session data and so on...) -> exatly what is `$row` in `"SELECT name FROM students WHERE number='$row'"`? — VolkerK, Nov 29 '15 at 12:01
that is what determines what will appear in the table row. It's supposed to show the name of the student selected from the dropdown menu. The syntax is probably wrong but I can't figure out how to handle it. I'm sorry I'm a bit of a newbie and don't understand how requests work. — Joe, Nov 29 '15 at 12:07

score 0 · Answer 1 · answered Nov 29 '15 at 12:04

0

Before I can formulate a complete answer, I must advise you that there are a few logical errors in your code.

How does your page "know" that a user selected an option from the select? You should perhaps intercept the event and respond to that using an asynchronoys mechanism, e.g. via AJAX.
Anyhow, there's no need to run two queries when you can make it with just one:
```
SELECT name, major FROM students WHERE number = ...
```

Once you have described how you mean to address issue #1 we can continue discussing the complete solution.

answered Nov 29 '15 at 12:04

godzillante

1,174
1
17
32

yeah I have no idea how to do that :/ Guess I'll have to google it up, thanks anyway. – Joe Nov 29 '15 at 12:08
It's all in the same page btw. Do I still need AJAX if that's the case? – Joe Nov 29 '15 at 12:17
Yes, if it's in the same page I recommend to include jquery and google some jquery AJAX tutorial. You have to write a sort of webservice (PHP is good for that, too) and return your array via in a machine-readable format. PHP's `json_encode()` function is useful for this purpose. – godzillante Nov 29 '15 at 16:05
Plus, using jQuery's `on()` function you can intercept the `select`'s change event and act accordingly. That's the way AJAX functions are usually started. – godzillante Nov 29 '15 at 16:07

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Well, I think there will be no $row in the the second snippet.
It seems that you didn't pass your $row from 1st snippet to 2nd snippet.

You can read this:

PHP Pass variable to next page

You can use session, cookie, get and post.
Or can just simply use "include", then the variables you defined can be used in the second page.



<?php
    include "page1.php";
?>
<table class="table"> 
<thead> 
<tr> 
    <th>Name</th> 
    <th>Major</th> 
</tr>                                                     
</thead> 
<tbody>
<?php
    $number = $row['number'];
    $sql1 = "SELECT name, major FROM students WHERE number='$number'";           
    $result1 = $conn->query($sql1);           
    $value = $result1->fetch_object();

    echo "<tr>
    <td>".$value['name']."</td>
    <td>".$value['major']."</td>
    </tr>";
?> 

</tbody>
</table>

According to godzillante's answer below, the mysql query should be like this:

Anyhow, there's no need to run two queries when you can make it with just one:

SELECT name, major FROM students WHERE number = ...

I notice that you use $row as the key of your second query.
But in the first snippet, the data you fetch is "$row" (it is an array, see PHP - fetch_assoc)
You should use $row['number'] instead.

edited May 23 '17 at 12:23

Community

1
1

answered Nov 29 '15 at 12:14

MAJA Lin

11
1
3

it's in the same page, the problem here lies on, how do I KNOW which number is selected from the dropdown menu, then storage it in a string variable that I can later use in that statement. Some people say I need AJAX but I'm not sure. – Joe Nov 29 '15 at 12:16
Yes, you can google some AJAX tutorial. Or try the simple one : pass your variable to another page, and let this "page 2" deal with the query & printout you want. – MAJA Lin Nov 29 '15 at 12:35
It's almost there. The only problem lies here : " $sql1 = "SELECT nome, curso FROM alunos WHERE numero='$row'"; " . that $row needs to change to something else, either I may need to create a variable to store the selected option from the dropdown menu and use it instead of $row. What do you think? – Joe Nov 29 '15 at 12:49
Yes, the most easiest way is like the snippet: create $number to store $row['name']. Because it may occur some problems when you use quotation marks in mysql query (single & double quotation). If you are not that familiar with php & mysql, create a variable will be safe. – MAJA Lin Nov 29 '15 at 13:15

PHP/MYSQL - Fetch DB values from dropdown menu, then into table in same page

2 Answers2