How to use LIKE and prepared statements at the same time?

Question

I am creating a bot and the user can enter name of a movie and results should show up by searching inside a SQL table and find the right movies name so I used prepared statements in PDO but I want to when a user type the incomplete name of a movie it shows up so I want it to be case insensitive and also find matches. I am using mysql dirver.

before this the SQL query was like this

also noting this is a string inside a PHP script

"SELECT * FROM movies WHERE name = :name LIMIT 5"

but then I thought to use the way that I explained to you above and I know it isn't case insensitive.

"SELECT * FROM movies WHERE name LIKE :name" . "'%' LIMIT 5"

but I don't think this will work properly and fine so how should I write this query to work as I explained at the beginning?

Should use different keywords like REGEXP ?

Tag your question with the database you are using. I removed "regex" because it is not appropriate to the question. — Gordon Linoff, Apr 07 '21 at 12:33
The question is not about `regexp`. It is about `like`. If it is going to be closed as a duplicate, it should be an appropriate duplicate. — Gordon Linoff, Apr 07 '21 at 12:42
What SQL statement do you want to achieve at the end, pls, show us a sample. — biesior, Apr 07 '21 at 12:45
This question has been reopened under the formal pretext but should be closed as a duplicate — Your Common Sense, Apr 07 '21 at 14:34

score 2 · Answer 1 · answered Apr 07 '21 at 12:34

2

Let me assume that you are using MySQL (based on the regexp reference).

You can then use:

WHERE name LIKE CONCAT(:name, '%')

In other databases you can use the standard string concatenation operator, ||.

Note: This is often handled at the application level, so :name is given the '%' in the application. Then your original code works.

A similar approach would work for a regular expression, but the logic would be:

WHERE name LIKE CONCAT('^', :name)

answered Apr 07 '21 at 12:34

Gordon Linoff

1,242,037
58
646
786

Not sure about PHP, but I typically would use `WHERE name LIKE ?`, and the bind the name concatenated with `%` from the application programming language. Your version might work too, but I would avoid it. (Not my downvote, by the way...CommonSense is the likely culprit). – Tim Biegeleisen Apr 07 '21 at 12:38
@TimBiegeleisen . . . (1) That is part of the answer. (2) Named parameters are just fine, often better than unnamed placeholders. – Gordon Linoff Apr 07 '21 at 12:41
how can I make it insensitive? – Pedi Apr 07 '21 at 12:43
@GordonLinoff That's not what I'm talking about at all, let me write it another way: `WHERE name LIKE :name` ... in some cases, you might be required to bind the `Gordon%` string value directly to a placeholder. – Tim Biegeleisen Apr 07 '21 at 12:44
@TimBiegeleisen . . . " This is often handled at the application level, so :name is given the '%' in the application. " – Gordon Linoff Apr 07 '21 at 12:46

How to use LIKE and prepared statements at the same time?

1 Answers1