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

Question

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));

Please read about SQL injection. Instead of building queries with string concatenation, use prepared statements with bound parameters. See this page and this post for some good examples. — Alex Howansky
– Alex Howansky, Commented Mar 13, 2019 at 16:30
Your title seems completely different to your actual question, can you please reword your title? — Script47
– Script47, Commented Mar 13, 2019 at 16:35
@AlexHowansky thank you for the references. Script47 Thanks for the warning — mejimaru
– mejimaru, Commented Mar 13, 2019 at 19:33

Edison Biba · Accepted Answer · 2019-03-13 16:43:42Z

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

answered Mar 13, 2019 at 16:43

Edison Biba

4,4833 gold badges20 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mejimaru Over a year ago

Hello Mr. Edison, your code running well and generate all the user reviews. Thank you so much... :)

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related