phpmysqlcodeigniterselectsql-like

CodeIgniter SELECT query with LIKE condition on two joined columns returns no rows


I want WHERE data select with $find, but it(WHERE) not work in following query and my output is There is not, how fix it?

$find="hello";
$query = $this->db->query('
SELECT tour_foreign.name, 
       tour_foreign_residence.name_re, 
       tour_foreign_airline.name_airline, 
       tour_foreign.service, 
       tour_foreign.date_go, 
       tour_foreign.date_back, 
       tour_foreign.term 
FROM   tour_foreign 
INNER JOIN tour_foreign_residence 
    ON ( tour_foreign.id = tour_foreign_residence.relation ) 
INNER JOIN tour_foreign_airline 
    ON ( tour_foreign.id = tour_foreign_airline.relation ) 
WHERE  tour_foreign.name LIKE "%' . $find . '%" 
        OR tour_foreign_residence.name_re LIKE "%' . $find . '%"
');

if ($query->num_rows() > 0) {
    foreach ($query->result() as $val) {
        echo $val->name . '<br>';
    }
} else {
    echo 'There is not';
}

Solution

  • You have an extra comma before the WHERE (after tour_foreign_airline.relation).

    Edit: I see you fixed it now, but the JOIN still looks wrong. Try this:

    INNER JOIN tour_foreign_residence
    ON (tour_foreign.id = tour_foreign_residence.relation)
    INNER JOIN tour_foreign_airline
    ON (tour_foreign.id = tour_foreign_airline.relation)