CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
Tahmid-ni7
Tahmid-ni7

Reputation: 410

After joining three sql tables in codeigniter, can't find the desired 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?

Upvotes: 2

Views: 76

Answers (3)

tumn
tumn

Reputation: 83

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.

Upvotes: 1

pauldrodriguez
pauldrodriguez

Reputation: 490

below, i am just selecting all columns from books table. I have also made books the main table instead of the category table

public function get_books($limit, $offset)
{   
    /*=== SQL join ===*/
    $this->db->select('books.*');
    $this->db->from('books');
    $this->db->join('category', '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();
}

Upvotes: 1

Ryan Wilson
Ryan Wilson

Reputation: 10765

Don't use select * in your query, Signify which columns you want by their table names or table aliases, since you have 3 tables joined and all have an id column it doesn't know which id column you want from your query result,

$this->db->select('books.id, books.status, users.someData, etc.');

Upvotes: 1

Related Questions