breinhart breinhart - 4 months ago 15
MySQL Question

get all mysql row data after selecting name from dropdown

Below is the php code I'm using to populate a select dropdown with the variable "full_name".

$sql = "SELECT * FROM Entries";
$result = mysql_query($sql);

echo "<select name='full_name'>";
while ($row = mysql_fetch_array($result)) {
echo "<option value='" . $row['full_name'] ."'>" . $row['full_name'] ." </option>";
}
echo "</select>";


I would like to be able to select a name from the dropdown list and display all the relevant data for that specific row so it can be easily seen by the admins.

Basically

select name (details populate below)

Name: $full_name<br>
DOB: $dob<br>
Work Phone: $work_phone<br>
etc...


Maybe something like this?

while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "Name :{$row['full_name']} <br> ".
"DOB : {$row['dob']} <br> ".
"Work Phone : {$row['work_phone']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";


I just don't know how to tie the dropdown selection to correctly display the information for that person's name. Any help is appreciated!

Thanks




UPDATE:



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script>

$(document).on("change", "#full_name", function(){

var elem = $(this),
full_name = elem.val();

$(".info").hide(200); /* HIDE ALL OTHER INFORMATION */

$(".info-"+full_name).show(200); /* SHOW THE INFO OF THE SELECTED FULL NAME */

});

</script>
</head>

<body>
<select name="full_name" id="full_name">
<?php

$connection = new mysqli("XXXX", "XXXX", "XXXX", "XXXX");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$info = ''; /* WE'LL BE STORING THEIR INFORMATION HERE */

$stmt = $connection->prepare("SELECT full_name, dob, work_phone FROM Entries");
if ( false===$stmt ) { die('prepare() failed: ' . htmlspecialchars($mysqli->error)); }
$stmt->execute();
$stmt->bind_result($full_name, $dob, $workphone);
while($stmt->fetch()){

echo '<option value="'.$full_name.'">'.$fullname.'</option>';
$info .= '<div class="info info-'.$fullname.'" style="display:none">'.$dob.' - '.$work_phone.'</div>';

}
$stmt->close();

echo '</select>';

echo $info; /* DISPLAY THE INFOs, BUT NOT REALLY BECAUSE THEY ARE HIDDEN */
?>
</body>
</html>


UPDATE:



id
requested_action
full_name
birth_date
sex
work_location
work_phone
hire_date
coverage_choice
network_choice
plan_choice
dependant_name_1
dependant_relationship_1
dependant_dob_1
dependant_sex_1
dependant_name_2
dependant_relationship_2
dependant_dob_2
dependant_sex_2
dependant_name_3
dependant_relationship_3
dependant_dob_3
dependant_sex_3
dependant_name_4
dependant_relationship_4
dependant_dob_4
dependant_sex_4
spouse_coverage
employee_enroll
signature
date_today
reg_date


UPDATE: found error with connection - now I get blank output



<body>
<select name="full_name" id="full_name">
<option value="Customer, Joe"></option><option value="Customer, Susie"></option><option value="Customer, Joe"></option><option value="Customer, Josie, B"></option><option value="Renoir, Thomas"></option><option value="Customer, Joe"></option></select><div class="info info-" style="display:none"> - </div><div class="info info-" style="display:none"> - </div><div class="info info-" style="display:none"> - </div><div class="info info-" style="display:none"> - </div><div class="info info-" style="display:none"> - </div><div class="info info-" style="display:none"> - </div></body>
</html>

Answer

You can achieve this using Javascript. But we will be using a Javascript library called jQuery.

Oh, and don't use deprecated mysql_* extension. We will be using mysqli_* extension instead.

Let's prepare first your HTML by adding an id tag on your <select></select> field.

echo '<select name="full_name" id="full_name">';

Then on your while loop, let's get the other information from each row.

$info = ''; /* WE'LL BE STORING THEIR INFORMATION HERE */

$stmt = $connection->prepare("SELECT id, full_name, birth_date, work_phone FROM Entries"); /* SEE HOW TO ESTABLISH CONNECTION TO YOUR DATABASE USING mysqli AT THE BOTTOM */
$stmt->execute();
$stmt->bind_result($id, $fullname, $dob, $workphone); /* CORRESPONDS TO THE SELECTED COLUMNS FROM YOUR QUERY */
while($stmt->fetch()){

    echo '<option value="'.$id.'">'.$fullname.'</option>';
    $info .= '<div class="info info-'.$id.'" style="display:none">
                  Date of Birth: '.$dob.'<br>
                  Tel. Phone (work): '.$workphone.'
              </div>';

}
$stmt->close();

echo '</select>';

echo $info; /* DISPLAY THE INFOs, BUT NOT REALLY BECAUSE THEY ARE HIDDEN */

Then, let's create the script to display the information of selected full_name:

<script src="jquery-1.9.1.min.js"></script> <!-- REPLACE JS FILE DEPENDING ON THE VERSION YOU HAVE DOWNLOADED AND THE DIRECTORY WHERE YOU PUT IT -->
<script>

    $(document).on("change", "#full_name", function(){

        var elem = $(this),
            id = elem.val();

        $(".info").hide(200); /* HIDE ALL OTHER INFORMATION */

        $(".info-"+id).show(200); /* SHOW THE INFO OF THE SELECTED FULL NAME */

    });

</script>

This is just a simple trick. But I think it is better than doing an Ajax call from every select of full_name.


Establish your connection to your database using mysqli_* extension also:

$connection = new mysqli("Host", "User", "Password", "Database");

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
Comments