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;
}
}