0

I know this will either use a left join or left outer join or something like that, but these types of queries always throw me off.

I have two tables:

categories

ID    CatName          CatSlug
1     Big Shirts       big-shirts

items

ID    ItemName         Category    Price    ...
1     Big Red Shirt    1           20.00

So I am trying to return all items in a given category, queried by the category slug. I also need to return the category name. So now, my two queries are like:

$return = array();

$query = "SELECT * FROM categories WHERE CatSlug = :CatSlug LIMIT 1"; 

$param = array(
    'CatSlug' => $slug
);

$result = $this->Db->selectOne($query, $param);

if ($result) { // If result exists

    $return['Category'] = $result['CatName']; // Need to return cat name

    $query = "SELECT * FROM items WHERE Category = :Category"; 

    $param = array(
        'Category' => $result['ID']
    );

    $result = $this->Db->selectAll($query, $param);

    if ($result) { // If result exists

        $return['Items'] = $result;

    } else { // If result does not exist

        $return['Items'] = array();

    } // End if result does not exist

}

return $return;

So, I am returning an array with two keys: Category contains the CatName value in the categories table, and Items contains an array of all items inside that category, or a blank array if there are none.

How can I combine these two queries?

MultiDev
  • 10,389
  • 24
  • 81
  • 148
  • If join "always throws you off", you need to read some good tutorials, because successful database use requires that you understand them. – Barmar Dec 29 '16 at 16:51
  • @Barmar I know that. If I could find a "good" tutorial I would. I've read many, but from what I've found, they all do a poor (in my opinion) job of explaining it properly. – MultiDev Dec 29 '16 at 16:55
  • A good start: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – Barmar Dec 29 '16 at 16:56

1 Answers1

0
  1. First of all, you should use LEFT JOIN between table page_categories and items.
SELET * FROM page_categories p   
LEFT JOIN items i ON p.category = i.id 
WHERE c.CatSlug = :CatSlug
  1. Loop in results and return data 
$return['CatName'] = :CatSlug
$return['Items'] = array (can be empty array)
tam nguyen
  • 196
  • 7