时间戳

首页 > 解决方案 > PHP PDO查询试图从值创建子数组

php - PHP PDO查询试图从值创建子数组

问题描述

我想弄清楚如何创建（子数组）？我不确定这是否是正确的术语，但我在下面列出了示例。

本质上，我想要每个度假村都有一个数组。在该数组中，我想要每个房间的数组/子数组。

任何帮助表示赞赏。

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

这是当前的输出：

[
  {
   "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"
  }
]

但我希望它看起来更像这样......（如果格式看起来有点奇怪，那是因为我是这个菜鸟）

[
  {
    "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"
      }
     ]
  }
]

标签： phpmysqlarraysjsonpdo

解决方案

现在知道您创建了您提供的数组，我更正了您的原始代码，以便创建您想要的所需格式：

$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];
}

我不确定您是否可以在 3 维数组中输出 DB 表，但您肯定可以像这样编辑当前输出：

# 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 输出/结果：

{
    "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"
        }
    ]
}

现场演示：http ://sandbox.onlinephpfunctions.com/code/98541684aea923463de76183c1e42898c1d47ca5

推荐阅读