How to query more then one databases from one application

The question is too broad.
Are the databases all accessible to the PHP script? If they are different schools they are most likely on different servers in which case you probably won't be able to access them directly.

To access multiple databases is very straight forward you simply create a new connection for each database you are connecting - but as noted above you must be able to access the database from the server your script is running on.

Usually with remote data sources you access the data through an API - one that has been written to provide access to that data.

In some cases the the host providers may open up the ports to the databases on the network - some do some dont  if they do then you can create a db connection to the server as you would any other database - if they don't then you would need to create an API that runs on the remote servers that can return you the data you want - HOWEVER, you want to be careful of this - once you create an API anyone can call those URL's so you want to make sure that you put proper authentication in place to prevent unauthorised access.

The scenario depends upon the situation. you may deploy multiple web server to access single DB or Multiple DB for Multiple Servers. Through single php page  can put into web server like Tomcat or nginx where need to create multiple nodes to access DB through different connection request. Nevertheless through automation like app container can be used to maintain single php page directly update in production server.

i have been doing some research and fund this link https://www.codexworld.com/connect-access-remote-mysql-database-cpanel-php/ 
it is about connecting remote mysql databases, but i am still studying how it works. do you think this is the best way to accomplish the task?

To clarify for everybody, are you trying to create an application in a similar manner to a crm such as salesforce where you have multiple clients (schools) that will log in to view their data on one page but the data needs to be in a separate database?  Or are you developing an application that will access database from different client's(schools) that you may not have control over?

Okay, in case everybody above is overthinking the problem, all the new php database libraries (or at least all the ones I have used) like mysqli allow for multiple database connections. When you create the link you give it a unique name, eg:

$DB1 = mysqli_connect($host1, $username1,$password1,$database1);
$DB2 = mysqli_connect($host2, $username2,$password2,$database2)

Then when you want to run a query against DB1, you use $DB1 as the LINK value eg:

$result = mysqli_query($DB1, "SELECT Student FROM Class LIMIT 10");

You do need to consider the GDPR implications of what you are doing, and make sure that one school cannot access the data of another school, and idiot check every returned value.

@Marco,

Please refer this comment

To access multiple databases is very straight forward you simply create a new connection for each database you are connecting - but as noted above you must be able to access the database from the server your script is running on.

@Julian. Yes, but you didn't explain how, and maybe that was what Moses wants to know. As I said, in case everybody else was over-thinking the question.

The how is irrelevant if he does not have access to the remote databases.

First we establish - can we connect to them - no - end of discussion move on to the API approach
Yes, ok now we can discuss the how.

So I don't think anyone was overthinking anything - it was methodical process of first understanding the landscape before jumping in with a recommendation that may not be possible.

Hi every one, sorry for the late reply. thanks very mush for the contributions

To Scott Fell, EE MVE,
basically what i want to do is to create database applications for schools individually that may be hosted by different providers base on the various schools hosting choices. now what i want to accomplish is to allow each school to querry one another students informations. this will allow one school to access the performance of students coming from other schools to their school. (School "A" should access student table in school "B" and school "B" should access students table in school "A" and so on.....) is there a way to create a script in php to allow that? if so, can you help?

There are multiple parts here.  One is just syncing and the other is getting around privacy policies and that will have to be built into your system.

I think the way to do this is create a central database on your end that can store things such as students, classes taken, sessions (semester vs trimester etc), grades (by class and session).  Create an api that allows schools to post to your site or your site can poll theirs.

Schools have different systems in place and you will want to research which ones they use and determine if there are any public or private api's. If you already have a relationship with a couple of schools, you may want to start there and build for their systems and continue to add on as  needed.

If you are going to use php, I suggest using a micro framework like https://www.slimframework.com/ (example https://www.cloudways.com/blog/simple-rest-api-with-slim-micro-framework/) or https://lumen.laravel.com/

I don't think this is going to work. You are assuming that other schools are going to allow their data to be made available. I would suggest that the way to do this is to create an API interface (with authentication) that exports the required data from the schools database in a common format while leaving the rest of the school's data behind a firewall. In my view this would be the right way of doing this.

EDIT
@Scott - I seemed to have mirrored your post somewhat

thanks very mush guys for your ideas. since i am going to be the one to manage the hosting accounts for the schools that will be using my system, i will have to make sure to talk to host providers to see whether this is possible.

thanks very mush Julian for your assistant.

i am going to be the one to manage the hosting accounts for the schools that will be using my system

Are you managing hosting only for your system and the schools already have another system for their grades somewhere else that you do not have control over OR are you providing hosting for everything and have control?

