MySql LIKE query in a form

Question

So, I have this query:

FROM test.cliente, test.contratto
WHERE test.contratto.Codice_Cliente = test.cliente.Codice_Cliente
AND test.cliente.Denominazione = :name;

But I'm trying to work with something like this:

FROM test.cliente, test.contratto
WHERE test.contratto.Codice_Cliente = test.cliente.Codice_Cliente<
AND test.cliente.Denominazione LIKE "%:name%";

The reason I want to use this query is because the user puts a name into the html form, but I don't want him to type the same piece of data that I have on the database, because there's no way that what he'll type will be the same piece of data as it's written in the database.

The code goes on like this:

$name = $_POST['Denominazione'];
            $statement = $connection->prepare($sql);
            $statement->bindParam(':name', $name, PDO::PARAM_STR);
            $statement->execute();
        $result = $statement->fetchAll();

I also tried to type

$name = '%'.$_POST['Codice_Cliente'].'%';

but it didn't find me anything.

Your second approach is right (see [PDO prepared statement with LIKE](https://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement)). If it didn't find anything, then it didn't match any rows. Are you certain your query should match something? — El_Vanja, Feb 04 '21 at 16:14
THERE IS THE SELECT missing in your query?? or did you post only a part of the query. — Alexander Dobernig, Feb 05 '21 at 03:26
I don't know why but [...] are missing, those are only portions of my query — gigiopasticcio, Feb 05 '21 at 09:29

score 0 · Answer 1 · answered Feb 04 '21 at 16:16

0

PDO prepared statements DO NOT allow % in SQL statements.

You have to use "FROM test.cliente, test.contratto
WHERE test.contratto.Codice_Cliente = test.cliente.Codice_Cliente
AND test.cliente.Denominazione LIKE :name";

And put the "%name%" inside the execute.

Please also see:

How do I create a PDO parameterized query with a LIKE statement?

PHP - Using PDO with IN clause array

answered Feb 04 '21 at 16:16

Alexander Dobernig

753
6
21

But how does that apply to my case? I'm wondering where I should put the '%' in $name, as before the execute() function I have bindParam(':name', $name, PDO::PARAM_STR); – gigiopasticcio Feb 04 '21 at 16:21
Is there a compelling reason to use bindParam? IMO it is much easier to pass a value to execute than to pass a reference in bindParam. wysiwyg with execute. – Tim Morton Feb 04 '21 at 16:43
I used it because that was the only way I knew. Could you provide a solution? – gigiopasticcio Feb 04 '21 at 16:45
Your "also tried" version is the correct one. If you do not get results then something else is wrong. Please try your query with PHPMyAdmin to see if the query is the problem. – Alexander Dobernig Feb 05 '21 at 03:21
I tried and the query is not the problem. If I type the exact name as it's in the database it shows me the result of the query. But if I cut, let's say, 2 letters from the end it doesn't show me the result. – gigiopasticcio Feb 05 '21 at 13:37
Does the query work with phpmyadmin with the incomplete name? – Alexander Dobernig Feb 05 '21 at 14:19
Please post an update with your complete current query and current prepare and execute code . – Alexander Dobernig Feb 05 '21 at 14:20

MySql LIKE query in a form

1 Answers1