DataMapper 1.6.0 - Printable Version

Code:

$sql = "SELECT  `problems`.*,

                        `members`.`name` as `member_name`,

                        `problem_statuses`.`name` as `status_name`,

                        `owners`.`first_name` as `owner_first_name`,

                        `owners`.`last_name` as `owner_last_name`,

                        `assigned_to`.`first_name` as `assigned_to_first_name`,

                        `assigned_to`.`last_name` as `assigned_to_last_name`

                FROM `problems` as problems

                LEFT JOIN `members` as members

                    ON `members`.`id` = `problems`.`member_id`

                LEFT JOIN `problem_statuses` as problem_statuses

                    ON `problem_statuses`.`id` = `problems`.`status_id`

                LEFT JOIN `users` as owners

                    ON `owners`.`id` = `problems`.`owner_id`

                LEFT JOIN `users` as assigned_to

                    ON `assigned_to`.`id` = `problems`.`assigned_to_id`

                WHERE (`members`.`name` LIKE '%".$sSearch."%' 

                      OR `members`.`sid` LIKE '%".$sSearch."%' 

                      OR `problem_statuses`.`name` LIKE '%".$sSearch."%')

                    AND `problems`.`id` IN (".implode(',',$problem_id).")

                LIMIT 0,10";



$result = mysql_query($sql);

        echo mysql_error();

        while ($m = mysql_fetch_assoc($result))

        {

            echo $m['sid'].' - 

'.$m['name'].' - '.$m['status_name'].' - 

'.$m['owner_first_name'].' '.$m['owner_last_name'].' - 

'.$m['assigned_to_first_name'].' '.$m['assigned_to_last_name'].'<br />';

        }

Code:

$problem = new Problem();



$problem->join_related('member', array('name'));

$problem->join_related('status', array('name')); // guessing here

$problem->join_related('owner', array('first_name', 'last_name'));

$problem->join_related('assigned_to', array('first_name', 'last_name'));

// create special query section

$search = '';

$sSearch = $problem->db->escape_str($sSearch);

foreach(array('members.name', 'members.sid', 'status.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_related_problems($problem_id);

$problem->get();



// verify the query

echo '<pre>' . $problem->db->last_query() . '</pre>';