Rosie. Cute, much?
DBs and DBMS
A database (DB) is a chunk of data about something, like the doggos in a neighborhood. The data just sits there in one or more files. A database management system (DBMS) is software that knows how to read the data in the DB, change the data, delete it, and so on. The DBMS makes the data active.
Your programs won't access DB files directly. Instead, your programs will ask a DBMS to do tasks. That makes our job easier. Easy is good.
There are many DBMS, like SQL Server (Microsoft), Access (also Microsoft), Oracle DBMS (from Oracle), SQLite (open)... the list goes on.
Your Reclaim server is running the database management system MySQL. MySQL is an open source DBMS owned by Oracle. There's a run-by-a-community-because-well-its-Oracle-and-we-don't-fully-trust-them equivalent, MariaDB. MySQL and MariaDB work the same.
You have MySQL on XAMPP as well. See why we're using XAMPP? It's the same stuff that's on your Reclaim server. That easy-is-good thing again.
One DBMS can manage a bunch of databases at the same time. So your server just has one copy of MySQL running, but handles many DBs.
Here's the DBs on my Reclaim server:
There's a bunch. They're all managed by one instance of MySQL.
The screen shot is of a tool called phpMyAdmin. It comes with your Reclaim account, and is part of XAMPP. Later, you'll use it to manage your own DBs.
Tables
All of the DBMSs mentioned store data in tables. Here's the doggo data, a table of rows and columns:
(OK, it's not exactly like this, but let's pretend for now.)
As people move in to the neighborhood, get new doggos, etc., someone would update the data in the table.
Programs, DBs, and DBMS
Here's the server drawing from last time:
Now you know about DBMS, we can add some more detail.
Your programs won't access DBs directly. Instead, they'll ask the DBMS to do the work. You don't have to know how the DBMS does its thing. Just know how to ask it to do something, and get the results.
A common language
Your program and the DBMS need to speak a common language. That's SQL. SQL is by far the most common language used to query databases.
Your program makes SQL queries. For example:
- SELECT * FROM doggos WHERE household = 'Mathieson'
(Not quite right, but OK for now.)
SELECT means "look up data in the doggos table." The * means "give me all the deets (all columns)." WHERE means "rows that match a condition." So the whole thing means "Give me all the data on doggos in the Mathieson household."
Your program sends the SELECT to the DBMS, and gets the results. So as far as DB operations are concerned, your program is the client, and the DBMS is the server.
Remember, both your program and the DBMS are running on your Reclaim server. Yeah, the word "server" gets applied to a lot of things.
Your program makes SELECTs
Your program makes the SELECT from data from the user. Here's what the PHP might look like, kinda:
- $householdId = (Get the household id sent by the client);
- $dbConnection = (Make a connection to the DBMS);
- $query = "SELECT * FROM doggos WHERE household='$householdId'";
- $results = $dbConnection->execute($query);
- (Make HTML showing the results.)
- (Send the HTML to the client.)
That is:
- Take the user's input (line 1).
- Connect to the DBMS (line 2).
- Make a query in a string variable (line 3).
- Send the query to the DBMS, and get the results (line 4).
- Make HTML showing the results (line 5).
- Send the HTML to the browser (line 6).
Check out line 3. $query is just a regular string variable. Put a query into it, and have the DBMS execute it.
Summary
- DBs store data in tables.
- A DBMS is software that manages DBs.
- Your programs interact with DBMSs, not DBs.
- Your programs make SQL statements, and send them to the DBMSs.