PHP PDO Query trying to create subarrays from value

Question

I am trying to figure out how to create (subarrays)? I'm not sure if that is the correct term, but I have examples listed below.

Essentially, I want an array for each resort. Within that array, I want array/sub-arrays for each room.

Any help is appreciated.

<?php
$servername = "localhost";
$username = "root";
$password = "";

$dbh = new PDO("mysql:host=$servername;dbname=checkavail", $username, $password);

$checkin = $_POST['checkin'];
$checkout = $_POST['checkout'];
$occupants = $_POST['occupants'];
$sql=
"SELECT
MIN(staydate) AS checkin,
MAX(staydate) AS lastnight,
MIN(available) AS available,
ra.resort AS resortcode,
ri.resort AS resortname,
ri.room AS roomname,
ri.roomcode AS roomcode,
ri.view AS viewname,
SUM(ra.points) AS points,
ri.sqfoot AS sqfoot,
ri.description AS roomdescription,
ri.bedding AS bedding,
ri.amenities AS amenities,
ri.sleeps AS sleeps,
ri.sleep_details AS sleepdetails,
ri.layout_img AS layoutimg,
ri.room_img AS roomimg,
ri.roomimg_thumb AS roomimgthumb
FROM resort_availability AS ra
    LEFT JOIN room_info AS ri ON (ra.room = ri.roomcode)
WHERE staydate >= '$checkin' AND staydate < '$checkout'
AND sleeps >= '$occupants'
GROUP BY resortname, roomname
ORDER BY points
";
$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);



$return = [];
foreach ($result as $row) {
    $points = $row['points'];
    $price = $points*20;
    $pricefrmt = "$".number_format ($price, 2);
    $return[] = [ 
        'resort' => $row['resortname'],
        'room' => $row['roomname'],
        'price' => $pricefrmt
    ];
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);
?>

This is the current output:

[
  {
   "resort":"Resort1",
   "room":"DStudio",
   "price":"$2,560.00"
  },
  {
   "resort":"Resort1",
   "room":"TVilla",
   "price":"$3,980.00"
  },
  {
   "resort":"Resort1",
   "room":"1-Bedroom Villa",
   "price":"$5,460.00"
  },
  {
   "resort":"Resort1",
   "room":"2-Bedroom LO Villa",
   "price":"$7,040.00"
  },
  {
   "resort":"Resort1",
   "room":"2-Bedroom Villa",
   "price":"$7,040.00"
  },
  {
   "resort":"Resort1",
   "room":"3-Bedroom Villa",
   "price":"$15,620.00"
  },

  {
   "resort":"Resort2",
   "room":"DStudio",
   "price":"$2,560.00"
  },
  {
   "resort":"Resort2",
   "room":"TVilla",
   "price":"$3,980.00"
  },
  {
   "resort":"Resort2",
   "room":"1-Bedroom Villa",
   "price":"$5,460.00"
  },
  {
   "resort":"Resort2",
   "room":"2-Bedroom LO Villa",
   "price":"$7,040.00"
  },
  {
   "resort":"Resort2",
   "room":"2-Bedroom Villa",
   "price":"$7,040.00"
  },
  {
   "resort":"Resort2",
   "room":"3-Bedroom Villa",
   "price":"$15,620.00"
  }
]

But I would like for it to look more like this... (if the formatting seems a bit weird it's because I am a noob at this)

[
  {
    "resort":"Resort1"
    [
      {
       "room":"DStudio",
       "price":"$2,560.00"
      },
      {
       "room":"TVilla",
       "price":"$3,980.00"
      },
      {
       "room":"1-Bedroom Villa",
       "price":"$5,460.00"
      },
      {
       "room":"2-Bedroom LO Villa",
       "price":"$7,040.00"
      },
      {
       "room":"2-Bedroom Villa",
       "price":"$7,040.00"
      },
      {
       "room":"3-Bedroom Villa",
       "price":"$15,620.00"
      }
     ]
  }
  {
    "resort":"Resort2"
    [
      {
       "room":"DStudio",
       "price":"$2,560.00"
      },
      {
       "room":"TVilla",
       "price":"$3,980.00"
      },
      {
       "room":"1-Bedroom Villa",
       "price":"$5,460.00"
      },
      {
       "room":"2-Bedroom LO Villa",
       "price":"$7,040.00"
      },
      {
       "room":"2-Bedroom Villa",
       "price":"$7,040.00"
      },
      {
       "room":"3-Bedroom Villa",
       "price":"$15,620.00"
      }
     ]
  }
]

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, May 14 '21 at 11:27

score 1 · Accepted Answer · edited May 14 '21 at 11:27

Now knowing you created the array you provided, I corrected your original code so it creates the desired format you want:

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);



