CODEWITHSUNDEEP
phpmysqlwhere-clause
Vereonix
Vereonix

Reputation: 1424

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

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.

Upvotes: 2

Views: 25561

Answers (4)

megh_sat
megh_sat

Reputation: 424

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

Upvotes: 0

Maverick
Maverick

Reputation: 945

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

Upvotes: 0

SkaiBoa
SkaiBoa

Reputation: 53

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.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71422

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.

Upvotes: 5

Related Questions