Member Avatar for CarterLangley

Hi guys, back again!

OK, this is what I am sitting with now. I have to select a person from the database and in the select box display their first, middle, and last names. The data returned should be the fields id to insert into the foreign key id of the related table.
This is what I have at the moment but, as usual with me, it is not working!

<form method="post" action="">
<table width="100%" align="left" border="0" cellspacing="1" cellpadding="2">
<tr>
<td><label for="person_id">Person</label></td>
<td>
<select name="person_id" id="person_id">
<option value="">--- Select a Person ---</option>
<?php
$sql_select = "SELECT * FROM person order by last_name asc";

$retval_selectperson = mysql_query( $sql_select, $conn );
if(! $retval_selectperson ) { die('Could not select data: ' . mysql_error()); }
while($row = mysql_fetch_assoc($retval_selectperson)) {
echo '<option value='.$row["first_name"]. " " . $row["middle_name"]. " " . $row["last_name"]'>'.$row["first_name"]." " .$row["middle_name"]. " " .$row["last_name"].'</option>';
}
?>
</select>
</td>
</tr>

Where have I gone wrong?

  • 2 Contributors
  • 7 Replies
  • 239 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

Common pitfalls and a concise, practical approach.

As discovered, opening the database connection inside the loop will cause errors and repeated work; the connection should be established once, before querying. As pointed out, the <option> value should be the person’s primary key (the id) so the submitted form supplies the correct foreign key for insertion. Additional points that make the solution robust and safe are: select only the needed columns, escape output to prevent XSS, validate the posted id on submit, and use parameterized queries (PDO or mysqli) instead of the old mysql_* functions (deprecated and removed in modern PHP).

Example: build the dropdown with PDO and safe escaping

<?php
// $pdo = new PDO(...); // connection established once, outside any loop
$sql = "SELECT user_id, first_name, middle_name, last_name FROM person ORDER BY last_name, first_name";
foreach ($pdo->query($sql, PDO::FETCH_ASSOC) as $row) {
    $fullname = trim($row['first_name'] . ' ' . ($row['middle_name'] ?? '') . ' ' . $row['last_name']);
    $id = (int)$row['user_id'];
    echo '<option value="' . htmlspecialchars($id, ENT_QUOTES) . '">' . htmlspecialchars($fullname, ENT_QUOTES) . '</option>';
}
?>

Server-side validation and insertion (concept)

  • Validate the incoming person_id with filter_input(..., FILTER_VALIDATE_INT) and reject non-integers.
  • Confirm the id exists with a prepared SELECT before inserting into the related table.
  • Use a prepared INSERT with bound parameters to store the foreign key.

Additional notes

  • Use CONCAT_WS(' ', first_name, middle_name, last_name) in SQL if preferred to build the label at the DB level and avoid extra spaces when middle name is NULL.
  • When editing records, compare the current FK to each row’s id to add the selected attribute.
  • Package the logic into a small helper function that accepts the DB handle and a selected id to avoid repeating code across pages.

Recommended Answers

All 7 Replies

Member Avatar for Member #120589

it is not working!

Helpful.

What's happening?

Member Avatar for CarterLangley

Getting a blank page. So presume that there is something wrong with the php code somewhere, but can't figure out what it is. restarted apache but no go.

Member Avatar for CarterLangley

Sorted!!!

This is where I was going wrong.
My database connection code was inside the loop instead of outside and it was configured incorrectly.

Also, the code that populates the select box was wrong.
Instead it looks like this now.

<tr>
<td><label for="person_id">Person</label></td>
<td>
<select name="person_id" id="person_id">
<option value="">--- Select a Person ---</option>
<?php
$sql_select = "select * from `person` order by `last_name` asc ";
$retval_selectperson = mysql_query( $sql_select, $conn );
if(! $retval_selectperson ) { die('Could not select data: ' . mysql_error()); }
while($row = mysql_fetch_assoc($retval_selectperson)) {
echo '<option value='.$row["first_name"].'>'.$row["first_name"]. " ".$row["middle_name"]. " ".$row["last_name"].'</option>';
}
?>
</select>
</td>
</tr>

My only concern now is when a value is chosen, will the code return the id related to that value and insert that into the variable for return to the database?

Member Avatar for Member #120589 
<?php
$ops = '';
$sql_select = "select user_id, first_name, middle_name, last_name from `person` order by `last_name` asc ";
$retval_selectperson = mysql_query( $sql_select, $conn );
if(! $retval_selectperson ) { die('Could not select data: ' . mysql_error()); }
while($row = mysql_fetch_assoc($retval_selectperson)) {
    $ops .=  "<option value='{$row['user_id']}'>{$row['first_name']} {$row['middle_name']} {$row['last_name']}</option>";
}
?>

Then

<tr>
    <td>
        <label for="person_id">Person</label
    </td>
    <td>
        <select name="person_id" id="person_id">
            <option value="">--- Select a Person ---</option>
            <?php echo $ops;?>
        </select>
    </td>
Member Avatar for CarterLangley

That will do the thing!
Thanks! Will do the changes tomorrow and check them on all 15 pages. Will mark as solved tomorrow.

Member Avatar for CarterLangley

Going to see about putting that into a function that can be called for all the select boxes. That makes more sense than typing the same code out for all the different pages. Just pass it a sql statement and it should do the job!

Member Avatar for Member #120589 
function optionizer(string $sql, string $value, array $display, int $default=0){}

A simple example.

value = field used in the value attribute of the option
display = array values to concatenate to display in the dropdown
default = "selected" row number

You could develop it further to accept more inticate parameters. Just an idea.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.