选择两个具有两个条件的表

Question

How to select the table-article rows in table-article_category category_id = 5 also in table-article visible = 1 with pdo and join method?

$category_id = 5;

article_category
id | article_id | category_id
1  | 1          | 5
2  | 2          | 1
3  | 3          | 5

article
id | subject | visible
1  |         | 1
2  |         | 1        
3  |         | 1
4  |         | 1

table-article id equal table-article_category article_id

This is what i do now, first select table-article_category make array then loop to select table article

... loop 
$sql = "SELECT * FROM article WHERE visible = :visible AND id = :id";
$stmt = $connect_db_article->prepare($sql);
$stmt->bindValue(':visible', $visible);
$stmt->bindValue(':id', $article_category[$i]['article_id']);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

How to use join to get the same result?

$sql = "SELECT * FROM article_category SUM(article) WHERE article_category.category_id = :category_id AND visible = :visible";
$stmt = $connect_db_article->prepare($sql);
$stmt->bindValue(':category_id', $category_id);
$stmt->bindValue(':visible', $visible);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Answer 1

SELECT * FROM article 
LEFT JOIN article_category 
  ON article_category.article_id = article.id 
WHERE article_category.category_id = 5;

This is working under the assumption that an article can have more than one category. If you can only have one category per article, it might make more sense to include the category_id field in the article table.