PHP mySQL search not working

Question

<?php
     $username = "root";
     $password = "";
     $hostname = "localhost";

     $db_handle = mysql_connect($hostname, $username, $password) or die ("Could not connect to database");

     $selected= mysql_select_db("login", $db_handle);

     $output='';

     if(isset($_POST['search'])){
         $searchq = $_POST['search'];

         $query= "SELECT * FROM PHP_Item WHERE Name LIKE '%searchq%' OR Description LIKE '%serachq%'" or die ("could not search");
         $result= mysql_query($query);
         $count= mysql_num_rows($result);

         echo $count;

         if($count <1){
             $output = 'there were no search results';
         }else{
             while($row = mysql_fetch_array($query)){
                 $mName = $row['Name'];
                 $price = $row['Price'];
                 $id= $row['ItemID'];

                 $output .= '<div>'.$mName.' '.$price.'</div>';
             }
         }
     }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
<html>
    <head>
        <title>Movie Search-Search for a movie</title>
    </head>
    <body>
        <form action="search.php" method="POST">
              <input type="text" name="search" placeholder="Find movies..."/>
              <input type="submit" value="Search movies"/>
        </form>

            <?php print("$output");?>
    </body>
</html>

Im trying to impliment a search bar on my website where users can enter the name of a movie and it will return movies with the same or a similar name to the user's search.

The database being searched has 3 fields-->> ItemID, Name, Description. I keep getting 0 results with the 'there were no search results' output. Any ideas what the problem is?

in the query try adding the $ before searchq, otherwise it would be considered the string ''searchq" ie - change '%searchq%' to '%$searchq%' — dbinns66, Apr 29 '15 at 17:21
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Apr 29 '15 at 17:34

score 2 · Accepted Answer · edited May 23 '17 at 12:28

There are a few issues with your code.

Firstly, you left out the dollar signs for the variables, which technically-speaking, you would be searching for the "searchq" or "serachq" literal strings. "serachq" being a typo as mentioned below.

'%searchq%' OR Description LIKE '%serachq%'

as per $searchq = $_POST['search'];

Plus, you also made a typo in the word serachq in LIKE '%serachq%'

Rewrite:

'%$searchq%' OR Description LIKE '%$searchq%'

Checking for errors would have spotted those.

Your or die ("could not search"); in your query, that doesn't help. See my note below about adding or die(mysql_error()) to mysql_query().

Then this line:

while($row = mysql_fetch_array($query))

that should be referencing $result in $result= mysql_query($query); and not $query

while($row = mysql_fetch_array($result))

Add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code

Sidenote: Error reporting should only be done in staging, and never production.

Also add or die(mysql_error()) to mysql_query().

You also have what seems to be commented out code in your HTML, and is broken; double-check that in:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> --><!-- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

Which will break you page.
Just change that whole block to <!DOCTYPE html>

Your present code is open to SQL injection. Use prepared statements, or PDO with prepared statements, they're much safer.

Conditional statements options:

This line: if(isset($_POST['search'])) could also be changed to

if( 
    isset($_POST['search']) && 
    !empty($_POST['search']) 
    )

in order to make sure that the input wasn't left empty.

or simply:

if( !empty($_POST['search']) )

Thanks for the edit @sємsєм, but I needed to make an additional edit at the bottom of my answer, but I have kept your edit; thanks again. — Funk Forty Niner, Apr 29 '15 at 17:53
Thank you for the help, I've only just started learning PHP which is why I'm making these silly mistakes. Next time i'll take more care when checking my code for errors. — tinOfBeans, Apr 29 '15 at 17:53

PHP mySQL search not working

1 Answers1