How to check array of strings to match with reference string in php?

Question

I have millions of string data in mysql table and have to cross check with the string one by one with the table, if matches return true or else false. I tried with simple preg_match as below, which is consuming more memory and time.

<?php

        $message = 'Hi xyz your account 123 credited Rs. 456 available balance is 789'; 
        $template = "/Hi .+? your account .+? credited Rs. .+? available balance is .+?/";
        $sql = "SELECT * FROM templates";

       $result = $conn->query($sql);

       $flag = false;

       if ($result->num_rows > 0) {
      // output data of each row
             while ($row = $result->fetch_assoc()) {
                     $template = $row["template"];
                      if (preg_match($template, $message)) {
                                $flag = true;
                                 break;
                       }
                       $flag = false;
               }
              $flag = false;
        }

        return $flag;

And also I tried with php_aho_corasick But no use. Please suggest some good way to deal with the problem. Thanks in advance.

Edit : We have millions of templates and here are the sample templates

$template1 = "/Hi .+? your account .+? credited Rs. .+? available balance is .+?/";
$template2 = "/Hi .+? your account .+? credited Rs. .+? available balance is .+? get more upate on/";
$templateN = "/Hello .+? click the link .+? to get your available balance./";

$message = "Hi xyz your account 123 credited Rs. 456 available balance is 789";

In the $message xyz, 123, 456, and 789 are the dynamic values which will change, now have to cross check with N number of templates which will matches with the message. If we replace .+? in template1 with message then will get exact match, while template2 has additional words and templateN differs completely. So which will be the better way to handle such scenarios.

Maybe a good solution is to try regex in your sql ? – Andrii Sukhoi Aug 14 '20 at 13:31 — Andrii Sukhoi, Aug 14 '20 at 13:31

score 0 · Answer 1 · edited Aug 14 '20 at 14:31

0

I think you need to run this query;

SELECT * FROM `templates` WHERE message LIKE 'Hi % your account % credited Rs. % available balance is %'

All results will match your search without needing to use the "preg_match" command

edited Aug 14 '20 at 14:31

David Buck

3,752
35
31
35

answered Aug 14 '20 at 14:09

Dominic OL Jr.

1
2

Thanks, but we don't know which will be the dynamic value in the message to query directly with like clause. – Sudhir Koimattur Aug 14 '20 at 15:56
@SudhirKoimattur maybe am not getting you, but this `Hi % your account % credited Rs. % available balance is %` has no static values. – Dominic OL Jr. Aug 15 '20 at 05:46

score 0 · Answer 2 · answered Aug 28 '20 at 15:27

0

Try this and let us know the results; Its seems to work fine on my server without taxing my resources. Try it on your huge table

$message = 'Hi xyz your account 123 credited Rs. 456 available balance is 789';

$sql5  = "SELECT template FROM templates ";
$sql5 .= "WHERE '$message' LIKE REPLACE( REPLACE(template,  '.+?', '%'),'/', '') ";
$qry5 = $connect->query($sql5) or trigger_error($connect->error, E_USER_ERROR);
$cnt5 = $qry5->num_rows;
    /* 
    echo $cnt5."<br>";
    //SHOWING RESULTS OF TEMPLATES THAT MATCH the message
    while($inf5 = $qry5->fetch_assoc()){
        $template = $inf5['template'];
        
        echo $template."<br>";
    }
    */

$flag = false;
if($cnt5>0){
    $flag = true;
}

answered Aug 28 '20 at 15:27

Dominic OL Jr.

1
2

Why don't you modify your previous answer? – Toto Aug 30 '20 at 10:50
Hi @Dominic, I tried with this code working fine for few hundreds of records but again as we have millions of records which is consuming time to query. We have millions of messages needs to be cross checked with millions of templates, Is there have any other way like elastic search or where we can store templates in an array or memory or indexed path and cross check with each message without making any query to process each time? – Sudhir Koimattur Sep 02 '20 at 08:20
I would use another table or a session(temporary) to save the last checked message id each time the script is run. Then i call for the value and use it to skip all the messages that have been already checked. This is only useful if you dont need check all messages everytime the script is run – Dominic OL Jr. Sep 04 '20 at 13:36

How to check array of strings to match with reference string in php?

2 Answers2