[Resolved] Getting prepopulated checkboxes from an associated table?

new7media

I have an application where I collect some of the form input with checkboxes. Part of this application is an edit form where people will login and get to their personal information and be able to modify it.

What I want to do with the checkbox data in the edit form is echo all possible checkboxes back to the screen and automatically check the ones that were previously selected while still providing people with all possible checkboxes so that they can uncheck ones that were previously checked, or check ones that now apply.

I've looked at a ton of posts in PHPBuilder that deal with this type of thing, but none that I've seen deal with data the way I've set it up in my database. I'm pretty sure this is because I'm using an associative table to collect some of the data rather than using comma-delimited data that all goes into one cell. Giving you the database set-up will be quicker than trying to describe what's going on. I'm not dealing with pets, but this is essentially what I've got going on:

Personal Info Table

person_id | name | location

1 | Lee | Chicago

2 | Phil | New York

Pet Lookup Table

pet_id | type_of_pet

1 | dog

2 | cat

3 | turtle

4 | goldfish

5 | ferrett

Pet Data Table

data_id | person_id | their_pet

1 | 1 | dog

2 | 1 | turtle

3 | 2 | dog

4 | 2 | cat

5 | 2 | goldfish

Since one person might have many pets, I decided to break this data out into its own associative Pet Data table. Using the data above and putting Phil's profile together, Phil is from New York and he has a dog, a cat, and a goldfish. When Phil comes back to the site to update his profile because his goldfish died, he needs to see the following in the Pet Checkboxes part of the edit for and be able to uncheck goldfish as well as, say, check turtle:

We currently have the following pets listed for your profile:
[x] dog
[x] cat
[ ] turtle
[x] goldfish
[ ] ferrett

I can get the initial data into the database and display it without any problems. But when someone comes back to the application to edit their profile, I can't get my checkboxes -- those that are already selected being checked and those that aren't yet selected showing as unchecked-- to display in the edit form. I'm pretty sure this will take a foreach condition, but I just can't figure out how to set it up. Any help is greatly appreciated! Thanks!

devinemke

first of all, your 3rd table, the "Pet Data Table" does not need the names of the pets in it, all you need is the pet_id from pet table. that's a standard way of doing a relational database (using primary keys).

anyway, once Phil is logged you will have his primary key, in his case "2". You can then relate that to his pets:

$id = 2; // Phil's primary key

$result = mysql_query ('SELECT * FROM pets');
while ($row = mysql_fetch_assoc ($result)) {$pets[$row['pet_id']] = $row['type_of_pet'];}

$result = mysql_query ('SELECT * FROM pet_data WHERE person_id = ' . $id);
while ($row = mysql_fetch_assoc ($result)) {$pet_data[$row['data_id ']] = $row['their_pet'];}

echo 'We currently have the following pets listed for your profile:';
foreach ($pets as $value)
{
	echo '<input type="checkbox" name="pet_data[]" value="' . $value . '"'; if (in_array ($value, $pet_data)) {echo ' checked';} echo '>' . $value . '<br>';
}

what if Phil has 2 dogs? you may want to reconsider your data structure.

new7media

Thanks for your help! I am sort of up and running, but not quite. I understand your comment about my need to change my lookup table so that I'm saving the pet_id number to the database rather than the label for type of pet. I made this switch, relabelling my test data so that the pet table now looks like this:

Pet Data Table

pet_data_id | person_id | pet_id

1 | 1 | 1
2 | 1 | 3
3 | 2 | 1
4 | 2 | 2

5 | 2 | 4

But the script doesn't work after making this change in the data table. Where before, with the pet types listed out in word form I was getting all checkboxes with the appropriate ones checked, now I get all of the checkboxes listed out, but none checked. What did I miss here? Appreciate your help!

//connect to database

$result = mysql_query ("SELECT * FROM pet_lookup", $db);
while ($row = mysql_fetch_array($result)) {
	$pets[$row['pet_id']] = $row['type_of_pet'];
}

$result = mysql_query ("SELECT * FROM pet_data WHERE person_id = '$person_id'", $db);
while ($row = mysql_fetch_array($result)) {
	$pet_data[$row['pet_data_id']] = $row['pet_id'];
}

echo 'We currently have the following pets listed for your profile:<br><br>'; 
foreach ($pets as $value) { 
    echo '<input type="checkbox" name="pet_data[]" value="' . $value . '"';
	if (in_array ($value, $pet_data)) {
		echo ' checked';
	}
	echo '>' . $value . '<br>';
}

devinemke

my original code sample was based on your old data structure. here is updated code for the new structure:

$result = mysql_query ('SELECT * FROM pets');
while ($row = mysql_fetch_array ($result)) {$pets[$row['pet_id']] = $row['type_of_pet'];}

$result = mysql_query ("SELECT * FROM pet_data WHERE person_id = '$person_id'");
while ($row = mysql_fetch_array ($result)) {$pet_data[$row['pet_data_id']] = $row['pet_id'];}

echo 'We currently have the following pets listed for your profile:<br><br>';  

foreach ($pets as $key => $value)
{  

    echo '<input type="checkbox" name="pet_data[]" value="' . $key . '"'; 
    if (in_array ($key, $pet_data)) {echo ' checked';}
	echo '>' . $value . '<br>'; 
}

new7media

Thanks so much Devin! This is exactly what I needed. You're great! And so's your script! I am going back to the drawing board on arrays -- I admit that I do not quite understand what is going on in this particular line of your code:

... {
$pet_data[$row['pet_data_id']] = $row['pet_id'];
}

But, that's for me to figure out. Thanks for your help! I feel like I can move on with my life...! 🙂 Have a great weekend. LB