LIKE query in sql is not working

Question

I have a database filled with values. I want to query and get certain values based on their title. I am using the LIKE query in sql to accomplish this.

For some reason, no results are coming up when I query 'new' in the search bar. The database has an item with the title: New Item

Html:

       <form method="get" action="">
            <input type="text" name="search" id="search" value="Search...">
            <br>
            <br>
            <center>
                <input type="submit" value="Search" name="searchsub" id="searchsub">
            </center>
        </form>

PHP:

        <?php
            if(isset($_GET['search']) && isset($_GET['searchsub']))
                {
                    $search = $_GET['search'];
                    $results = mysqli_query($connection, "SELECT * FROM mi4 WHERE Title LIKE '$search'");
                    echo "<table><tr><td>Title</td><td>Teacher</td><td>Date</td><td>Description</td><td>Submitter</td></tr>";
                    while($row = mysqli_fetch_array($results))
                    {
                        echo "<tr><td>". $row['Title'] ."</td><td>". $row['Teacher'] ."</td><td>". $row['Date'] ."</td><td>". $row['Description'] ."</td><td>". $row['Submitter'] ."</td></tr>";
                    }
                    echo "</table>";
                }
        ?>

Add `or die(mysqli_error($connection))` to `mysqli_query()` see if it yields anything. Plus, make sure you're connected and using the same API. — Funk Forty Niner, Feb 09 '15 at 23:01
"New Item" wont = 'new' you need LIKE '$search%'" You should escape the users input also. — chris85, Feb 09 '15 at 23:05
I added that, it returns no error. Something else must be the problem. — Rumple, Feb 09 '15 at 23:05
Plus, in doing `LIKE '$search'` will need to yield an exact search. You might want to use something like `LIKE '%$search'` or `LIKE '$search%'` or `LIKE '%$search%'` — Funk Forty Niner, Feb 09 '15 at 23:06
You can delete the question then. It has been solved in comments. — Funk Forty Niner, Feb 09 '15 at 23:07
As a side note: you also may want to sanitize those inputs before passing them to the database — Alvaro Montoro, Feb 09 '15 at 23:07
**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. Running SQL code built with outside data is like eating soup made from ingredients found on your doorstep. — Andy Lester, Feb 09 '15 at 23:41

score 1 · Answer 1 · answered Feb 09 '15 at 23:12

Depends on what is the $search string. When exact same string you have in db Title you are passing to the $search it must work. But when you want also to find substrings you have to put percentage char % before and/or after your $search string.

For example

LIKE '%$search%'

Dimitri Acosta · Answer 2 · 2015-02-10T00:19:53.660

First, I wouldn't use a default value in the input, instead you should use a placeholder attribute to make it look like this

<input type="text" name="search" id="search" placeholder="Search...">

And in the query try to use it like this

$results = mysqli_query($connection, "SELECT * FROM mi4 WHERE Title LIKE '%$search%'");

The % symbol is a wildcard so if you don't use it, the query will search for the exact text on the variable, using this symbol the query will search for the text on the variable in the whole string.

Here's a list of some wildcars used in MyQSL SQL Wildcards

LIKE query in sql is not working

2 Answers2