How to fetch data from MySQL database to Select drop-down list?

Question

I am trying to populate a select list from a MySQL database after I have selected an autocomplete suggestion from jQuery autocomplete.

For example, if i click on one of the autocomplete suggestions like 10 - computer in the first text box (see my sample data below), I want to fetch the corresponding names (sahil, sumit, anil, shweta), and add them to the select in second text box.

If instead I click 12-History I want to fetch those corresponding names (sanchita, riya, neha, pratik.

How to achieve this? Any help would be appreciated.

table structure:

--
-- Table structure for table `stndb`
--

DROP TABLE IF EXISTS `stndb`;
CREATE TABLE IF NOT EXISTS `stndb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Div_No` int(2) DEFAULT NULL,
  `Div_Name` varchar(30) DEFAULT NULL,
  `Student_Name` varchar(30) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `stndb`
--

INSERT INTO `stndb` (`id`, `Div_No`, `Div_Name`, `Student_Name`) VALUES
(1, 10, 'computer', 'sahil'),
(2, 10, 'computer', 'sumit'),
(3, 10, 'computer', 'anil'),
(4, 10, 'computer', 'shweta'),
(5, 11, 'IT', 'apoorva'),
(6, 11, 'IT', 'neeta'),
(7, 11, 'IT', 'sachin'),
(8, 11, 'IT', 'priya'),
(9, 12, 'History', 'sanchita'),
(10, 12, 'History', 'riya'),
(11, 12, 'History', 'neha'),
(12, 12, 'History', 'pratik');

SearchForm.php

<html>
    <head>
    <title> Search Record </title> 
    <script src="//code.jquery.com/jquery-1.11.1.min.js"></script>
    <link rel="stylesheet" type="text/css" href="css/jquery-ui.css"/>
    </head>
    <body>
    <form action="" method ="POST" accept-charset="UTF-8" role="form" >
        <fieldset>
        <div class="form-group">
            <input class="form-control" id="search" name="DivNumber" type="text" placeholder="Enter Division Number"  type="text" required>
        </div>

        <div class="form-group">
            <select input class="form-control" id= "nameList" name="StName" type="text" placeholder="Select Student"  type="text" readonly="readonly">
                <option value="one">Sahil</option>
                <option selected value="two">Sumit</option>
                <option value="three">Anil</option>
                <option value="four">Shweta</option>
            </select>
        </div>
        <input class="btn btn-lg btn-info btn-block" type="submit" value="Search  ">
        </fieldset>
    </form>

<script src="js/jquery-3.2.1.min.js"></script>
<script src="js/jquery-ui.js"></script>
<script type="text/javascript">
    $(document).ready(function(){
    $("#search").autocomplete({
        source: 'search.php',
        minLength: 1,
    });
});
</script>

</body>
</html>

search.php

<?php
require_once 'conn.php';

$search = $_GET['term'];

$list = array();

$search = $conn->real_escape_string($search);   

$query = $conn->query("SELECT DISTINCT Div_No, Div_Name FROM `stndb` WHERE Div_No LIKE '$search%' OR Div_Name LIKE '$search%' LIMIT 10") or die(mysqli_connect_errno());

$rows = $query->num_rows;

if($rows > 0){
    while($fetch = $query->fetch_assoc()){

        $data['value'] = $fetch['Div_No'];
        $data['label'] = $fetch['Div_No']. "-". $fetch['Div_Name']."";  
        array_push($list, $data);
    }
}

echo json_encode($list);
?>

names.php

 <?php
  require_once 'conn.php';

  $names = $_GET['term'];

  $names = array();

  $names = $conn->real_escape_string($names);   

  $stmt = $conn->query("SELECT Student_Name FROM `stndb` WHERE 
  Div_No='$names'");

  $rows = $stmt->num_rows;

  if($rows > 0){
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $names[] = $row['Student_Name'];
    }   
  }

  echo json_encode($names);
  ?>

You have already shown us your jQuery and other code ... so does it work? What happens? Do you see errors? What is your question? — Don't Panic, Sep 12 '19 at 09:15
That jQuery code is for autocomplete suggestions.. but for pop-up drop-down select options using that suggestions how to do that.. — ssbits, Sep 12 '19 at 09:20
What does `pop-up drop-down` mean? [jQuery autocomplete already displays a dropdown for matches](https://jqueryui.com/autocomplete/), are you talking about something else? Edit your question and describe what you are trying to do. — Don't Panic, Sep 12 '19 at 09:23
Actually I am trying to display select options dynamically into secondtext box after I click on suggestions in first text box.. not like what I have shown in my SearchForm.php file — ssbits, Sep 12 '19 at 09:27
@Don'tPanic i mean if i click in "10 - computer" suggestion i want to fetch "sahil,sumit, anil, shweta" as drop-down select in another text box .. and if i click "12-History" i want to fetch " sanchita, riya, neha, pratik" as drop-dwon dynamically.. how to achieve this? — ssbits, Sep 12 '19 at 09:42
See also: https://stackoverflow.com/questions/15318368/mysqli-or-die-does-it-have-to-die — Dharman, Sep 12 '19 at 10:14
@Dharman sir as of now keep aside a vulnerability in code, but can you please give me some logic how to populate select option list dynamically based on autocomplete selection.. will greatly appreciate.. — ssbits, Sep 12 '19 at 10:21

Don't Panic · Answer 1 · 2019-09-12T11:05:40.533

FIRSTLY as has been pointed out in the comments your code is open to SQL injection and you should IMMEDIATELY CHANGE THAT. Update your search.php to use PDO and prepared statements, there are many guides and tutorials online.

Once you have done that, seriously, don't proceed until you've fixed that -

As I understand it, you want to use autocomplete in the first #search input to display the Div_no and Div_name values. Once the user selects one of those values, a second select field will load the Student_Names of the selected Div_no.

Your existing code already does the first part. You need to update it to something when the user selects a value - jQuery autocomplete has a select event for that.

And to load the set of students, you will need to fire of an AJAX request when that select event is triggered. You will have to prepare a new PHP file, something like search BUT USING PDO, let's say you call it names.php. It will receive the selected Div_no as a GET parameter, and should query your database to find the students. The query would be something like:

SELECT Student_Name FROM stndb WHERE Div_No = ?

And just like in search.php, you will iterate over the results, generating an array of names:

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $names[] = $row['Student_Name'];
}

And it should return the results as JSON, easy to work with in Javascript:

echo json_encode($names);

So, first your HTML:

<form action="" method ="POST" accept-charset="UTF-8" role="form" >
    <fieldset>
        <div class="form-group">
            <input class="form-control" id="search" name="DivNumber" type="text" placeholder="Enter Division Number"  type="text" required>
        </div>
        <div class="form-group">
            <select input class="form-control" id="nameList" name="StName" type="text" placeholder="Select Student" type="text" readonly="readonly">
        </div>
        <input class="btn btn-lg btn-info btn-block" type="submit" value="Search  ">
    </fieldset>
</form>

Now your Javascript:

$( "#search").autocomplete({
    source: 'search.php',
    minLength: 1,
    // Here is the select event handler, fired when someone selects something
    select: function(event, ui) { 

        // Here is an AJAX request to load the right set of students
        $.ajax({
            url: 'names.php', // This is your new PHP file which returns the names
            data: {
                div_no: ui.item.value // This is the selected Div_No
            },
            dataType: 'json',
            success: function(response) { // This is what happens when the AJAX 
                                          // call succeeds and returns the names
                var output = '';

                // Iterate over the data and build a list of options
                for (var i in response) {
                    output += '<option value="">' + response[i] + '</option>';
                }

                // Update our select with the options we just built
                $('#nameList').html(output);
            }
        });
    }
});

hi i am not getting output please have look at "names.php" i have edited in question.. where is wrong.. — ssbits, Sep 12 '19 at 12:35
You can't mix PDO with old `mysqli` type code. You need to switch to PDO fully - make a connection (probably in your `conn.php`), [as described in the docs](https://www.php.net/manual/en/pdo.connections.php). Then using your connection (`$dbh` in the docs), prepare a statement, and execute it, [as described in the docs](https://www.php.net/manual/en/pdo.prepared-statements.php). — Don't Panic, Sep 12 '19 at 13:03
Made changes in conn.php as per doc , what to change in search.php? — ssbits, Sep 12 '19 at 13:32
do i need to change every php page of my project to PDO, i mean shift mysqli to pdo — ssbits, Sep 13 '19 at 15:23

How to fetch data from MySQL database to Select drop-down list?

1 Answers1