Reputation: 318
I need to exclude some records from a table, and show them, then, i need to show the other records, which were excluded.
I got 3 tables:
+----Songs----+ +----Tags----+ +----s_in_tag----+
| id | | id | | id |
| title | | name | | song_id |
+-------------+ +------------+ | tag_id |
+----------------+
In table Songs I store a list of songs, table Tags have a list of tags, and table s_in_tag bind those 2 tables.
Let say i have in Songs: 1, song nr1. In Tags: 1, POP; 2, Rap; 3, Classical. And in s_in_tag: 1, 3, 2. This means that song nr1 has tag Classical
I want to be able to add/remove tags in my dashboard, but how do i make a right query which will get all song tags, and will show them as a checked checkbox, and then other tags from table Tags as unchecked checkboxes?
I've got this so far:
SELECT * FROM tags
JOIN s_in_tag
ON tags.id = s_in_tag.tag_id
WHERE s_in_tag.song_id = *song id*
AND s_in_tag.tag_id NOT IN (SELECT tags.id FROM tags) GROUP BY tags.id
Im just trying what I found on internet, hope someone can help me there.
Upvotes: 0
Views: 775
Reputation: 68
Try this:
<?php
// Query
$sql = "SELECT t.id, t.`name` AS tag, IF(st.`id`, 'checked', 'unchecked') AS checkstatus
FROM tags t
LEFT JOIN s_in_tag st ON st.`tag_id` = t.`id`";
$rs = mysql_query($sql);
while ($row = mysql_fetch_assoc($rs)) {
$checked = $row['checkstatus'] ? ' checked="checked" ' : '';
?>
<p><input type="checkbox" <?php print $checked; ?> /> <?php print $row['tag']; ?> </p>
<?php } ?>
Upvotes: 2
Reputation: 50776
Why don't you include them both in the same query?
SELECT t.id, t.name, st.id IS NOT NULL AS is_checked
FROM Tags t
LEFT JOIN s_in_tag st ON t.id = st.tag_id AND st.song_id = [song_id];
Upvotes: 5
Reputation: 501
I would create two sql queries. One that gets you all tags and the other that gets you all used tags.
$allTags = "SELECT * FROM tags";
$usedTags = "SELECT * FROM s_in_tag;
On php layer you iterate through the $allTags array and on every iteration you look in the $usedTags array and if it contains the current tag you mark it as checked.
foreach($allTags as $key=>$value)
{
if (in_array($value, $usedTags))
{
echo "<input type='checkbox' checked /> ".$value;
}
else
{
echo "<input type='checkbox' /> ".$value;
}
}
The php part is not tested but should work.
Upvotes: 0