Populating/Checking Checkbox Values Based on Lookup

[Jeff Donnici]

I'm sure there's a simple answer but after browsing php.net and google for a
while, I can't seem to find the right function to handle this. Assume I've
got three tables -- USERS, COLORS, and USER_COLOR_MAP. The USERS table has
an ID and a user's name. The COLORS table has an ID and a color's name. The
USER_COLOR_MAP table has two columns -- user_id and color_id. This lets me
map N colors to each user.

In a form, I want to display a checkbox for each color and, if I'm
displaying an existing user, I want to pre-check any boxes that have been
mapped to that user... the HTML is all squared away, so no worries there.

// grab ALL colors
$ALL_sql = "SELECT id, name FROM colors;"

// grab colors for a certain user
$MAPPED_sql = "SELECT color_id FROM user_color_map WHERE user_id = ".
$a_users_id;

Ok, so assuming I run both queries, I've now got two result-sets. I iterate
through the ALL result set and create a checkbox for each color. But, at
each stop, I'd like to search the MAPPED result set to see if the ID value
appears in it (in the COLOR_ID column)... If so, add the word 'CHECKED' to
the HTML to pre-check the box.

This is the part I can't seem to find a simple function for. I know I could
loop through the MAPPED result set each time to find a match, but that seems
awfully inefficient. The IN_ARRAY function looks like just the ticket except
that it doesn't seem to like being given a MySQL result set instead of an
array. Ok, so maybe I create an array from the MAPPED result set... but it
doesn't look like EXPLODE will do that for me, so I'm back at looping
through the MAPPED result set and creating an array of all the values.
Surely there must be a better way.

I don't need a bunch of code, but a pointer or reference to function(s) that
would be applicable would be most appreciated!

Thanks in advance,

-- jeff

[Thyme]

This code I wrote might help you, but I don't know if it will do the exact
thing you want. I use it to

//Goes through a string and writes "CHECKED" in to an array with each char
in the string as the identifyer.

$i=0;
while ($i < strlen($myrow[1])){
$key=substr($myrow[1], $i, 1);
$ischecked[$key] = "CHECKED";
$i++;
}

\\ $gname is an array with the name of each of the checkboxes. $ischecked
will either be blank or "CHECKED"

