PHP 如何将我的代码转换为预准备语句代码

Question

我已经尝试了一堆关于如何做预准备语句的不同例子，但它对我不起作用。

我在将此代码转换为预准备语句代码时遇到问题

function get_all_crew() {
            global $db;
            $query = "SELECT * FROM crew WHERE crew_active_state = 1";
            if ($result = $db->query($query)) {
                if ($result->num_rows) {
                   while ($row = $result->fetch_object()) {
                       $posts[] = $row;
                   }
                   $result->free(); // Frigør hukommlsen
                   return $posts;
                }
            }
        } 

我试过这个，但没有用

function get_all_crew() {
            global $db;
            $query = "SELECT crew_member_id, crew_member_img, crew_member_name, crew_member_rank, crew_member_fb, crew_member_steam FROM crew WHERE crew_active_state = ?";
            $stmt = mysqli_stmt_init($db);
                if (!mysqli_stmt_prepare($stmt, $query)) {
                    echo "SQL statement failed";
                } else {
                    mysqli_stmt_bind_param($stmt, "i", $crew_active_state);
                    mysqli_stmt_execute($stmt);
                    $result = mysqli_stmt_get_result($stmt);
                    if ($result = $db->query($query)) {
                        if ($result->num_rows) {
                           while ($row = $result->fetch_object()) {
                               $posts[] = $row;
                           }
                           $result->free(); // Frigør hukommlsen
                           return $posts;
                        }
                    }
                }
        } 

Answer 1

我总是这样做并且它有效。我从不使用fetch_object()但你只是要求转换为参数化语句，所以这里是：

<?php
function get_all_crew(){
    try {
        global $db;
        $query = "SELECT * FROM `crew` WHERE `crew_active_state`=?";
        if($stmt = $db->prepare($query)){
            $crew_active_state = 1;
            $stmt->bind_param('i', $crew_active_state); // OR $stmt->bind_param('i', 1);
            $stmt->execute();
            $result = $stmt->get_result();
        }
        if($db->errno){
            throw new Exception('MySQL error: '.$db->error); // if there is an error, the rest of the code won't be executed.
        }
        while($rows = $result->fetch_object()) {
            $posts[] = $rows;
        }
        unset($stmt);
        return $posts;
    } catch(Exception $e){
        return ['error' => $e->getMessage()]; // just for the sake of returning an array as well.
    }
}
?>