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
– Twisted1919, Commented Aug 6, 2014 at 8:17

MattRogowski · Accepted Answer · 2014-08-06 08:28:54Z

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 6, 2014 at 8:28

MattRogowski

8862 gold badges8 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

Both these also worked. Thanks alot. Is there much of a difference between using {} or `` or nothing

MattRogowski Over a year ago

{$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.

codeGig · Accepted Answer · 2014-08-06 08:22:19Z

0

Make query like

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

answered Aug 6, 2014 at 8:22

codeGig

1,0841 gold badge8 silver badges11 bronze badges

1 Comment

fpietka Over a year ago

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 stackoverflow.com/questions/11321491/…

Collectives™ on Stack Overflow

PHP mysql search with Variables

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related