$return = [];
foreach ($result as $row) {
    $points = $row['points'];
    $price = $points*20;
    $pricefrmt = "$".number_format ($price, 2);
    # You dont need this code anymore:
    #    $return[] = [ 
    #    'resort' => $row['resortname'],
    #    'room' => $row['roomname'],
    #    'price' => $pricefrmt
    #];
    # Just this one line below is all you need
    $return[$row['resortname']][] = ['room' => $row['roomname'], 'price' => $pricefrmt];
}

I'm not sure if you can output the DB table in 3 dimensional arrays, but you sure can just edit the current output like so:

# Declare new array
$newarr = [];

# Iterate through each of the second dimensional arrays as $arr
foreach($return as $arr){

    # Parse said data into the new array
    $newarr[$arr['resort']][] = array("room" => $arr['room'], "price" => $arr['price']);
}

JSON Output/Results:

{
    "Resort1": [
        {
            "room": "DStudio",
            "price": "$2,560.00"
        },
        {
            "room": "TVilla",
            "price": "$3,980.00"
        },
        {
            "room": "1-Bedroom Villa",
            "price": "$5,460.00"
        },
        {
            "room": "2-Bedroom LO Villa",
            "price": "$7,040.00"
        },
        {
            "room": "2-Bedroom Villa",
            "price": "$7,040.00"
        },
        {
            "room": "3-Bedroom Villa",
            "price": "$15,620.00"
        }
    ],
    "Resort2": [
        {
            "room": "DStudio",
            "price": "$2,560.00"
        },
        {
            "room": "TVilla",
            "price": "$3,980.00"
        },
        {
            "room": "1-Bedroom Villa",
            "price": "$5,460.00"
        },
        {
            "room": "2-Bedroom LO Villa",
            "price": "$7,040.00"
        },
        {
            "room": "2-Bedroom Villa",
            "price": "$7,040.00"
        },
        {
            "room": "3-Bedroom Villa",
            "price": "$15,620.00"
        }
    ]
}

Live Demo: http://sandbox.onlinephpfunctions.com/code/98541684aea923463de76183c1e42898c1d47ca5

The sandbox seems to work, and I am sure I am doing something wrong but I get an undefined variable error for $JSON. Do I need to replace $array with what I have as $return? — Bunsen Honeydew, May 14 '21 at 05:06
@BunsenHoneydew Updated the code, I didn't realize you created the array you provided, because in that case you could probably just do it where you created `$return` — Crimin4L, May 14 '21 at 05:11
No problem, but let me see if I can just fix the code where you created `$return` so you dont have this extra code for no reason — Crimin4L, May 14 '21 at 05:13
@BunsenHoneydew check the answer now, you don't need the original code I provided, you can just do it by getting rid of your original `$return` code and using the one I provided above in my updated answer — Crimin4L, May 14 '21 at 05:20
Thank you very much! That works. I just needed to change my print and it worked great. print_r( json_encode($return, JSON_PRETTY_PRINT) ); — Bunsen Honeydew, May 14 '21 at 05:25
Oof, I found an issue. It's combining matching rooms. If I have 2 studios for the same resort, it combines them. Same with the other rooms. I found it because it is throwing the price out of whack. It shows dstudio - standard view but does not show dstudio - preferred view. — Bunsen Honeydew, May 14 '21 at 05:39

PHP PDO Query trying to create subarrays from value

1 Answers1