From what you are saying, it reads like you will just be managing/have control of the account but the hosting is going to be via multiple providers.  If that is the case, you can simply open up the port for the database and scope it to be made available only through localhost or the IP of where your main server is.  Then it is just a matter of adjusting the database server location from localhost to the ip or url:port.  Although the idea of building an api interface instead of accessing the db directly would be a better idea.

you got it right.  i am going to be managing only systems that i built for now, all hosting credentials will be under my control but the hosting providers for various schools may be different. therefore, i need to first talk to hosting providers to see whether it is possible to open up relevant ports to allow connection from a specific ip address to the database.

from study, i learn that configuring  RemoteMysql on each host provider can make that possible even though i have't try it yet.

THANK VERY MUSH FOR ASSISTANT!!!!!!!!!!!!!!!!!!!!!!

Sounds like an API will be the way to go if you are going to do lots of these. That way you have control over what is connecting and how, and you can get some of your own logging in place. It also means that you can restrict what the API serves up. You might also want to consider creating a MySQL user with very restricted access, limited to selects on just the right tables, just in case.

With an API you could set up a list of allowed IP addresses in PHP and get it to only return a valid answer (eg xml or json) when a strict criteria of source IP / username / password / time-date-salt hash / etc is matched.

i need to first talk to hosting providers to see whether it is possible to open up relevant ports to allow connection from a specific ip address to the database.

Before you do this, please take some time to understand the risks of what you are doing. If you open the databases to the outside world it needs to be done properly - make sure you set it up so that connections are only allowed from a specified IP.

Even so, I would be very cautious about doing it this way. With an API you control exactly what data is accessed / updated and how without exposing the raw database.

Another advantage of API's is that you can present a common interface on the public facing side. This is useful if the databases you are connecting to do not store data in the same way - the API can be customised for each installation so that the internal interface retrieves data from the database using custom requests. The API then massages the data into a common external format. This will yield significant benefit to the receiving application as it gets a consistent data stream from each data source - makes processing much easier - it also acts as a buffer against any changes any particular source may make to their systems. If a school upgrades or migrates to a different system then only the API at that school has to change.

Julian, that is exactly what i want to do - create an  API to do the job to avoid security threat ,but i am actually new in programming and don't  know how to go about doing this.

An API is all about inputs and outputs.

What do you want the API to return and what do you need to give it in order to get you the data you are interested in.

So in its simplest form let's say you create an API to return a list of students
http://yourserver/api/getStudents

On the server you setup your scripts and routing so that the above URL is routed to a script / function that can return your data.
Your function should get passed a database connection - or has access to one through a class property

function getStudents($db)
{
   $query = "SELECT * FROM students"'
   $result = $db->query($query);
   $students = [];
   while($row = $result->fetch_object()) {
        $item = (object) array(
           'firstname' => $row->student_first_name,
           'lastname' => $row->student_last_name
         );
       $students[] = $item;
   }    
   die(json_encode($students));
}

Select allOpen in new window

This is just an illustration, obviously there would need to be some code that routes the request, checks the authentication / authorisation and then passes control to the function that actually returns the results.
The point of this example is to show how you take results from the DB and mutate them into an external representation.

You could have this function run on another server where student_first_name and student_last_name are firstname and lastname - but you still return the results using the same external format.

There are many frameworks out there that will allow you to build an API. It is not actually that difficult you need
* .htaccess to route requests to a common entry point
* a bootstrap script that
  - extracts parameters from the request,
  - determines what object / script to give them to based on
      -  request method
      -  parameters passed
  - checks to see if the requester has authenticated
  - checks to see if the requester is authorised to access this function
  - builds a database connection to inject into the handler
* Scripts to process the various requests.

I am sure if you look you can find API boilerplate code out there (PHP REST would be terms to search for). I don't know of any offhand as I have created my own API framework which works well for my needs.

i want the query to do something like this from remote servers: select * from studentgrade where stschool = '$studentschool' and stclass = '$studentclass' and year ='$acadamicyear' stid='$studentid';

the variables there will be fetching data from post.
 
if school "A" is selected, send the query to school "A" server; if school "B" is selceted, send the query to school "B" server; if school "C" is selected, send the query to school "C" server...

upon running the query, i want the request to go to that remote server and fetch the data. i want this to work to stop students from criminally changing their report cards while changing schools

i try the link you send but it's not working.

I did not send a link - that was a sample to demonstrate a concept.

On school A you would setup a script on a particular URL - lets say it is
http://schoolA/api/getStudents
You would write a script in whatever script you choose (say PHP as this is a PHP thread). The PHP script listens for requests. When it is activated it connects to the server - runs the query to get the students, formats the result into a common format and sends the data back (usually as JSON)
On the receiving side you call the script using the URL above (changed obviously to match the actual endpoint) - get the return data and process it.
You do the same for School B etc.

This thread is getting a bit long though -if you need to know more about writing API's I suggest you open another thread.