I’ve been trying to get this query to work but without success. I’m using PHP to run the query. What I am trying to do is update the “rating” column from one table with the average rating from another table. " UPDATE products SET prod_rating = (SELECT AVG(prod_rating) FROM rating WHERE ProductID = ?) WHERE pid = ?"
This is my PHP PDO functions class:
<?php
class DB_TestFunctions {
private $conn;
// constructor
function __construct() {
require_once 'DB_Connect.php';
// connecting to database
$db = new Db_Connect();
$this->conn = $db->connect();
}
// destructor
function __destruct() {
}
/**
* Storing new rating
* return overall rating
*/
public function storeRating($pid, $userid, $ratingpnt) {
$stmt = $this->conn->prepare("INSERT INTO rating(ProductID,UserID,prod_rating) VALUES(?, ?, ?)");
$stmt->bind_param("sss", $pid, $userid, $ratingpnt);
$result = $stmt->execute();
$stmt->close();
// check for successful rate
if ($result) {
$stmt = $this->conn->prepare(" UPDATE products SET
prod_rating = (SELECT AVG(prod_rating) FROM rating WHERE ProductID = ?)
WHERE pid = ?");
$stmt->bind_param("s", $pid);
$stmt->execute();
$rating = $stmt->get_result()->fetch_assoc();
$stmt->close();
return $rating;
} else {
return false;
}
}
/**
* Check if rating exists
*/
public function checkDuplicate($pid, $userid) {
$stmt = $this->conn->prepare("SELECT prod_rating from rating WHERE ProductID = ? AND UserID = ?");
$stmt->bind_param("ss", $pid, $userid);
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {
// user existed
$stmt->close();
return true;
} else {
// user not existed
$stmt->close();
return false;
}
}
}
?>
And this is my POST class:
<?php
require_once 'include/DB_TestFunctions.php';
$db = new DB_TestFunctions();
// json response array
$response = array("error" => FALSE);
if (isset($_POST['pid']) && isset($_POST['userid']) && isset($_POST['rating'])) {
// receiving the post params
$pid = $_POST['pid'];
$userid = $_POST['userid'];
$rating = $_POST['rating'];
// check if user is already existed with the same email
if ($db->checkDuplicate($pid, $userid)) {
// user already existed
$response["error"] = TRUE;
$response["error_msg"] = "Rating already exists." ;
echo json_encode($response);
} else {
// create a new user
$rating = $db->storeRating($pid, $userid, $rating);
if ($user) {
// user stored successfully
$response["error"] = FALSE;
$response["rating"] = $rating["prod_rating"];
echo json_encode($response);
} else {
// user failed to store
$response["error"] = TRUE;
$response["error_msg"] = "Unknown error occurred in registration!";
echo json_encode($response);
}
}
} else {
$response["error"] = TRUE;
$response["error_msg"] = "Required parameters (pid, userid or rating) is missing!";
echo json_encode($response);
}
?>
You would need to post the structure (not the data) of your database
Plus, I don’t know why you have put ? inside the query, surely it might be ProductID = $pid where $pid is the variable of the given for the what ever your code is supposed to do
Your update query has two parameters, but you only bind one value. If you’re using PDO, though, shouldn’t it be bindParam() as opposed to bind_param(), and I think the “sss” is a mysqli thing rather than PDO?
I’m not that experienced with PDO, that is just how things were structured in the tutorial I followed. I just modified the queries and the POST parameters as well as the functions. Everything works except for that query.
This is my database structure: rating table:
CREATE TABLE rating ( RatingID int(11) NOT NULL, ProductID int(11) NOT NULL, UserID int(2) NOT NULL, prod_rating float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
products table:
CREATE TABLE products ( pid double NOT NULL, prod_name varchar(50) NOT NULL, prod_pic varchar(100) NOT NULL, prod_recipe varchar(400) NOT NULL, prod_thumb varchar(255) NOT NULL, prod_rating float NOT NULL, created_by varchar(250) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
If I run the sql query from phpMyAdmin, I get the following error:
Static analysis:
1 errors were found during analysis.
A new statement was found, but no delimiter between it and the previous one. (near "SELECT" at position 41)
SQL query: Documentation
UPDATE `products` SET `prod_rating` = (SELECT AVG `prod_rating` FROM `rating` WHERE `ProductID` = 1) WHERE `pid` = 1
MySQL said: Documentation
#1054 - Unknown column 'AVG' in 'field list'
Yes, I guess the error message might have been better if it was Syntax Error
instead of pointing you to the problem area and you needing to figure it out on your own.
Try putting function arguments inside parentheses.
I ran this in phpMyAdmin and it worked perfectly.
UPDATE products SET prod_rating = (SELECT AVG (prod_rating) FROM rating WHERE ProductID = 1) WHERE pid = 1
But if I run my PHP code it checks for a duplicate rating successfully, posts the rating to the “rating” table successfully but somewhere after that it crashes and give a HTML 500 error