Jump to content

APIs importing to MySql logic

Go to solution Solved by kicken,

Recommended Posts

Hi all,


I'm working on a project involving an orders set of APIs, and having trouble coming up with the best logic to handle this - with the smallest amount of overhead and client facing slowness.  Here's the jist of what I'm looking at... 

There are 21 API methods to pull different types of orders, and each of them handle paging by requesting a number of records (10 - 500), and a page number.  So I could say in my URI, "page-no=1&no-of-records=50", followed by "page-no=2&no-of-records=50", to get subsequent records.  The API will return in each call the total records returned as well as the total records in their database.

Now, I want to insert all of this into my database, and so that means something like 21 APIs * 3 SQL requests each = 63 sql queries (check it exists, if so update - else insert).


This seems to me like way too much work for a client facing application.  Any suggestions?



Link to comment
Share on other sites

  • Solution

You might be able to use a simple INSERT INTO ... ON DUPLICATE KEY UPDATE query to handle the process rather than having to have three separate queries. You can use that with the multi insert syntax as well so you could just do one insert per page or less. Try and request as many items per page as they will allow to minimize the number of API requests you need to make.


To make it more client friendly you can combine the process with some JS and Ajax requests so you can provide status updates as the items update. If this is something you just need to do periodically rather than in response to some user action you'd probably be better off making it a CLI script and running it via a cron task (or manually).

Link to comment
Share on other sites

This is a Joomla component, and so natively I can't use ON DUPLICATE, but I've thought about putting it into just standard SQL... I just wouldn't have the portability.  I think I'm going to look into running this through a cron plugin, instead of at the user page views.



Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.