[eluser]tdktank59[/eluser]
Just finished reading up on the new stuff and now updating a few queries...
heres one that has stumped me thus far. (Even stumped me on the last version too)
Code:
$sql = "SELECT `problems`.`id`,
`members`.`sid`, `members`.`name`,
`problem_statuses`.`name`,
`owner`.`first_name`, `owner`.`last_name`,
`assigned_to`.`first_name`, `assigned_to`.`last_name`
FROM `problems`
LEFT JOIN `members` ON `members`.`id` = `problems`.`member_id`
LEFT JOIN `problem_statuses` ON `problem_statuses`.`id` = `problems`.`status_id`
LEFT JOIN `users` as owner ON `users`.`id` = `problems`.`owner_id`
LEFT JOIN `users` as assigned_to ON `users`.`id` = `problems`.`assigned_to`
WHERE `problems`.`id` IN ".$problem_id."
AND (`members`.`name` LIKE '%".$sSearch."%'
OR `members`.`sid` LIKE '%".$sSearch."%'
OR `problem_statuses`.`name` LIKE '%".$sSearch."%')
ORDER_BY ".$order." LIMIT ".$iDisplayStart.", ".$iDisplayLength;
Heres what I was using (thanks OverZealous for the help on this one too!)
// create special query section
$search = '';
$sSearch = $problem->db->escape_str($sSearch);
foreach(array('`members`.`name`', '`members`.`sid`', '`status_problem_statuses`.`name`') as $col)
{
if(!empty($search)) { $search .= ' OR '; }
$search .= "{$col} LIKE '%{$sSearch}%'";
}
// note: calling directly on the db library, to bypass DataMapper.
$problem->db->where('(' . $search . ')');
$problem->where_in('id',$problem_id);
if ($source == 'inbox')
$problem->where('assigned_to_id',$user_id);
else
$problem->where('owner_id',$user_id);
if (isset($order)) $problem->db->order_by($order);
$problem->limit($iDisplayLength,$iDisplayStart);
$problem->get();
The select statement is not that important but those are the values I am using...
And the above DMZ code is throwing this error From the looks of it, its not doing any of the joins...
Quote:A Database Error Occurred
Error Number: 1054
Unknown column 'members.name' in 'where clause'
SELECT `problems`.*, `assigned_to_users`.`first_name` AS assigned_to_first_name, `assigned_to_users`.`last_name` AS assigned_to_last_name FROM (`problems`) LEFT OUTER JOIN `users` as assigned_to_users ON `assigned_to_users`.`id` = `problems`.`assigned_to_id` WHERE (`members`.`name` LIKE '%%' OR `members`.`sid` LIKE '%%' OR `status_problem_statuses`.`name` LIKE '%%') AND `problems`.`id` IN ( [LIST OF IDS REMOVED] ) AND `problems`.`assigned_to_id` = '2' ORDER BY `problems`.`id` asc LIMIT 10
Ill continue trying to get this to work... However Im pretty stumped in how to do this...