PHP retrieve MySQL result based on nth letter(s)

Question

I am trying to retrieve some results from the database based on some letters known.

Here is my PHP script:

$sql = "SELECT * FROM lexem WHERE charLength = '$total'";
$doh = "__a_d";
$sql .= " AND formUtf8General LIKE '$doh'";

The length, letters, letters number and letters position are variable. My script only returns results that contain some or one of my letters. How can I improve it?

Are you looking for answers that have a and d in a certain position? Are you looking for answers that have both a and d? Are the underscores valid search items? — Jay Blanchard, Oct 01 '15 at 17:21
I am looking for answers that have a and d in a certain position. — Sergiu, Oct 01 '15 at 17:49

score 1 · Accepted Answer · edited May 23 '17 at 12:29

I hope that i have understood correctly. I really want you to try the code below. It uses an array containing both letters and their position in the SQL parameter.

$filter_string = '';
$offset = 0;
$total = 10;    //or any other number
$escaped_characters = array('_', '%');  //Any other MySQL special characters
$filters = array(    
    3=>'a', 5=>'d', 9=>'%', 11=>'r', 0=>'n', 2=>'k', 1=>'w', -1=>'t',
);
ksort($filters);

foreach($filters as $key => $value) {
    if ($key < 1 || strlen($value) != 1) {
        continue;
    }
    for($i = 1; $i < ($key - $offset); $i++) {
        $filter_string .= '_';
    }
    if (in_array($value, $escaped_characters)) {
        $filter_string .= '\\'.$value;
    } else {
        $filter_string .= $value;
    }
    $offset = $key;
}

//Code that escapes parameters in query

$sql = "SELECT * FROM lexem WHERE charLength = '$total' AND formUtf8General LIKE '$filter_string%';";

The $filters array is the one that has the positions of the characters in a key => value format (one character each time). We need to sort it by key so the offset variable will take the correct values each time in the foreach loop. In case you need characters that are also MySQL special characters then $escaped_characters array is useful.

Important: You have to escape all query parameters before you run queries like that because of security reasons (SQL Injection).

Additional info: How can I prevent SQL injection in PHP?

Yes, this worked! Thanks a lot! – Sergiu Oct 03 '15 at 08:42 — Sergiu, Oct 03 '15 at 08:42

PHP retrieve MySQL result based on nth letter(s)

1 Answers1