+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: how to sort by date field on three(3) tables (/showthread.php?tid=18394)
how to sort by date field on three(3) tables - El Forum - 05-05-2009
[eluser]newbie boy[/eluser]
not sure if this issue goes in this thread, i think its a SQL question more than anything but if anybody can help i would appreciate it.
if i got three(3) tables, each of them with a "date" field.
how do i sort my results on the date field?
is this sort of thing possible with groupby or other method?
how to sort by date field on three(3) tables - El Forum - 05-05-2009
[eluser]Dam1an[/eluser]
Assuming you're asking how do you specify on which tables date field to sort, just put the table name with a dot in front of the column name
eg
Code:
$this->db->order_by('table_1.date', 'asc');how to sort by date field on three(3) tables - El Forum - 05-05-2009
[eluser]überfuzz[/eluser]
Do you get the rows using join or union? You could us column AS namn and sort by namn on all rows. (Sort the rows as Damian showed.)
how to sort by date field on three(3) tables - El Forum - 05-10-2009
[eluser]newbie boy[/eluser]
leftjoin or union....
or other ways ...
badly needed the solution for this...
thanks guys...
how to sort by date field on three(3) tables - El Forum - 05-25-2009
[eluser]newbie boy[/eluser]
all tables have a date field that i must display the results through a descending order...
the query should be like the results came from a single table's date field...
somehow i need to combine all the date field from all the tables then make my query...
this, really i just don't know how...
how to sort by date field on three(3) tables - El Forum - 05-26-2009
[eluser]überfuzz[/eluser]
What do you got now? The thing you describe could be done exactly as we wrote.
how to sort by date field on three(3) tables - El Forum - 10-11-2012
[eluser]Vaibhav Jain[/eluser]
Hello everyone,
Well I also have same issue.
I have three tables :
1.)tbl_sales(temp table)
2.) tbl_items
3.) tbl_inventory
Now what happens when I create join with sale and item tables then it works fine and when i include third column from inventory table then data comes wrongly. Don't know what I'm doing wrong, may be some join problem.
here is the right code :
Code:
public function getData(array $inputs)
{
$this->db->select('name, sum(quantity_purchased) as "Qty Sold", tbl_items.quantity');
$this->db->from('sales_items_temp');
$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
$this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
$this->db->group_by('items.item_id');
$this->db->order_by('name');
return $this->db->get()->result_array();
}and this is my code :
Code:
public function getData(array $inputs)
{
$this->db->select('name, sum(tbl_inventory.trans_inventory) as "reports_quantity_received", sum(quantity_purchased) as "Qty Sold", tbl_items.quantity as "Qty remain"') ;
$this->db->from('sales_items_temp');
$this->db->join('items', 'sales_items_temp.item_id = items.item_id');
$this->db->join('inventory','items.item_id = inventory.trans_items', 'LEFT OUTER');
$this->db->where('trans_inventory >= 0');
$this->db->where('sale_date BETWEEN "'. $inputs['start_date']. '" and "'. $inputs['end_date'].'"');
$this->db->or_where('trans_date BETWEEN "'.$inputs['start_date']. '" and "'.$inputs['end_date'].'"');
$this->db->group_by('items.item_id');
$this->db->order_by('name');
return $this->db->get()->result_array();
}but this is not correct code, don't know what I'm doing wrong.:down:
If anyone know how to solve this issue then please let me know as soon as possible.
It would be great if someone give me a prompt reply..:-)
Thanks in advance..
Regards,
Vaibhav Jain