Most interactive websites save data in a database. An app with the ability to manipulate database data is often called CRUD – Create, Read, Update, Delete, because these are the functions we use to change the information in the database.
The types of web servers that support this function are usually ones with a full server back-end, as opposed to static website hosts like Neocities. Leprd.space actually offers a really easy way to host your own database app, which I plan to write more about in the future.
Databases store information. The biggest benefit that databases give to your site is a way to store and access organized data.
For this guide, I want to give a basic overview of what a database looks like and how it stores data. The easiest thing to do is just to think of a database as a collection of tables.
Databases
Each database has at least one table, which basically look like spreadsheets or, well, tables.
Every table has a schema, which is the basic structure of columns and data types.
Below is an example table for a webring:
| id | site_name | url | owner_name |
| 1 | Working toward a better internet | https://sadgrl.online | Sadness |
The table above has four columns:
- id
- site_name
- url
- owner_name
Using queries, it’s possible to create new items, read existing items, update items and delete them.
Let’s look at another table in the same database, one which manages the users who can log in to edit the webring.
| id | username | password | |
| 1 | sadness | sadness@sadgrl.online | xxxxxx |
It would look like this!
Notice how both tables have a column dedicated to ID. IDs are incredibly important because each table needs at least one column that is guaranteed to always be unique. This allows us to select an individual entry to delete from the database and know for sure we are selecting the one we intended.
To illustrate more, I’ll give some examples of what database back-ends might look like for storing certain types of data:
Blog:
| id | date | mood | title | content |
| 1 | 2/19/22 | calm | My Blog Title | Here is where my blog entry text is stored! |
Using forms for data manipulation
Keep in mind, the database is only the back-end. It’s difficult to add items directly to a database without a front-end (usually a form) because you need to communicate with the database in a query language. The end-goal of using a language like PHP is to automate the communication with the database in a user-friendly way.
The form that would accompany the blog database above, might look something like this:
- Title: [Input field]
- Post [Large Input Field]
Even fields like checkboxes and select dropdowns can transport data to be inserted into a database.
Database Schema
Aside from column names, every schema has a set of rules that define a column. There are a lot of different rules, so I’ll just outline and define the ones I use the most:
| Name | Description |
| Name | The name of the column |
| Type | The data type of the column (integer, varchar, longtext, etc) |
| Length/Values | The allowed size of data in the column |
| Default | This is a dropdown of possible default values including “null” and custom values |
| Null | Making a column ‘null’ means the row can be updated or created without a value in this field |
| A_I | Used primary for the id column, this automatically increments a number for every entry in the table |
In our blog submission form in the previous section, there’s no input for id because this is an auto-incrementing value. That means each new addition to the database will be assigned the next available ID without us having to do it ourselves.
There’s also no input for the date field because the current date is grabbed via PHP automatically at the time the form is submitted.
phpMyAdmin
The software phpMyAdmin has been so incredibly helpful to me for learning how to use a database. This is basically open-source software you can install on any Apache webserver that acts as a back-end GUI for your database. If you have a site on leprd.space, you’re in luck because it’s part of the package!
For me, personally, it’s extremely difficult to work with a database without a GUI. It’s my impression (please correct me if I’m wrong) that most developers work with DBs through the command line. It’s not the worst, but I’m still in my transition phase where it makes me feel so comforted to just click a “delete” button next to an actual table row and watch it disappear in real time.
With phpMyAdmin, it’s extremely easy to:
- set-up your database schema
- view, edit and delete database entries
- send SQL queries directly to the database
I’m going to include more about how to navigate phpMyAdmin in an upcoming tutorial!