Welcome Guest, Not a member yet? Register   Sign In
[SOLVED] Joining Information from two database using Active Record
#1

[eluser]tkaw220[/eluser]
I need assistance on below issue:

Said I have 2 table, one named 'orders' storing order no, currency, shipping and billing info, purchased date ...

Another table named 'order_items' storing order no, and items purchased with same order_no.

Below is my code:

function get_order($order_no)
{
$this->db->select('order_no, item, qty, currency, price, subtotal');
$this->db->from('order_items');
$this->db->where('order_no', $order_no);
$this->db->join('orders', "orders.order_no = $order_no");
$query = $this->db->get();
}

My above code return error: Column 'order_no' in field list is ambiguous

What is the proper code to join these two tables?
#2

[eluser]CroNiX[/eluser]
Quote:My above code return error: Column ‘order_no’ in field list is ambiguous
That usually means you have the same field name in both tables, and you are doing a join so it doesn't know which field from which table you are referring to. If you are only using 1 table in a query, it is implied that you want the data from that table so you don't need to be explicit.

In your selects and wheres, change them to "table.field" instead of just "field".
your join would probably look like:
Code:
$this->db->join('orders', 'orders.order_no = order_items.order_no');

I usually use multiple selects if I am doing a join (one for each table)... If you have fields named the same in both tables you will also need to select them AS something else or there will be an error because you will get two columns returned with the same name, which can't happen.
Code:
$this->db->select('order_items.order_no AS order_no, order_items.item AS item, order_items.qty AS qty');//get order_no, item and qty from order_items
$this->db->select('orders.subtotal AS order_subtotal');  //get the subtotal from orders

Now your result will have a row with:
Code:
$row->order_no; //from order_items
$row->item;     //from order_items
$row->qty;      //from order_items
$row->order_subtotal; //from orders
#3

[eluser]tkaw220[/eluser]
Hi CroNiX,

Very useful information. Thanks.
#4

[eluser]anyamanggar[/eluser]
i have sam problem here

Code:
function cariinfo($keyword) {
$this->db->select('title');
$this->db->from('info');
$this->db->join('berita', 'info.id = berita.id');
$this->db->like('info.title', $keyword);
return $this->db->get()->result();
}


[quote]
Error Number: 1052

Column 'title' in field list is ambiguous

SELECT `title` FROM (`info`) JOIN `berita` ON `info`.`id` = `berita`.`id` WHERE `info`.`title` LIKE '




Theme © iAndrew 2016 - Forum software by © MyBB