|
|
Posted by "Kristen G. Thorson" on 06/15/05 17:28
Hopefully I understand your question correctly.
You have this SQL:
$media_query = 'SELECT media_id,media_name FROM media GROUP BY media_name';
And
"For every media_id which is associated with $cartoon in the table
media_art, check the box; for all others leave them unchecked."
The problem is, the SQL you have is not returning every media_id
(because you're grouping on media_name). I wonder if you truly need to
group on media_name. I would assume that media_name is unique in the
media table. If it is not, then you need to rethink your table
structure. If you group on media_name and there are duplicate
media_names, you can have data like this:
media_id media_name
4 Name1
16 Name1
Grouping by media_name as in your query above will produce a line like:
media_id media_name
4 Name1
You then output this to check boxes and use those to set the relation to
art and media. But what's the difference between media_id 4 and
media_id 16? In this case, if the database always returns the group by
this way, you would never set the media_id 16. If there is a distinct
difference between media_names with different ids, then I think you
probably need to make some changes.
That being said, if you do not need to group by media_name, then you can
simply do something like this:
select media.media_id, media.media_name, media_art.art_id, from media
left join media_art on media_art.media_id=media.media_id and
media_art.art_id=$cartoon['art_id']
Which would return a table like this if $cartoon['art_id']=15:
media_id media_name art_id
4 Name1 NULL
8 Name2 15
12 Name3 NULL
16 Name4 15
17 Name5 NULL
18 Name6 NULL
You could then test the output of art_id:
$checkbox_media[] = "<input type='checkbox' name='media_types[]'
value='{$media_rows['media_id']}'
".(rows['art_id']==$cartoon['art_id']?"
checked":"")."/>{$media_rows['media_name']} ";
HTH
kgt
Jack Jackson wrote:
> Hi,
> With your help I got some checkboxes generated the other day for a
> form. I would like some help getting a similar problem solved.
>
> I am now making a form to edit db entries made in the previous form. I
> have three tables involved: art, media and media_art. I need to show
> checkboxes for all available media. For the chosen record ($cartoon,
> which equals an art_id selected by the user) I must also go into the
> media_art table, and where the selected art ID has a corresponding
> media_id, display that media_id's media_name as a checked box.
>
> TABLE media:
> media_id media_name
> 1 ink
> 2 pencil
> 3 watercolor
> 4 gauche
> 5 watercolor pencil
>
>
> To find out the art_id of the chosen record, the user is selecting
> from a dropdown box in the form. I'm doing queries like this to make a
> publisher dropdown in a similar vein:
>
> $query = "SELECT * FROM art WHERE art.art_id = '$cartoon'";
> $publisher_query = 'SELECT * FROM publisher';
>
> $result = mysql_query($query);
> $publisher_result = mysql_query($publisher_query);
>
> while ($rows = mysql_fetch_assoc($result)){
>
> $publisher_dropdown = '<select name="publisher_id">';
> while ($pub = mysql_fetch_assoc($publisher_result)){
> $publisher_dropdown .= '<option value="' . $pub['publisher_id'];
> if ($pub['publisher_id'] == $rows['publisher_id']){
> $publisher_dropdown .= '" selected ';
> }
> $publisher_dropdown .= '">' . htmlentities($pub['publisher_name']);
> }
> $publisher_dropdown .= '</select>';
> }
>
>
>
> I now need to formulate how to make the checkboxes similarly. The
> original setup to make the checkboxes was :
>
> $media_query = 'SELECT media_id,media_name FROM media GROUP BY
> media_name';
> $media_result = mysql_query($media_query);
>
> $checkbox_media = array ();
> $media_types = array();
> while ($media_rows = mysql_fetch_assoc($media_result)){
> $checkbox_media[] = "<input type='checkbox'
> name='media_types[]' value='{$media_rows['media_id']}'
> />{$media_rows['media_name']} ";
> }
>
> Those which were checked were inserted into media_art thusly:
>
>
> media_id art_id
> 3 1
> 4 1
> 5 1
>
> What I want to do is say "For every media_id which is associated with
> $cartoon in the table media_art, check the box; for all others leave
> them unchecked.
>
> How can I do this?
> Thanks very much in advance,
>
> Jack
>
Navigation:
[Reply to this message]
|