use PHP variable in MySQL REGEXP

Question

I have multiple ids separated by + in one field of a row in a table Like : (123+21654+412+12387)

I need Only EXACT MATCHES, (e.g.: only "123" and not "123 & 12387")

My code is like this:

$var = $value['id'];
$result = mysqli_query($this->dbh, "SELECT id FROM table
      WHERE id REGEXP '[[:<:]]$var[[:>:]]' ");

I have a problem with using a variable in REGEXP.

in case of :

$result = mysqli_query($this->dbh, "Select id FROM table
      WHERE id REGEXP '^$id|[\+]$id' ");

it works, but it does not return only exact matches

What is the exact problem you have with word boundaries? What is your MySQL version? — Wiktor Stribiżew, Sep 28 '18 at 08:41
I Cannot put PHP variable in MySQL query which includes REGEXP — iCode98, Sep 28 '18 at 08:53
Well, `"SELECT id FROM table WHERE id REGEXP '[[:<:]]" . $var . "[[:>:]]' "` should work. — Wiktor Stribiżew, Sep 28 '18 at 08:59
I think your question is a dupe of https://stackoverflow.com/questions/10512452/php-using-a-variable-inside-a-double-quotes — Wiktor Stribiżew, Sep 28 '18 at 09:01
Actually NOT, my problem is about using the variables in "REGEXP", which doesn't follow some common regex rules — iCode98, Sep 28 '18 at 09:05
Your question is *use PHP variable in MySQL REGEXP* and https://stackoverflow.com/questions/10512452/php-using-a-variable-inside-a-double-quotes solves that part. Whatever issue you have later, you have not explained it. — Wiktor Stribiżew, Sep 28 '18 at 09:07
Can you try to put the query into variable, and echo that variable first? Make sure you get the query that you want. — Adlan Arif Zakaria, Sep 28 '18 at 09:51

Rick James · Answer 1 · 2018-10-04T21:16:46.053

PHP tip: "Interpolation" works these ways:

"...$var[...]..." treats that as an array lookup for $var[...].
Without [, $var is assumed to be a scalar.
"...{$var}[...]..." is what you need

This last example has braces {} to tell PHP to evaluate what is inside without being concerned about what follows. More common usage:

$var = 'abc';
// I want to generate "abc123"
$bad  = "$var123";   // looks for variable 'var123'
$good = "{$var}123;  // correctly yields "abc123"

Your second attempt can be fixed thus:

REGEXP '(^|\+)$id(\+|$)'

meaning:

At the beginning or after a +,
Find $id (after interpolating),
And stop with a + or the end of the string.

score -1 · Answer 2 · answered Sep 28 '18 at 08:45

-1

I'll go with :

$sql = "SELECT id FROM table WHERE id='123' OR id LIKE '123+%' OR id LIKE '%+123' OR id LIKE '%+123+%'";

The first condition will apply if you only have the value, the second if the field starts with the value, the third if the field ends with the value and the fourth if the value is in the middle of the field.

answered Sep 28 '18 at 08:45

niiwig

150
4

as I have multiple ids in one field I must use REGEXP! – iCode98 Sep 28 '18 at 08:51

use PHP variable in MySQL REGEXP

2 Answers2