PHP Search code is displaying all items in table

[Michael_93]

Hi All,

I am trying to create some search functionaltiy for my website, but for some reason when I search any word, even if its not in my database it will display all the results from a particular column.

Here is my code

<?php

 error_reporting( ~E_DEPRECATED & ~E_NOTICE );

 define('DBHOST', 'localhost');
 define('DBUSER', 'root');
 define('DBPASS', 'mypass123');
 define('DBNAME', 'topgrub');

 $conn = mysqli_connect(DBHOST,DBUSER,DBPASS);
 $dbcon = mysqli_select_db($conn,DBNAME);

 if ( !$conn ) {
  die("Connection failed : " . mysqli_error());
 }

 if ( !$dbcon ) {
  die("Database Connection failed : " . mysqli_error());
 }
?>

<?php
if(isset($_POST['search'])){
    $q = $_POST['q'];
    $query = mysqli_query($conn,"SELECT * FROM `products` WHERE `name` LIKE '%$qname%'");

    $count = mysqli_num_rows($query);
    if($count == "0"){
        $output = '<h2>No result found!</h2>';
    }else{
        while($row = mysqli_fetch_array($query)){
        $s = $row['name']; 
                $output .= '<h2>'.$s.'</h2><br>';
            }
        }
    }
?>

Any ideas wheer I have gone wrong?

Thanks

[gentlemedia]

Off topic, but why not reply first on the previous threads of yours where you got several answers, before you start a new thread?

[Michael_93]

As this is a different topic, I want to seperate it so i dont get myself confused. TBH didnt realise I had replys to some, dont get alerted by email, which I have chnaged now.

[Ray Paseur]

Change to error_reporting(E_ALL) and PHP will tell you what is wrong. The script relies on an undefined variable, $qname and so PHP silently substitutes a null string. The resulting query string has a LIKE clause that says LIKE '%%' thus it matches everything.

[Ray Paseur]

Here's an annotated code snippet. Hopefully the ideas in the comments will be useful for you.

<?php
/**
 * Demonstrate some of the basics of MySQLi
 *
 * Some useful references for PHP and MySQL(i):
 *
 * http://php.net/manual/en/mysqli.overview.php
 * http://php.net/manual/en/class.mysqli.php
 * http://php.net/manual/en/class.mysqli-stmt.php
 * http://php.net/manual/en/class.mysqli-result.php
 * http://php.net/manual/en/class.mysqli-warning.php
 *
 * http://php.net/manual/en/mysqli.construct.php
 * http://php.net/manual/en/mysqli.real-escape-string.php
 * http://php.net/manual/en/mysqli.query.php
 * http://php.net/manual/en/mysqli.errno.php
 * http://php.net/manual/en/mysqli.error.php
 * http://php.net/manual/en/mysqli.insert-id.php
 *
 * http://php.net/manual/en/mysqli-result.num-rows.php
 * http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS!
 * http://php.net/manual/en/mysqli-result.fetch-object.php
 *
 * Interesting: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
 */

/**
 * E_ALL setting will cause PHP to report Notice, Deprecated, etc.
 * You REALLY WANT those hidden errors to be revealed!  You want to display
 * the errors during your development cycle, and log the errors during your
 * deployment cycle.
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('log_errors', TRUE);

/**
 * You may want to use variables instead of globally defined constants.  As you get
 * to more advanced programming your work may use more than one database (or different
 * users with different DB privileges).  Sometimes you may need to make more than
 * one DB connection object.  Since you cannot reDEFINE() a constant, this might not
 * be a good long-term habit.
 */
define('DBHOST', 'localhost');
define('DBUSER', 'root');
define('DBPASS', 'mypass123');
define('DBNAME', 'topgrub');

/**
 * Get used to setting your output variables to some predictable value at the top
 * of your script, function, class, method, etc.  It will save you from the confusion
 * of spaghetti code, because you will be forced to think about what the script is
 * is creating, and you will be able to see it at a glance.  Avoid creating any 
 * unnecessary variables.
 */
$output = NULL;

/** 
 * Get used to using the object-oriented extension - much easier than procedural
 * with less fiddly punctuation, better error indicators, etc.
 */
$mysqli = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);
if ($mysqli->connect_errno) {
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR); 
}

/**
 * Assuming that this request came from an HTML form with a "search" button might
 * be a complicating factor for (human) clients who just typed a string into the
 * search box and hit Enter, so we will pay attention to the search string and 
 * ignore the "search" button.  The next line of code makes the external data 
 * safe for use in your query string
 */
$str = !empty($_POST['q']) ? $mysqli->real_escape_string($_POST['q']) : NULL; // "Ternary" notation

/**
 * An empty search string will expose the entire 'products' table, so we take
 * a security step here and suppress this inappropriate output dump.
 */
if (!$str) $output .= "<h2>Please enter a search value</h2>";
die($output);

/**
 * By setting the query string up as a separate variable, you can print it out
 * which is useful if something goes wrong (as things often do with MySQL).
 *
 * You also want to avoid SELECT * Disease.  SELECT * causes the database engine
 * to return ALL of the data in each matched row, and this makes it the least 
 * efficient approach to data retrieval.  Just SELECT the columns, by name.
 * Why is this an important habit?  Some day  you will find yourself working on 
 * a project where some doofus has added images into the database.  When that 
 * happens, SELECT * will cause a performance nightmare!
 */
$sql = "SELECT `name` FROM `products` WHERE `name` LIKE '%$str%' ";

/**
 * You always want to test to see if the query worked or failed, and visualize
 * any error information along with the fully resolved query string.
 */
if (!$res = $mysqli->query($sql)) {
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

/**
 * If the script gets this far without error, you will have a "results" object
 * in $res, and you can use it to get the counts, retrieve the data, etc.
 */
if($res->num_rows == 0) {
    $output .= "<h2>No result found for $str!</h2>";
}
else {
    $output .= "<h2>$res->num_rows Results Matching '<b>$str</b>'</h2>";

    while($row = $res->fetch_object()){
        $output .= "<p>$row->name</p>";
    }
}

/**
 * Show the results 
 */
echo PHP_EOL . $output; // PHP_EOL is the context-aware newline character

/** 
 * NEVER use the close-PHP tag.  It's too easy to bollix things up, so
 * find another way to deal with the issue.  Usually it's fine to leave
 * the close-PHP tag off the end of the script.
 */