PHP, MySql drop down list only showing one record per selection

Question

I am newbie to this and I am trying to create a dynamic drop down list that retrieves data from a database. The problem is, it only gives me one drop down item(on second list) per every selection on the first drop down. Somebody please help. Here is the code.

 <?php
    require_once("dbcontroller.php");

    $query ="SELECT * FROM campus";

    ?>
    <html>
    <head>
        <TITLE>Campus and Faculty Select</TITLE>
    <head>
    <style>
            body{width:610px;}
            .frmDronpDown {border: 1px solid #F0F0F0;background-color:#C8EEFD;margin: 2px 0px;padding:40px;}
            .demoInputBox {padding: 10px;border: #F0F0F0 1px solid;border-radius: 4px;background-color: #FFF;width: 50%;}
            .row{padding-bottom:15px;}
    </style>
        <script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
    <script>
        function getcampus_id(val) {
            $.ajax({
            type: "POST",
            url: "get_faculty.php",
            data:'campus_id='+val,
            success: function(data){
                $("#faculty-list").html(data);
            }
            });
        }

        function selectcampus_id(val) {
        $("#search-box").val(val);
        $("#suggesstion-box").hide();
        }
    </script>
    </head>
    <body>
        <div class="frmDronpDown">
            <div class="row">
                <label>Country:</label><br/>
                    <select name="campus" id="campus-list" class="demoInputBox" onChange="getcampus_id(this.value);">
                        <option value="">Select Country</option>
                        <?php
                                    $query ="SELECT * FROM campus";
                                    $results = mysqli_query($con, $query);
                                    //loop
                                    foreach ($results as $campus){
                                ?>
                            <option value="<?php echo $campus["campus_id"]; ?>"> <?php echo $campus["name"]; ?></option>
                            <?php
                                }
                            ?>
                    </select>
            </div>
            <div class="row">
                <label>State:</label><br/>
                    <select name="faculty" id="faculty-list" class="demoInputBox">
                        <option value="">Select State</option>
                    </select>
            </div>
        </div>
    </body>
    </html>

The get_faculty.php

    <?php
    require_once("dbcontroller.php");

    if(!empty($_POST["campus_id"])) {
        $campus_id = $_POST["campus_id"];
        $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id";
        $results = mysqli_query($con, $query);
    ?>
        <option value="">Select Campus</option>
    <?php
        foreach($results as $faculty) {
    ?>
        <option value="<?php echo $faculty["faculty_id"]; ?>"><?php echo $faculty["faculty_name"]; ?></option>
    <?php
        }
    }
    ?>

and the dbcontroller.php

<?php
$username = "root";
$password = "";
$host = "localhost";
$dbname = "registration";

    $con = mysqli_connect($host, $username, $password) or die("Could not Connect");
    mysqli_select_db($con, $dbname);
?>

use while($row = mysqli_fetch_assoc($result) rather than foreach. — RJParikh
– RJParikh, Commented May 6, 2016 at 12:38

RJParikh · Accepted Answer · 2016-05-06 12:43:25Z

2

Use while($row = mysqli_fetch_assoc($result) to fetch all records from database.

<?php
    require_once("dbcontroller.php");

    if(!empty($_POST["campus_id"])) {
        $campus_id = $_POST["campus_id"];
        $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id";
        $results = mysqli_query($con, $query);
    ?>
        <option value="">Select Campus</option>
    <?php
        while($row = mysql_fetch_assoc($results)) {
    ?>
        <option value="<?php echo $row ["faculty_id"]; ?>"><?php echo $row ["faculty_name"]; ?></option>
    <?php
        }
    }
    ?>

answered May 6, 2016 at 12:43

RJParikh

4,1661 gold badge24 silver badges38 bronze badges

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

2 Comments

Njonjo Kiroga Over a year ago

I implemented it like so but now it shows no items at all. What am i not seeing? <?php require_once("dbcontroller.php"); if(!empty($_POST["campus_id"])) { $campus_id = $_POST["campus_id"]; $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id"; $results = mysqli_query($con, $query); ?> <option value="">Select Campus</option> <?php while($faculty = mysql_fetch_assoc($results)) { ?> <option value="<?php echo $faculty ["faculty_id"]; ?>"><?php echo $faculty ["faculty_name"]; ?></option> <?php } } ?>

RJParikh Over a year ago

something wrong with faculty_id and campus_id record. Please check it rather then code is correct implemented. @NjonjoKiroga

Njonjo Kiroga · Accepted Answer · 2016-05-06 16:12:18Z

0

The problem is here,

if(!empty($_POST["campus_id"])) {
    $campus_id = $_POST["campus_id"];
    $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id";
    $result = mysqli_query($con, $query);

    if ( $result === false ) {
        echo mysqli_error($con);
        exit;
    }

The

$query ="SELECT * FROM faculty WHERE faculty_id = $campus_id";

Should be

 $query ="SELECT * FROM faculty WHERE campus_id = $campus_id";

answered May 6, 2016 at 16:12

Njonjo Kiroga

157 bronze badges

Comments

RiggsFolly · Accepted Answer · 2016-05-06 17:17:32Z

0

foreach processes an array! The $result variable is not an array its a mysqli_result object

So you need to get each result row and then use that rows data. For this use a while loop.

Its also useful when you are coding PHP and HTML like this to use the

while () :
   . . .
endwhile;

syntax, as it makes it easier to see where loops etc start and finish. Otherwise you can get very lost in this sort of code.

<?php

error_reporting(E_ALL); 
ini_set('display_errors', 1);

require_once("dbcontroller.php");

if(!empty($_POST["campus_id"])) {
    $campus_id = $_POST["campus_id"];
    $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id";
    $result = mysqli_query($con, $query);

    if ( $result === false ) {
        echo mysqli_error($con);
        exit;
    }

    echo '<option value="">Select Campus</option>';

    while ( $faculty = mysqli_fetch_assoc($result) ) :
        echo '<option value="' . $faculty['faculty_id'] . '">';
        echo $faculty['faculty_name']; 
        echo '</option>';
    endwhile;
}
?>

Of course I am assuming that the code you did not show has the required <select....> and </select> tags before and after this code

Dont see anything wrong in dbcontroller but I would do

<?php
$username = "root";
$password = "";
$host = "localhost";
$dbname = "registration";

$link = mysqli_connect($host, $username, $password, $dbname);

if (!$link) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}

As mysqli_select_db(); is really for switching to a second database once a first database waqs successful connectioned to

edited May 6, 2016 at 17:17

answered May 6, 2016 at 12:41

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

10 Comments

Funk Forty Niner Over a year ago

Most informative. However, I'm questioning get_campus.php < their code and url: "get_faculty.php", - Hm.... oh well, let them sort it out ;-)

Njonjo Kiroga Over a year ago

I implemented it like so but now it shows no items at all. What am i not seeing? <?php require_once("dbcontroller.php"); if(!empty($_POST["campus_id"])) { $campus_id = $_POST["campus_id"]; $query ="SELECT * FROM faculty WHERE faculty_id = $campus_id"; $results = mysqli_query($con, $query); ?> <option value="">Select Campus</option> <?php while($faculty = mysql_fetch_assoc($results)) { ?> <option value="<?php echo $faculty ["faculty_id"]; ?>"><?php echo $faculty ["faculty_name"]; ?></option> <?php } } ?>

RiggsFolly Over a year ago

@Fred-ii- Yea looked odd to me also I just assumed (and we know wherwe that leads) he had pasted the wrong bit of code. Now I am not so sure

Njonjo Kiroga Over a year ago

yeah @Fred-ii- i meant get_faculty.php my bad.

RiggsFolly Over a year ago

Ok if you want to call the variable $faculty I have amended the code. I have also broken the <option... tag display up into easy bites.

|

Collectives™ on Stack Overflow

PHP, MySql drop down list only showing one record per selection

3 Answers 3

2 Comments

Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related