What is the best way to run this query?

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?

That’s for the prepared statements, surely?

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

At the least, I’d expect you to need to do this:

$stmt = $this->conn->prepare(" UPDATE products SET
  prod_rating = (SELECT AVG(prod_rating) FROM rating WHERE ProductID = ?)
  WHERE pid = ?");
  $stmt->bind_param("ss", $pid, $pid);

to replace both parameters with the value of $pid. I still think it’s mysqli rather than PDO, but I’m not 100% on it.

I’m also puzzled by this bit:

$rating = $db->storeRating($pid, $userid, $rating);
if ($user) {

Where does $user come from?

1 Like

work hard only.

Sorry?

Lol sorry that was a typo. It’s supposed to be $rating.

Why sorry

I didn’t understand your post of “work hard only” as a response to the original question. I meant “sorry” as in “pardon” or “what do you mean”?

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.