that would be because i called it $places in the previous codeblock, and $prices in the next, because i’m forgetful.
Thanks i will change it now,
For each $form[‘Quotes’] section the index you used $quote[‘place’] seems not to exist, please what should be there?
Except I didnt.
This time, you’ve tripped yourself up by using search-and-replace, haven’t you?
Wow thanks a million times, everything works perfectly now, i have fixed the typo
Lol, nop i didn’t, i had to manually edit it
I mistook $quote[‘price’] and made it place and then made $places as place
Thanks alot, i noticed something, since i didn’t finish up the insert function into the database especially the arrays for $form[‘Carriers’] and Currencies.
But i can use this method to work my self home about it.
But then if you look at the json raw data you will notice that only two carriers were returned instead of four carries to match the four quote given, and this means that out of four quotes only two had a known carrier.
So with this code how can i know if it was quote number 4 or 1 or any was the once without any carrier?
As in the case of Direct which values where empty but left us with a blank space indicating the value was empty, which is perfect but for Carrier it left both the keys blank and if we run the code it may add the values to line 1 and 2 but it could be that it was actually 3 and 4 or 1 and 4 were the once with missing Carriers.
What is your suggestions regarding this and how can i handle it?
Hi @pandglobal,
It would be helpful for the thread to know that what you are doing, at least in part, is a booking site.
1 Like
@benanamen you are perfectly right. Yes am doing a flight booking website where i had to call an Skyscanner API and get the response as Json data and then use @m_hutley code to insert it into my database which i can then serve some content on my site using data that i stored in my database instead of calling the API which has a different layout from my site layouts and data or content delivery method.
So i can serve customized and well structured travel content uniquely based on data i have in my database.
Example, suppose you are running a flight research and analysis website or eduction or blog, will you allow visitors to be call the API on each page load or visit and secondly how do you serve them these contents?
So that was the purpose that made me stress @m_hutley knowing he will always deliver me from hitting the rock and yes he did.
Hi @pandglobal,
When we get down to it, you are asking us for help with your attempted solution to solving the “real” problem. Bottom line, your app should not care “where” the data is coming from. Whether it is coming from the actual source data (API) or your soon to be outdated copy of the data from your own database. It makes no difference. You can do the exact same thing using the current live data as you could with your stored copy of the data.
Not exactly, you can’t do same thing with a raw data pulled from API and the one stored in your databases.
I know exactly what am talking about.
Secondly i save much more money in storing it into the database.
Even skyscanner themselves are using cached method.
Now let me show you an illustration.
If you are serving your content from a direct API call and in 5mins you have 100 visitors it means you have made 100 API calls printing about same result.
But if you made one call and save to the database and serve the content to 100000 visitors from your database in one API call within 5 mins.
Supposed those 100000 persons where to get the data live from a API call in one minute how much did you save from using @m_hutley method?
Some API have limits to per minute call which is no problem for less traffic sites but when your per minute visit exceeds the API limit it means more extra minutes of waiting or slow response from your calls.
My data is updated every 5mins not much an outdated data.
Yes, good point about third-party API calls. I hadn’t considered that some api’s will have their own restrictions, limits and expenses.
1 Like
@benanamen yes they do, imagine some API gives 10000 limit calls per month and that means one single 10000 unique visitors will blow that up for you in one day, thats why this caching and database method was born
Please @m_hutley help me restructure this to use foreach loop method, am confused using the array_map method, i think is very okay to flow with what i understand then i can study the mapping method at my leisure time
And we are not forgetting the filtering and sanitation aspects you promised to implement so I stay safe of sql injections
@m_hutley uncle we still have an unfinished business here please how do i sanitize and bind_param to avoid sql injection
The main thing you want to do is to use prepared statements for anything database related. This will prevent things like ‘DROP DATABASE’ from getting into your system. You always want to use prepared statements. Not just for this example but for everything.
You can do sanitation and validation but it’s probably not so important when using public API. But I’ll show you an example.
Start by converting the json data to a php array just like in your original post:
$data = '{"Routes":[],"Quotes":[{"QuoteId":1,"MinPrice":1775.0 blah blah blah
$data = json_decode($data,true);
Now we need to extract the places information so it can be looked up later. This is done by creating an array indexed by placeId:
$places = [];
foreach($data['Places'] as $placeRaw) {
$place = [
'placeId' => $placeRaw['PlaceId'],
'iataCode' => $placeRaw['IataCode'],
'name' => $placeRaw['Name'],
];
$places[$place['placeId']] = $place;
}
Do the same thing for quotes while using the $places for place information:
$quotes = [];
foreach($data['Quotes'] as $quoteRaw) {
$placeId = $quoteRaw['OutboundLeg']['DestinationId'];
$place = $places[$placeId];
$quote = [
'quoteId' => $quoteRaw['QuoteId'],
'minPrice' => filter_var($quoteRaw['MinPrice'],FILTER_SANITIZE_NUMBER_FLOAT),
'direct' => $quoteRaw['Direct'],
'placeId' => $place['placeId'],
'iataCode' => $place['iataCode'],
'name' => $place['name'],
];
$quotes[$quote['quoteId']] = $quote;
}
You may have noticed the filter_var function for min price. filter_var is used for filtering and sanitizing input data. There are a bunch of filter types. The one I used here ensures that doubles only contain numbers or decimal points. I’ll let you decide which filter types are best for your data. I’m not convinced that you really need to do this for this sort of data but you certainly can if you want to.
At this point we have a nice simple array of quotes to be inserted into the database. Start by preparing an insert statement.
$link = mysqli_connect("localhost", "user", "password", "booking");
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$stmt = mysqli_prepare($link,
'INSERT INTO quotes
(quoteid, minprice, direct, placeid, iatacode, name)
VALUES(?,?,?,?,?,?)'
);
All those question marks represent places where you bind data to the statement. SQL injections will not be able to pass through bound data. You can think of a prepared statement as a function to which you will later pass arguments for processing.
Next we need to tell the statement where the ? data is coming from. This is called binding:
// Binding is a bit of a pain with mysqli, PDO does it much better
// Create the variables to bind with fake values
$quoteId = 0;
$minPrice = 0.0;
$direct = false;
$placeId = 0;
$iataCode = 'iata code';
$name = 'destination name';
mysqli_stmt_bind_param($stmt,'idiiss',
$quoteId,$minPrice,$direct,$placeId,$iataCode,$name);
The ‘idiiss’ string indicated the type of data being passed. Int Dec Int Int String String
Once the statement is prepared and bound, it’s just a question of looping through the quotes array and executing the statement:
foreach($quotes as $quote) {
$quoteId = $quote['quoteId'];
$minPrice = $quote['minPrice'];
$direct = $quote['direct'];
$placeId = $quote['placeId'];
$iataCode = $quote['iataCode'];
$name = $quote['name'];
mysqli_stmt_execute($stmt);
}
And that is it. Your database table should be loaded.
2 Likes