douhui8025 2019-04-23 19:12
浏览 74
已采纳

在codeigniter中加入三个sql表后,找不到所需的id

Can not find the desired Id

I am joining three SQL tables in CodeIgniter. I can able to retrieve the data from three tables. But I face a problem in finding the proper id.

I put my three tables below:

books table:

CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `book_name` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `author` varchar(200) NOT NULL,
  `publisher` varchar(200) NOT NULL,
  `price` varchar(200) NOT NULL,
  `quantity` int(11) NOT NULL,
  `categoryId` int(11) NOT NULL,
  `book_image` varchar(200) NOT NULL,
  `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `userId` int(11) NOT NULL,
  `status` enum('1','0') NOT NULL DEFAULT '0' COMMENT '1 = published | 0 = unpublished'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

category table:

    CREATE TABLE `category` (
      `id` int(11) NOT NULL,
      `category` varchar(100) NOT NULL,
      `description` text NOT NULL,
      `tag` varchar(100) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

users table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `contact` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `address` varchar(500) NOT NULL,
  `city` varchar(50) NOT NULL,
  `type` varchar(20) NOT NULL DEFAULT 'U',
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

My Model:

public function get_books($limit, $offset)
{   
    /*=== SQL join ===*/
    $this->db->select('*');
    $this->db->from('category');
    $this->db->join('books', 'books.categoryId = category.id');
    $this->db->join('users', 'books.userId = users.id'); #...Join three sql table

    $this->db->order_by('books.id', 'DESC');
    $this->db->where('books.status', '1');
    $this->db->limit($limit, $offset);
    $query = $this->db->get();
    return $query->result();
}

Now I get the id of users in my joined table. But I want books id as the main id. How I could solve that problem?

  • 写回答

3条回答 默认 最新

  • doudie2693 2019-04-23 20:05
    关注

    As the previous answer, you should not use the '*' this. You should write the select query manually, select whatever column you want from the tables.

    To get the book's id as your desired id, you should change your model code as below.

    Model code

    public function get_books($limit, $offset)
    {   
        $this->db->select('books.id, books.book_name, books.description, books.author, books.book_image, books.otherCol, category.category, users.name'); // You can add many more if you want.
    
        $this->db->from('books');
        $this->db->join('category', 'books.categoryId = category.id'); //2nd join
        $this->db->join('users', 'books.userId = users.id'); //3rd join
    
        $this->db->order_by('books.id', 'DESC');
        $this->db->where('books.status', '1');
        $this->db->limit($limit, $offset);
        $query = $this->db->get();
        return $query->result();
    }
    

    I think now you will get your desired id from the books table.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 delta降尺度计算的一些细节,有偿
  • ¥15 Arduino红外遥控代码有问题
  • ¥15 数值计算离散正交多项式
  • ¥30 数值计算均差系数编程
  • ¥15 redis-full-check比较 两个集群的数据出错
  • ¥15 Matlab编程问题
  • ¥15 训练的多模态特征融合模型准确度很低怎么办
  • ¥15 kylin启动报错log4j类冲突
  • ¥15 超声波模块测距控制点灯,灯的闪烁很不稳定,经过调试发现测的距离偏大
  • ¥15 import arcpy出现importing _arcgisscripting 找不到相关程序