Looking to Integrate an External Database via API Query

  • Resolved bammichael

    (@bammichael)


    2 years, 5 months ago

    I’m not a regular software developer, so I need some advice and guidance on a change we wish to make.

    We used to run a commerce site on Magento, but overall it is incredibly inefficient and very unstable. It also guzzles resources and costs a boatload to maintain, both from hosting and maintenance costs.

    We wish to evaluate using WordPress as a solution, but without WooCommerce. We currently store our backend product data in a database with Knack, and right now Authorize.net is our merchant for backend payments. I was wondering if it would be possible to have WordPress send search queries to our Knack database to look for matches (with filtered conditions) via API requests, then return data from the records directly to WordPress to display on a page. This would include files, images, data, and possibly custom displays like a map file. We also would like to be able to cache/store selected products into a cart, and then hand off the payment to a merchant service like Authorize.net or Paypal.

    We haven’t decided if we want to allow user logins on our website at this time. Seems like a good idea, but we also issue a separate login for additional payments and we don’t want to overwhelm our customers.

    I’m not very practiced at coding WordPress, but I’d be open to documentation/explanation if there exists an avenue currently for our proposed solution. Any advice on this would be greatly appreciated.

    Thanks,
    Mike

    • This topic was modified 2 years, 5 months ago by bammichael.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator bcworkz

    (@bcworkz)

    2 years, 5 months ago

    If the external API is accessible via HTTPS, then WP can utilize it to get data, process it, and display it to end users. However, unless the API is a very common one, you’ll likely need to custom code the interaction. For example, there are existing plugins which interact with some of the more common Google APIs. For Knack I’m not so sure. It’s always worth doing a search before embarking on a custom coding project.

    The built-in WP_HTTP class will let your code make any sort of HTTP(S) request that’s needed to get API data. Often the trickiest part of API access is getting authorized to retrieve data. Other times getting data is free and open, authorization is only needed to write data. In some cases, an existing plugin may offer a partial solution that your custom code can build upon. For example, oAuth authorization can be difficult to implement with custom code, but there are oAuth plugins that will help with at least the authorization part of API interaction.

    Similarly, there are plugins for authorize.net and PayPal APIs. Sadly, many are specifically for WooCommerce. Standalone payment gateways are much less common. There are many who have implemented WooCommerce simply to get access to its payment gateways. Not very efficient, but easier than building a custom app.

    Thread Starter bammichael

    (@bammichael)

    2 years, 5 months ago

    We also want to go about this practically, especially since issues like “load times” can complicate things and discourage users from buying products.

    The main reason we don’t want to go WooCommerce is simply this: we don’t want two databases of products. Unless there existed a mechanism to make WooCommerce always 100% sync up with Knack’s record data, it would never work out for us.

    Knack has api keys, so the authorization would need those keys. There is an app key and a private key. I’m unsure how to hide the private key at this time, haven’t researched this extensively yet. Then again, we do other work in this database, so we cannot allow public access to everything.

    Regardless, if Authorize.net won’t work without a solution like WooCommerce, that doesn’t really leave us an “other” option.

    How difficult and practical (or unpractical) would it be to look into some kind of external server script to perform maybe 4-12 syncs a day, downloading data out of Knack and syncing it directly into the WooCommerce database? Maybe we run a small virtual server via one of our machines or a cloud solution like Amazon to perform this sync maintenance for us? It only needs to go one way, the only time we’d need to go the other way would be making sure a product is hidden on a sale (only one of each), we have triggers in our Knack database to determine this.

    • This reply was modified 2 years, 5 months ago by bammichael.
    • This reply was modified 2 years, 5 months ago by bammichael.
    • This reply was modified 2 years, 5 months ago by bammichael.
    Moderator bcworkz

    (@bcworkz)

    2 years, 5 months ago

    Sending keys with the request via the WP_HTTP class is relatively simple. Because it’s server to server communication, any private key can be securely sent over HTTPS. It can be safely stored somewhere protected on the server or even hard coded into custom PHP. The WP server acts as a sort of “gate keeper”, adding, removing, or altering data as it passes back and forth between the client app and remote DB.

    It looks like there are at least some non-WooCommerce options for authorize.net:
    https://duckduckgo.com/?q=wordpress+authorize.net+-woocommerce&ia=web

    I’ve seen apps that sync WooCommerce products with external DBs. IDK if there is one for a Knack DB. It is feasible to develop custom sync code for the purpose. It can actually run within WP. WP will let you schedule recurring events much like server cron jobs. The events are triggered by common HTTP requests, so a certain minimal level of traffic is required. Otherwise one could create an actual server cron job.

    If you’re willing to develop custom code, then what’s possible in WP is practically unlimited, only restricted by what’s possible with PHP and client side scripting.

    Thread Starter bammichael

    (@bammichael)

    2 years, 5 months ago

    I wonder if at this point, just writing some kind of script on one of our Windows Server machines and having it “GET” from Knack and “PUT” into WordPress would be the most effective route. I’m not a seasoned webdev, so I don’t trust that I could properly cloak our application API data. I’d rather hide the process inside our company network on a protected machine, instead of on a WordPress machine that could become an attack target.

    Is WooCommerce part of the WordPress SQL database? Where do you recommend I look for the documentation on putting/updating/deleting records in WooCommerce via API calls?

    Also thanks a ton for the help, we don’t have an in-house software developer and I serve as the IT Admin for our business. We aren’t huge, so I imagine I’ll be a jack-of-all-trades by the end of the year haha.

    Moderator bcworkz

    (@bcworkz)

    2 years, 5 months ago

    With APIs it doesn’t really matter where the script is located that’s driving a sync operation.

    WooCommerce data is saved in the WP DB. A lot of it are post types of one type or another: products and orders. There are also a number of WooCommerce specific tables created on installation.

    WooCommerce has its own REST API you can use to update product data.
    https://woocommerce.com/document/bundles-rest-api-reference/

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Looking to Integrate an External Database via API Query’ is closed to new replies.