将查询转换为准备好的语句

Question

已经尝试了几个小时，但运气不佳。

我们有这个查询

$commercial_name = strip_tags($_POST['commercial_beer']);    
$q = "SELECT bb_comm_id FROM bb_commercial WHERE bb_comm_name = '" . $commercial_name . "'";    
$r = @mysqli_query ($dbc, $q); // Run the query.
$row = @mysqli_fetch_array ($r, MYSQLI_ASSOC);
$comm_id = $row['bb_comm_id'];

这很好用。我正在尝试使用准备好的报表，并提出了这个

$commercial_name = strip_tags($_POST['commercial_beer']);   
$q = "SELECT bb_comm_id FROM bb_commercial WHERE bb_comm_name = ?"; 
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 'i', $commercial_name);
$r = mysqli_stmt_execute($stmt);
$row = @mysqli_fetch_array ($r, MYSQLI_ASSOC);
$comm_id = $row['bb_comm_id'];

这不起作用。我做错了什么？

Answer 1

由于您的问题不太清楚，我将分享用于调试数据库查询的代码。我甚至会把它简化为你的代码。

$commercial_name = strip_tags($_POST['commercial_beer']);
if($stmt=$dbc->prepare("SELECT bb_comm_id FROM bb_commercial WHERE bb_comm_name = ?")){
    if (!$stmt->bind_param("s", $commercial_name)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
    $meta = $stmt->result_metadata();
    while ($field = $meta->fetch_field()) {
        $parameters[] = &$row[$field->name];
    }
    call_user_func_array(array($stmt, 'bind_result'), $parameters);
    while ($stmt->fetch()) {
        foreach($row as $key => $val) {
            $x[$key] = $val;
        }
        $comm_id[] = $x;
    }
    $stmt->close();
}else{
    echo "Prepare failed: (" . $dbc->errno . ") " . $dbc->error;
}

如果每个步骤都发生错误，此代码将报告，并将结果集中的所有行保存在$comm_id中。

试试看，如果你还有问题，告诉我。

此外，我认为$commercial_name之前的字符应该是"s"，而不是"i"，因为它可能是一个字符串，而不是整数。

更"生产就绪"的版本：

$stmt=$dbc->prepare("SELECT bb_comm_id FROM bb_commercial WHERE bb_comm_name = ?");
    $stmt->bind_param("s", $commercial_name);
    $stmt->execute();
    $meta = $stmt->result_metadata();
    while ($field = $meta->fetch_field()) {
        $parameters[] = &$row[$field->name];
    }
    call_user_func_array(array($stmt, 'bind_result'), $parameters);
    while ($stmt->fetch()) {
        foreach($row as $key => $val) {
            $x[$key] = $val;
        }
        $comm_id[] = $x;
    }
$stmt->close();

之后，您可以从$comm_id中获取行，例如，第一个结果将存储在$comm_id[0]["bb_comm_id"] 中

或者如果你只期望一行作为答案：

$stmt=$con->prepare("SELECT bb_comm_id FROM bb_commercial WHERE bb_comm_name = ?");
$stmt->bind_param("s", $commercial_name);
$stmt->execute();
$stmt->bind_result($comm_id);
$stmt->fetch();
$stmt->close();