在SQL中加入什么我需要根据来自不同表的ID获取类别名称？

Question

I am trying to work out the inner joins or the type of join I need in SQL for me to get the Category name based on the ID of a product from a different table.

I'm making a PHP product page script using my own MVC framework and right now I'm able to get all the data easily for my product but I need to get the category name based on the cat_id. I'm unsure how to do this kind of query as I have never actually had to use them before.

I have two tables, the items table that has lots of data and the important one in this question is 'cat_id':

id      name         ###cat_id###
-----------------------------
1      item name         2
2      item name         1
3      item name         3

and the second database is the category table that looks like this:

###id###      name         status
-----------------------------
1      catty            1
2      cat              0
3      cat              1

So what I want to happen is say item 1 has cat_id = 1 it echos Catty as the category on the views bit but i cant figure out how to do the joins.

Here is my PHP code in the views:

                <?php if($data['isProducts']) { foreach($data['products'] as $product) : ?>
                    <div class="col-lg-4 col-md-4 col-sm-6 mb-3">
                        <div class="card h-100 shadow-1 rounded-0">
                            <a href="<?php echo FULL_ROOT;?>/item/<?php echo $product['id']; ?>/">
                                <img class="card-img-top rounded-0" src="<?php echo FULL_ROOT;?>/uploads/items/<?php echo $product['id']; ?>/<?php echo $product['preview_img']; ?>" alt="<?php echo $product['name']; ?>">
                            </a>
                            <div class="card-body text-center">
                                <a href="<?php echo FULL_ROOT;?>/item/<?php echo $product['id']; ?>/"><h2 class="card-title font-weight-bold f-18"><?php echo $product['name']; ?></h2></a>
                                <div class="clearfix">
                                    <a data-toggle="tooltip" data-placement="top" title="Add to Wishlist" href="#" class="btn btn-sm btn-light btn-lightb float-left mr-1"><i class="far fa-heart"></i></a>
                                    <a data-toggle="tooltip" data-placement="top" title="Live Preview" href="<?php echo $product['demo']; ?>" target="_blank" class="btn btn-sm btn-light btn-lightb float-left"><i class="fas fa-desktop"></i></a>
                                </div>
                            </div>
                            <div class="card-footer bg-white">
                                <div class="clearfix">
                                    <button type="button" class="btn btn-sm btn-light float-left btn-lightb">
                                      <!-- get name from cat table based on id -->  <?php echo $product['cat_id']; ?>
                                    </button>
                                    <button type="button" class="btn btn-sm btn-light float-right btn-lightb">
                                        <?php echo $data['settings']['payment_currency_sym'].$product['price']; ?>
                                    </button>
                                </div>
                            </div>
                        </div>
                    </div>
                <?php endforeach; } else { ?>
                    <div class="col-12">
                        <div class="alert alert-primary shadow-1 rounded-0" role="alert">
                            <b><i class="fas fa-info-circle"></i> No Items Available!</b>
                        </div>
                    </div>
                <?php } ?>

Here is my controller code:

public function profile($user_id = '')
{
    // Get data from model
    $sdata = $this->setting->getAll();
    // Check if empty
    if($user_id == '') { redirect(''); }
    // Get data from model
    $udata = $this->user->getUserByUsername($user_id);
    $pdata = $this->user->getUserProducts($udata['id']); //Get the cat name based on id from this bit
    if(is_array($pdata[0]) ) {
        $isProducts = true;
    } else {
        $isProducts = false;
    }
    $data = array(
       "user" => $udata,
       "products" => $pdata,
       "isProducts" => $isProducts,
       "settings" => $sdata
    );
    // Checks if no data found (404)
    if($udata == false) { redirect('error'); }
    // Load view
    $this->view('marketplace/user/profile', $data);     
}

Here is my model code:

public function getUserProducts($user_id)
{
   // $bind = [':username' => $user_id];
    $sql = "select ??????????? from msi_items INNER JOIN/OUTER JOIN ?????";
    $results = $this->run($sql);
    //$results = $this->db->select('msi_items','status = 1 AND author_id = :username', $bind);
    if(!is_array($results[0])) {
        $new_results = array();
        array_push($new_results, $results);
        return $new_results;
    } else {
        return $results;
    }
}

Answer 1

You could try something like:

select i.cat_id, c.name
from items i inner join category c on i.cat_id = c.id

Or, like the below if you want to have all items even if there are no categories. In this case you will get nulls in where there are no c.names

select i.cat_id, c.name
from items i left join category c on i.cat_id = c.id