get all mysql row data after selecting name from dropdown

Question

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

Are the data you want to display in the same table - `Entries`? I think you may want to use Javascript to display those data without reloading. — Logan Wayne, Jul 20 '16 at 01:11
Yes, sorry for not clarifying. All data variables are in the same table. That would be ideal so you can select a name and the data would display. I've never worked with jquery or json to grab data from a database. — breinhart, Jul 20 '16 at 01:18
I assume that those rows have their designated id(s)? What is the name of that column? Or I hope the `full_name` column is unique, so there is no chance of duplication. — Logan Wayne, Jul 20 '16 at 01:21
Correct. I set the table up with "id" to auto increment and that is the first variable in each row, then all the data variables are contained within that unique row. — breinhart, Jul 20 '16 at 01:26

Logan Wayne · Accepted Answer · 2016-07-21T02:17:41.253

2

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();
}

edited Jul 21 '16 at 02:17

answered Jul 20 '16 at 01:29

Logan Wayne

6,001
16
31
49

Thank you! Once I get back to my computer I'll work on putting this together. I'll be sure to call the latest jQuery prior to inserting the specific code. – breinhart Jul 20 '16 at 02:00
I'm getting the following...Fatal error: Call to a member function execute() on a non-object in /home/content/pathto/form/select_test.php on line 37. Line 37 of my file is $stmt->execute(); – breinhart Jul 21 '16 at 00:11
@breinhart - did you establish your connection in `select_test.php`? – Logan Wayne Jul 21 '16 at 00:14
Yes, and I didn't get the "Connect failed:" message. I just see a blank select form. – breinhart Jul 21 '16 at 00:23
The HTML output – breinhart Jul 21 '16 at 00:26
@breinhart - you can check for errors on your prepared statement. See this link and follow the answer [here](http://stackoverflow.com/questions/2552545/mysqli-prepared-statements-error-reporting). – Logan Wayne Jul 21 '16 at 00:26
I edited my question but it's under review. I added the following and I got the same result...$rc = $stmt->execute(); if ( false===$rc ) { die('execute() failed: ' . htmlspecialchars($stmt->error)); } No specific fail message was output. – breinhart Jul 21 '16 at 00:40
@breinhart - You should verify your `$stmt` variable, where you put your query. – Logan Wayne Jul 21 '16 at 00:43
@breinhart, your edit was not approved. Please use comments box to provide additional info or code. Use the `help` tool for advice on showing code. – VC.One Jul 21 '16 at 01:04
@breinhart - try checking your `$stmt` by `if ( false===$stmt ) { die('prepare() failed: ' . htmlspecialchars($mysqli->error)); }` – Logan Wayne Jul 21 '16 at 01:12
@Logan Wayne - Is it possible to provide a snippet example? [code]$stmt = $connection->prepare("SELECT full_name, dob, work_phone FROM Entries"); $stmt->execute(); $stmt->bind_result($full_name, $dob, $workphone); while($stmt->fetch()){ echo ''; $info .= '
'.$dob.' - '.$work_phone.'
'; } $stmt->close(); [/code] – breinhart Jul 21 '16 at 01:13
It only outputs "prepare() failed:" but no errors are listed. I'm wondering if the GoDaddy hosting that I'm working on is having issues executing the functions. – breinhart Jul 21 '16 at 01:19
@breinhart - You can try what VC.One did, just hit help to see how to include code snippet to your comments. You can also update your post for updates. – Logan Wayne Jul 21 '16 at 01:20
`$connection = new mysqli("XXXX", "XXXX", "XXXX", "XXXX"); if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $info = ''; $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()){` – breinhart Jul 21 '16 at 01:24
@breinhart - I put your updated code to your post. Just update it if you want to show more of the code you are working on. – Logan Wayne Jul 21 '16 at 01:26
@breinhart - what are the column names of your `Entries`? The likely cause of the error you are receiving is from your query. – Logan Wayne Jul 21 '16 at 01:38
@Logan - all column names have been added above to the update. (in that order) – breinhart Jul 21 '16 at 01:51
@breinhart - now that is what's wrong. There is no `dob` column, it should be `dependant_dob_1`. Try my updated query/answer. – Logan Wayne Jul 21 '16 at 01:55
After posting I spotted that it should have been `birth_date`. I got past the connection error, and I updated my question above with the blank select output that I'm getting now. – breinhart Jul 21 '16 at 02:04
@breinhart - actually, I'm testing this code right now, and it is working fine with me, using your database. Try my updated answer. Is it okay if I use `id` instead of `full_name` to the `value` tag of your `select` element? Because it is working great on me – Logan Wayne Jul 21 '16 at 02:05
The people that will be checking the information would like to select a name, and the automated email I created after the info is submitted includes the name. If we need to use the id I can modify the auto generated email to echo that and provide directions. – breinhart Jul 21 '16 at 02:10
I refreshed, but am not seeing the updated code you're testing with. – breinhart Jul 21 '16 at 02:12
@breinhart - I just changed the `class` of each `div` elements, and the value of `select` element to `id`. – Logan Wayne Jul 21 '16 at 02:14
@Logan - seems to have been a time issue. I modified the code, and this is now working! I'll just have to go in and add all the other fields to the `
` tag so the user can see all the information they need to view. I really appreciate all your help with this.
– breinhart Jul 21 '16 at 02:17
@breinhart - No problem, Brad! Good luck with your project. – Logan Wayne Jul 21 '16 at 02:19
@Logan - Unfortunately my rep isn't high enough, or you would definitely get my upvote! Take care, and many thanks. – breinhart Jul 21 '16 at 02:25
I have a delete link inside of the "info" variable so that it grabs the proper id. It was built to go to a separate delete page, but now I need to combine everything into one page. `if( isset($_GET['id'] ) && is_numeric( $_GET['id'] ) ){ mysql_query("DELETE FROM Entries WHERE id=$id"); } $info .= '
Delete this receord
';` how can I implement this delete query into the same page? – breinhart Aug 02 '16 at 01:44

score 0 · Answer 2 · answered Aug 02 '16 at 18:25

0

I ended up using an ajax call in the link to grab the delete page in the background to avoid the http redirect that was already setup and causing the issue.

answered Aug 02 '16 at 18:25

breinhart

5
7

get all mysql row data after selecting name from dropdown

UPDATE:

UPDATE:

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

2 Answers2