Use results of SQL query in next query

Question

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 tags, 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

Did you know that SQL supports WHERE col IN ('list', 'of', 'values')? — ThiefMaster
– ThiefMaster, Commented Jul 8, 2011 at 23:19
"I havent used SQL before" -- please read this: en.wikipedia.org/wiki/Sql_injection — Jon Seigel
– Jon Seigel, Commented Jul 8, 2011 at 23:32
Also, you may find the Stack Exchange data dump schema of interest: meta.stackexchange.com/questions/2677/… — Jon Seigel
– Jon Seigel, Commented Jul 8, 2011 at 23:34
"This doesnt seem very clean, but I couldnt think of anything better." No, the opposite, this is the best way to do that! You, in fact, reinvented the "middle" or "associaltion" or "junction" table, used for many-to-many relationships (one post can have many tags, one tag can tag many posts). Congrats. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jul 8, 2011 at 23:36
Could anyone help with the exclusion? Theres no OUT to negate IN, and Im not finding anything online. Im sure theres something, but I dont quite have a grasp of INNER JOIN's workings with two joins or any idea what syntax to search for — zacaj
– zacaj, Commented Jul 9, 2011 at 2:22

Francesco Terenzani · Accepted Answer · 2011-07-11 12:57:25Z

2

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); 

}

edited Jul 11, 2011 at 12:57

answered Jul 8, 2011 at 23:37

Francesco Terenzani

1,3917 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

zacaj Over a year ago

Is there a way to join the posts together that have the same posts.Name?

ypercubeᵀᴹ Over a year ago

@zacaj: You can try to work with this query by adding a GROUP BY and using various aggegate functions.

zacaj Over a year ago

Also, this seems to find posts that have ANY of iTags, I need only posts with ALL iTags

Francesco Terenzani Over a year ago

To find posts with all the iTags: $sql .= " GROUP BY p.id HAVING COUNT(p.id) = " . count($iTags);

ElonU Webdev · Accepted Answer · 2011-07-08 23:26:02Z

1

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...

answered Jul 8, 2011 at 23:26

ElonU Webdev

2,45914 silver badges15 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:24:31Z

0

zacaj,

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

Similar

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Jul 8, 2011 at 23:20

lockdown

1,5065 gold badges16 silver badges34 bronze badges

1 Comment

Francesco Terenzani Over a year ago

a sub query could work fine, but usually it is slower then a join

James Scriven · Accepted Answer · 2011-07-08 23:41:39Z

0

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.

answered Jul 8, 2011 at 23:41

James Scriven

8,2341 gold badge34 silver badges37 bronze badges

Collectives™ on Stack Overflow

Use results of SQL query in next query

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related