How can i work with multi dimensional array in php

PHP
1

Please i need your a guidance,
I am working on a JSON data that i encoded into an array and it has 5 array keys and arrays or arrays in each array and i want to insert values from this arrays into the database

// The Json content below

$data = '{"Routes":[],"Quotes":[{"QuoteId":1,"MinPrice":1775.0,"Direct":false,"OutboundLeg":{"CarrierIds":[1530],"OriginId":81727,"DestinationId":66035,"DepartureDate":"2020-08-03T00:00:00"},"QuoteDateTime":"2020-07-30T03:17:00"},{"QuoteId":2,"MinPrice":739.0,"Direct":false,"OutboundLeg":{"CarrierIds":[851],"OriginId":81727,"DestinationId":66035,"DepartureDate":"2020-08-16T00:00:00"},"QuoteDateTime":"2020-07-28T23:46:00"},{"QuoteId":3,"MinPrice":709.0,"Direct":false,"OutboundLeg":{"CarrierIds":[819],"OriginId":81727,"DestinationId":66035,"DepartureDate":"2020-08-19T00:00:00"},"QuoteDateTime":"2020-07-29T08:20:00"},{"QuoteId":4,"MinPrice":706.0,"Direct":false,"OutboundLeg":{"CarrierIds":[819],"OriginId":81727,"DestinationId":66035,"DepartureDate":"2020-08-27T00:00:00"},"QuoteDateTime":"2020-07-30T03:17:00"}],"Places":[{"PlaceId":66035,"IataCode":"LOS","Name":"Lagos","Type":"Station","SkyscannerCode":"LOS","CityName":"Lagos","CityId":"LOSA","CountryName":"Nigeria"},{"PlaceId":81727,"IataCode":"SFO","Name":"San Francisco International","Type":"Station","SkyscannerCode":"SFO","CityName":"San Francisco","CityId":"SFOA","CountryName":"United States"}],"Carriers":[{"CarrierId":819,"Name":"Aegean Airlines"},{"CarrierId":851,"Name":"Alaska Airlines"},{"CarrierId":1530,"Name":"Asiana Airlines"}],"Currencies":[{"Code":"USD","Symbol":"$","ThousandsSeparator":",","DecimalSeparator":".","SymbolOnLeft":true,"SpaceBetweenAmountAndSymbol":false,"RoundingCoefficient":0,"DecimalDigits":2}]}';

Then i did this to convert it into an array
$form = json_decode($data, true);
And then used print_r($form) to print the array and this is what i got

Array ( [Routes] => Array ( ) [Quotes] => Array ( [0] => Array ( [QuoteId] => 1 [MinPrice] => 1775 [Direct] => [OutboundLeg] => Array ( [CarrierIds] => Array ( [0] => 1530 ) [OriginId] => 81727 [DestinationId] => 66035 [DepartureDate] => 2020-08-03T00:00:00 ) [QuoteDateTime] => 2020-07-30T03:17:00 ) [1] => Array ( [QuoteId] => 2 [MinPrice] => 739 [Direct] => [OutboundLeg] => Array ( [CarrierIds] => Array ( [0] => 851 ) [OriginId] => 81727 [DestinationId] => 66035 [DepartureDate] => 2020-08-16T00:00:00 ) [QuoteDateTime] => 2020-07-28T23:46:00 ) [2] => Array ( [QuoteId] => 3 [MinPrice] => 709 [Direct] => [OutboundLeg] => Array ( [CarrierIds] => Array ( [0] => 819 ) [OriginId] => 81727 [DestinationId] => 66035 [DepartureDate] => 2020-08-19T00:00:00 ) [QuoteDateTime] => 2020-07-29T08:20:00 ) [3] => Array ( [QuoteId] => 4 [MinPrice] => 706 [Direct] => [OutboundLeg] => Array ( [CarrierIds] => Array ( [0] => 819 ) [OriginId] => 81727 [DestinationId] => 66035 [DepartureDate] => 2020-08-27T00:00:00 ) [QuoteDateTime] => 2020-07-30T03:17:00 ) ) [Places] => Array ( [0] => Array ( [PlaceId] => 66035 [IataCode] => LOS [Name] => Lagos [Type] => Station [SkyscannerCode] => LOS [CityName] => Lagos [CityId] => LOSA [CountryName] => Nigeria ) [1] => Array ( [PlaceId] => 81727 [IataCode] => SFO [Name] => San Francisco International [Type] => Station [SkyscannerCode] => SFO [CityName] => San Francisco [CityId] => SFOA [CountryName] => United States ) ) [Carriers] => Array ( [0] => Array ( [CarrierId] => 819 [Name] => Aegean Airlines ) [1] => Array ( [CarrierId] => 851 [Name] => Alaska Airlines ) [2] => Array ( [CarrierId] => 1530 [Name] => Asiana Airlines ) ) [Currencies] => Array ( [0] => Array ( [Code] => USD [Symbol] => $ [ThousandsSeparator] => , [DecimalSeparator] => . [SymbolOnLeft] => 1 [SpaceBetweenAmountAndSymbol] => [RoundingCoefficient] => 0 [DecimalDigits] => 2 ) ) )