foreach($gname as $key => $groupname){
echo "<br><input type=\"checkbox\" name=\"chk[$key]\" style=\"border:0px\"
$ischecked[$key]>$groupname";

\\ I hope that helps, but you might have to put things into an array.

"Jeff Donnici" <jdon...@ay-tee-tee-bee-eye-dot.com> wrote in message
news:JhnZ8.569709$cQ3.52707@sccrnsc01...

[Cliff Crawford]

In article <JhnZ8.569709$cQ3.52707@sccrnsc01>, Jeff Donnici wrote:
| I'm sure there's a simple answer but after browsing php.net and google for a
| while, I can't seem to find the right function to handle this. Assume I've
| got three tables -- USERS, COLORS, and USER_COLOR_MAP. The USERS table has
| an ID and a user's name. The COLORS table has an ID and a color's name. The
| USER_COLOR_MAP table has two columns -- user_id and color_id. This lets me
| map N colors to each user.

If you have a fixed number of colors, it might be easier to get rid of
the COLORS table, and store the colors as a bitfield in the
USER_COLOR_MAP table...

CREATE TABLE USER_COLOR_MAP (
user_id INT REFERENCES USERS,
colors BIT(8) -- or however many colors you have
);

Then when you display the page, you only have to do a SELECT on the
USER_COLOR_MAP table, and check off each checkbox corresponding to the
set bits in the colors field.

--
Cliff Crawford :: cjc26 at cornell dot edu
It goes against the grain of modern education to teach children to program.
What fun is there in making plans, acquiring discipline in organizing thoughts,
devoting attention to detail and learning to be self-critical? -- Alan Perlis

[Reha Sterbin]

In article <JhnZ8.569709$cQ3.52707@sccrnsc01>, Jeff Donnici wrote:

> Ok, so assuming I run both queries, I've now got two result-sets. I iterate
> through the ALL result set and create a checkbox for each color. But, at
> each stop, I'd like to search the MAPPED result set to see if the ID value
> appears in it (in the COLOR_ID column)... If so, add the word 'CHECKED' to
> the HTML to pre-check the box.
>
> This is the part I can't seem to find a simple function for. I know I could
> loop through the MAPPED result set each time to find a match, but that seems
> awfully inefficient. The IN_ARRAY function looks like just the ticket except
> that it doesn't seem to like being given a MySQL result set instead of an
> array. Ok, so maybe I create an array from the MAPPED result set... but it
> doesn't look like EXPLODE will do that for me, so I'm back at looping
> through the MAPPED result set and creating an array of all the values.
> Surely there must be a better way.
>

You might want to think of the MySQL result resource as directions to the next
row of your results, rather than as an array[1]. Of course, if you want an
array, it's pretty easy to make one (see below).

If I were in your shoes, I'd go get the user's color_ids first and put them
into an array:

while ($row = mysql_fetch_assoc($result)){
$users_colors[] = $row['color_id'];
}

Then, when you go circling through your ALL colors results, you can use
in_array() on $users_colors to make your check marks as you go.

Hope this helps.

-Reha

[1] - What the result resource is, precisely, is not really something I know
much about. Perhaps someone else could clear it up?
--
/(bb|[^b]{2})/ ...THAT is the question.
=======================================
http://www.stwing.upenn.edu/~rsterbin

[Zac Hester]

Jeff,

I do that all the time. You're not going to find a simple solution.
There's no function that will map multiple arrays together in precisely the
way you want to do it. The way I do it may not help out your particular
problem depending on what you want to accomplish in the user interface. I
keep my PHP code pretty simple by loading all the data into objects in the
browser's JavaScript. I create objects in JavaScript that use arrays whose
dimensions aren't important. The big benefit of this system is that you can
then cycle through your primary item (USERS, in your case) with a select box
and populate a field of check boxes (corresponding to your secondary item:
COLORS) on the fly (without any trips to the server).

If you want, I can post some code I use on an image gallery system I use.
It databases images along with a set "groups" that each image can be
associated with. The groups determine the sections of the image gallery and
each image can belong to any number of galleries. In the upload form,
there's a place to associate an uploaded image with one or more groups. All
uploaded images have an entry in a select box and as the user changes the
image in the select box, its list of associations is updated to reflect the
groups that image belongs to (the groups are arranged as a field of
checkboxes). The user can then make changes to an individual image using
the form (or just view each image's associations). I know you didn't ask
for any code, so let me know if you're interested in seeing how I've done
it.

Unfortunately, there is still a bit of looping through arrays involved. If
you're performance is that critical in this situation, I would recommend
writing a PHP extension and compiling it in with PHP and Apache. It's
actually pretty easy if you have some C background. Check out the
documentation on php.net or get a hold of a copy of "Programming PHP" by
Lerdorf and Tatroe. I've written several little extensions and found it
quite simple.

HTH,
Zac

"Jeff Donnici" <jdon...@ay-tee-tee-bee-eye-dot.com> wrote in message
news:JhnZ8.569709$cQ3.52707@sccrnsc01...

[Jeff Donnici]

Hi Zac,

Thanks for the message. As mentioned to Reha, I pretty much went the route
of pre-loading their choices into an array (using array_push()) and then as
I go through ALL the items, I just use in_array to see if I should add the
CHECKED statement to the INPUT tag. That pre-checks them for me when editing
an existing user. As far as processing the results, I'm representing all the
checkboxes as an array[] in the FORM, which makes iterating through it in
the ACTION very easy.

Thanks!

-- jeff

"Zac Hester" <ne...@planetzac.net> wrote in message
news:3d376105$1...@news.enetis.net...