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

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"]

或者如果您期望只有 1 行作为答案:

$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();