How to correctly fetch data to array with bind_params in PHP

Question

I am currently trying to execute some MySQL query and save the results into an array using bind_params for 2 params: userUniqueId (String) and limit (used to LIMIT query records). I've tried to do sth on my own but seems the code does not work properly. I've tried different ways to do that but still nothing. Can you try to help me? Query works fine cause I've tested it in phpMyAdmin The function I want to write must fetch records into and array and then I would like to json_encode that array using that array as a result of the function.

Here is my function code:

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;
    }
}

Here I try do encode it:

<?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);
?>

Thanks in advance for your help.

What do you mean "the code does not work properly"? Does it give you an error? Or what? Explain the issue. — rjdown
– rjdown, Commented Nov 28, 2015 at 16:58
It always returns [] as an aoutput which means the tabel is null inside. — anton86993
– anton86993, Commented Nov 28, 2015 at 20:00

ondrejba · Accepted Answer · 2015-11-28 17:34:17Z

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;
}

edited Nov 28, 2015 at 17:34

answered Nov 28, 2015 at 16:58

ondrejba

2817 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ondrejba Over a year ago

Now it should work. I accidentally returned true instead of the array.

anton86993 Over a year ago

I have tried your way but it always returns me null instead of an array. Here is the code: ideone.com/qwxNVm

ondrejba Over a year ago

I think I see my error, try now. However, I think that the other answer is better.

Rajdeep Paul · Accepted Answer · 2015-11-28 21:00:08Z

0

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

edited Nov 28, 2015 at 21:00

answered Nov 28, 2015 at 17:03

Rajdeep Paul

16.9k3 gold badges21 silver badges39 bronze badges

11 Comments

anton86993 Over a year ago

I have tried like you told me but i get an error: Call to undefined method mysqli_stmt::fetchAll(). Hehe is corrected code: ideone.com/81lwLx

Rajdeep Paul Over a year ago

@anton86993 That's because you can't execute fetchAll() on a mysqli_stmt object. PDO is not the same thing as mysqli. I've updated my answer. Please see the edited section of my answer.

Rajdeep Paul Over a year ago

@anton86993 You can see the encoded json object using var_dump($json);. Let me know if it works for you.

anton86993 Over a year ago

Here u can see the result: anton869.linuxpl.eu/android_login_api/loadfavouriterecipes.php It still returns null :(( This is corrected function: ideone.com/Hh4krr

Rajdeep Paul Over a year ago

@anton86993 Is it the output you're getting after incorporating my edited answer?

|

Collectives™ on Stack Overflow

How to correctly fetch data to array with bind_params in PHP

2 Answers 2

3 Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related