[eluser]kikz4life[/eluser]
after i used this
Code: $this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status",FALSE);
Code: <h1>A Database Error Occurred</h1>
<p>Error Number: 1064</p><p>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 'FROM (`erp_ar_so`)
LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_' at line 2</p><p>SELECT `so_id` as pkey, `so_no`, DATE_FORMAT(so_date, `'%b` %d, `%Y')`, `erp_e`.`data_display` as type, `erp_b`.`customer_name`, `emp_name`, `net`, `erp_ar_so`.`contact`, `k`.`data_display` as status
FROM (`erp_ar_so`)
LEFT JOIN `erp_maint_customers` erp_b ON `erp_ar_so`.`client_id`=`erp_b`.`cust_id`
LEFT JOIN `erp_maint_customers` erp_c ON `erp_ar_so`.`del_client_id`=`erp_c`.`cust_id`
LEFT JOIN `erp_maint_employee` erp_d ON `erp_ar_so`.`salesman_id`=`erp_d`.`emp_id`
LEFT JOIN `erp_sec_dataset` erp_e ON `erp_ar_so`.`type_id`=`erp_e`.`data_id`
LEFT JOIN `erp_sec_dataset` k ON `erp_ar_so`.`status`=`k`.`data_value` AND k.data_code = "TRAN_STATUS"
WHERE `erp_ar_so`.`is_deleted` = '0'
ORDER BY `so_date` desc
LIMIT 15</p>
with alias it also says internal server error.,. grrR. xD
[eluser]imn.codeartist[/eluser]
paste this code in your model
Code: $this->db->join("maint_customers erp_b","ar_so.client_id=b.cust_id","left");
$this->db->join("maint_customers erp_c","ar_so.del_client_id=c.cust_id","left");
$this->db->join("maint_employee erp_d","ar_so.salesman_id=d.emp_id","left");
$this->db->join("sec_dataset erp_e","ar_so.type_id=e.data_id","left");
$this->db->from("ar_so");
$this->db->where("ar_so.is_deleted","0");
$num = $this->db->count_all_results();
$this->db->join("sec_dataset k","ar_so.status=k.data_value AND k.data_code = 'TRAN_STATUS'","left");
$this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,"%b %d, %Y"), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status",FALSE);
$this->db->order_by($sortname,$sortorder);
$this->db->limit($rp, $start);
$query = $this->db->get("ar_so");
[eluser]Zeeshan Rasool[/eluser]
Quote:Code: $this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status",FALSE);
Where you have given other table names as you are using their alias
i-e b.customer_name e.data_display ??
[eluser]kikz4life[/eluser]
applied it and returns a Parse error so i change the double quotes to single quotes. But still an Internal Server Error. Is there any way to declare date besides date_format? btw: is this a bug in CI?
[eluser]imn.codeartist[/eluser]
there must be something wrong with your join queries as i dont see its CI problem.
print the query and try running in the mysql see if that generated query works.
I have no problem using DATE_FORMAT
here is the test code of me and its giving desired output
Code: $this->db->select("date_format(t.ticket_post_date,'%b %d, %Y'),m.username",FALSE);
$this->db->from("tblticket t");
$this->db->join("tbl_memberinfo m","t.user_id=m.user_id");
$query=$this->db->get();
echo $this->db->last_query();
print_r($query->row());
and the Output is:
stdClass Object (
[date_format(t.ticket_post_date, '%b %d, %Y')] => Jul 07, 2009
[username] => haico )
[eluser]Zeeshan Rasool[/eluser]
You are making a mistake i think in selecting columns line,
Ok use it like this simple query. Also make sure order by variables and limit has a value.
Code: $this->db->join('sec_dataset k','ar_so.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');
$this->db->select("DATE_FORMAT(ar_so.so_date,'%b %d, %Y') as dateFormated");
$this->db->order_by($sortname,$sortorder);
$this->db->limit($rp, $start);
$query = $this->db->get("ar_so");
And then replay
[eluser]kikz4life[/eluser]
@zeshan,
yes, i have used other aliases in one model but in two different function name.
i.e
Model.php
Code: function getDetails($data)
{
$data['ar_so.is_deleted'] = '0';
$this->db->join('maint_customers b','ar_so.client_id=b.cust_id','left');
$this->db->join('maint_customers c','ar_so.del_client_id=c.cust_id','left');
$this->db->join('maint_employee d','ar_so.salesman_id=d.emp_id','left');
$this->db->join('maint_marketsegment e','ar_so.ms_id=e.ms_id','left');
$this->db->join('sec_dataset f','ar_so.type_id=f.data_id','left');
$this->db->join('sec_dataset g','ar_so.currency_id=g.data_id','left');
$this->db->join('sec_dataset h','ar_so.pay_type_id=h.data_id','left');
$this->db->join('sec_dataset i','ar_so.term_id=i.data_id','left');
$this->db->join('sec_dataset j','ar_so.int_term_id=j.data_id','left');
$this->db->join('sec_dataset k','ar_so.status=k.data_value AND k.data_code = "TRAN_STATUS"','left');
$this->db->join('sec_users l','ar_so.prepared_by_id=l.user_id','left');
$this->db->select( "ar_so.*,
b.customer_code, b.customer_name,
c.customer_code as delcustomer_code, c.customer_name as delcustomer_name, c.telephone, c.mobile,
c.fax, c.email,
d.emp_code,d.emp_name,
e.ms_code,e.ms_desc,
f.data_display as order_type,
g.data_display as currency_type,
h.data_display as pay_type,
i.data_display as term_type,
j.data_display as intterm_type,
k.data_display as status_name,
concat_ws(' ', l.first_name,l.last_name) as first_name", false
);
$this->db->where($data);
return $this->db->get('ar_so')->row_array();
}
function getList()
{
$sortname = $this->input->post('sortname');
$sortorder = $this->input->post('sortorder');
$page = $this->input->post('page');
$rp = $this->input->post('rp');
$query = $this->input->post('query');
$qtype = $this->input->post('qtype');
$stype = $this->input->post('s_type');
$otype = $this->input->post('o_type');
$skey = $this->input->post('s_key');
if (!$sortname) $sortname = 'so_id';
if (!$sortorder) $sortorder = 'DESC';
if (!$page) $page = 1;
if (!$rp) $rp = 25;
$start = (($page-1) * $rp);
$this->db->start_cache();
if ($stype)
{
foreach ($stype as $key => $value)
{
if ($skey[$key])
{
if ($stype[$key]=='so_date') $match = convertDate($skey[$key]);
else $match = $skey[$key];
switch ($otype[$key])
{
case "=":
$this->db->where($stype[$key],$match);
break;
case "!=":
$this->db->where($stype[$key]." !=",$match);
break;
case "like":
$this->db->like($stype[$key],$match);
break;
case ">":
$this->db->where($stype[$key]." >",$match);
break;
case "<":
$this->db->where($stype[$key]." <",$match);
break;
}
}
}
}
//if (isset($filter)) $this->db->like($filter);
$this->db->join("maint_customers erp_b","ar_so.client_id=b.cust_id","left");
$this->db->join("maint_customers erp_c","ar_so.del_client_id=c.cust_id","left");
$this->db->join("maint_employee erp_d","ar_so.salesman_id=d.emp_id","left");
$this->db->join("sec_dataset erp_e","ar_so.type_id=e.data_id","left");
$this->db->from("ar_so");
$this->db->where("ar_so.is_deleted","0");
$num = $this->db->count_all_results();
$this->db->join("sec_dataset k","ar_so.status=k.data_value AND k.data_code = 'TRAN_STATUS'","left");
$this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status",FALSE);
$this->db->order_by($sortname,$sortorder);
$this->db->limit($rp, $start);
$query = $this->db->get("ar_so");
//echo $this->db->last_query();
//exit;
$this->db->flush_cache();
$data['db'] = $query;
$data['page'] = $page;
$data['num'] = $num;
return $data;
}
[eluser]Zeeshan Rasool[/eluser]
You are giving alias without declaring table names.
[eluser]kikz4life[/eluser]
what do you mean by giving alias w/out declaring table names? which line? i think i've declare all necessary aliases needed... can you point my error/ mistake?
[eluser]Zeeshan Rasool[/eluser]
Code: SELECT s.id, s.title, count(c.book_id) as total_comments,
c.bbok as avg
FROM sponsor_books s
LEFT JOIN book_comments c on s.id=c.book_id
GROUP BY s.id
ORDER BY avg desc, total_comments desc
limit 25
if you see in above query you will find a condition s.id=c.book_id
In which we are using s and c name that are assigned to tables already like
sponsor_books s AND
book_comments c
but in your query
[quote author="kikz4life" date="1256571079"]@zeshan,
Code: $this->db->join("sec_dataset k","ar_so.status=k.data_value AND k.data_code = 'TRAN_STATUS'","left");
$this->db->select("so_id as pkey, so_no, DATE_FORMAT(so_date,'%b %d, %Y'), e.data_display as type, b.customer_name, emp_name, net, ar_so.contact, k.data_display as status",FALSE);
$this->db->order_by($sortname,$sortorder);
$this->db->limit($rp, $start);
$query = $this->db->get("ar_so");
[/quote]
you are using b.customer_name where b is not assigned to any table or b is unknown. when you use alias then all columns should be with proper alias.
|