And when i try getting the array keys using
$arraykey = array_keys($form);
print_r($arraykey);

// This is what i got
Array ( [0] => Routes [1] => Quotes [2] => Places [3] => Carriers [4] => Currencies )

Now my own approach to the problem is shown below even though i got stucked only getting the only them parts by parts which i know is not the bset approach.

foreach($form['Quotes'] as $one){

$quoteid = $one['QuoteId'];
$minprice = $one['MinPrice'];
$direct = $one['Direct'];

// then i did a sql that will insert into database (Note: am not using prepared statement here)
$sql = "INSERT INTO mytable (quoteid, minprice, direct) VALUES ('$quoteid', '$minprice', '$direct')";
mysqli_query($con, $sql);	
}

// Then i did same for $form('Places')
foreach($form['Places'] as $two){

$placeid = $two['PlaceId'];
$IataCode = $two['IataCode'];
$Name = $two['Name'];

// then i did a sql that will insert into database (Note: am not using prepared statement here)
$sql = "INSERT INTO mytable (placeid, iatacode, name) VALUES ('$placeid', '$IataCode', '$Name')";
mysqli_query($con, $sql);	
}

The Below Diagram is how it should look like when inserted into the database, and not adding new extra rows with empty columns making 8rows instead of normal 4 rows with its related data.

```
Here is the Sample Database Structure.
**quoteid | minprice | direct | placeid | iatacode |name**
   1      | $209     | yes    | 22200   |  LOS     | Lagos
   2      | $109     | no     | 12200   |  NYC     | New York
   3      | $409     | yes    | 32200   |  LON     | London
   4      | $309     | yes    | 42200   |  LOS     | Lagos
```

But at this Junction it dawned on me that i took the wrong step because when they got to the database they were not properly aligned.
Please I appreciate your time for reading through and will be grateful if you suggest a better way of doing this.
Thanks in Advance
2

Where is the data actually coming from? Are you putting it into JSON or is that the only way you can get the data?

3

@benanamen am getting the data from skyscanner API using curl and thatā€™s the only format am getting the response in

5

Thanks. Why are you storing data that is already stored instead of using the ā€œliveā€ data for your purposes? Give us the high level overview of what you have going on please.

6

@benanamen thanks for asking, but I need the data in my database so it can blend with my system work flow, as it is like that i donā€™t have control over that data, because is already configured in a certain way, but once i have them in my database i can build custom utilities from it and used for references and flight analysis and also for my online booking website that is under construction

7

I will let benanamen take you through the exercise of why this might be a bad idea, and will address the immediate question in the meantime:

I make the following interpretations from your statements and data:

You wish to store data about quotes.
You need to associate a quote to a place, and store this information in the same table as the quotes. (This is bad practice, but again, I will stick to your desired output for now.)

So, you need to do a single insert per Quote, but you need the information from Places to get all of the data required.

This isnā€™t so much about ā€˜work with multi dimensional arrayā€™, as it is ā€˜filtering an arrayā€™. There are several ways you could go about this. Let me show you a few things about how I might go about this.

Letā€™s examine the structure of a Place.

{"PlaceId":81727
,"IataCode":"SFO"
,"Name":"San Francisco International"
,"Type":"Station"
,"SkyscannerCode":"SFO"
,"CityName":"San Francisco"
,"CityId":"SFOA"
,"CountryName":"United States"}

(For the sake of assumption, I assume that all Places conform to the same data structure.)

We need a PlaceId,
We need the IATA Code,
and we need the Name. Simple enough.

How do we relate (hey, thereā€™s a key word when talking about databasesā€¦) Quotes to Places?

Well, the quoteā€™s OutboundLegā€™s DestinationId should be a Place ID thatā€™s in our data - otherwise, our data doesnā€™t tell us where theā€¦plane? is going. So we can relate a quote to a Place through those shared values.

How do we make that association in PHP? Well, thereā€™s a couple of ways to go about it. Iā€™m going to do some data shaping on our original data blob to make it a bit more palatable to our brains with a function called array_map.

$form['Quotes'] = array_map(function($x) { return array('Id' => $x['QuoteId'],'Price' => $x['MinPrice'],'Direct' => $x['Direct'],'Dest' => $x['OutboundLeg']['DestinationId']); },$form['Quotes']);

This just narrows the data down to the fields iā€™m going to be interested in. You can certainly skip if if you want; it just makes the rest of the code a bit more readable.

Iā€™m also going to reform the Places array. This one iā€™m going to do a bit more directly, because thereā€™s a nice key to this array; the PlaceIds must necessarily be unique.

