One of my goals here at Rettger Galactic is to make programming fun and accessible to everyone. I showcase a lot of advanced topics, but today I want to get back to basics and show you step by step how to set up a MySQL database and get it working with your website. By the end of this tutorial, you’ll have a good understanding of how a database works, and you’ll have a working example to use as a jumping off point for your own projects.
Let’s start with an overview of databases. I like to think of a database like a file cabinet. If you have a lot of files, you keep them organized with folders. You want your folder names to be specific enough that you know where to find each file, but general enough that you don’t have to make too many folders. Let’s say you have a stack of bills you want to file. You might make a file drawer called “Bills”. Inside that drawer you’ll put a folder for all the types of bills you have. One folder will be for Utilities, one for Credit Cards, one for Mortgage Payments, etc. Now whenever you need to find a bill, you have a good idea which folder it will be in.
Databases are just like file cabinets, only they use tables instead of folders. Your first task when creating a database is deciding which categories make sense for your data. If you sell products on your site, you probably want to make a table called “Products”. If you also allow users to sign up for the site, you’ll need a “Users” table.
As I mentioned before, you don’t want your tables to be too general or too specific. If you own a bike shop, you might be inclined to put all your bikes into a table called “Bicycles”. But one day you decide you’re also going to sell bike helmets. Now you need to make a whole new table just for the bike helmets. Since all your products have the same types of attributes, like a name, a description, and a price, you can save yourself some trouble and put them all in a table called “Products”.
Okay, enough with the theory. Let’s make a database. In fact, let’s make a database for our imaginary bike shop. We’re going to create a page that shows all the products we sell, and lets users submit their own reviews for our products.
I’m using Siteground’s web hosting and user control panel for this tutorial. If you are looking for a place to host your website, I highly recommend them. I’ve hosted my websites there for several years and have always been impressed with their quality and customer service. (Full disclosure: If you sign up through this link It won’t cost you any more but I’ll get a small commission for referring you).
To create a database, log into CPanel on Siteground and click on “MySQL databases” under the Databases category.
Under “Create New Database, type “bike-store-example” and click Create Database. Click “Go Back” when it says it created a database for you.
On the same page, scroll down to “Add New User” and create a user named “dbuser”. Choose a password for your user. (Feel free to use any username and password you like, just make sure to remember what you used later when we enter those in our PHP file). Click “Create User”. Click “Go Back” when it says it created a user for you.
The last step is to add our new user to our new database. On the same page, scroll to where it says “Add User to Database” and add dbuser to the bike-store-example database. Click “Add”. This gives the user we just create permission to read and write to the database we just created.
That’s it! We’ve created a new database for our bike store, and created a user who can access the database. You might be wondering why we had to create a new user. This is for security. You, as the owner of your website, have full permissions to create, modify, and delete all the databases you create and all the items in your databases. When you make a website, you don’t want to give your website the same permissions, so you create a special user for your website to log in as with only the permissions that your website will need. In the event that your website is hacked and somebody gains access to that user, they won’t be able to completely wipe your databases or cause more harm than the permissions that you have allowed them. This is why we give our website’s user account the permissions it needs to do its job, and nothing more.
We learned a lot about the basics of databases in this tutorial. In the next tutorial, we will create our tables and learn how to display all our products on our website.