Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
715 views
in Technique[技术] by (71.8m points)

php - Querying 2 Tables in a single query

I have a table for posts and a table for categories, what i want is to select posts that are in a specific category. The problem is that the category is stored in another table and not in the posts table, here is the example:

posts

id   title       body
---------------------------
125  Some title  Blah blah

categories

postid  category
----------------
125     politic

I want in single query to fetch posts in the politic category by example, what to do?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Use:

SELECT p.id,
       p.title, 
       p.body
  FROM POSTS p
  JOIN CATEGORIES c ON c.postid = p.id
 WHERE c.category = 'politic'

The issue I have with your CATEGORIES table is that storing the category value as a string means the data isn't normalized - you should instead have a CATEGORY table:

CATEGORY

  • category_id (primary key, auto_increment)
  • category_description

...and use the category_id value in the CATEGORIES table:

CATEGORIES

  • category_id (primary key, foreign key to CATEGORY.category_id)
  • post_id (primary key, foreign key to POSTS.postid)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...