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
– MonkeyZeus, Commented Dec 3, 2013 at 0:32
Save ya-self the trouble of pointless concatenation like this and use prepared statements and non-deprecated tools like pdo and mysqli. — Jonast92
– Jonast92, Commented Dec 3, 2013 at 0:41
Does this help : stackoverflow.com/questions/3683746/escaping-mysql-wild-cards — crafter
– crafter, Commented Dec 3, 2013 at 0: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
– orrd, Commented Dec 3, 2013 at 0:51

Mike Brant · Accepted Answer · 2013-12-03 00:33:17Z

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 3, 2013 at 0:33

answered Dec 3, 2013 at 0:27

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Vereonix Over a year ago

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 Over a year ago

Also thanks, you're right, I went overboard with the "' works fine now with just $_POST.

MonkeyZeus Over a year ago

+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]%'";

Vereonix Over a year ago

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.

Mike Brant Over a year ago

@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.

|

SkaiBoa · Accepted Answer · 2013-12-03 00:35:06Z

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 3, 2013 at 0:35

SkaiBoa

537 bronze badges

Comments

Maverick · Accepted Answer · 2013-12-03 00:39:04Z

0

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

answered Dec 3, 2013 at 0:39

Maverick

9459 silver badges25 bronze badges

Comments

satyayy_ · Accepted Answer · 2019-05-14 07:48:36Z

0

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']."%' ";

answered May 14, 2019 at 7:48

satyayy_

4244 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related