php - MYSQL: Adding and updating, conditionally, a temp colum into a table -

i writing tagging system.

i have table of tags, , full of items tagged consisting of tagged items id , id of tag.

i writing fetch script give data generate checkboxes can visualize/edit tags given item.

i had idea add temporary value called checked , on condition supplied item id present in tagged table (it has tag) set temp check value true in table.

select t.name name, t.id id, '0' checked  tags t if( id in (select distinct tag_id tagged workshop_id = $id) update t set checked='1' 

it throws generic sql error , doesn't work. don't have enough experience know if bad idea, or its doable. input appreciated.

you don't need update, can use case expression test whether tags.id found in tagged table or not, , put in temporary column checked, this:

select    t1.id id,   t1.name name,    case when t2.tag_id null 0 else 1 end checked tags t1 left join (    select *     tagged ) t2 on t1.id = t2.tag_id; 

see in action here:

• sql fiddle demo

this give this:

| id | name | checked | ----------------------- |  1 |    |       1 | |  2 |    b |       0 | |  3 |    c |       1 | |  4 |    d |       0 | 

---

update:

if want add condition tagged table where workshop_id = 26, add in subquery not left join condition, this:

select    t1.id id,   t1.name name,    case when t2.tag_id null 0 else 1 end checked tags t1 left join (    select *     tagged    workshop_id = 26 ) t2 on t1.id = t2.tag_id; 

• updated sql fiddle demo