SQL query cannot receive right value from database?

Question

Solution: I asked this question with little knowledge in PHP and SQL, the solution to my answer was to wrap up the variable like this '$PLATE_NUMBER' since it is a string. This is also answered in the first comment below.

As you can see from the code, when I click search, I receive "There was no search results!" There is something wrong with my query, because if I change this to

"SELECT * FROM `tires` WHERE PLATE_NUMBER LIKE '%$PLATE_NUMBER%'"

then I receive the data inside the mySQL database?

Btw: I have also tested both query in mySQL, and it works. So what can be the problem here? I wish to search for instance "CV33610", and receive the row from my database..

$query = mysql_query("SELECT * FROM `tires` WHERE PLATE_NUMBER='%$PLATE_NUMBER%'", $connection) or die("Could not search!"); 
$count = mysql_num_rows($query); // Returns an integer of how many rows in your table that is picked up.

if($count == 0){

    $output = 'There was no search results!';

} else {

    // Collect the data into an array
    while($row = mysql_fetch_array($query)){

        $platenumber = $row['PLATE_NUMBER'];
        $id = $row['id'];

        // View the parameters to the screen. 
        $output .= '<div>'.$id.' '.$platenumber.'</div>';

    } // End while statement
} // End if statement

That looks like a SQL injection vulnerability... – David Feb 28 '15 at 21:55 — David, Feb 28 '15 at 21:55

Jesper · Answer 1 · 2015-02-28T22:00:25.340

$PLATE_NUMBER will have to be the exanct entry in PLATE_NUMBER.

$query = mysql_query("SELECT * FROM `tires` WHERE PLATE_NUMBER = $PLATE_NUMBER", $connection) or die("Could not search!");

$PLATE_NUMBER just have to be some of the text in PLATE_NUMBER

$query = mysql_query("SELECT * FROM `tires` WHERE PLATE_NUMBER LIKE CONCAT('%' . $PLATE_NUMBER . '%')", $connection) or die("Could not search!");

LIKE is searching for a row where PLATE_NUMER can have something before $PLATE_NUMBER and something after. (%) is used if any text can stand before or after the variable set.

This should be working, CONCAT gathers the variables send to it. So it will looks correct when MySQL are reading it.

You should also consiser using PDO or MySQLi prepare statements for making MySQL calls on your website, since normal mysql isn't secure at all.

The CONCAT, as you put it, will not work. The OP's "interpolation" is valid in PHP. — Rick James, Mar 01 '15 at 01:27

score 0 · Accepted Answer · edited May 23 '17 at 12:09

Since we're dealing with a string value CV33610, you need to wrap your $PLATE_NUMBER variable using single quotes.

$query = mysql_query("SELECT * FROM `tires` 
WHERE PLATE_NUMBER = '$PLATE_NUMBER'", $connection) 
or die(mysql_error());

Having added or die(mysql_error()) to your query would have signaled the syntax error.

Sidenote:

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

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.

score 0 · Answer 3 · answered Mar 01 '15 at 16:37

Thanks so much guys for helping me solving this problem. In the end, I found out what the problem was. The only problem was this

PROBLEM: <input type="text" name="search" placeholder="Search for tires...."/>

Solution: <input type="text" name="PLATE_NUMBER" placeholder="Search for tires...."/>

Have a nice day guys!!

SQL query cannot receive right value from database?

3 Answers3