You’re may be wondering what exactly CRUD is.
CRUD simply stands for Create, Read, Update and Delete and it is the one of the fundamental principles of programming logic that can be expanded and applied to larger projects.
For example, let’s imagine we’re creating a social network and we like to have the ability for users to create accounts, edit and update information for those accounts and also delete said accounts; that is CRUD at work.
Now let’s get started using PHP & MySQL, to create an app that can store video game titles.
Create the Database
First let’s create a database named mygames to store our data. I’ll be using phpMyAdmin to take care of that but you can use any MySQL database management interface.
Refrain from using spaces or special characters when making names, also to ensure multi-language support change the database collation to utf-8_unicode_ci (case-insensitive).
Now that we have created the database we need to create a table that will have all the necessary fields to input and store our data. We’ll name the table videogames and give this table 7 fields.
The first field will be called id, this will be our unique identifier and will help us know which video game we’re editing, adding or deleting. The type is int(11), which basically means an integer with a length of 11 digits. The last thing we have to do for our id field is to make sure that Auto Increment (A_I) is checked, this function is self-explanatory.
Our second field is called title type will be varchar(255) meaning a string that won’t be longer than 255 characters. Also set the collation of this field to utf-8_unicode_ci.
The next field will be release_date, the only thing we’ll do for this field is set the type to date.
The fourth field, publisher will be a varchar(50) and the collation will be utf- 8_unicode_ci.
Field number five will be called system and this too will be varchar(50) and collation will be utf-8_unicode_ci.
The next field is rating where the type is int(1) and the default value will be 0.
Our final field is called num_players and the type will be int(2) with a default value of 1.
Connect to our Database
Our database is ready to properly store and sort information so just add some entries to your videogames table to give it some data to work with.
Next we need to create a folder for our project so in your local/remote server folder create a folder called mygames. In this folder make another folder called includes, and using the text editor of your choice create a blank PHP file in this folder and name it db.php.
Using this db.php file we will establish a connection between our PHP code and the database we just created. There are a couple of ways we can do this but the most easiest and reusable method in my mind is using a PDO (PHP Data Object). Using this method we can use the same file over and over again, needing only to change a couple things.
This is going to be the structure of our db.php file:
<?php
$dbinfo = 'mysql:dbname=mygames;host=localhost';
$user = 'root';
$pass = 'root';
//If you need to change database information, just change values
above.
$db = new PDO($dbinfo, $user, $pass);
$db->exec('SET CHARACTER SET utf8');The first 3 lines are variables that store our database information (username, password etc.). These values are user specific, so they will change in your case whether you’re using XAMP, WAMP, MAMP or such the like.
The fourth variable $db is our connection to the database; our code will be using this variable to do everything in our code. The last line ensures that we can support all languages by setting the character set to utf8. Now we can go to our index.php file and start adding some code that will communicate with the database.
This is our PHP code block for the index.php page:
<?php
require_once 'includes/filter-wrapper.php';
require_once 'includes/db.php';
$sql = $db->query('SELECT id, title, release_date, publisher, rating,
system, num_players FROM videogames ORDER BY title ASC');
$results = $sql->fetchAll(PDO::FETCH_OBJ);
?>The first line includes a filter wrapper, which I’ll explain more of what it does in part 2 of this tutorial. Depending on what version of PHP you have running on your server you may or may not need this but let’s keep it in there for now.
The second line includes our db.php file that we created earlier, so now on our index page is now connected with our database.
The third line is our query that we make to the database; we’re selecting all the entries in the database by their respective fields and sorting them by their title in alphabetical order.
Now that we have them selected we need to get them out of the database and store it somehow so we can display it on our index page.
Our last line of code takes care of that. Using the variable $results, we fetch all the entries from the database and they are stored in that variable. Think of the results variable receiving a box with all the entries from our database in there. The entries are stored in an array, which gives us the freedom to just display one by its unique identifier (id) or to just display them all.
In part two we’ll create the html needed to display our data and we’ll tackle editing, adding and deleting entries all within our app.
