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

Question

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?

Answer 1

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.