CODEWITHSUNDEEP
phpmysql
alp_the_developer
alp_the_developer

Reputation: 785

Merge one array (list of objects) with same keys

I am trying to develop a rating system with php/mysql.

I have a simple rating object like this: (t is type of rating, r is value of rating)

  [{"t":"1","r":2},{"t":"2","r":4},{"t":"3","r":1},{"t":"4","r":2},{"t":"5","r":2}]

In DB, I have a lot of rating records Like this:

     object1=> [{"t":"1","r":2},{"t":"2","r":4},{"t":"3","r":1},{"t":"4","r":2},{"t":"5","r":2}]

     object2=> [{"t":"1","r":1},{"t":"2","r":5},{"t":"3","r":3},{"t":"4","r":3},{"t":"5","r":1}]

In short for output I need a new object like this (I need to calculate average rating, with same keys.)

objectAverageCalculated=> [{"t":"1","r":1.5},{"t":"2","r":4.5},{"t":"3","r":2},{"t":"4","r":2.5},{"t":"5","r":1.5}]

My sql:

CREATE TABLE `ratings` (
  `id` int(11) NOT NULL,
  `rating` text NOT NULL,
  `item_id` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



INSERT INTO `ratings` (`id`, `rating`, `item_id`) VALUES
(6, '[{\"t\":\"1\",\"r\":2},{\"t\":\"2\",\"r\":4},{\"t\":\"3\",\"r\":1},{\"t\":\"4\",\"r\":2},{\"t\":\"5\",\"r\":2}]', 'ABC123'),
(7, '[{\"t\":\"1\",\"r\":1},{\"t\":\"2\",\"r\":5},{\"t\":\"3\",\"r\":3},{\"t\":\"4\",\"r\":3},{\"t\":\"5\",\"r\":1}]', 'ABC123');


--
ALTER TABLE `ratings`
  ADD PRIMARY KEY (`id`);



ALTER TABLE `ratings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

My code

$result = mysqli_query($con, "SELECT * FROM ratings WHERE item_id='ABC123' ");
while ($row = mysqli_fetch_array($result)) {
    $tempArray = json_decode($row['rating'], true);
    array_push($ratingsRaw, $tempArray);
}

I can not save every object with new variable (like $item1,$item2, etc...)

How can I store every object in one array and how can I get average of every rating type in one output object?

Upvotes: 0

Views: 142

Answers (1)

JSowa
JSowa

Reputation: 10572

You can use AVG() method in your MySQL query and retrieve average value directly from database.

SELECT AVG(rating) AS avg_rating FROM ratings WHERE item_id='ABC123'

Or when you don't specify ID and you want average value for all items.

SELECT AVG(rating) AS avg_rating, item_id FROM ratings GROUP BY item_id

Upvotes: 1

Related Questions