SELECT All Posts In A Certain Category

I indeed read this post (Get all posts from a specific category) but it does not seem to apply to my situation.

My situation is that i have two tables as follows:

1/ table categories

category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
lang_id TINYINT UNSIGNED NOT NULL,
name VARCHAR(60) NOT NULL,
PRIMARY KEY (category_id),
UNIQUE (name)
         ) ENGINE = INNODB';

in which lang_id values are 1 (equivalent to English) and 2 (equivalent to Vietnamese) which is used for filtering by using $_SESSION[‘lid’].

category_id     lang_id         name
  1                 1           Arts and Entertainment
  2                 1           Computers
  3                 2           Nghệ thuật và Giải trí
  4                 2           Máy tính

2/ table posts:

    'CREATE TABLE posts (
    post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id TINYINT UNSIGNED NOT NULL,      
    lang_id TINYINT(3) UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    subject VARCHAR(150) NOT NULL,
    PRIMARY KEY (post_id),
    INDEX (category_id),
    INDEX (lang_id),
    INDEX (user_id)
    ) ENGINE = MYISAM';

In which the category_id is the foreign key of the first table.

question_id     category_id     lang_id     user_id     subject
  1               1               1                1      arts  
  2               4               2                1      máy tính 
  3               5               1                1      business and money

I would like to select the posts in a certain category when we mouse-click on it. So, I run this query:

$q = "SELECT subject
FROM categories AS ca
INNER JOIN questions AS q
USING (category_id)
WHERE q.lang_id = {$_SESSION['lid']}  
GROUP BY ca.category_id

$r = mysqli_query($database_connect, $q)
if(mysqli_num_rows($r) > 0) {
    while ($subject = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
       echo '<ul>
        <li>'. $subject['subject']. '</li>
         </ul>';
}

But the result does not return as desired. For example, When I click on category 1 (Arts and Entertainment), the result returns two subjects (arts posted in category_id 1, business and money posted in category_id 5 ).

Can you help me to re-build the query, please? I really really got stuck here.

Answer

SELECT categories.*, posts.* from posts left join posts on categories.category_id=posts.category_id where posts.lang_id = {$_SESSION['lid']} and posts.category_id={your given category}

Advertisement

Answer