Use PHP variables in MySQL Query with LIKE '%%'

Question

I'm building a search function for my site, however the MySQl query won't read the PHP variables, and I don't mean errors, it just seems to think they're NULL.

My current code is:

$conn = mysql_connect('localhost', 'root', '');
        mysql_select_db('library', $conn);

        $sql = "SELECT * FROM Books";

        if($_POST['find']!="")
            {
                if($_POST['field'] == "Books")
                    {
                    $sql = "SELECT * 
                            FROM Books
                            JOIN bookauthor ON books.BookID = bookauthor.BookID
                            JOIN authors ON bookauthor.AuthorID = authors.AuthorID
                            WHERE books.BookName LIKE  '%''".($_POST['find'])."''%'
                            GROUP BY books.BookName                                
                            ORDER BY authors.AuthorID";
                    }
                else if ($_POST['field'] == "Authors")
                    {
                    $sql = "SELECT * 
                            FROM Books
                            JOIN bookauthor ON books.BookID = bookauthor.BookID
                            JOIN authors ON bookauthor.AuthorID = authors.AuthorID
                            WHERE authors.Forename LIKE  '%J.%'
                            AND authors.Surname LIKE  '%%'
                            GROUP BY books.BookName                                
                            ORDER BY authors.AuthorID";
                     }
            }

        $result = mysql_query($sql, $conn) or die("Can't run query");
        $loopnumber = 1;                            
        if (mysql_num_rows($result) ==0 ){echo "No Results have been found";}

The POST variable does contain data as I've tested by echo'ing it, however my site just gives the "No Results have been found" message meaning the query retuned no results. Even if I pass the POST into a normal variable I get the same results.

However if I remove the "LIKE '%%'" and have it look for and exact match from typing in the search on the site it works fine.

Edit: Hmmmm, just made it so I pass the POST into a variable like so..

$searchf = "%".$_POST['find']."%";

and having that variable in the WHERE LIKE makes it work, now I'm just curious as to why it doesn't work the other way.

I seems to love quotation marks too much, and should go to bed.

`die($sql);` will always be a good way to see if you have well-formatted SQL because you can then copy+paste it into PHPMyAdmin or whatever RDBMS you have installed and the RDBMS will tell you what's wrong. — MonkeyZeus, Dec 03 '13 at 00:32
Save ya-self the trouble of pointless concatenation like this and use prepared statements and non-deprecated tools like pdo and mysqli. — Jonast92, Dec 03 '13 at 00:41
Does this help : http://stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — crafter, Dec 03 '13 at 00:43
Someone needs to point out that this kind of code should never be used on an actual public website. Anyone using the website could inject SQL and run any query they want. At the very least you would have to use mysql_real_escape_string() on all of the query data that comes from user input. But the mysql_ functions are deprecated anyway (try PDO, or at least mysqli_). — orrd, Dec 03 '13 at 00:51

Mike Brant · Accepted Answer · 2013-12-03T00:33:17.067

5

Well first of all, I am guessing you are getting a MySQL syntax error when trying to execute that first query. This line:

WHERE books.BookName LIKE  '%''".($_POST['find'])."''%'

Should be

WHERE books.BookName LIKE  '%".$_POST['find']."%'

Because right now you are getting

WHERE books.BookName LIKE  '%''ABC''%'

when you should be getting

WHERE books.BookName LIKE  '%ABC%'

I don't admit to understand what you are doing with your second query, which just hard codes and has %% as one of the search criteria, which is, in essence meaningless.

edited Dec 03 '13 at 00:33

answered Dec 03 '13 at 00:27

Mike Brant

70,514
10
99
103

The second is to search a different table, the form for the search has a drop down, so can choose to search for Authors, or Books, and display Books accordingly. – Vereonix Dec 03 '13 at 00:30
Also thanks, you're right, I went overboard with the "' works fine now with just $_POST. – Vereonix Dec 03 '13 at 00:32
1

+1 for you but I just wanted to supplement with additional PHP concatenation methods. `$sql = "SELECT * FROM Books WHERE BookName LIKE '%".$_POST['find']."%'";` and `$sql = "SELECT * FROM Books WHERE BookName LIKE '%$_POST[find]%'";` – MonkeyZeus Dec 03 '13 at 00:35
I don't quite follow the second one, without the fullstops to concatenate it just throws a big wobbly at me, I'm very tied and I'm probably missing your point. – Vereonix Dec 03 '13 at 00:44
@Tom MonkeyZeus was simply suggesting that since you have double quotes around your entire SQL statement string, you don't absolutely need to use concatenation operator, PHP can directly interpolate the variable into the string as shown in his second example. Now while I personally find this to be bad practice (such usage doesn't alwats show up well in code editors), many developers use this approach extensively. – Mike Brant Dec 03 '13 at 00:49
No sweat Tom feel free to look it over and test it out after you get some rest. Mike Brant explained the code I posted perfectly so I have nothing else to say except that the following code won't work =) `$_POST['find'] = array('zero\'th position', 'first positions'); $sql = "SELECT * FROM Books WHERE BookName LIKE '%$_POST[find][0]%'"; die($sql);` – MonkeyZeus Dec 03 '13 at 01:16
Also I do not practice directly placing variables inside of double-quoted strings because it personally makes it horrid to read especially when you've trained yourself to look for syntax highlighting clues. – MonkeyZeus Dec 03 '13 at 01:19

score 0 · Answer 2 · answered Dec 03 '13 at 00:35

0

Its in your LIKE expression. If in $_POST['find'] the value is LOTR the query would be WHERE books.BookName LIKE '%''LOTR''%' and the resault would be empty. Just remove the double ' and it should be work.

answered Dec 03 '13 at 00:35

SkaiBoa

53
7

score 0 · Answer 3 · answered Dec 03 '13 at 00:39

Try this way:

  $sql = "SELECT * 
          FROM Books
          JOIN bookauthor ON books.BookID = bookauthor.BookID
          JOIN authors ON bookauthor.AuthorID = authors.AuthorID
          WHERE books.BookName LIKE  '%".$_POST['find']."%'
          GROUP BY books.BookName                                
          ORDER BY authors.AuthorID";

It should be work

score 0 · Answer 4 · answered May 14 '19 at 07:48

use this, worked for me:

$query_casenumber = "SELECT * FROM pv_metrics WHERE casenumber='$keyword' OR age='$keyword' OR product='$keyword' OR eventpreferredterm='$keyword' OR  patientoutcome='$keyword' OR  eventsystemclassSOC='$keyword' OR   asdeterminedlistedness='$keyword' OR narrative LIKE '%".$_POST['keyword']."%' ";

Use PHP variables in MySQL Query with LIKE '%%'

4 Answers4