如何在 PHP 中使用 bind_params 正確獲取數據到數組 (How to correctly fetch data to array with bind_params in PHP)

這是我的功能代碼：

public function getUserFavouriteRecipes($user_unique_id, $limit) {
    $stmt = $this‑>conn‑>prepare("SELECT recipe.`unique_id`, recipe.`title`, recipe.`img_tumbnail_link`, recipe.`add_date`, recipe.`kitchen_type`, recipe.`meal_type`, user.`name`, user.`surname`, 
                                    (SELECT count(*) from `like` WHERE recipe.`unique_id` = `like`.`recipe_unique_id_fk`) AS like_count 
                                    FROM `recipe` 
                                    JOIN `favourite` ON (recipe.`unique_id` = `favourite`.`recipe_unique_id_fk`) 
                                    JOIN `user` ON (recipe.`user_unique_id_fk` = user.`unique_id`) 
                                    WHERE favourite.`user_unique_id_fk` = ?
                                    ORDER BY recipe.`add_date` DESC
                                    LIMIT ?, 10");
    $converted_limit = intval($limit);                      

    $stmt‑>bind_param("si", $user_unique_id, $limit);   
    $result = $stmt‑>execute();

    if ($result) {
        $myArray = array();

        // Fetching Rows From Query
        while ($row = $result‑>get_result()‑>fetch()) {
            $myArray[] = $row;
        }
        $stmt‑>close();

        return $myArray;
    } else {
        return NULL;
    }
}

我在這裡嘗試對其進行編碼：

<?php
require_once 'include/DB_Functions.php';
$db = new DB_Functions();

// Receiving The Post Params
$user_unique_id = $_POST['user_unique_id_fk'];
$limit = $_POST['limit'];

// Getting Result Array
$resultArray = $db‑>getUserFavouriteRecipes($user_unique_id, $limit);
echo json_encode($resultArray);
?>

提前感謝您的幫助。

參考解法

方法 1:

The correct way to fetch results using prepared statements is:

...    
$stmt‑>execute();
$myArr = array();
$tmp = array();
$stmt‑>bind_result($tmp['unique_id'], $tmp['title'], ...); // all fields in select
$i = 0;
while($stmt‑>fetch()) {
    $myArr[$i] = $tmp;
    $i++;
}
if (count($myArr) > 0) {
    return $myArr;
} else {
    return false;
}

方法 2:

You can use PDOStatement::fetchAll() to return an array containing all of the result set rows, like this:

public function getUserFavouriteRecipes($user_unique_id, $limit) {

    // your code

    $stmt‑>execute();
    return $stmt‑>fetchall(PDO::FETCH_ASSOC);
}

$resultArray = $db‑>getUserFavouriteRecipes($user_unique_id, $limit);

To encode the results you can do:

$json=json_encode($resultArray);

And if you want to display the results, you can do something like this:

//loop over the $resultArray, setting $result to an array representing each row
foreach($resultArray as $result){
    //loop over each $result (row), setting $key to the column name and $value to the value in the column.
    foreach($result as $key=>$value){
        //echo the key and value.
        echo "{$key} = {$value}<br>";
    }
}

Edited:

public function getUserFavouriteRecipes($user_unique_id, $limit) {

    // your code

    $status = $stmt‑>execute();

    $myArray = array();
    if($status){
        // Extract result set and loop rows
        $result_set = $stmt‑>get_result();

        while($result = $result_set‑>fetch_assoc()){
            $myArray[] = $result;
        }
        return $myArray;
    }else{
        return NULL;
    }

}

$resultArray = $db‑>getUserFavouriteRecipes($user_unique_id, $limit);
$json = json_encode($resultArray);

Your issue lies here:

The following illustrates the LIMIT clause syntax with two arguments:

SELECT 
    column1,column2,...
FROM
    table
LIMIT offset , count;

Let’s examine the LIMIT clause parameters:

• The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
• The count specifies maximum number of rows to return.

So the reason you're getting this string '[]' (length=2) result is because of the fact that it's not able to fetch any record based on your offset and count.

Try this:

public function getUserFavouriteRecipes($user_unique_id, $limit) {
    $stmt = $this‑>conn‑>prepare("SELECT recipe.`unique_id`, recipe.`title`, recipe.`img_tumbnail_link`, recipe.`add_date`, recipe.`kitchen_type`, recipe.`meal_type`, user.`name`, user.`surname`, 
                                    (SELECT count(*) from `like` WHERE recipe.`unique_id` = `like`.`recipe_unique_id_fk`) AS like_count 
                                    FROM `recipe` 
                                    JOIN `favourite` ON (recipe.`unique_id` = `favourite`.`recipe_unique_id_fk`) 
                                    JOIN `user` ON (recipe.`user_unique_id_fk` = user.`unique_id`) 
                                    WHERE favourite.`user_unique_id_fk` = ?
                                    ORDER BY recipe.`add_date` DESC
                                    LIMIT ?");
    $converted_limit = intval($limit);                      

    $stmt‑>bind_param("si", $user_unique_id, $converted_limit);   

    $status = $stmt‑>execute();

    $myArray = array();
    if($status){
        // Extract result set and loop rows
        $result_set = $stmt‑>get_result();

        while($result = $result_set‑>fetch_assoc()){
            $myArray[] = $result;
        }
        return $myArray;
    }else{
        return NULL;
    }
}

$resultArray = $db‑>getUserFavouriteRecipes($user_unique_id, $limit);
$json = json_encode($resultArray);
//var_dump($json);

(by anton86993、ondrejba、Rajdeep Paul)

參考文件

1. How to correctly fetch data to array with bind_params in PHP (CC BY‑SA 2.5/3.0/4.0)