MySQL Select statement

Question

I have 2 values that I'm suppling my script - I want to search for any one of those datas. How do I write my query like this:

SELECT * FROM table WHERE id = '".$id."' or "name='".$name."';

my problem is escaping the quotes in the query.

Any help will be appreciated.

Answer 1

There are a few ways to do it, a lot of them frowned on but generally I would stick to using MySQLi and using the

mysqli_real_escape_string($id)

function or in OOP

$mysqli = new mysqli('host', 'user', 'pass', 'database');
$id = $mysqli -> real_escape_string($id);
$name = $mysqli -> real_escape_string($name);
$results = $mysqli -> query("SELECT * FROM table WHERE id = '{$id}' or "name='{$name}'");

• Johan: You don't need the {} just leave them out and all will work ok, you only need them if you want to include a function result.
• Dave Mackintosh: I put them in since they didn't specify wether it was the result of a funtion or not and it doesn't stop concatenation in any way if it isn't from a function.
• Michael: If you are going to use MySQLi, why not use a prepared statement?
• Dave Mackintosh: I was just keeping it simple for the user since prepared statements can be quite a pain and they're asking how to write a query which I'd say is a fairly good benchmark of their ability, I'd assume..
• Michael: Good point. I just felt it should be at least mentioned, so she can look into upgrading to it as some point.

Answer 2

You may use curly brackets to avoid confusion with escaping characters as follows:

$query = "SELECT * FROM table WHERE id = '{$id}' or name = '{$name}' ";

You may also consider using wildcards such as %$letter% to search for word anywhere in the name field as:

$query = "SELECT * FROM table WHERE id = '{$id}' or name LIKE '%{$name}%' ";

SUGGESTTION: You should always use id fields as integer for better performance.

• Julie: This is one awesome solution - it has everything I need. Thanks a million.
• Ghazanfar Mir: You are welcome. Please like the solution you liked the most. This would encourage us to answer as much and as relevant as possible :)

Answer 3

Use this fancy function, mayhaps? The examples have what you're looking for.

Answer 4

You've got an extra quote; if you want to stick with your original code (not recommended), try something like this:

$query = "SELECT * FROM table WHERE id = '".$id."' or name='".$name."'";

But really you should be using parameterised queries so that you avoid possible SQL injection security issues!

• Julie: Thanks for that, I have a code that escapes the supplied data. Nice solution BTW.

Answer 5

Write it as:

$name = mysql_real_escape_string($name);
$id = mysql_real_escape_string($id);
$query = "SELECT * FROM table WHERE id = '$id' or name= '$name' ";

Because you started with double quotes the single quotes are part of the query and the $vars are expanded.