How to make MySQL query more efficient using LIKE

Question

I have a script where I'm trying to match the droppable data I retrieved in the form of an ajax $.post request that contains a firstname, lastname, description and grade and match it with that of the same data in my database. In doing so I want to find the user and fetch them out in my query. I'm having trouble making an efficient query using LIKE and am unsure what other component to add to make this happen correctly

if (isset($_POST['data'])){
    $data = $_POST['data'];
    $query = mysql_query("SELECT * FROM `tempusers`
        WHERE `firstname` LIKE '%" . $data . "%'
            OR `lastname` LIKE '%" . $data . "%'
            OR `description` LIKE '%" . $data . "%'
            OR `grade` LIKE '%" . $data . "%'");

    if (! $query){
        echo'Database error: ' . mysql_error();
    }

    while ($row=mysql_fetch_assoc($query)) {
        $description = $row['description'];
        echo $description;
    }

    exit;
}

The problem is that I am not returning any results and instead getting the errors

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntaxandmysql_fetch_assoc() expects parameter 1 to be resource, boolean given.

I welcome any tips that I can implement and wouuld greatly appreciate it

Forget optimizing this code until you learn about and fix the gaping [sql injection hole](http://bobby-tables.com). You can twiddle this to your hearts' content until it runs faster than superman, but your server is STILL going to get destroyed by the first script kiddy who comes along. — Marc B, Nov 08 '12 at 16:12
Have you properly set `$grade = "' OR 1=1 --"`? Then after doing so, you should read up on SQL injection. — lc., Nov 08 '12 at 16:14
You need to make sure that you are selecting on indexed fields. LIKE is very inefficient by nature. — Jay Blanchard, Nov 08 '12 at 16:14
@jay: indexes won't help, since it's `like' %foo%'`. The only way to make it more efficient is to switch to fulltext indexes+queries. — Marc B, Nov 08 '12 at 16:15
The error is not finished, it usually says `...MySQL server version for the right syntax near XXX`. Please post XXX. The query here doesn't seem wrong. (Except for the SQL injection problem.) — Bart Friederichs, Nov 08 '12 at 16:16
why not you give the droppable data an id (`
..`) such that there is no need for a like query, or anything similar. For the error - *look* at the query you're actually generating. — AD7six, Nov 08 '12 at 16:20
What is `$data`? I'm assuming there's an apostrophe in the value? — Michael, Nov 08 '12 at 16:20
Near "Jeremmy Lin ..."%' OR `lastname` LIKE '%'Jeremy Lin' at line 2 @BartFriederichs — Octavius, Nov 08 '12 at 16:24
No apostrophe in the value, why do you say that? $data is the html that is dropped. Look at my JSFIDDLE example @MichaelRushton — Octavius, Nov 08 '12 at 16:27
There is an apostrophe, @Octavius -- before the "J" in "Jeremy". — Michael, Nov 08 '12 at 16:59

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

The query's syntax is valid, that means you have a single quote somewhere in your input data, causing the query to fail with the syntax error.

To solve that you can pass your $data through mysql_real_escape_string() which will escape any quotes, and most importantly prevent SQL Injection.

Even better than escaping would be to use a parameterised query with PDO or MySQLi.

As for optimisation, you can try REGEXP instead of multiple LIKE's. Example here.

edited May 23 '17 at 11:43

Community

1
1

answered Nov 08 '12 at 16:23

MrCode

63,975
10
90
112

1

Right, the Irish name problem -- O'Malley -- may be hitting this OQ. – O. Jones Nov 08 '12 at 16:25

score 1 · Answer 2 · answered Nov 08 '12 at 16:24

You've asked for help optimizing a MySQL query that does not work.

First, you need to get the query working. The usual procedure to do that is to type your query into a MySQL client (like phpmyadmin) and see what happens. You may also find that outputting the constructed query with echo will help you see your SQL syntax error.

Second, queries of the form

  column LIKE '%matchstring%'

simply cannot be optimized. The leading % on the match string means that the MySQL server has to search every row of the column you mentioned to find a match. Indexes don't help this. If you can query like this

 column LIKE 'matchstring%'

you can use indexes to optimize these searches.

Third, you're using OR in your query. The MySQL optimizer doesn't really like OR, and it rescans the table for each one.

You might put this table into the MyISAM access method (if it isn't already) and use FULLTEXT matching. Try this:

  WHERE MATCH (firstname, lastname, description, grade) 
        AGAINST ('matchstring' IN BOOLEAN MODE)

This will do the search you want. You can optimize it with a fulltext index.

Finally, as Marc B pointed out, you must fix your SQL injection problem. Primary schools, never mind secondary schools, are full of people who would love to pwn the grading system. To find out the hazard, look up "little Johnny Droptables" in a search engine.

score 1 · Answer 3 · answered Nov 08 '12 at 16:34

1

Place die; after echo'Database error: ' . mysql_error(); and run your code to check what the database error is.

answered Nov 08 '12 at 16:34

The Real Coder

138
1
7

score 0 · Answer 4 · answered Nov 08 '12 at 16:27

You are not sanitizing user input, this results in getting stray quote characters in your SQL (as proven by your error message). Simple fix can be (escape the string before putting it into the query):

if (isset($_POST['data'])){

    // sanitize user input
    $data = mysql_real_escape_string($_POST['data']);

    $query = mysql_query("SELECT * FROM `tempusers`
        WHERE `firstname` LIKE '%" . $data . "%'
            OR `lastname` LIKE '%" . $data . "%'
            OR `description` LIKE '%" . $data . "%'
            OR `grade` LIKE '%" . $data . "%'");

    if (! $query){
        echo'Database error: ' . mysql_error();
    }

    while ($row=mysql_fetch_assoc($query)) {
        $description = $row['description'];
        echo $description;
    }

    exit;
}

Please read up on SQL injection.

About making it more efficient: indexes won't work on fields that you query with LIKE.

Further, you are discouraged to use the mysql_ function, as they are deprecated. Use mysqli instead.

How to make MySQL query more efficient using LIKE

4 Answers4