Search field with two options

I have a page where I need to be able to search for invoices. I want to be able to search either by invoicenumber or by customer name. (a customer can have multiple invoices in the database.

In other to reach that I use the following query within my model:

function search_invoices($search)    
{
    $sql =  "SELECT O.order_id
                  , O.order_date
                  , C.name
               FROM offline_orders O
               JOIN customers C ON O.customer_id = C.customer_id
              WHERE (O.order_id LIKE search OR C.name LIKE :search2)";
    
    $stmt = $this->pdo->prepare($sql);          
    $stmt->execute(array(':search' => $search.'%', ':search2' => $search.'%'));
    
    return $stmt->fetchAll();        
}

and in the controller I have

$search         = filter_input(INPUT_GET, 'search', FILTER_FLAG_STRIP_LOW);
$klant_facturen    = $this->administratie->search_invoices($search);

But whatever I search for (order_id or name) neither is returning someting.

and when I run the query in PHPMyAdmin like this:

SELECT O.order_id , O.order_date , C.name FROM offline_orders O JOIN customers C ON O.customer_id = C.customer_id WHERE (O.order_id LIKE '%000001%' OR K.name LIKE '%Donald Boers%')

It is returning the righ results

Missing a : in front of search

Your test query has % surrounding $search, your prepared query only has % at the end.

And double check that you are actually processing the results correctly. Maybe dump the results first just to see if you are getting anything.

Hi ahundiak. Thanks for the reply

I changed the where clause to

WHERE (O.order_id LIKE :search OR C.name LIKE :search2)

and the prepared statement to:

$stmt->execute(array(':search' => '%'.$search.'%', ':search2' => '%'.$search.'%'));

But still no results. I also did a var_dump on $klant_facturen in the controller and that gives me all 5 test records but no results in the output

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.