MySQLi 附加更多/更深的结果 [英] MySQLi append more/deeper results
问题描述
我最难解决这个问题，这可能是因为我没有使用正确的术语.如果有人能指出我正确的方向，那就太棒了.我将使用一个假设的情况来简化事情:
我有一个包含两个表的数据库:tableA 包含房屋销售记录(房屋 ID、地址、价格、当前所有者 ID 等)tableB 包含展示房屋的房地产经纪人的记录(房屋 ID、房地产经纪人 ID、时间和日期、注释等).
我想要一个查询，它可以搜索当前的所有者 ID 并下拉他们所有的房子，其中包含有关展示该房子的每个人的信息.我想要检索的是一个 JSON 数组，该数组将每个 tableB 记录的信息附加/附加/添加到 tableA 的单个记录中.
例如，如果我搜索所有者X(拥有两所房子)拥有的房屋，我希望它返回两个主要项目以及表B中每个相关条目的子项目.在下面的例子中，ownerX 有两个房子.位于 1234 Fake St 的第一所房子有 2 个不同的房地产经纪人，总共进行了 3 次访问.555 Nowhere St 的第二套房子有 1 位房地产经纪人来访过两次.
以下是我想要检索信息的方式:
tableA - 结果 1(地址 1234 Fake St 的房子)tableB - 结果 1(房地产经纪人 ID 1234 等)tableB - 结果 2(房地产经纪人 ID 1234 等)tableB - 结果 3(房地产经纪人 ID 2222 等)表 A - 结果 2(地址 555 Nowhere St 的房子)tableB - 结果 1(房地产经纪人 ID 1111 等)tableB - 结果 2(房地产经纪人 ID 1111 等)相反，我得到的是:
tableA - 结果 1(地址 1234 Fake St 的房子)，tableB(房地产经纪人 ID 1234 等)tableA - 结果 2(地址为 1234 Fake St 的房子)，tableB(房地产经纪人 ID 1234 等)tableA - 结果 3(地址为 1234 Fake St 的房子)，tableB(房地产经纪人 ID 2222 等)tableA - 结果 4(地址为 555 Nowhere St 的房子)，tableB(房地产经纪人 ID 2222 等)tableA - 结果 5(地址为 555 Nowhere St 的房子)，tableB(房地产经纪人 ID 2222 等)我不想每次都检索 tableA 信息.我只需要一次，然后是 tableB 的每个子结果.这很重要，因为我要将数据返回到创建新列表的应用程序.我目前正在使用 mysqli_multi_query
$sql = "SELECT * FROM tableA WHERE ownerID = "ownerX";";$sql.= "SELECT tableB.*, tableA.houseID FROM tableB,tableA WHERE tableB.houseID = tableA.houseID;";同样，实际内容只是一个假设.我在寻找更多这样的话，你是个白痴，你应该使用 _____"，而不是你拼错了房地产经纪人，这可能是问题的根源.".
另外，请注意，我并不是要求使用上面的破折号和括号对结果进行格式化.我只是简单地这样写，这样更容易理解.我正在寻找一种在 JSON 数组中包含子对象的方法.
任何为我指明正确方向的帮助将不胜感激！感谢任何花时间尝试这一点的人！托尼
其他信息:这是我用来运行查询的代码:
$sql = "SELECT * FROM clocks WHERE user_key='".$userkey."';";$sql .= "SELECT * FROM 里程碑 WHERE (SELECT clock_key FROM clocks WHERE user_key='".$userkey."') =里程碑.clock_key";如果 (mysqli_multi_query($con,$sql)){做{如果 ($result=mysqli_store_result($con)) {而 ($row=mysqli_fetch_row($result)){$myArray[] = $row;}回声 json_encode($myArray);mysqli_free_result($result);}}while(mysqli_more_results($con) && mysqli_next_result($con));}更新答案:
感谢@vmachan 在下面的帖子，我最终一次获取了所有数据，然后运行了一些循环来调整数组.我将使用上面的房屋/关系者示例.
我用他的代码得到了我的结果($house_id 是一个可变的输入 id):
$sql = "SELECT * FROM tableA INNER JOIN tableB ON tableA.houseID = tableB.houseID WHERE tableA.houseID='".$house_id."';";我得到了一个包含 5 个项目的数组，因为 tableB 有 5 个条目.由于 tableA 中只有 2 个房子条目，它看起来像这样:
["houseID"=>"1","price"=>"50000", "owner" =>迈克 G"，州"=>CA"，房地产经纪人"=>Jane D"、visitDay"=>周二"、笔记"=>他们喜欢房子"],[houseID"=>1"，价格"=>50000"，所有者"=>迈克 G"，州"=>CA"，房地产经纪人"=>Jane D"、visitDay"=>周三"、笔记"=>他们喜欢这座房子"],[houseID"=>1"，价格"=>50000"，所有者"=>迈克 G"，州"=>CA"，房地产经纪人"=>Stephanie W"、visitDay"=>星期五"、笔记"=>他们不喜欢房子"],[houseID"=>2"，价格"=>65000"，所有者"=>米歇尔K"，州"=>AL"，房地产经纪人"=>Mark S"、visitDay"=>Tuesday"、notes"=>他们提出了一个提议"],[houseID"=>2"，价格"=>65000"，所有者"=>米歇尔K"，州"=>AL"，房地产经纪人"=>Jim L"、visitDay"=>星期一"、笔记"=>他们偷了东西"]前 3 个元素来自 tableA，不会改变.所以，我用一个循环来基本检查houseID，如果是新房子，则创建一个新房子数组项，否则，将tableB中的详细信息添加到当前房子元素中:
$rv["名称"],'天' =>$rv["day"],'houseID' =>$rv["houseID"],'笔记' =>$rv["注释"]);//在每次迭代结束时，我们将临时添加到主 $relatorVisitArray.$relatorVisitArray[] = $tempRealitorVisit;}}//此时，子循环已经结束，我们创建了一个包含所有 $tempRealitorVisit 数组的数组 ($relatorVisitArray).//请记住，仍然在第一个循环中并确定这是一所新房子.//现在我们将在本次迭代中基于当前的houseID创建一个新的房子数组.//这个数组是在循环中创建的，因为我们希望它在下一次迭代时确定它是一个新房子.$house = 数组('houseID' =>$item["houseID"],'所有者' =>$item["所有者"],'价格' =>$item["价格"],'位置' =>$item["位置"],'relatorVisits' =>//在这里，我们简单地将 $relatorVisitArray 添加到名为relatorVisits"的键(即数组中的数组).$relatorVisitArray);//然后我们将 $house 添加到 $houseArray.$houseArray[] = $house;//最后，我们将 $currentID 设置为 $item["houseID"].在下一次迭代中，它将根据下一个房屋 ID 检查此 ID.如果它们相同，则整个代码将跳过，直到数据库中出现新的 houseID.$currentID= $item["houseID"];}}//这会打印所有信息，以便于阅读.echo '';打印_r($houseArray);echo '</pre>';}?>
最后，我只剩下一个包含两个子数组的数组.第一个子数组(房屋 1)包含 3 个子数组(对该房屋的 3 次访问).第二个子数组(House 2)包含 2 个子数组(2 次访问那个房子).
我希望这可以帮助任何和我有同样问题的人.如果有人知道更清洁的方法来做到这一点，请在此处发布！谢谢指导！托尼
我认为您可以将如下所示的 SQL 语句组合起来，将 clock_key 上的时钟和里程碑表加入用户提供的值，即 $userkey.然后在您的代码中，您可以遍历结果，然后检查连续的 house_id.
$sql = "SELECT * FROMclocks INNER JOIN里程碑ONclocks.clock_key =里程碑.clock_key WHEREclocks.user_key='".$userkey."';";然后，您可以使用类似于 SO 发布中的代码.您需要更改它，以便在循环内检查先前的house_id"是否与当前的相同，如果不是，您将启动一个新的父数组，否则继续添加到现有数组中.在循环结束时，您可以调用编码来获取您的 JSON 格式.
希望这会有所帮助.
I'm having the hardest time figuring this out and it's probably because I'm not using the correct terms. If someone could point me in the right direction, that would be amazing. I'm going to use a hypothetical situation to make things easier:
I have a database with two tables: tableA contains records for a house sale (house ID,address, price, current owner ID, etc) tableB contains records for realtors who have shown a house (house ID, realtor ID, time and date, notes, etc).
I would like to have a query that can search a current owner ID and pull down all of their houses with information on everyone who showed the house. What I would like to retrieve is a JSON array that has the info from each tableB record appended/attached/added to a single record from tableA.
For example, if I search the the houses that are owned by ownerX (who owns two houses), I would like it to return two main items with sub items for each related entry in tableB. In the example below, ownerX has two houses. The first house on 1234 Fake St had 2 different realtors make a total of 3 visits. The second house on 555 Nowhere St had 1 realtor visit twice.
Here's how I'd like to retrieve the info:
tableA - Result 1 (House at address 1234 Fake St)
tableB - Result 1 (Realtor ID 1234, etc)
tableB - Result 2 (Realtor ID 1234, etc)
tableB - Result 3 (Realtor ID 2222, etc)
tableA - Result 2 (House at address 555 Nowhere St)
tableB - Result 1 (Realtor ID 1111, etc)
tableB - Result 2 (Realtor ID 1111, etc)
Instead, what I'm getting is this:
tableA - Result 1 (House at address 1234 Fake St),tableB(Realtor ID 1234, etc)
tableA - Result 2 (House at address 1234 Fake St),tableB(Realtor ID 1234, etc)
tableA - Result 3 (House at address 1234 Fake St),tableB(Realtor ID 2222, etc)
tableA - Result 4 (House at address 555 Nowhere St),tableB(Realtor ID 2222, etc)
tableA - Result 5 (House at address 555 Nowhere St),tableB(Realtor ID 2222, etc)
I don't don't want to retrieve tableA information each time. I only need that once, then each sub-result from tableB. This is important because I'm returning the data to an app that creates a new list. I'm currently using mysqli_multi_query
$sql = "SELECT * FROM tableA WHERE ownerID = "ownerX";";
$sql. = "SELECT tableB.*, tableA.houseID FROM tableB,tableA WHERE tableB.houseID = tableA.houseID;";
Again, the actual content is just a hypothetical. I'm looking for more of a, "You're an idiot, you should be using _____" and not, "You misspelled realtor and that's probably causing the problem.".
Also, please note that I'm not asking for the results to be formatted with the dashes and parentheses as they are above. I'm just simply writing it that way so it's easier to understand. I'm looking for a way to have sub-objects in a JSON array.
Any help pointing me in the correct direction would be much appreciated! Thanks to whoever takes the time to take a stab at this! Tony
Additional information: Here's the code I'm using to run the query:
$sql = "SELECT * FROM clocks WHERE user_key='".$userkey."';";
$sql .= "SELECT * FROM milestones WHERE (SELECT clock_key FROM clocks WHERE user_key='".$userkey."') = milestones.clock_key";
if (mysqli_multi_query($con,$sql))
{
do
{
if ($result=mysqli_store_result($con)) {
while ($row=mysqli_fetch_row($result))
{
$myArray[] = $row;
}
echo json_encode($myArray);
mysqli_free_result($result);
}
}
while(mysqli_more_results($con) && mysqli_next_result($con));
}
UPDATE WITH ANSWER:
Thanks for @vmachan's post below, I ended up getting all of my data at once, then ran through some loops to adjust the array. I'm going to use the house/relator example from above.
I used his code to get my results ($house_id is a variable input id):
$sql = "SELECT * FROM tableA INNER JOIN tableB ON tableA.houseID = tableB.houseID WHERE tableA.houseID='".$house_id."';";
I was given an array with 5 items because tableB had 5 entries. Since there are only 2 house entries in tableA, it looked like this:
["houseID"=>"1","price"=>"50000", "owner" => "Mike G", "state"=>"CA", "realtor" => "Jane D", "visitDay"=>"Tuesday", "notes" => "They liked the house"],
["houseID"=>"1","price"=>"50000", "owner" => "Mike G", "state"=>"CA", "realtor" => "Jane D", "visitDay"=>"Wednesday", "notes" => "They loved the house"],
["houseID"=>"1","price"=>"50000", "owner" => "Mike G", "state"=>"CA", "realtor" => "Stephanie W", "visitDay"=>"Friday", "notes" => "They didn't like the house"],
["houseID"=>"2","price"=>"65000", "owner" => "Michelle K", "state"=>"AL", "realtor" => "Mark S", "visitDay"=>"Tuesday", "notes" => "They made an offer"],
["houseID"=>"2","price"=>"65000", "owner" => "Michelle K", "state"=>"AL", "realtor" => "Jim L", "visitDay"=>"Monday", "notes" => "They stole stuff"]
The first 3 elements are from tableA and don't change. So, I used a loop to basically check the houseID, if it's a new house, create a new house array item, otherwise, add the details from tableB to the current house element:
<?php
//$house is an array will hold all of our indiviaul houses and their infomation.
$houseArray = array();
//Start the foreach loop
foreach($items as $item){
//$item["houseID"] is the houseID from our database that we got from the above code.
$houseID =$item["houseID"];
//$currentID is a varible that is set after the first iteration.
//This checks to see if we're still working with the same house, or a new house.
if($currentID!=$houseID){
//Create an array to hold all of the relator visit information arrays.
//This is created within the loop as it will erased if a new houseID is found in the array.
$relatorVisitArray = array();
//This is a secondary loop that checks the same array. This time, we are only working with the new houseID that from the condition above.
foreach($items as $rv){
//This cheecks to see if there is a match between the current houseID that we're working with and the other houseIDs in the array. Since we're going through the same array that we're already iterating, it will find itself (which is good).
if($houseID==$rv["houseID"]){
//Once is gets a match, it will create a temporary array to hold the "Relator Visit" information. The array is created within the loop as it needs to be cleared through each iteration.
$tempRealitorVisit = array(
'name' => $rv["name"],
'day' => $rv["day"],
'houseID' => $rv["houseID"],
'notes' => $rv["notes"]
);
//At the end of each iteation, we add the temporary to the main $relatorVisitArray.
$relatorVisitArray[] = $tempRealitorVisit;
}
}
//At this point, the subloop has ended and we're created an array ($relatorVisitArray) which contains all of the $tempRealitorVisit arrays.
//Remember, were are still within the first loop and have determined that this is a new house.
//Now we'll create a new house array based on the current houseID in this iteration.
//This array is created within the loop because we want it to cear at the next iteation when it's determined that it's a new house.
$house = array(
'houseID' => $item["houseID"],
'owner' => $item["owner"],
'price' => $item["price"],
'location' => $item["location"],
'relatorVisits' =>
//Here, we simply add the $relatorVisitArray to a key called, "relatorVisits" (ie an array within an array).
$relatorVisitArray
);
//We then add the $house to the $houseArray.
$houseArray[] = $house;
//Finally, we set $currentID to $item["houseID"]. At the next iteration, it will check this id against the next house ID. If they are the same, this entire code will skip until a new houseID appears from your database.
$currentID= $item["houseID"];
}
}
//This prints all of the information so it's easy to read.
echo '<pre>';
print_r($houseArray);
echo '</pre>';
}
?>
In the end, I'm left with one array that contains two sub arrays. The first sub array (House 1) contains 3 sub arrays (3 visits to that house). The second sub array (House 2) contains 2 sub arrays (2 visits to that house).
I hope this helps anyone that had the same issue as me. If anyone knows of a cleaner way to do this, please post it here! Thanks for the guidance! Tony
I think you can combine the SQL statements as shown below to JOIN the clocks and milestones tables on the clock_key for a user-provided value i.e. $userkey. Then in your code you could loop thru the results and then check for consecutive house_ids.
$sql = "SELECT * FROM clocks INNER JOIN milestones ON clocks.clock_key = milestones.clock_key WHERE clocks.user_key='".$userkey."';";
You can then use the code similar to the one in ths SO posting. You would need to change it so that inside the loop you check if the previous 'house_id' is the same as the current one and if not, you would start a new parent array other wise keep adding to the existing array. At the end of the loop you could then call the encode to get your JSON format.
Hope this helps.
这篇关于MySQLi 附加更多/更深的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
