PHP mysql search with Variables

Question

I'm trying to query a database using a variable to detect the chosen column along with the search term.

For simplicity I removed the DB connection error handling code. The problem code is:

$find = mysqli_real_escape_string($dbc, test_input($_POST["find"]));
$field = $_POST["field"];

$data = mysqli_query($dbc, "SELECT * FROM ticket WHERE '$field' = '$find'"); 
//Results
while($result = mysqli_fetch_array($data)) {  

    echo "Result 1" . $result['number']; 
    echo "<br>"; 
    echo "<br>"; 
    echo "Result 2" . $result['description']; 
    echo "<br>"; 
    echo "<br>"; 
    echo "Result 3" . $result['contact']; 
    echo "<br>"; 
    echo "<br>"; 
    echo "Result 4" . $result['assignee']; 
    echo "<br>"; 
    echo "<br>"; 
    echo "Result 5" . $result['priority']; 
    echo "<br>"; 
    echo "<br>"; 

}

$anymatches = mysqli_num_rows($data); 

if ($anymatches == 0) { 
    echo "Sorry, but we can not find an entry to match your query<br><br>"; 
}

I enter a term in a textbox ($find) and choose what Im searching for from a dropdown menu ($field) e.g. I select assignee.

Even though I know its in the database, I still returns the "Sorry, but we can not find an entry to match your query".

However, if I change the $field variable within the MYSQL query to an actual column name. Example:

$data = mysqli_query($dbc, "SELECT * FROM ticket WHERE assignee = '$find'");

it returns with the correct data. I even echo'd the $field variable during execution to make sure its holding the correct selection, and it does. Any ideas?

Thanks

in the sql statement, it's `\`$field\`` not `'$field'`. also watch out for sql injection in your code. — Twisted1919, Aug 06 '14 at 08:17

score 1 · Answer 1 · answered Aug 06 '14 at 08:28

1

The problem is you're surrounding the field name with quotes (') instead of backticks.

$data = mysqli_query($dbc, "SELECT * FROM ticket WHERE '$field' = '$find'");

to

$data = mysqli_query($dbc, "SELECT * FROM ticket WHERE `$field` = '$find'");

or

$data = mysqli_query($dbc, "SELECT * FROM ticket WHERE $field = '$find'");

Your current code will essentially be trying to find rows where one string matches another string, which will never be true.

answered Aug 06 '14 at 08:28

MattRogowski

726
2
7
22

Both these also worked. Thanks alot. Is there much of a difference between using {} or `` or nothing – Chris Aug 06 '14 at 08:45
{$variable} is just for using the variable in the string in PHP, but this wasn't a PHP issue, and that syntax is not directly related to the query. Ideally, you should always put backticks around table and column names in queries. – MattRogowski Aug 06 '14 at 09:00

score 0 · Accepted Answer · answered Aug 06 '14 at 08:22

0

Make query like

   $data = mysqli_query($dbc, "
                SELECT * FROM ticket 
                WHERE {$field} = '$find'
                ");

answered Aug 06 '14 at 08:22

codeGig

1,024
1
8
11

1

Your problem here is related to SQL, not PHP. You need to understand how to write a proper SQL query. Maybe take a look at http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – fpietka Aug 06 '14 at 08:44

PHP mysql search with Variables

2 Answers2