PHP MySQL statement returns nothing?

Question

I am currently developing an application that will be communicating with a database via a PHP service I have written.

The service I am having problems with is one that should look for a row or rows in a table based on a search string from the users.

The PHP below is designed to recieve a GET request with a variable of "name" which will be the data the SQL query uses. I cannot see anything wrong with my code however the rows returned from a search is always 0.

// checks for the post data
if (isset($_GET["name"])) {
    $name = '%' . $_GET['name'] . '%';

    // get a list of products from the database
    $result = mysql_query("SELECT * FROM products WHERE name LIKE $name");

    if (!empty($result)) {
        // check for empty result
        if (mysql_num_rows($result) > 0) {

            $result = mysql_fetch_array($result);

            $products = array();
            $products["id"] = $row["id"];
            $products["name"] = $row["name"];
            $products["type"] = $row["type"];
            $products["price"] = $row["price"];
            $products["photo"] = $row["photo"];
            // success
            $response["success"] = 1;

            // products node
            $response["products"] = array();

            array_push($response["products"], $products);

            // echoing JSON response
            echo json_encode($response);
        } else {
            // no products found
            $response["success"] = 0;
            $response["message"] = "No products found";

            // echo no products JSON
            echo json_encode($response);
        }
    } else {
        // no products found
        $response["success"] = 0;
        $response["message"] = "Search Complete... No products found";

        // echo no products JSON
        echo json_encode($response);
    }
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";

    // echoing JSON response
    echo json_encode($response);
}

I have an entry in the table it is looking at and a name of "crisps", so when I send a get request with data of say "cr" i would expect to see that entry in the results, however it returns 0 rows.

Then whats even stranger is when I run the SQL below directly against my database it actually pulls back the correct record.

SELECT * FROM products WHERE name LIKE "%cr%"

Any ideas??

Note that all `mysql_*` functions are deprecated (see the [red box](http://php.net/mysql_query)). You also need to escape `%` and `_` in `$_GET['name']`. — Marcel Korpel, Mar 01 '13 at 14:12
Off-Topic Help: Your going to have more issues after you solve this. See: `$result = mysql_query();` then you have `$result = mysql_fetch_array()` but then you use `$row["price"]`. `$row` will be undefined and `$result` will contain an array of arrays. Best wishes :-) — phpisuber01, Mar 01 '13 at 14:12

score 1 · Accepted Answer · answered Mar 01 '13 at 14:09

1

In your query you need to add the quotes ' to your '$name'

  $result = mysql_query("SELECT * FROM products WHERE name LIKE '$name'");

answered Mar 01 '13 at 14:09

jcho360

3,724
1
15
24

score 1 · Answer 2 · edited May 23 '17 at 11:43

Because you didn't wrap the value with single quote, remember that it is a string literal.

SELECT * FROM products WHERE name LIKE '$name'

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

PHP MySQL statement returns nothing?

2 Answers2