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?

Member Avatar

diafol

it is not working!

Helpful.

What's happening?

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.

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

diafol

<?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>

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

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

diafol

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.