PHP MySQL bind variable not working with REGEXP_INSTR

Question

MySQL version is 10.1.19-MariaDB. PHP version is 7.0.13.

I have a table called words2:

CREATE TABLE words2 (word varchar(64));
INSERT INTO words2 VALUES ('ABSCESS'), ('ABSCISE'),('ABSCOND'),('ABSENCE');
INSERT INTO words2 VALUES ('ABSINTH'), ('ABSOLVE'), ('ABSTAIN');
INSERT INTO words2 VALUES ('ACCORDS'), ('ACCRUES'), ('ASCENDS');

SELECT * FROM words2 ORDER BY 1;

+---------+
| word    |
+---------+
| ABSCESS |
| ABSCISE |
| ABSCOND |
| ABSENCE |
| ABSINTH |
| ABSOLVE |
| ABSTAIN |
| ACCORDS |
| ACCRUES |
| ASCENDS |
+---------+

Now, I want to run a query that lists words starting with 'ABS' but not containing 'O' or 'D'.

In the MySQL client, I run:

select upper(word) word 
from words2
where lower(word) like lower('abs%')
and regexp_instr(lower(word), '[od]' ) = 0
order by 1;

Which correctly gives:

+---------+
| word    |
+---------+
| ABSCESS |
| ABSCISE |
| ABSENCE |
| ABSINTH |
| ABSTAIN |
+---------+

In PHP, I have the following:

$matchchars = 'abs%';
$ignore = '[od]';

$sql = 'select upper(word) word from words w where lower(word) like ? ';
$sql .= ' and regexp_instr(lower(word), ?) = 0 ';
$sql .= ' order by upper(word)';

$stmt  = $dbh->prepare($sql);
$stmt->bind_param('ss', $matchchars, $ignore);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

$num_rows = $result -> num_rows;
echo 'Num rows: '. $num_rows;

But this returns 0 rows. If I remove the 2nd WHERE condition so that I have:

$matchchars = 'abs%';

$sql = 'select upper(word) word from words w where lower(word) like ? ';
$sql .= ' order by upper(word)';

$stmt  = $dbh->prepare($sql);
$stmt->bind_param('s', $matchchars);
$stmt->execute();
$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

$num_rows = $result -> num_rows;
echo 'Num rows: '. $num_rows;

I get the expected results.

Is there a problem with using a bind variable which contains a regular expression with REGEXP_INSTR?

Answer 1

Solved. There was a bug in my PHP that was slightly different to the snippet above which runs perfectly. I had got the variable name in the bind_param('ss', $matchchars, $ignore); wrong.