Giorgos Eleftheriou Giorgos Eleftheriou - 1 month ago 8
MySQL Question

$stmt->fetch() is not fetching the data

I don't understand why my function is not getting the results and just getting null value. I'm using PhpStorm; all my connections are fine (Apache, MySQL, PhpMyAdmin) I've check them, also the other rest services are working.

This is my

dbhandler.php
file:

public function getRating($rating_id, $user_id) {
$stmt = $this->conn->prepare("SELECT ur.restaurant_id, ur.service_rating, ur.food_rating, ur.music_rating FROM user_ratings ur , user u WHERE u.user_id = ? AND u.user_id = ur.user_id AND ur.rating_id = ?");
$stmt->bind_param("ii", $rating_id, $user_id);


if ($stmt->execute()) {

$stmt->bind_result( $restaurant_id, $service_rating, $food_rating, $music_rating);
// TODO
//$rating_id = $stmt->get_result()->fetch_assoc();
$stmt->fetch();
$res = array();
$res["user_id"] = $user_id;
$res["rating_id"] = $rating_id;
$res["restaurant_id"] = $restaurant_id;
$res["service_rating"] = $service_rating;
$res["food_rating"] = $food_rating;
$res["music_rating"] = $music_rating;
$stmt->close();

return $res;
} else {
return NULL;
}
}


and this is my
index.php
file

$app->get('/userRatings/:rating_id', 'authenticate', function($rating_id) {
global $user_id;
$response = array();
$db = new DbHandler();

// fetch rating
$result = $db->getRating($rating_id, $user_id);

if ($result != NULL) {
$response["user_id"] = $result["user_id"];
$response["rating_id"] = $result["rating_id"];
$response["restaurant_id"] = $result["restaurant_id"];
$response["service_rating"] = $result["service_rating"];
$response["food_rating"] = $result["food_rating"];
$response["music_rating"] = $result["music_rating"];
echoRespnse(200, $response);
} else {
$response["error"] = true;
$response["message"] = "The requested resource doesn't exists";
echoRespnse(404, $response);
}
});


The response of the request is:

{"user_id":19,
"rating_id":"171",
"restaurant_id":null,
"service_rating":null,
"food_rating":null,
"music_rating":null}

Answer

There is a problem in param values. Change param values as follows.

$stmt = $this->conn->prepare("SELECT  ur.restaurant_id, ur.service_rating, ur.food_rating, ur.music_rating FROM user_ratings ur , user u WHERE u.user_id = ? AND u.user_id = ur.user_id  AND ur.rating_id = ?");

$stmt->bind_param("ii", $user_id,$rating_id);

According to your sql first param should be $user_id. Not the $rating_id.

According to your parameter settings there is no record to fetch.