Select with LEFT JOIN and LIKE

Question

Hello, I have created a search field, and I need to return the results of tb_orders based on the client’s name. However, only the client’s id is saved in this table, and the search is done by name. The client’s data is in the tb_clients. I looked for some information on the forums, but I couldn’t resolve.

$get_ordens = $pdo->prepare('SELECT a.*, b.nome FROM tb_ordens as a LEFT JOIN tb_clientes as b on a.cliente = b.nome WHERE fechada = 0 AND (cliente LIKE :busca) ORDER BY data');

$get_ordens->execute(array(
     ':busca' => '%'.$busca.'%'
));

Answer 1

I believe you are comparing the order table account id with the client table name. you need to do the JOIN by linking the proper relations of the tables and in the WHERE, you make the filter by the name of the client that is saved in the client table.

I made a few edits assuming it’s so the structure of your database:

$sql = '
SELECT o.*, c.nome FROM tb_ordens as o 
LEFT JOIN tb_clientes as c on o.cliente = c.id 
WHERE o.fechada = 0 AND (c.nome LIKE :busca) 
ORDER BY o.data';

$get_ordens = $pdo->prepare($sql);
  

Answer 2

The solution that André Walter passed is the most ideal. If you want to take an excellent SQL course I leave below the link of my course portal www.fpw.com.br/ead There they have the most complete SQL course that you find on the internet