How to display mysql records as preselected checkboxes?

Question

I have a table column called post_tags within a table called posts where assigned tags are stored separated by the @ symbol. I also have a table called tags where all tag names are stored. I would like to design my database in a more normalized way but for the purpose I am trying to achieve this is the easiest option.

Anyway, I want to display on the screen all the entries from the tags table as checkboxes, so I do:

    $query = mysql_query("SELECT * FROM tags ORDER BY name");

    while ($row = mysql_fetch_assoc($query)) {

        $tag = $row['name'];

        echo "<input type='checkbox' name='tags[]' value='$tag' />\n";

    }

Next I want to have the tags that are assigned to a particular post be preselected. For example, if I have a post with the following in it's post_tags column:

party@beaches@dolphins@

I want the "party", "beaches" and "dolphin" checkboxes to be checked by default (while the checkboxes for the other options are unchecked). How can this be done?

Answer 1

The first thing to do is see if there is any existing data. So run that query and put the result of that table cell into lets say $checkedstring if not then put your default string in.

<?php
$checkedstring = "party@beaches@dolphins@";
//Pull from DB if exsists and set $checkedstring to that value
///
$checkeditems =  explode ( "@" , $checkedstring);
$checked = array();
foreach($checkeditems as $item)
{
  $checked[$item]=true;
}    

$query = mysql_query("SELECT * FROM tags ORDER BY name");

while ($row = mysql_fetch_assoc($query)) 
{
    $tag = $row['name'];
    $checkedstatus = '';
    if($checked[$tag])
    {
      $checkedstatus = "checked='checked'";
    }
    echo "<input type='checkbox' name='tags[]' value='$tag' $checkedstatus />\n";
}


?>

Answer 2

try the two results and the in_array() function.

<?php
$tags = mysql_query("SELECT * FROM tags ORDER BY name");
$post_tags = "party@beaches@dolphins@";
$arr_tags = explode("@", $post_tags);

while ($row = mysql_fetch_assoc($query)) {
    $check = in_array($arr_tags, $row['name'])? 'checked="checked"' : "";
    echo '<input type="checkbox" name="tags[]" value="'.$row['name'].'" '.$check.' />';
    echo "\n";
}
?>

UPDATE Because of Jeff question on performance, I looked for faster solutions and using isset() is faster so this would do a faster lookup of the values. the array_flip() is 3 time less taxing than in_array():

<?php
$tags = mysql_query("SELECT * FROM tags ORDER BY name");
$post_tags = "party@beaches@dolphins@";
$arr_tags = array_flip(explode("@", $post_tags));

while ($row = mysql_fetch_assoc($query)) {
    $check = isset($arr_tags[$row['name']])? 'checked="checked"' : "";
    echo '<input type="checkbox" name="tags[]" value="'.$row['name'].'" '.$check.' />';
    echo "\n";
}
?>

Answer 3

I consistently have errors on the while( line (Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in [dir]/locationstest.php on line 9)

Any ideas why? I've tried other code with that same line and had the same errors.