Use results of SQL query in next query

Im trying to make a tag based CMS/blog. Nothing complicated, as this is a learning exercise mainly. Im using PHP and MySQL, although I have the suspicion this would be easier with custom DB code written to the disk.

Anyway, I wanted to have two tables, a list of tags, with a name and id, and a list of posts, with content, and a list of IDs of tags that theyre associated with. However, it doesnt seem that you can put an array of integers in a single field of a table. So I made a third table, posttags, which contains OwningPost and TagID. There are multiple duplicate OwningPost in the table, each with a different TagID, so a post can have multiple tags. This doesnt seem very clean, but I couldnt think of anything better.

Now, I want to get a list of all the posts that have certain t开发者_JS百科ags, and do not have certain other tags. I havent even gotten to the not tags, im still stuck on posts containing the specified tags. I have this code:

printPostsTagged(array('Blog Post'),array('First')); 
function printPostsTagged($iTags,$eTags)
{
 $tagQ="SELECT ID from tags WHERE (";
 for($i=0;$i<count($iTags)-1;$i++)
    $tagQ=$tagQ."Name='".$iTags[$i]."' OR ";
 $tagQ=$tagQ."Name='$iTags[$i]')";
 $tagR=mysql_query($tagQ);
     ...

Which makes a query by stringing together all the iTags (included tags) with ORs, that will get a list of the ID of each tag in iTags, and now I want to use this whole list to select the OwningPost from posttags, and then use that list of indexes to get a list of all the corresponding posts from the posts table.

I havent used SQL before, but none of my googling seems to turn anything up, and I get the strong feeling Im doing something completally wrong here

For a good table schema look at: http://forge.mysql.com/wiki/TagSchema#Recommended_Architecture

Usually items aren't queried by tag "name", but by tag id or tag slug, anyway you can simplify your function using a JOIN:

function printPostsTagged($iTags, $eTags) {
  $in = '"' . implode('", "', array_map('mysql_real_escape_string', $iTags)) . '"';
  $sql = "SELECT p.* FROM posts p 
   INNER JOIN post_tag pt ON pt.post_id = p.post_id
   INNER JOIN tags t ON pt.tag_id = t.tag_id
  WHERE name IN ($in)";

  // it removes duplicates and fetch just the posts with all the iTtags assigned     
  $sql .= " GROUP BY p.id HAVING COUNT(p.id) = " . count($iTags); 

}

You're actually right on target with your database design - I can't think of a cleaner way than to use the 3 tables. Your SQL query should look something like this:

SELECT * FROM content_table INNER JOIN OwningPost ON content_table.postID = OwningPost.postID WHERE OwningPost.tag = 'tag1' OR OwningPost.tag = 'tag2' etc...

Let me know if that does it...

zacaj,

It sounds to me like you are trying to do Sub Queries. I have posted a similar example here:

Similar

Your third table is what the call a join table or a linking table. That is the proper way to model it. It's these relations that make a relational db relational. As Brandon pointed out you should probaby use a subquery. Don't worry that you might be throwing away data from your first query. That's fine. Also, as ThiefMaster points out, you should use IN instead of OR when possible.