dongmeixi5311 2018-06-12 13:24
浏览 95
已采纳

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

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;
    }
}
  • 写回答

1条回答 默认 最新

  • douchen4534 2018-06-12 13:34
    关注

    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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 线性代数的问题,我真的忘了线代的知识了
  • ¥15 有谁能够把华为matebook e 高通骁龙850刷成安卓系统,或者安装安卓系统
  • ¥188 需要修改一个工具,懂得汇编的人来。
  • ¥15 livecharts wpf piechart 属性
  • ¥20 数学建模,尽量用matlab回答,论文格式
  • ¥15 昨天挂载了一下u盘,然后拔了
  • ¥30 win from 窗口最大最小化,控件放大缩小,闪烁问题
  • ¥20 易康econgnition精度验证
  • ¥15 msix packaging tool打包问题
  • ¥28 微信小程序开发页面布局没问题,真机调试的时候页面布局就乱了