PHP way to execute SQL LIKE matching without a database query?

Question

I want to match an input string to my PHP page the same way a match done by the LIKE command in SQL (MySQL) for consistency of other searches. Since (I have seen but don't comprehend) some of the PHP syntax includes SQL commands I am wondering if this is possible?

The reason for this is I am now implementing a search of a keyword versus fields in the DB that are stored in a serialized array, which I have to unserialize in PHP and search depending on the structure of the array. I can't query against the table, just need the matching ability of the query. Otherwise I need to find an alternate matching routine, which won't be consistent. I can't go back and re-structure the DB since this wasn't anticipated wayyy back in the spec. Yes, I need an ugly hack, but am looking for the most elegant.

If it's not possible I could use any recommendation of matching user typed text as a keyword against stored text.

EDIT (for clarification): my main problem is I don't have a thorough grasp on how the LIKE command works (just copying the code) and as the keyword implies some degree of vagueness, I would like that vagueness preserved if I switch to a regular expression. I am better with regex's just not so good with like. My query is "LIKE 'matchme%'"

Answer 1

I'd think you'd need preg_match but that's not exactly the same behavior as a LIKE.

<?php // The "i" after the pattern delimiter indicates a case-insensitive search 
if (preg_match("/php/i", "PHP is the web scripting language of choice.")) {
    echo "A match was found."; 
} else {
    echo "A match was not found."; } 
?>

Answer 2

Do you mean you want to be able to check if the input string is LIKE var% ?

You could use strpos(haystack, needle) to match %var%.

if( strpos($source, "var") == 0 ) echo "matches var%";
if( strlen($source) - (strpos($source, "var")) == strlen("var") ) echo "matches %var";

That is pretty ugly. And actually probably not the most elegant.

Answer 3

Update

Based on tomalak's comment and OIS's brilliant idea to use preg_grep, this might be something more along the lines of a final solution for you.

<?php

function convertLikeToRegex( $command )
{
    return "/^" . str_replace( '%', '(.*?)', preg_quote( $command ) ) .  "$/s";
}

function selectLikeMatches( $haystack, $needle )
{
    return preg_grep( convertLikeToRegex( $needle ), $haystack );
}

$likeClauses = array(
    '%foo'
    ,'foo%'
    ,'%foo%'
);

$testInput = array(
    'foobar'
    ,'barfoo'
    ,'barfoobaz'
);

foreach ( $likeClauses as $clause )
{
    echo "Testing $clause:";
    echo '<pre>';
    print_r( selectLikeMatches( $testInput, $clause ) );
    echo '</pre>';
}

Original Post Below

Is this along the lines of what you're after?

<?php

function convertLikeToRegex( $command )
{
    return "/^" . str_replace( '%', '(.*?)', $command ) .  "$/s";
}

$likeClauses = array(
    '%foo'
    ,'foo%'
    ,'%foo%'
);

$testInput = array(
    'foobar'
    ,'barfoo'
    ,'barfoobaz'
);

foreach ( $testInput as $test )
{
    foreach ( $likeClauses as $clause )
    {
     echo "Testing '$test' against like('$clause'): ";
     if ( preg_match( convertLikeToRegex( $clause ), $test ) )
     {
      echo 'Matched!';
     } else {
      echo 'Not Matched!';
     }
     echo '<br>';
    }
    echo '<hr>';
}

Answer 4

What you need is preg_grep actually.

$arr = array("tstet", "duh", "str");
$res = preg_grep("#st#i", $arr); //i for case insensitive
var_dump($res);

results in

array(2) {
  [0]=>
  string(5) "tstet"
  [2]=>
  string(3) "str"
}

edit:

the user supplies the text, I add the wildcards behind the scenes. I do use one %. LIKE 'text%'

here is how you specify it in regex

"#st#i"  regex is the same as in sql "%st%"
"#^st#i" regex is the same as in sql "st%"
"#st$#i" regex is the same as in sql "%st"

Also, remember to use preg_quote on any text you get from a third party. $regex = "#" . preg_quote($text) . "#i"; $res = preg_grep($regex, $arr);

Answer 5

Depending on how your strings are formatted, storing your array in a single string with delimiters can make your queries much faster with Regex. Unless you use array for other purposes such as index tracking.

Let's say your array is {"foo", "bar"}; and pipe character is guaranteed not to be in the list. And your like query is "%r"

In pseudo code (I don't know PHP):

str = "|foo|bar|"
regexquery = "|(" + replace(likequery, "%", ".*") + ")|" 
match = regex_match(str, regexquery)