Join two table getting some error in codeigniter php

Question

I want to join 2 table, but getting some error. See the below code.

Error-:

A Database Error Occurred Error Number: 1052

Column 'user_id' in where clause is ambiguous

SELECT * FROM fuel_item JOIN fuel_unit ON fuel_item.unitid = fuel_unit.id WHERE user_id = '1' ORDER BY id DESC

Filename: D:/xampp/htdocs/fuel/system/database/DB_driver.php

Line Number: 691

 <?php 

    $query = $this->db->select('*');
    $query = $this->db->from('fuel_item');
    $query = $this->db->join('fuel_unit', 'fuel_item.unitid = fuel_unit.id');
    $query = $this->db->where('user_id',$_SESSION['user']);
    $query = $this->db->order_by('id','Desc');
    $query = $this->db->get();
    if ($query->num_rows() > 0) { 
    foreach ($query->result() as $row)
           {
  ?>

Add `alias` to your table names and then use that `alias` in `user_id`. The error is because column `user_id` is present in both tables `fuel_item` and `fuel_unit` — Arif Sher Khan, Sep 27 '19 at 05:56
Possible duplicate of [mysql ...in where clause is ambiguous](https://stackoverflow.com/questions/25678777/). Also see [Where clause is ambiguous](https://stackoverflow.com/questions/31021434/), ['user_id' in where clause is ambiguous problem](https://stackoverflow.com/questions/2788326/), [Column in where clause is ambiguous - What does that mean?](https://stackoverflow.com/questions/6062185/), and [Column 'id' in where clause is ambiguous](https://stackoverflow.com/questions/22632441/). — showdev, Sep 27 '19 at 06:22

Hasta Dhana · Answer 1 · 2019-09-27T05:59:51.507

0

change the where clause from 'user_id' to 'fuel_item.user_id' to prevent ambiguous column :

 <?php 

    $query = $this->db->select('*');
    $query = $this->db->from('fuel_item');
    $query = $this->db->join('fuel_unit', 'fuel_item.unitid = fuel_unit.id');
    $query = $this->db->where('fuel_item.user_id',$_SESSION['user']);
    $query = $this->db->order_by('fuel_item.id','Desc');
    $query = $this->db->get();
    if ($query->num_rows() > 0) { 
    foreach ($query->result() as $row)
           {
  ?>

edited Sep 27 '19 at 05:59

answered Sep 27 '19 at 05:56

Hasta Dhana

4,699
7
17
26

Error Number: 1052 Column 'id' in order clause is ambiguous SELECT * FROM `fuel_item` JOIN `fuel_unit` ON `fuel_item`.`unitid` = `fuel_unit`.`id` WHERE `fuel_item`.`user_id` = '1' ORDER BY `id` DESC Filename: D:/xampp/htdocs/fuel/system/database/DB_driver.php Line Number: 691 – Amit Sep 27 '19 at 05:59
I've updated my answer – Hasta Dhana Sep 27 '19 at 06:00
yes sir but gating some error – Amit Sep 27 '19 at 06:03
(id and user_id) is user two table – Amit Sep 27 '19 at 06:04
what is the error message shows up? – Hasta Dhana Sep 27 '19 at 06:05
error herr: Error Number: 1052 Column 'id' in order clause is ambiguous SELECT * FROM `fuel_item` JOIN `fuel_unit` ON `fuel_item`.`unitid` = `fuel_unit`.`id` WHERE `fuel_item`.`user_id` = '1' ORDER BY `id` DESC Filename: D:/xampp/htdocs/fuel/system/database/DB_driver.php Line Number: 691 – Amit Sep 27 '19 at 06:05
I've also updated the `order_by` codes, have you try it? – Hasta Dhana Sep 27 '19 at 06:05

score 0 · Answer 2 · answered Sep 27 '19 at 06:16

0

Please change the where condition

 $query = $this->db->where('user_id',$_SESSION['user']);

to

$query = $this->db->where('fuel_item.user_id',$_SESSION['user']);

You have to tell the MySQL that user_id exists in which table while using join.

answered Sep 27 '19 at 06:16

lalit mittal

444
3
8

score 0 · Answer 3 · edited Sep 27 '19 at 09:02

<?php 

    $query = $this->db->select('*');  
    $query = $this->db->from('fuel_item fi');
    $query = $this->db->join('fuel_unit fu', 'fi.unitid = fu.id');
    $query = $this->db->where('fi.user_id',$_SESSION['user']);
    $query = $this->db->order_by('fi.id','Desc');
    $query = $this->db->get();
    if ($query->num_rows() > 0) { 
    foreach ($query->result() as $row)
           {
  ?>

score 0 · Accepted Answer · answered Sep 27 '19 at 09:55

It means that both tables in the query have the column user_id. So you need name which table column you want.

For where condition prefixing the reference with either the full table name will fix this error.

$query = $this->db->select('*');
$query = $this->db->from('fuel_item');
$query = $this->db->join('fuel_unit', 'fuel_item.unitid = fuel_unit.id');
$query = $this->db->where('fuel_item.user_id',$_SESSION['user']);
$query = $this->db->order_by('fuel_item.id','Desc');
$query = $this->db->get();

Join two table getting some error in codeigniter php

4 Answers4