如何进行参数化查询

发布于 2024-08-21 21:30:24 字数 820 浏览 4 评论 0原文

[状态：学习者]

我正在尝试实现参数化查询，但遇到问题。 Jonathan Sampson 最近暗示了如何做到这一点（#2286115），但我没有正确遵循他的建议。这是我的脚本

$cGrade = "grade" ;

include_once ( "db_login.php" ) ;

$sql = "SELECT   last_name   AS last_name
               , first_name  AS first_name
               , grade       AS gr
               , ethnic      AS eth
               , sex         AS sex
               , student_id  AS id_num
               , reason      AS reason
               , mon_init    AS since
          FROM t_tims0809
         WHERE tag <> '' AND 
               tag IS NOT NULL AND
               schcode = {$schcode}
         ORDER
            BY ('%s') " ;

$qResult = mysql_query ( sprintf ( $sql, $cGrade ) or ( "Error: " . mysql_error() ) ) ;

查询与 ORDER BY 短语中的 grade 配合得很好。

谢谢。

原文

[ Status: Learner ]

I am attempting to implement a parameterized query but I am having problems. Jonathan Sampson recently hinted at how this could be done (#2286115), but I'm not following his suggestion correctly. Here is my script

$cGrade = "grade" ;

include_once ( "db_login.php" ) ;

$sql = "SELECT   last_name   AS last_name
               , first_name  AS first_name
               , grade       AS gr
               , ethnic      AS eth
               , sex         AS sex
               , student_id  AS id_num
               , reason      AS reason
               , mon_init    AS since
          FROM t_tims0809
         WHERE tag <> '' AND 
               tag IS NOT NULL AND
               schcode = {$schcode}
         ORDER
            BY ('%s') " ;

$qResult = mysql_query ( sprintf ( $sql, $cGrade ) or ( "Error: " . mysql_error() ) ) ;

The query works fine with grade in the ORDER BY phrase.

Thanks.

分享到QQ

分享到微博

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

发布评论

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

夏天碎花小短裙 2024-08-28 21:30:24

查看 MySQLi 准备好的语句类：

$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("sss", $val1, $val2, $val3);

$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';

/* Execute the statement */
$stmt->execute();

来自 PHP 手册。

我觉得这是一种执行参数化查询的更优越的方式，我已经在可能的情况下切换到准备好的语句，特别是在批量插入/选择期间。

Check out the MySQLi prepared statements class:

$query = "INSERT INTO myCity (Name, CountryCode, District) VALUES (?,?,?)";
$stmt = $mysqli->prepare($query);

$stmt->bind_param("sss", $val1, $val2, $val3);

$val1 = 'Stuttgart';
$val2 = 'DEU';
$val3 = 'Baden-Wuerttemberg';

/* Execute the statement */
$stmt->execute();

From the PHP manual.

I feel it's a much superior way of doing parameterized queries, I've switched over to prepared statements when possible, especially during bulk inserts/selects.

回复收藏 0 原文

将军与妓 2024-08-28 21:30:24

Xorlev的回答是完全正确的。还有其他语法选项。您可以按名称在查询中指定绑定变量：

$stmt = $mysqli->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

或者，如果您想速记并跳过对 bindParam() 的调用：

$stmt = $mysqli->prepare('INSERT INTO tbl VALUES(?)');
$stmt->execute($stmt, array("some input"));
$stmt->execute($stmt, array("some other input"));
$stmt->execute($stmt, array("some more input"));

Xorlev's answer is entirely correct. There are other options for syntax too. You can specify the bind variables within the query by name:

$stmt = $mysqli->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

Or if you want to do things shorthand and skip the call to bindParam():

$stmt = $mysqli->prepare('INSERT INTO tbl VALUES(?)');
$stmt->execute($stmt, array("some input"));
$stmt->execute($stmt, array("some other input"));
$stmt->execute($stmt, array("some more input"));

回复收藏 0 原文

~没有更多了~