Man kan godt opbygge komplekse SQL saetninger samtidigt med at man bruger prepared statement.
Demo med mysqli:
<?php
//$xf1 = 3;
$xf2 = 'BB';
$con = new mysqli('localhost', 'root', '', 'Test');
if(mysqli_connect_errno()) die(mysqli_connect_error());
$first = true;
$sql = 'SELECT f1,f2 FROM t1';
$typ ='';
if(isset($xf1)) {
$sql .= $first ? ' WHERE' : ' AND';
$sql .= ' f1 = ?';
$typ .= 'i';
$val[] = &$xf1;
$first = false;
}
if(isset($xf2)) {
$sql .= $first ? ' WHERE' : ' AND';
$sql .= ' f2 = ?';
$typ .= 's';
$val[] = &$xf2;
$first = false;
}
if($stmt = $con->prepare($sql)) {
call_user_func_array(array($stmt, 'bind_param'), array_merge(array($typ), $val));
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($f1, $f2);
while ($stmt->fetch()) {
print $f1 . ' ' . $f2 . "\n";
}
$stmt->close();
} else {
die($con->error);
}
$con->close();
?>
Demo med PDO (lidt mere elegant!):
<?php
//$xf1 = 3;
$xf2 = 'BB';
$db = new PDO('mysql:host=localhost;dbname=Test');
$first = true;
$sql = 'SELECT f1,f2 FROM t1';
if(isset($xf1)) {
$sql .= $first ? ' WHERE' : ' AND';
$sql .= ' f1 = :f1';
$val[':f1'] = $xf1;
$first = false;
}
if(isset($xf2)) {
$sql .= $first ? ' WHERE' : ' AND';
$sql .= ' f2 = :f2';
$val[':f2'] = $xf2;
$first = false;
}
if($stmt = $db->prepare($sql)) {
$stmt->execute($val);
while ($row = $stmt->fetch()) {
print $row['f1'] . ' ' . $row['f2'] . "\n";
}
$stmt->closeCursor();
} else {
die($con->errorInfo());
}
?>