$places = array();
foreach($form['Places'] AS $place) {
  $places[$place['PlaceId']] = array('IATA'=>$place['IataCode'],'Name'=>$place['Name']); 
};

Now, we want 1 entry for every Quote. So, weā€™re going to walk through the Quotes.

Note: I will deviate from your code here slightly, as my fingers refuse to do a looping query for every single row.

$values = [];
foreach($form['Quotes'] AS $quote) {
   $values[] = "(".$quote['Id'].",".$quote['Price'].",".(int)$quote['Direct'].",".$quote['Dest'].",'".$prices[$quote['Dest']]['IATA']."','".$prices[$quote['Dest']]['Name']."')";
}

Right, so now I have an array of strings the match the layout required. We related quotes to prices by using the ā€˜Destā€™ value of the Quote as a key to the $prices array.

I will now employ array_chunk to insert the rows, 100 at a time. Iā€™m guessing at 100 being a safe number; a query is limited in size, but it is better to do 5 queries than 500; there is overhead of communication every time you contact the database server to do a task.

foreach(array_chunk($values,100) as $valueset) {
  $sql = "INSERT INTO mytable (quoteid,minprice,direct,placeid,iatacode,name) VALUES ".implode(",",$valueset);
  mysqli_query($con,$sql);
}

(Standard rules of data filtering/sanitizing should apply here. Iā€™m glossing those for the moment.)

1 Like
8

Wow a huge thanks @m_hutley your codes always have a touch of excellence in them, Am following the code as to understand them on my own rather than just seeing them work as purposed.
I Love the part you did for places i understand that part but the one you did on quotes array still remains a mistery,
Why canā€™t we use same approach?

Then at the end of this whole awesome guide i will like to know why you considered it a bad idea, scared of me blowing up my database, lol

9

Yes @m_hutley we talk about filtering and sanitization later very important, let me take this work flow and dissect and then respond about my errors and success and possibly further questions, thanks a million times.

10

You can. A foreach works as well. you can array_map, array_walk, foreach, forā€¦ all of them loop over values, its just a question of what they do. I gave you a look at array_map to see a new way of doing things, but feel free to use a foreach.

11

I know but my last use of array_map was a nite mare, was one of the things that came to my mind, array_map and array_combine but wrong use of those functions will make you scared of using them, i even tried flattening array.

Thanks so much, but i want to point out something.

Let me quote it.

12

This $values called above the foreach and then used inside a foreach to create an array was a way of getting those values out of foreach loops so you can use it outside of loop and avoid looping a query?

Why is looping a query bad because i found myself doing that all the time

13

It was to initialize $values to an empty array, so that when i say $values = inside the loop, PHP doesnt have a fit because $values is undefined.

Every time you send a query, your webserver has to send data to the database server (so X microseconds, depending on lag), wait for the database server to do its processing (The database server now needs to open the table record, scan indexes, attempt the insert, determine the result, and form a response. Call this Y microseconds), and then the database server has to send the response to your webserver (Z microseconds, depending on lag).

Send X
Wait Y
Send Z
Send X
Wait Y
Send Z
Send X
Wait Y
Send Z
Send X
Wait Y
Send Z
Send X
Wait Y
Send Z

ā€¦ orā€¦

Send X
Wait N*Y (It will actually be less than that, because the database server doesnā€™t need to open the table multiple times if itā€™s inserting multiple rows, etcā€¦ It can streamline its internal processes.)
Send Z

Now extrapolate that to the number of rows in your tableā€¦ it may not be much, but it can add up.

1 Like
14

Is really a bad idea especially at matters like this insert function which may be upto 2000 or more, i love the chunking part

15

The chunking is to avoid hitting the limit on a queryā€™s length (generally 1 MB in size). Chunking in a small enough chunk (I guessed 100 based on a rough estimate of average name length + the numbers involved) means you get the benefit of minimal queries to the database (reducing overhead), without running the risk of one of the queries being so long that it fails.

16

That said, the airport names are not likely to be a KB long, so chunking at 1000, or even 2000, is probably fine in this case. I didnā€™t know how big your dataset was going to be, so I wanted to show chunking.

17

What about here, was a single insert but was looped because of the chucking?

18

Right - you have to loop over the chunks, so that you send all of the chunks (so all of the rows go in)

but because each chunk represents (in this case, 100) many rows of data, you send 1 query for every X rows, reducing your overhead by a factor of 1/X.

1 Like
19

It means had it been i dont want to chunck i would simply use one insert with an implode of values, i want to echo the $valueset variable to see what it looks like.

20

If your dataset is small enough that you think itā€™ll fit in a single query, yeah, you can simply implode the values array as a whole.

INSERT INTO table(c1,c2) VALUES (x,y),(z,r) is inserting two rows at once. The implode is just a way of taking an array of any size and sticking it together with glue (in this case, a comma) to make it one big string.

1 Like
21

The IataCode and the Name is not being inserted into the database prints errors like undefined variable prices and array offset