Prepared Statements with dynamic WHERE clause [dup

问题:

This question already has an answer here:

• How to create a dynamic WHERE clause [duplicate] 2 answers

OK so my question is I have a function but i want to parse it different WHERE clauses for when it executes a query. for example:

function query($where)
{
$query = $mysql->prepare("SELECT * FROM table WHERE ?");
$query->bind_param("s", $where);
$query->execute();

...

}

query("table.id=123 AND table.name='abc'");

I have learnt that this is incorrect So how do I perform something similar, i have many places where I need to use this function with different WHERE clauses, and make a function for each is impractical and so is not making a function and calling it directly.

回答1:

For a prepared statement with a WHERE clause you have to specify what values will be specified later, for instance:

SELECT * FROM table WHERE ID=?

if you want to make it more dynamic you can have specify the query in one function and then call the query function. For example, you have this:

function query($query, $param, $where)
{
$query = $mysql->prepare($query);
$query->bind_param($param, $where);
$query->execute();

...

} 

and in your other function you say:

$results=query("SELECT * FROM table WHERE Id=?","s","1");

you could make this even more sophisticated by making a query class that can contain an array of where clauses:

class query
{
  public $query;
  public $param;
  public $where;
}

$query=new query();
$query->query="SELECT * FROM Table WHERE group=? AND name like ?";
$query->param="ss";
$query->where = array();
$query->where[]="administrators";
$query->where[]="sam";

and change your query function to look like:

function SQLCall(query $query)
{
$db = $mysql->prepare($query->query);
call_user_func_array(array(&$db, 'bind_param'), $where)
$db->execute();

...

}

回答2:

Different queries in an if (or if you have a lot of variances, switch) statement:

if($item === 'option1'){
    $query = "SELECT * FROM table WHERE item = ?;";
    $preparedQuery = $mysql->prepare($query);
    $preparedQuery->bind_param("s", $someString);
} elseif($item === 'option2'){
    $query = "SELECT * FROM table WHERE different = ?;";
    $preparedQuery = $mysql->prepare($query);
    $preparedQuery->bind_param("i", $someInteger);
}

$preparedQuery ->execute();

Overly generified obviously, but you should get the general idea. If you know that there will always be the same number of values, and it will always be the same type, you can simplify it:

if($item === 'option1'){
    $query = "SELECT * FROM table WHERE item = ?;";
} elseif($item === 'option2'){
    $query = "SELECT * FROM table WHERE different = ?;";
}

$preparedQuery = $mysql->prepare($query);
$preparedQuery->bind_param("s", $someString);
$preparedQuery->execute();