Options on how to Display member specific data from a MS SQL database

Question

I am in the conceptual stage still but I want to create a word press database that will do the following in the following scenario:

Lets say i have 10 sales people in my store. I want them to be able to: -log into my WordPress site (loginID and password) -once they log in, i want for my website to fetch all the sales they have in my Microsoft SQL database (that I already have) and be displayed for them. (only the sales that belong to them) also display them in an easy to read table.

Can you guys point me in the right direction with this? every time i search wordpress and SQL its always about the SQL database and not on how to reach an external database and display info from that external database. Thanks!

Answer 1

You can create a new wpdb object and connect to an external MySQL database, though there will be a performance cost to making a remote connection, don't expect it to be fast.

But, you mentioned it's an MS SQL database, not MySQL. There won't be a WordPress based mechanism to do this, you'll have to look for generic PHP solutions

You would need to use the Microsoft PHP SQL Drivers to connect to the database, (https://blogs.msdn.microsoft.com/sqlphp/) but keep in mind you will have to write all the code, all the UI, everything, from scratch. There is nothing in WordPress that will be of help. This is assuming your MSSQL database can be accessed remotely, most database servers are not configured this way for security reasons.

Any questions about using MSSQL in PHP would need to go on StackOverflow as they wouldn't be WordPress questions, and finding anybody who uses WordPress and MSSQL would be extremely difficult/rare.

It would be much easier to make requests to a remote API that talked to the MSSQL database, but you would have to build and maintain that API yourself.