My search engine emits "mysql_fetch_array() expects parameter 1 to be resource, boolean given in select" (closed)

Question

I have made a search engine, but every time I search for something like "Black Pants" it comes up with this error:

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/u567454288/public_html/search/search.php on line 36 No results found for Black Pants

Here is the code:

<?php 
error_reporting(E_ALL ^ E_NOTICE); 
ini_set('display_errors', '1'); 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<titleSearch For <?php echo $_GET['k']; ?></title>
</head>
<body>

    <h2>Search Engine</h2>
    <form action='./search.php' method='get'>
        <input type='text' name='k' size='50' value='<?php echo $_GET['k']; ?>' required='required' />
        <input type='submit' value='Search' />
    </form>
    <hr />
    <?php 
        $k = $_GET['k'];
        $terms = explode(" ", $k);
        $query = "SELECT * FROM products WHERE ";

        foreach($terms as $each){
            $i++;
            if ($i == 1)
                $query .= "product_name LIKE '%$each%' ";
            else
                $query .= "product_name LIKE '%$each%' ";
        }

        // connect
        require("../storescripts/connect_to_mysql.php");

        $query = mysql_query($query);
        $numrows = mysql_num_rows($query);
        if ($numrows > 0){

            while($row = mysql_fetch_assoc($query)){
                $id = $row['id'];
                $product_name = $row['product_name'];
                $price = $row['price'];
                $details = $row['details'];
                $category = $row['category'];
                $subcategory = $row['subcategory'];
                $date_added = $row['date_added'];

                echo '<table width="100%" border="0" cellspacing="0" cellpadding="6">
                <tr>
                    <td width="17%" valign="top"><a href="../product.php?id=' . $id . '"><img style="border:#666 1px solid;" src="../inventory_images/' . $id . '.jpg" alt="' . $product_name . '" width="77" height="102" border="1" /></a></td>
                    <td width="83%" valign="top"><b>' . $product_name . '</b><br />
                        $' . $price . '<br />
                    <a href="../product.php?id=' . $id . '">View Product Details</a></td>
                </tr>
                </table>';
            }

        }
        else
            echo "No results found for <b>$k</b>";

        // disconnect
        mysql_close();

    ?>
</body>
</html>

Can you suggest how to fix the problem, so I can add a space within words and still be able to search?

**Danger**: You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Dec 23 '13 at 21:09
See [this answer](http://stackoverflow.com/a/11674313/250259) for how to troubleshoot this. — John Conde, Dec 23 '13 at 21:10
It's not recommended to use mysql_* functions anymore. PDO are the preferred methods. — gskema, Dec 23 '13 at 21:11

iambriansreed · Accepted Answer · 2013-12-23T21:20:01.577

Your code creates the following SQL for your black pants search:

SELECT * FROM products WHERE product_name LIKE '%Black%'product_name LIKE '%Pants%'

Replace your original code block:

$query = "SELECT * FROM products WHERE ";

foreach($terms as $each){
    $i++;
    if ($i == 1)
        $query .= "product_name LIKE '%$each%' ";
    else
        $query .= "product_name LIKE '%$each%' ";
}

With this:

$query = "SELECT * FROM products WHERE 1=1";

foreach($terms as $each)
    $query .= " AND product_name LIKE '%$each%'";

The AND above could be OR as well.

This will result in an SQL statement:

SELECT * FROM products WHERE 1=1 OR product_name LIKE '%Black%' OR product_name LIKE '%Pants%'

Pay attention to the comments on your OP as well as there area lot of best practice recommendations there.

Unnecessary counters are just that unnecessary. You never defined `$i` either. You might throw an error on some servers. — iambriansreed, Dec 23 '13 at 21:21

My search engine emits "mysql_fetch_array() expects parameter 1 to be resource, boolean given in select" (closed)

1 Answers1