MySQL query to list words containing user submited letters in order

Question

I have this MySQL table containing a list of words:

desc words;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| word    | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

I also have a HTML form with three input fields where the user should enter three letters:

<form action='load.php' method='post'>
    <input type='text' name='first_letter'>
    <input type='text' name='second_letter'>
    <input type='text' name='third_letter'>

    <input type='submit' name='submit'>
</form>

Is it possible to create a MySQL query to fetch words containing the three letters in order of apperance, in the word?

For example if we have the words

adams
damn
mad

... and the user submits the letters "a", "d", "m" it should only give the result

adams

since the first submitted letter is "a", and the second submitted letter comes after "a" and so forth (even if there is other letters in between).

Or is it easier to sort the words using PHP? If so, how? I'm a beginner programmer.

Just to get an idea for the scope, how many words do you expect to have in your DB, how long are the words, and how many letters do you expect a user to submit? — Michael Mior, Dec 19 '12 at 22:06

G-Nugget · Accepted Answer · 2012-12-19T22:23:55.920

5

Well it won't be efficient by any means, but the following should get the job done:

$string = '%' . implode('%', $letters) . '%';
$query = "SELECT word FROM words WHERE word LIKE '$string'";

This will work regardless of if the letters have anything between them, as long as they appear in the correct order. It also allows varying numbers of letters to be specified.

Edit: $letters needs to be built for the query, too. Specific to this example, it can be built like this:

$vars = array('first_letter', 'second_letter', 'third_letter');
foreach($vars as $var){
    if($_POST[$var]){
        $letters[] = $_POST[$var];
    }
}

If the POST variable names were changed to letters[], the process could be simplified to this:

foreach($_POST['letters'] as $letter){
    if($letter){
        $letters[] = $letter;
    }
}

All of the data going into this should also be validated/escaped, but that is outside of the scope of this question.

edited Dec 19 '12 at 22:23

answered Dec 19 '12 at 22:01

G-Nugget

8,666
1
24
31

Thank's, it seem's to do the work. Is it possible to filter the result a bit more to only populate the words starting with the first submitted letter? In my example list only the words starting with "a" – David Dec 19 '12 at 22:12
@David Yes, just remove the first `%` on the `$string` line. – G-Nugget Dec 19 '12 at 22:15
Please excuse my lack of PHP knowledge but how does the `$letters` variable look like? – David Dec 19 '12 at 22:17
@David You'd need to build it. I'm updating my answer now with an example. – G-Nugget Dec 19 '12 at 22:18
2

**Your code is vulnerable to SQL injection attack.** You *really* should be using [prepared statements](http://stackoverflow.com/a/60496/623041), into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of [Bobby Tables](http://stackoverflow.com/q/332365/623041). – eggyal Dec 19 '12 at 22:26
@G-Nugget Thank you very much! Work's like a charm. I really appreciate your help. – David Dec 19 '12 at 22:31

BryanH · Answer 2 · 2012-12-20T23:57:05.237

1

A simple query would do it

$stmt = $pdo->prepare("select word from words where word like '%?%?%?%'");

$stmt->bind_param(
  'sss'
  , $first_letter
  , $second_letter
  , $third_letter
);

$stmt->execute();

Update: Using parameters instead

edited Dec 20 '12 at 23:57

answered Dec 19 '12 at 22:03

BryanH

5,826
3
34
47

You can't have parameters inside a string literal. To use parameters, your pattern would need to be the result of an expression that performs string concatenation within SQL, such as `WHERE word LIKE CONCAT_WS('%', '', :A, :B, :C, '')`. – eggyal Dec 19 '12 at 22:25

score 0 · Answer 3 · answered Dec 19 '12 at 22:03

0

It's not clear how the user is "submitting the letters 'a', 'd', and 'm'" but the LIKE clause is the most common for this sort of pattern matching.

Try:

SELECT id, word
FROM words
WHERE word LIKE '%a%d%m%'

answered Dec 19 '12 at 22:03

Bacon Bits

30,782
5
59
66

What the OP is looking for seems to be `LIKE '%a%d%m%'`. – Michael Mior Dec 19 '12 at 22:05

MySQL query to list words containing user submited letters in order

3 Answers3