如何在 PHP 中为动态查询构建参数化 PDO 语句？

发布于 2024-07-14 07:03:32 字数 541 浏览 3 评论 0原文

如果已经有人问过这个问题，我们深表歉意。我已经看到了有关静态 SQL 的答案，但在这种情况下，我想使用 PDO->prepare() 作为在运行时动态构建的查询字符串。

分解为一个简单的示例：

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = '$val1' ";
} else {
    $TempSQL = $TempSQL . " field2 = '$val2' ";
    $TempSQL = $TempSQL . " AND field3 = '$val3' ";
}

db->query($TempSQL);

如何将其重写为 db->prepare()？

我是否也应该动态构建语句->execute(array(':param' => $var))) ？

有更好/更简洁的方法吗？

原文

Apologies if this has been asked already. I've seen answers regarding static SQLs, but in this case I'd like to use PDO->prepare() for a query string that is built dynamically at runtime.

Breaking down into a simple example:

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = '$val1' ";
} else {
    $TempSQL = $TempSQL . " field2 = '$val2' ";
    $TempSQL = $TempSQL . " AND field3 = '$val3' ";
}

db->query($TempSQL);

How do I rewrite this as a db->prepare()?

Should I build the statement->execute(array(':param' => $var))) on the fly as well?

Is there a better / neater way?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无敌元气妹 2024-07-21 07:03:32

也许是这样的。（未经测试）

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";
$args=array();

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = :val1"
    $args[':val1']=$val1;
} else {
    $TempSQL = $TempSQL . " field2 = :val2 and field3 = :val3";
    $args[':val2']=$val2;
    $args[':val3']=$val3;
}

$stmt=$db->prepare($TempSQL);
$stmt->execute($args);

Perhaps something like this. (untested)

$TempSQL = "SELECT field1, field2, field3 FROM table WHERE ";
$args=array();

if ($numberParams == 1) {
    $TempSQL = $TempSQL . " field1 = :val1"
    $args[':val1']=$val1;
} else {
    $TempSQL = $TempSQL . " field2 = :val2 and field3 = :val3";
    $args[':val2']=$val2;
    $args[':val3']=$val3;
}

$stmt=$db->prepare($TempSQL);
$stmt->execute($args);

回复收藏 0 原文

回首观望 2024-07-21 07:03:32

根据您的示例，更简洁的方法是循环而不是切换。

db->prepare() 允许您使用 bindParam() 或 PDOStatement->exec()。您可以使用？来自示例 3 和 4，而不是命名字段值。

它仍然要求 SQL 语句已知所有字段。

回复收藏 0 原文

握住你手 2024-07-21 07:03:32

$TempSQL = 'SELECT field1, field2, field3 FROM table';
$cond = array();
$params = array();
if (!empty($val1)) {
    $cond[] = "field1 = ?";
    $params[] = $val1;
}
if (!empty($val2)) {
    $cond[] = "field2 = ?";
    $params[] = $val2;
}
if (!empty($val3)) {
    $cond[] = "field3 = ?";
    $params[] = $val3;
}
if (count($cond)) {
    $TempSQL .= ' WHERE ' . implode(' AND ', $cond);
}
$stmt = $pdo->prepare($TempSQL);
$stmt->execute($params);

$TempSQL = 'SELECT field1, field2, field3 FROM table';
$cond = array();
$params = array();
if (!empty($val1)) {
    $cond[] = "field1 = ?";
    $params[] = $val1;
}
if (!empty($val2)) {
    $cond[] = "field2 = ?";
    $params[] = $val2;
}
if (!empty($val3)) {
    $cond[] = "field3 = ?";
    $params[] = $val3;
}
if (count($cond)) {
    $TempSQL .= ' WHERE ' . implode(' AND ', $cond);
}
$stmt = $pdo->prepare($TempSQL);
$stmt->execute($params);

回复收藏 0 原文

~没有更多了~