CODEWITHSUNDEEP
mysqlsqldatabasejoinleft-join
AymDev
AymDev

Reputation: 7554

PHP SQL - left join + where + or

I'm improving my search bars in a project's back-office.
I want to make a research in the protfolio table, joining the categorytable to include the category's names in the available keywords. Seems simple, but the only message I'll get is :

Warning: PDOStatement::execute(): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'search_0' in 'where clause' in [...]

I am creating the SQL query dynamically before executing it, like this :

$keywords = explode(" ", $_POST['search']);
$query = "SELECT * FROM portfolio LEFT JOIN category
          ON portfolio.portfolio_category_id = category.category_id
          WHERE ";

$query_array = array();
for ($i = 0; $i < count($keywords); $i += 1) {
    $query .= "portfolio.portfolio_title LIKE :search_" . $i;
    $query .= " OR category.category_name LIKE search_" . $i;

    if ($i != (count($keywords) - 1)) {
        $query .= " OR ";
    }
    $query_array['search_' . $i] = "%" . $keywords[$i] . "%";
}

$list_portf = $bdd->prepare($query);
$list_portf->execute($query_array);

I'm not a crack in SQL I don't really understand where is the error (I didn't find an answer in the question I found). Thanks.

Upvotes: 1

Views: 75

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

seems you missed colon : before your second ref to search_

   " OR category.category_name LIKE :search_" . $i;

Upvotes: 1

Related Questions