CODEWITHSUNDEEP
phpmysqlregex
sotirios
sotirios

Reputation: 165

Escape square brackets while use REGEXP and IMPLODE

I have an array with first names and a column on a mysql table with full names (first name is some times in square brackets). How is the right mysql statement which compares the elements of the array and the elements of the column and returns the full names only if the first name is in the array and is in square brackets too? 

   id      |      full_names

-----------------------------------
    1       |   [John] Mclay
    2       |   [John] Rossi
    3       |   Will John
    4       |   [Brian] Cosby
    5       |   Brian O'neal

$names_array=array("[John]" ,"[Brian]");

sql_query="SELECT full_names FROM myTable
              WHERE full_names REGEXP '^(".implode("|", $names_array).")[[:>:]]'";

As a result I would like to have only the first, the second and the fourth full name. My problem is because of the square brackets. Each name in the array has 2 square brackets and for each name I must use \\ two times to escape the special character. But how can I write it in combination with implode? Thank you in advance!

Upvotes: 2

Views: 1072

Answers (2)

Mariano
Mariano

Reputation: 6521

But how can I write it in combination with implode?

will give me a string like this \[John\]\|\[Brian\]. But this is not what I want.

That is expected behaviour, because "|" is a regex metacharacter. So escape the regex metacharacters in $names_array first:

// Loop the array and escape each item with preg_quote
foreach($names_array as $index => $name) {
    $names_array[$index] = preg_quote($name);
}

However, in order to pass it as a string to MySQL you also need to escape backslashes and single quotes. We can use str_replace here:

// Escape backslashes and single quotes for SQL
$names_array = str_replace( array("\\","'"), array("\\\\","\\'"), $names_array);

Note: this should be done with mysqli_real_escape_string. This is just for the scope of this question.

And now, we can finally add the pipes for alternation.

You also have one more thing to correct. You're using the POSIX word bounday [[:>:]], which matches at a position that is preceded by a word character but not followed by a word character. However, a "]" is not a word character. So it won't match your example.

If you want to check if there's a space "" after the name, simply put it in your pattern:

$sql_query="SELECT full_names FROM myTable
              WHERE full_names REGEXP '^(".implode("|", $names_array).") '";
                                                                        ^
                                                                 (here) ^

Code:

$names_array = array("[John]" ,"[Brian]");

// Loop the array and escape each item with preg_quote
foreach($names_array as $index => $name) {
    $names_array[$index] = preg_quote($name);
}

// Escape backslashes and single quotes for SQL
$names_array = str_replace( array("\\","'"), array("\\\\","\\'"), $names_array);

// Now you can add "|"s that won't be escaped
$sql_query="SELECT full_names FROM myTable
              WHERE full_names REGEXP '^(".implode("|", $names_array).") '";

Run this code here

Value of $sql_query:

SELECT full_names FROM myTable
              WHERE full_names REGEXP '^(\\[John\\]|\\[Brian\\]) '

MySQL query result:

[John] Mclay
[John] Rossi
[Brian] Cosby

Test in SQL Fiddle

Upvotes: 2

gyaani_guy
gyaani_guy

Reputation: 3209

Does modifying the the names_array with something like array_map before sending it to implode help?

Untested Example:

$names_for_regex = array_map(function($name){
     return preg_quote($name) ; // the next statement might be more suitable
     return '('.preg_quote($name).')' ;
}, $names);

Upvotes: 0

Related Questions