How to format an array in PHP according to my needs?

144
March 14, 2019, at 12:10 PM

I have a simple destination database,

$sql=mysqli_query($con,"SELECT * FROM users,rating WHERE users.user_id=rating.user_id GROUP BY rating.user_id");
$output=array();
while($hsl=mysqli_fetch_array($sql)){
$str=$hsl['username'];
$sql1=mysqli_query($con,"SELECT * FROM destination,rating WHERE rating.dest_id=destination.dest_id AND rating.user_id='$hsl[user_id]'");
while($hsl1=mysqli_fetch_array($sql1)){
$user_id=$hsl1['user_id'];
$title=$hsl1['title'];
$rating1=$hsl1['1_rating'];
$rating2=$hsl1['2_rating'];
$rating3=$hsl1['3_rating'];
$rating4=$hsl1['4_rating'];
$avg=($rating1+$rating2+$rating3+$rating4) / 4;
$str1=$title;
$str2=$avg;

How to format the array to get the result of each user as below?

$rekom =  array(
"user1" => array("Monumen Nasional" => 3.25, 
              "Kota Tua" => 3,
                "Tidung Island" => 2),
"user2" => array("Monumen Nasional" => 3.5, "Kota Tua" => 4.25,
                  "Tidung Island" => 2),
"user3" => array("Monumen Nasional" => 2.25, "Kota Tua" => 4.5,
                "Tidung Island" => 3.75),
"user4" => array("Monumen Nasional" => 2, "Kota Tua" => 4, "Tidung Island" => 4),
"user5" => array("Monumen Nasional" => 4.25, "Tidung Island" => 4)    

);

I have tried this code but cannot parse all of users :

$rekom = array($str => array($title => $str2));
Answer 1

You can use joins to get all information that you need so you can have one query instead of 1 + number of users in database. That can have a huge performance benefit if you have a lot of users.

Below I have assumed that you save destination name in a column title and these values 'user1', 'user2' ... are stored in user_id column. If column names are different feel free to make changes to my code.

$sql = "SELECT * FROM users 
        INNER JOIN rating ON users.user_id=rating.user_id
        INNER JOIN destination ON rating.dest_id = destination.dest_id";

$result = mysqli_query($con, $sql);
$reviews = [];
while($row = mysqli_fetch_array($result)) {
    $rating = ($row['1_rating'] + $row['2_rating'] + $row['3_rating'] + $row['4_rating']) / 4;
    $reviews[$row['user_id']][$row['title']] = $rating;
}
print_r($reviews);

This will get you all destination that have reviews from users.

Edit You can calculate average by adding AVG(1_rating + 2_rating + 3_rating + 4_rating) in your SELECT. Also you should add group by dest_id, user_id

Rent Charter Buses Company
READ ALSO
mysql trigger after insert new line table1 copy to table2 selected fields without duplicates

mysql trigger after insert new line table1 copy to table2 selected fields without duplicates

I want to create trigger after inserting a new row on TABLE1 and a trigger for the update when I update table1

136
Unity WebGl and MySQL: how to access?

Unity WebGl and MySQL: how to access?

I'm developing a WebGL app with Unity 2018

141
Does anyone have a link for an Angular CLI / Spring Boot project w/one to many and ManyToMany relationship?

Does anyone have a link for an Angular CLI / Spring Boot project w/one to many and ManyToMany relationship?

I'm working on project that use Angular CLI, Spring Boot, Hibernate, JPA and mySQL

110
MySQL - Compare TWO tables and SUM them

MySQL - Compare TWO tables and SUM them

I'm struggling on how to compare to TWO tables against eachother whilst totaling (SUM) them up

118