Getting data from three tables codeigniter using Query builder

Question

I have three tables in database.

1.george_holiday_users(id,package_id,name,email)

2.george_holiday_passengers(id,user_id,type,name,age,gender)

3.george_packages (id,package_name,description)

Now i want to select data from three tables george_holiday_users,george_packages and george_holiday_passengers tables where george_holiday_users.id=george_holiday_passengers.user_id and george_holiday_users.package_id=george_packages.id

    $this->db->select('*');
           $this->db->from('george_holiday_users');
           $this->db->join('george_holiday_passengers', 'george_holiday_users.id = george_holiday_passengers.user_id');
           $this->db->join('george_packages', 'george_packages.id = george_holiday_users.package_id');
           $query = $this->db->get();
return $query->result();

But i will the data from only george_holiday_users table.

What results are you getting and are you sure you should be getting the result you seem to be expecting? Are you calling $query->result() to get the result set? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 4, 2016 at 5:55

Norvert John Abella · Accepted Answer · 2016-01-04 08:23:46Z

My opinion is that you can remove the george_holiday_passengers and add the type,age,gender columns to the george_holiday_users as looking to it; its only a extended data of the holiday_users table and build query with your new database tables.

$query = $this->db->select('u.*, p.package_name, p.description')
       ->from('george_holiday_users u')
       ->join('george_packges p', 'u.package_id = p.id', 'left')
       ->get();

return $query->result();

anyway, you can achieved the result using this query.

$query = $this->db->select('u.*, hp.type, hp.name AS passengers_name, hp.age, hp.gender, p.package_name, p.description')
       ->from('george_holiday_users u')
       ->join('george_holiday_passengers hp', 'hp.user_id = u.id', 'left')
       ->join('george_packges p', 'u.package_id = p.id', 'left')
       ->get();

return $query->result();

you should be careful about the column names because on your database there are a lot of columns with the same names, It might affect your query when your using joins, etc.

Hope that helps.

Abdulla Nilam · Accepted Answer · 2016-01-04 06:54:43Z

0

Try this

$query = $this->db->select("SELECT * 
                FROM george_holiday_users 
                INNER JOIN george_holiday_passenger ON george_holiday_users.id = george_holiday_passengers.user_id
                INNER JOIN george_packages ON george_holiday_users.package_id = george_packages.id");
$result = $query->result_array();
return $result;

edited Jan 4, 2016 at 6:54

answered Jan 4, 2016 at 6:09

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

5 Comments

Blessan Kurien Over a year ago

Let me try your answer

Blessan Kurien Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = ghu.package_id' at line 1 SELECT SELECT * FROM george_holiday_users AS ghu LEFT JOIN george_holiday_passengers AS ghp ON ghu.id = ghp.user_id LEFT JOIN george_packages` AS gp ON gp.id = ghu.`package_id``

Blessan Kurien Over a year ago

check the manual that corresponds to your MySQL server version for the right syntax to use near '.id = george_holiday_users.package_id' at line 1 SELECT SELECT * FROM george_holiday_users LEFT JOIN george_holiday_passenger ON george_holiday_users.id = george_holiday_passengers.user_id LEFT JOIN george_packages ON george_packages.id = george_holiday_users.package_id

Abdulla Nilam Over a year ago

is this fields are in table?? user_id, package_id

Blessan Kurien Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

Getting data from three tables codeigniter using Query builder

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related