Siddharth Lele
Reputation: 27748
PHP Structure To Create An Array Within Array (JSON)
I am currently building an API for an Android Application and my PHP skills aren't too hot. The API I am trying to create returns Pet Adoption listing from a specific city and that result is paginated. In isolation, this part works fine. What has me stuck is the Promoted Adoption listings. Both the adoptions and promoted adoptions come from different MySQL tables.
The current code (posted below) returns the data like this:
{
"error": false,
"totalPages": 2,
"pageNumber": 1,
"adoptions": [{
"adoptionID": "1",
"petTypeID": "1",
"petTypeName": "Dog",
"breedID": "126",
"breedName": "German Shepherd Dog",
"userID": "9",
"userName": "Siddharth Lele",
"cityID": "172",
"cityName": "Pune",
"adoptionName": "Test Pet #1",
"adoptionCoverPhoto": "https://firebasestorage.googleapis.com/v0/b/zen-pets-4c786.appspot.com/o/Adoption%20Covers%2Ftest_pet_9?alt=media&token=553e1159-4a0b-4514-ba49-d0698b76b925",
"adoptionDescription": "Just testing the new adoption creator to check how the content fits with the new layout. Now checking how it handles multiple lines in the Adoption listing's description...",
"adoptionGender": "Male",
"adoptionTimeStamp": "1529302079",
"adoptionStatus": "Open"
},
{
"adoptionID": "2",
"petTypeID": "2",
"petTypeName": "Cat",
"breedID": "289",
"breedName": "Abyssinian",
"userID": "9",
"userName": "Siddharth Lele",
"cityID": "172",
"cityName": "Pune",
"adoptionName": "Test Pet #2",
"adoptionDescription": "Just testing the new adoption creator...",
"adoptionGender": "Female",
"adoptionTimeStamp": "1529302079",
"adoptionStatus": "Open"
}
],
"totalPromoted": "12",
"totalPromotionPages": 4,
"promotionPageNumber": 1,
"promotions": [{
"promotedID": "3",
"adoptionID": "3",
"optionID": "3",
"paymentID": "pay_ARtc4aZ7V1M69o",
"promotedFrom": "2018-06-26",
"promotedTo": "2018-07-26",
"promotedTimestamp": "1529996304",
"petTypeID": "1",
"petTypeName": "Dog",
"breedID": "126",
"breedName": "German Shepherd Dog",
"userID": "9",
"userName": "Siddharth Lele",
"cityID": "172",
"cityName": "Pune",
"adoptionName": "Test Pet #3",
"adoptionDescription": "Just testing the new adoption creator...",
"adoptionGender": "Female",
"adoptionTimeStamp": "1529302079",
"adoptionStatus": "Open"
}
]
}
The PHP Code is:
$queryCount = $conn->prepare('
SELECT COUNT(*) AS totalAdoptions
FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \'Open\'
AND adoptions_test.cityID = ?');
$queryCount->bindParam(1, $cityID);
if ($queryCount->execute()) {
$adoptionCount = $queryCount->fetch();
$response = array();
if ($adoptionCount != null) {
$response["error"] = false;
$response["totalAdoptions"] = $adoptionCount["totalAdoptions"];
$query = $conn->prepare("SELECT * FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
LIMIT " . $offsetNumber . ", " . $itemsPerPage);
$query->bindParam(1, $cityID);
if ($query->execute()) {
$response = array();
$response["error"] = false;
$response["totalPages"] = ceil($adoptionCount["totalAdoptions"] / $itemsPerPage);
$response["pageNumber"] = ceil($pageNumber);
$adoptions = $query->fetchAll();
if ($adoptions != null) {
$response["adoptions"] = array();
foreach ($adoptions as $adoption) {
$tmp = array();
$tmp["adoptionID"] = $adoption["adoptionID"];
$tmp["petTypeID"] = $adoption["petTypeID"];
$tmp["petTypeName"] = $adoption["petTypeName"];
$tmp["breedID"] = $adoption["breedID"];
$tmp["breedName"] = $adoption["breedName"];
$tmp["userID"] = $adoption["userID"];
$tmp["userName"] = $adoption["userName"];
$tmp["cityID"] = $adoption["cityID"];
$tmp["cityName"] = $adoption["cityName"];
$tmp["adoptionName"] = $adoption["adoptionName"];
$tmp["adoptionCoverPhoto"] = $adoption["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $adoption["adoptionDescription"];
$tmp["adoptionGender"] = $adoption["adoptionGender"];
$tmp["adoptionTimeStamp"] = $adoption["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $adoption["adoptionStatus"];
array_push($response["adoptions"], $tmp);
}
$queryPromotedCount = $conn->prepare('SELECT COUNT(*) AS totalPromoted
FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
WHERE adoptions_test.cityID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo');
$queryPromotedCount->bindParam(1, $cityID);
if ($queryPromotedCount->execute()) {
$promotedCount = $queryPromotedCount->fetch();
if ($promotedCount != NULL) {
$response["totalPromoted"] = $promotedCount["totalPromoted"];
$queryPromoted = $conn->prepare("SELECT * FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptions_test.adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo
LIMIT " . $promotedOffsetNumber . ", " . $promotedItemsPerPage);
$queryPromoted->bindParam(1, $cityID);
if ($queryPromoted->execute()) {
$response["error"] = false;
$response["totalPromotionPages"] = ceil($promotedCount["totalPromoted"] / $promotedItemsPerPage);
$response["promotionPageNumber"] = ceil($pageNumber);
$promotions = $queryPromoted->fetchAll();
if ($promotions != NULL) {
$response["promotions"] = array();
foreach ($promotions as $promotion) {
$tmp = array();
$tmp["promotedID"] = $promotion["promotedID"];
$tmp["adoptionID"] = $promotion["adoptionID"];
$tmp["optionID"] = $promotion["optionID"];
$tmp["paymentID"] = $promotion["paymentID"];
$tmp["promotedFrom"] = $promotion["promotedFrom"];
$tmp["promotedTo"] = $promotion["promotedTo"];
$tmp["promotedTimestamp"] = $promotion["promotedTimestamp"];
$tmp["petTypeID"] = $promotion["petTypeID"];
$tmp["petTypeName"] = $promotion["petTypeName"];
$tmp["breedID"] = $promotion["breedID"];
$tmp["breedName"] = $promotion["breedName"];
$tmp["userID"] = $promotion["userID"];
$tmp["userName"] = $promotion["userName"];
$tmp["cityID"] = $promotion["cityID"];
$tmp["cityName"] = $promotion["cityName"];
$tmp["adoptionName"] = $promotion["adoptionName"];
$tmp["adoptionCoverPhoto"] = $promotion["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $promotion["adoptionDescription"];
$tmp["adoptionGender"] = $promotion["adoptionGender"];
$tmp["adoptionTimeStamp"] = $promotion["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $promotion["adoptionStatus"];
array_push($response["promotions"], $tmp);
}
}
}
}
}
} else {
$response["error"] = true;
$response["message"] = "There are no open Adoptions available. Please try again";
}
/** SHOW THE RESULT **/
$content_type = 'application/json';
return $result->withStatus(200)
->withHeader('Content-type', $content_type)
->write(json_encode($response));
}
}
}
What I want is that the following structure
"totalPromoted": "12",
"totalPromotionPages": 4,
"promotionPageNumber": 1,
"promotions": [{
to be inside the "adoptions" tag so that the "promoted" array goes inside the "adoptions" array. How do I do that?
Upvotes: 0
Views: 53
Answers (2)
Dharmesh Patel
Reputation: 1891
Following code will do the trick for you:
$queryCount = $conn->prepare('
SELECT COUNT(*) AS totalAdoptions
FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \'Open\'
AND adoptions_test.cityID = ?');
$queryCount->bindParam(1, $cityID);
if ($queryCount->execute()) {
$adoptionCount = $queryCount->fetch();
$response = array();
$tempPromotions = array();
$tempPromotions['promotions'] = array();
if ($adoptionCount != null) {
$response["error"] = false;
$response["totalAdoptions"] = $adoptionCount["totalAdoptions"];
$query = $conn->prepare("SELECT * FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
LIMIT " . $offsetNumber . ", " . $itemsPerPage);
$query->bindParam(1, $cityID);
if ($query->execute()) {
$response = array();
$response["error"] = false;
$response["totalPages"] = ceil($adoptionCount["totalAdoptions"] / $itemsPerPage);
$response["pageNumber"] = ceil($pageNumber);
$adoptions = $query->fetchAll();
if ($adoptions != null) {
$response["adoptions"] = array();
foreach ($adoptions as $adoption) {
$tmp = array();
$tmp["adoptionID"] = $adoption["adoptionID"];
$tmp["petTypeID"] = $adoption["petTypeID"];
$tmp["petTypeName"] = $adoption["petTypeName"];
$tmp["breedID"] = $adoption["breedID"];
$tmp["breedName"] = $adoption["breedName"];
$tmp["userID"] = $adoption["userID"];
$tmp["userName"] = $adoption["userName"];
$tmp["cityID"] = $adoption["cityID"];
$tmp["cityName"] = $adoption["cityName"];
$tmp["adoptionName"] = $adoption["adoptionName"];
$tmp["adoptionCoverPhoto"] = $adoption["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $adoption["adoptionDescription"];
$tmp["adoptionGender"] = $adoption["adoptionGender"];
$tmp["adoptionTimeStamp"] = $adoption["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $adoption["adoptionStatus"];
array_push($response["adoptions"], $tmp);
}
$queryPromotedCount = $conn->prepare('SELECT COUNT(*) AS totalPromoted
FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
WHERE adoptions_test.cityID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo');
$queryPromotedCount->bindParam(1, $cityID);
if ($queryPromotedCount->execute()) {
$promotedCount = $queryPromotedCount->fetch();
if ($promotedCount != NULL) {
$tempPromotions["totalPromoted"] = $promotedCount["totalPromoted"];
$queryPromoted = $conn->prepare("SELECT * FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptions_test.adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo
LIMIT " . $promotedOffsetNumber . ", " . $promotedItemsPerPage);
$queryPromoted->bindParam(1, $cityID);
if ($queryPromoted->execute()) {
$response["error"] = false;
$tempPromotions["totalPromotionPages"] = ceil($promotedCount["totalPromoted"] / $promotedItemsPerPage);
$tempPromotions["promotionPageNumber"] = ceil($pageNumber);
$promotions = $queryPromoted->fetchAll();
if ($promotions != NULL) {
foreach ($promotions as $promotion) {
$tmp = array();
$tmp["promotedID"] = $promotion["promotedID"];
$tmp["adoptionID"] = $promotion["adoptionID"];
$tmp["optionID"] = $promotion["optionID"];
$tmp["paymentID"] = $promotion["paymentID"];
$tmp["promotedFrom"] = $promotion["promotedFrom"];
$tmp["promotedTo"] = $promotion["promotedTo"];
$tmp["promotedTimestamp"] = $promotion["promotedTimestamp"];
$tmp["petTypeID"] = $promotion["petTypeID"];
$tmp["petTypeName"] = $promotion["petTypeName"];
$tmp["breedID"] = $promotion["breedID"];
$tmp["breedName"] = $promotion["breedName"];
$tmp["userID"] = $promotion["userID"];
$tmp["userName"] = $promotion["userName"];
$tmp["cityID"] = $promotion["cityID"];
$tmp["cityName"] = $promotion["cityName"];
$tmp["adoptionName"] = $promotion["adoptionName"];
$tmp["adoptionCoverPhoto"] = $promotion["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $promotion["adoptionDescription"];
$tmp["adoptionGender"] = $promotion["adoptionGender"];
$tmp["adoptionTimeStamp"] = $promotion["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $promotion["adoptionStatus"];
$tempPromotions['promotions'][] = $tmp;
}
}
}
}
}
array_push($response["adoptions"], $tempPromotions);
} else {
$response["error"] = true;
$response["message"] = "There are no open Adoptions available. Please try again";
}
/** SHOW THE RESULT **/
$content_type = 'application/json';
return $result->withStatus(200)
->withHeader('Content-type', $content_type)
->write(json_encode($response));
}
}
}
Upvotes: 1
IVO GELOV
Reputation: 14259
$queryCount = $conn->prepare('
SELECT COUNT(*) AS totalAdoptions
FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \'Open\'
AND adoptions_test.cityID = ?');
$queryCount->bindParam(1, $cityID);
if ($queryCount->execute()) {
$adoptionCount = $queryCount->fetch();
$response = array();
if ($adoptionCount != null) {
$response["error"] = false;
$response["totalAdoptions"] = $adoptionCount["totalAdoptions"];
$query = $conn->prepare("SELECT * FROM adoptions_test
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
LIMIT " . $offsetNumber . ", " . $itemsPerPage);
$query->bindParam(1, $cityID);
if ($query->execute()) {
$response = array();
$response["error"] = false;
$response["totalPages"] = ceil($adoptionCount["totalAdoptions"] / $itemsPerPage);
$response["pageNumber"] = ceil($pageNumber);
$adoptions = $query->fetchAll();
if ($adoptions != null) {
$response["adoptions"] = array();
$refs = Array();
foreach ($adoptions as $adoption) {
$tmp = array();
$tmp["adoptionID"] = $adoption["adoptionID"];
$tmp["petTypeID"] = $adoption["petTypeID"];
$tmp["petTypeName"] = $adoption["petTypeName"];
$tmp["breedID"] = $adoption["breedID"];
$tmp["breedName"] = $adoption["breedName"];
$tmp["userID"] = $adoption["userID"];
$tmp["userName"] = $adoption["userName"];
$tmp["cityID"] = $adoption["cityID"];
$tmp["cityName"] = $adoption["cityName"];
$tmp["adoptionName"] = $adoption["adoptionName"];
$tmp["adoptionCoverPhoto"] = $adoption["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $adoption["adoptionDescription"];
$tmp["adoptionGender"] = $adoption["adoptionGender"];
$tmp["adoptionTimeStamp"] = $adoption["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $adoption["adoptionStatus"];
$refs[$adoption["adoptionID"]] = &$tmp;
array_push($response["adoptions"], $tmp);
}
$queryPromotedCount = $conn->prepare('SELECT adoption_promoted.adoptionID,COUNT(*) AS totalPromoted
FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
WHERE adoptions_test.cityID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo
GROUP BY adoption_promoted.adoptionID');
$queryPromotedCount->bindParam(1, $cityID);
if ($queryPromotedCount->execute()) {
$promotedCount = $queryPromotedCount->fetchAll();
if ($promotedCount != NULL) {
foreach($promotedCount as $promoted)
{
$adoptionID = $promoted["adoptionID"];
$refs[$adoptionID]["totalPromoted"] = $promoted["totalPromoted"];
$queryPromoted = $conn->prepare("SELECT * FROM adoption_promoted
INNER JOIN adoptions_test ON adoption_promoted.adoptionID = adoptions_test.adoptionID
INNER JOIN pet_type ON adoptions_test.petTypeID = pet_type.petTypeID
INNER JOIN breeds ON adoptions_test.breedID = breeds.breedID
INNER JOIN users ON adoptions_test.userID = users.userID
INNER JOIN cities ON adoptions_test.cityID = cities.cityID
WHERE adoptions_test.adoptionStatus = \"Open\"
AND adoptions_test.cityID = ?
AND adoption_promoted.adoptionID = ?
AND CURDATE() BETWEEN promotedFrom AND promotedTo
LIMIT " . $promotedOffsetNumber . ", " . $promotedItemsPerPage);
$queryPromoted->bindParam(1, $cityID);
$queryPromoted->bindParam(2, $adoptionID);
if ($queryPromoted->execute()) {
$response["error"] = false;
$refs[$adoptionID]["totalPromotionPages"] = ceil($promoted["totalPromoted"] / $promotedItemsPerPage);
$refs[$adoptionID]["promotionPageNumber"] = ceil($pageNumber);
$promotionsList = $queryPromoted->fetchAll();
if ($promotionsList != NULL) {
$refs[$adoptionID]["promotions"] = array();
foreach ($promotionsList as $promotion) {
$tmp = array();
$tmp["promotedID"] = $promotion["promotedID"];
$tmp["adoptionID"] = $promotion["adoptionID"];
$tmp["optionID"] = $promotion["optionID"];
$tmp["paymentID"] = $promotion["paymentID"];
$tmp["promotedFrom"] = $promotion["promotedFrom"];
$tmp["promotedTo"] = $promotion["promotedTo"];
$tmp["promotedTimestamp"] = $promotion["promotedTimestamp"];
$tmp["petTypeID"] = $promotion["petTypeID"];
$tmp["petTypeName"] = $promotion["petTypeName"];
$tmp["breedID"] = $promotion["breedID"];
$tmp["breedName"] = $promotion["breedName"];
$tmp["userID"] = $promotion["userID"];
$tmp["userName"] = $promotion["userName"];
$tmp["cityID"] = $promotion["cityID"];
$tmp["cityName"] = $promotion["cityName"];
$tmp["adoptionName"] = $promotion["adoptionName"];
$tmp["adoptionCoverPhoto"] = $promotion["adoptionCoverPhoto"];
$tmp["adoptionDescription"] = $promotion["adoptionDescription"];
$tmp["adoptionGender"] = $promotion["adoptionGender"];
$tmp["adoptionTimeStamp"] = $promotion["adoptionTimeStamp"];
$tmp["adoptionStatus"] = $promotion["adoptionStatus"];
array_push($refs[$adoptionID]["promotions"], $tmp);
}
}
}
}
}
}
} else {
$response["error"] = true;
$response["message"] = "There are no open Adoptions available. Please try again";
}
/** SHOW THE RESULT **/
$content_type = 'application/json';
return $result->withStatus(200)
->withHeader('Content-type', $content_type)
->write(json_encode($response));
}
}
}
Upvotes: 0
Related Questions
- create a json object containing an array of json object
- How to create this JSON structure in PHP?
- PHP - create a JSON object with multiple arrays
- Creating nested json with php
- Creating json array with php
- php - Create a JSON array with objects
- Create array for Data JSON
- Creating a JSON Array in PHP
- Create a json object with array of arrays in PHP
- array structure help in php to generate json