Re: [PHP] populate form input option dropdown box from existing data

PJ wrote:
> I'm including the relevant code:
> 
> // select categories for book to be updated
> $sql = "SELECT id, category FROM categories, book_categories
>         WHERE book_categories.bookID = '$bid' &&
> book_categories.categories_id = categories.id";
> if ( ( $results = mysql_query($sql, $db) ) ) {
>   while ( $row = mysql_fetch_assoc($results) ) {
>     $selected[] = $row['id'];
>     }
>   }
> else $selected = Array( 0 => '0');
> echo $selected;
> print_r($selected);
> 
> $sql = "SELECT * FROM categories";
> echo "<select name='categoriesIN[]' multiple size='8'>";
>   if ( ( $results = mysql_query($sql, $db) ) !== false ) {
> while ( $row = mysql_fetch_assoc($results) ) {
>             if (in_array($row['id'], $selected)) {
>                echo "<option value=", $row['id'], " selected='selected'
>> ", $row['category'], "</option><br />";
>                    }
>                else echo "<option value=", $row['id'], ">",
> $row['category'], "</option><br />";
>             }
>         }
> 
> Problem #1)    in the first query result. I can't figure out how to deal
> with it. The code works fine if there are categories assigned to the
> book. If not, an undefined variable error is spewed out for selected.
> 

That's because the test you use for the success of the query:
	( ( $results = mysql_query($sql, $db) ) !== false )
is true if and only if the query succeeds, whether or not you get any rows returned.
You then start looping over the fetched rows, and if there are none $selected
never gets anything assigned to it, and so never gets defined.

Since you don't rely on $selected being actually populated (in_array works fine
on an empty array...), you might be better off pre-setting $selected before you
start the first query:

$selected = Array();
$sql = "SELECT id, category FROM categories, book_categories
         WHERE book_categories.bookID = '$bid' &&
               book_categories.categories_id = categories.id";
if ( ( $results = mysql_query($sql, $db) ) )
{
    while ( $row = mysql_fetch_assoc($results) )
    {
        $selected[] = $row['id'];
    }
}

> Problem #2) in the second query, the selected is in the source code but
> it is not highlited. Several times I did get the categories highlighted,
> but I could never catch what it was that made it work. When I had the
> $id problem, i was trying this code from Yuri (but I don't understand
> where he got the $id from ) :
> 

The HTML you generate in the selected case is not quite right - you should have
quotes around the "value" attribute's value, and you missed a closing '>' off
the option tag...

while ( $row = mysql_fetch_assoc($results) )
{
    if (in_array($row['id'], $selected))
    {
        echo "<option value='", $row['id'], "' selected='selected' >",
$row['category'], "</option><br />";
    }
    else
    {
        echo "<option value=", $row['id'], ">", $row['category'], "</option><br />";
    }
}

More succinctly:

while ( $row = mysql_fetch_assoc($results) )
{
    $sel = in_array($row['id'], $selected) ? "selected='selected'":"";
    echo "<option value='{$row['id']}' $sel>{$row['category']}</option><br />";
}

Unless the code is seriously performance critical, I still think variable
interpolation is nicer to read than all those quotes and commas, and it keeps
the HTML structure together better...


Good luck

-- 
Peter Ford                              phone: 01580 893333
Developer                               fax:   01580 893399
Justcroft International Ltd., Staplehurst, Kent