How do I build a search connected to an External MySQL Database

By: Zoe Sterling | Asked: 09/27/2023
ForumsCategory: How-toHow do I build a search connected to an External MySQL Database
Zoe SterlingZoe Sterling asked 7 months ago
I want to build front end form that allows the user to search for food items from an external database on one of my other websites.  This is because I want to maintain the food database in one location and not have it copied to all my other clients' websites except for the items they have searched for and added to their daily food entries (i.e. a food tracker). How do I connect to an external MySQL database and get data from the fields and also to be able to search them. And is it possible to make the search box use a barcode scanner? Thanks.
1 Answers
Victor Font answered 7 months ago
It is possible to connect to external databases through the Formidable API, WordPress wp_remote_get(), or ODBC. It depends on the external source and which interface is available in the remote system. As far as a barcode reader, no, not without a custom interface. I would start here: https://www.datasymbol.com/barcode-web-sdk/barcode-scanner-plugin-for-wordpress.html. This will require custom code to integrate with Formidable.
Zoe SterlingZoe Sterling replied 7 months ago

ok, thanks Victor. I don't know any PHP at all, so is wp_remote_get() a PHP script? And does that mean I can't connect to an external database (a) without upgrading to Business edition of Formidable Forms and (b) without PHP knowledge? So I'd need to upgrade and hire a developer?

Victor Font replied 7 months ago

I don't know of any WordPress form tool that allows connections to an external database without some sort of customization. You probably don't need to upgrade Formidable. Since you mentioned searching for an item in a remote DB, AJAX is probably your best approach. AJAX requires custom PHP and custom jQuery. wp_remote_get() is a built-in WordPress function that serves as a wrapper for CURL. It's been available in WordPress since version 2.7. It's part of the WP HTTP API. See this: https://developer.wordpress.org/reference/functions/wp_remote_get/

What you want to do is complex, and how it's done depends on your remote system. You also need to be able to write SQL if the remote system doesn't have a REST API, because then you'll be limited to ODBC.

You can find highly competent Formidable developers here: https://formidable-masterminds.com/developers-directory/

If you'd like to engage my company, you can start with our project application. https://formidable-masterminds.com/project-application/

Zoe SterlingZoe Sterling replied 7 months ago

<p>with wp-forms I can at least pull data from other tables in my wordpress website and not just formidable tables. Can I do this with formidable?</p>

Victor Font replied 7 months ago

Yes, if you know how to write SQL. Formidable Forms is an application development tool. There has never been a requirement that I've had from a client in using Formidable for over a decade that I couldn't meet. I've built enterprise systems with it. It's an incredibly capable tool if you know how to use it.

Zoe SterlingZoe Sterling replied 7 months ago

Excellent! I'm working on a project with a business partner and we want to create a web based version of of a food tracking app like MyFitnessPal or Nutracheck. And we want to put the tracker behind a paywall that only members of our website can use. We have been using Yeken Food Tracker plugin but it is quite basic and this application is the developers hobby, so for us to build a business around someone's hobby plugin wouldn't make sense. So we are looking to build our own. If we can get the majority of it built ourselves, then once we have clients through the door we can look at paying for custom development work.

We have already paid the developer of Yeken to do some custom work for us in the past, but it is a false economy to continue if we plan to do more with this.

I feel like I could get some of the basic functionality working with formidable forms myself. But my sticking point is that my database of food items needs to be kept on its own mysql database so we can update and maintain it there. The developer has written us a plugin to his food tracker that gives us up to 10 API keys that we can use for 10 clients (he said he can add more at a later date), but 10 is more than enough for us currently. So he has adapted the Food Tracker to allow us to put in a web address and an API key and point the search facility to look at the master food database on its own URL and we have installed his API plugin as an addon to give us these 10 API keys on the master food database.

I was hoping to just kind of tap into the database for search purposes. So adding a search box, that allows me to search for a food item and it query's the master food database and retrieves the item, and when the person clicks on 'add to food tracker' it would save it to the website that generated the query in to its own database, recording the food item against the user. Being able to develop the application ourselves would mean that we can quickly make tweaks and changes as we evolve with our customers requirements. Like today I've just been asked to add a calculation to show the recommended grams of sugar per day based on their calorie allowances.

I'm currently using Calculated Form Fields (I thought it was WPForms but its not) free version and it is easy that I can just do this, but with the free version I cannot connect to the existing database to do the calculations and the end user has to enter their calorie allowance for the day in to a box and click on 'calculate'. Whereas, if I could interact with the existing data from the mysql database, I could make this happen automatically.

So I have lots of different pieces of the puzzle and I'm good enough with a database (limited SQL but google helps here), but mostly hindered by my lack of PHP skills!

Sorry, that was a lot of information!

Victor Font replied 7 months ago

Good luck with your project. I’m skeptical as to how far you’ll get without a well rounded development background, but it’s possible if you stick to standards.

Zoe SterlingZoe Sterling replied 7 months ago

Thanks! I feel like I am just on the edge of a breakthrough LOL!

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right