Populating Select boxes

ffsja

I'm creating a data entry system using php/mysql that includes the standard add/update/delete forms in PHP with a MySQL backend.

On the update pages I'm trying to fill the select boxes (that come from other tables) with the values in those other tables, yet defaulting the selection in each of those select boxes to the value currently stored in the main table which is being updated.

For example, the main table is called 'dates' and a related table is called 'department'. So one of the select boxes should have all the various departments listed in the select box but the currently selected value should be whatever is in the department field in the 'dates' table for the record the user is trying to update.

Here's what I currently have for a sample select box code (I'm getting nothing in the select box whatsoever btw):

<select name="department" value="<? echo "$department"; ?>">
$option_block
</select>

The $option_block variable was previously set with the following code:

while ($row = mysql_fetch_array($result7)) {
$department_id = $row['department_id'];
$ddepartment = $row['department'];

$option_block .="<option value=\"$ddepartment\">$ddepartment</option>";

}

Furthermore, $result7 comes from the following code:

$result7 = @($sql7,$connection) or die(mysql_error());

And, $sql7 comes from the following code:

$sql7 = "SELECT department_id, department FROM $table_name3 ORDER BY department";

The department values from the 'dates' table are also set in similar code.

Any help figuring out how to populate my select boxes is appreciated.

Thanks,
S./

Roger_Ramjet

A select query with a left join will give you all the departments plus the one that is in dates. This works because the left join will return null values for the dates columns except for the one that actually exists in dates. You test for null and insert 'SELECTED' into your option html when you get to the not null row.

Here is the query form

$sql = "SELECT * FROM departments LEFT JOIN dates USING (department_id)"

See if you can work out the rest from there.

ffsja

Thank you, that makes sense 🙂

But what if my 'related' field in the 'dates' table are varchar data types and not numbers (that is, they are 'department' values NOT 'department_id' values)?

Also, I'm not sure how to test for null in my <html> Do you mean by using an <if> statement? If so, how does one use PHP to test for a null MySQL value?

Sorry these questions are so mundane, but that's why I'm posting to the Newbie's forum.

S./

Roger_Ramjet

ffsja wrote:
Thank you, that makes sense 🙂

But what if my 'related' field in the 'dates' table are varchar data types and not numbers (that is, they are 'department' values NOT 'department_id' values)?

Then specify that column for the join condition.

ffsja wrote:
Also, I'm not sure how to test for null in my <html> Do you mean by using an <if> statement? If so, how does one use PHP to test for a null MySQL value?

Sorry these questions are so mundane, but that's why I'm posting to the Newbie's forum.

S./


$dates=$row['dates.department'];
if (!is_null($dates)) {
     echo ' SELECTED ';
}

means if the dates.department column in the query results IS NOT NULL then that is the row with an entry and the one you want to be selected in your <option>

ffsja

I'm having trouble getting my code to work with these snippets you provided.

I put the following code in as you suggested:

$sql7 = "SELECT * FROM $table_name3 LEFT JOIN dates USING (department)";

Then, while populating the <option> boxes using my while statement I tried inserting the rest of the code you provided (even manipulating it several times to get it to work) to no avail. Here's the code where I aim to put the if statement code you gave me - do you think this is right?

while ($row = mysql_fetch_array($result7)) {
$department_id = $row['department_id'];
$ddepartment=$row['department'];

$option_block .= "<option value=\"$department_id\">$ddepartment</option>";

}

Depending on where I put the code snippets I'm either getting a blank select box or various errors with regards to the if statement. The syntax seems right, but I don't know.

Thanks,
S./

Roger_Ramjet

First problem is that the 2 tables should be a PK:FK pair. So dates should have a column that contains department_id, not department.
Both columns should be indexed to facilitate the join.

Now lets turn to your real problem.

The USING syntax requires that PK:FK pairs have exactly the same name and data type in both tables. I take it that yours do or you would have got an error. So we should have the correct results we need.

Whenever you select from multiple tables that have columns with the same name then the results will be named eg departments.department, dates.department to differentiate between them (and tables and columns should NEVER have the same names or yet more confusion will occur).

We can use aliases to cut down on confusion and save keystrokes


$sql7 = "SELECT t1.department_id AS a, t1.department AS b, t2.department AS c 
		FROM " .  $table_name3 . " AS t1 LEFT JOIN dates AS t2 
		ON t1.b=t2.c";

$result7 = mysql_query($sql7);

while ($row = mysql_fetch_array($result7)) {
	$option_block .= '<option value="' . $row['a'] . '"';
	if (!is_null($row['c'])) {
		$option_block .= ' selected ';
	}
	$option_block .= '>' . $row['b'] . '</option>';
}

That should do it, but expect some typos 🆒

And note the way I use single quotes round my strings so I don't have to escape the html double quotes all the time; and use . concatenation to make the logic visible as well. Saves an awfull lot of syntax errors in the long run.