PHP and SQL Databases - Understanding CRUD

As a recap from working with SQL databases, we to created the two ‘users’ and ‘professions’ tables, with the ‘user’ (i.e. single record from the ‘users’ table) containing a foreign key to the ‘profession’. All of which we controlled using a SQL database client, e.g. HeidiSQL, phpMyAdmin or other preferred.

I’d like to progress further with working with databases and instead of us accessing and manipulating the database using a client ourselves manually, I’d like for us to tie this together by using a programming language, in our case PHP. By using a server-side language such as PHP to handle all database operations and by developing a front-end jointly, users can then perform actions to the database using a Graphical User Interface (GUI) and no technical/programming experience would be necessary for our end-users.

Using Facebook as an example, the users do not need technical database experience to add, update or delete a post. A GUI/front-end is presented to users and Facebook’s application code then processes the user’s input to a database. Additionally the post is retrieved from the database when another user wants to view the post.

The above Facebook example highlights all the primary operations of developing an application using a database, where the application is able to perform all CRUD operations. The CRUD acronym stands for Create, Read, Update and Delete. (You can read more about it on “Create, read, update and delete” via Wikipedia or by performing a Google search on “CRUD principles”.) These CRUD operations must be executable by the programming code on a database table. For example, on our ‘users’ table CRUD would mandate that a ‘user’ can be:

• Created (i.e. added)
• Read (i.e. viewed)
• Updated (i.e. edited)
• Deleted (i.e. removed).

Some of the above actions should be restricted based on user levels (which I do not expect you to implement at this stage), such as creating, editing and deleting users. However if the database table was regarding ‘posts’ on a forum for example, the user levels would again vary; a post could be created by anyone, read by anyone and updated/deleted only by the author or someone with higher access levels.

By implementing CRUD operations on database tables via a programming language, we have effectively built an application. In a real-world scenario we can create the tables to be specific to our application’s objective and allow the user to interact with them appropriately. CRUD operations are present on all applications which require the storing of data, even the ones we use daily and we may not realise e.g. Uber, Facebook, Instagram, Wikipedia, YouTube, eBay, Amazon and etc.

1. Building CRUD Functionality into our Site

As we have introduced CRUD, I’d like for us to implement this principle into our programming code for the ‘users’ and ‘professions’ tables we created. I’d like you to work on the site we’ve been building on throughout this course from your Github repo.

To implement CRUD functionality, you’re going to have to:

• Firstly using PHP you need to establish a connection to your local database. You may need to create a new file and page for this on your site.
  • Please see the “PHP MySQL Database” via w3schools page for how to connect to a database using PHP. W3Schools also describes additional SQL operations, such as creating a database, inserting records and etc. on the left hand navigation which therefore maybe a useful read if you’re not familiar with them.
  • Please use the .env file we previously created for securely referencing credentials in your programming code, this would include database credentials, e.g. ‘DB_HOST’, ‘DB_NAME’, ‘DB_USER’, ‘DB_PASS’ and etc. You may choose your own variable names in your .env file.
• Next on your site, create two new pages ‘Users’ and ‘Professions’.
• Using a Bootstrap table, on your newly created pages add a table. The table should have the same columns as your database table respectively for the ‘users’ and ‘professions’ (e.g. for users, this could be ‘id’, ‘name’, ‘age’ etc.)
• Now connect to your database using PHP and select all the records from the ‘users’ table (for the users page) and then populate your Bootstrap table with the results. Similarly select all the records from the ‘professions’ table (for the professions page) and populate your Bootstrap table with the results.
  • Now when we visit the ‘Users’ or ‘Professions’ page, all the users and professions should be presented within the table on your site (i.e. the front-end/client-side). We have so far achieved the ‘Read’ part of CRUD.
• On your Bootstrap table add an additional column on the end titled ‘Action’. Then within this column add an ‘Edit’ button.
  • The ‘Edit’ button should link to another PHP file. The link should include a parameter denoting the ID of the record we’re editing, e.g. /edit-user.php?id=1.
• Now create a new PHP file for handling the editing of the record (i.e. one for user and another for profession). This PHP file should connect to the database and get the details of the record currently being edited.
  • This page should present a HTML form with inputs, drop-downs and etc. allowing the user to provide information. The inputs will be related to your table’s columns, e.g. for users this may be ‘Title’, ‘First Name’, ‘Last Name’ and etc. In other words, the input fields will correspond to your table’s columns.
  • Upon saving this form, you should process the user input and update the database record accordingly using PHP.
  • Finally when presenting the HTML form, you should pre-populate the fields with data retrieved from the database. I.e. When clicking to ‘Edit’ a record, the records’ data should be pre-populated on the HTML form.
  • After completing this stage, we have implemented the ‘Update’ ability of the CRUD principle.
• Finally on your ‘Users’ and ‘Professions’ pages, add a button called ‘Add User’ or ‘Add Profession’ respectively. Upon clicking this should take the user to another new page with a HTML form (you can reuse the HTML form you created when building the edit functionality).
  • Clicking ‘Add User’ should present a HTML form to create a new user. You then need to process this form and save the user’s input to the database as a new user. (You should then repeat this for the professions table).
  • By completing this step, you will have implemented the ‘Create’ ability of the CRUD principle.

If you’d like to advance further, you may optionally create the ability to delete records (achieving the ‘Delete’ CRUD principle). This should be relatively simple if you’ve achieved the above.

If you’re stuck, you may need to do your own research online (which will be the best way to learn). There are plenty of guides on how to do all the above.

Also as a useful resource, you can see the “Build a PHP & MySQL CRUD Database App From Scratch” article. This describes an alternative way to connect to MySQL databases in comparison to W3Schools. I’m completely open for you to adopt either method, as ultimately neither are the ideal way to communicate with databases (we will cover better standards when we develop using Laravel).