Join Bill Weinman for an in-depth discussion in this video Touring the CRUD application, part of SQL Essential Training (2014).
CRUD is an acronym that stands for Create, Read, Update and Delete. These are the four basic functions of a database systems. This CRUD application is a demonstration of how these functions work in a relational context. This application demonstrates use of two tables that are related that uses the same album.db database we've been using for examples in the rest of the course. I'm just going to take you for a little tour through the application first. You can see, here on this page, we display all of the albums in the database.
So far, there's just seven of them, it looks like. And for each of these albums, there's a title, there's an artist, there's a label, there's a release date. And you can edit or delete the albums. We can go into one of these album by pressing Edit, and you can see it brings up the album. There's the album information at the top and the individual tracks down below. If we want to we can correct something. I left a mistake in here on purpose. We can take that little x out and say update. And now its updated in the database.
See Status at the top says Blue Suede Shoes has been updated, track number three, and it now shows the correct album title there in the tracks. Likewise, I could update any of this information here. I could put in a release day. I don't actually know what the correct day is, but I'll just put something in there and press update, and now the album has been updated. And I can put that back if I want to, 0. And you notice that it leaves a 01 there, because that's the first day of the month.
I can say Done, and we come back to the albums view. And I can add an album if i want to. And when I press Add album, it now allows me to add tracks. I can say track number one is this track. And it is 10 minutes long. And I say add, and I can add another track. And this one, say, it's 90 seconds long and you'll notice that it puts in 1:30. It does the math on that.
And I can do another track on that out of order. And say this one is 12 seconds long. And you'll notice, if I put one in, in between, it'll sort them properly There's our third track, there. And if I want to, I can delete that track. Press the delete button, it says to confirm it, and now that track is gone. And when I say, Done, there's that whole album there in order. And I can delete the whole album by pressing that delete. And it confirms I want to delete the whole album, so now I've got all the four functions of a common database.
Create, Read, Update, and Delete. You'll find the entire source code for this application in your exercise files in the CRUD folder. And it's written in PHP. And it actually supports three different database systems. Postgres, SQLite, and MySQL. This version of it is using the SQLite 3 database, and the other ones are commented out. You wanted to use one of the other ones, you would need to comment out this one And uncomment the other ones and set the constants for your environment with your user and password and whatever it's asking for there.
The database and the name of the engine. Like most of my PHP applications, it uses an init function and a main function to initialize and start the application. And those are called here at the top. The emit function sets up the database. And you'll see there's a switch, construct there for the database engine. SQLite, or MySQL, or PostgreSQL. And we're using sql lite.
And this sets up the PDO driver in PHP. We're using the PDO driver. The PDO driver in PHP is great because it allows you to use pretty much the same functions for different databases. And that makes a lot of this possible. So once this is all set up. There is a page routine for displaying pages. And there's a jump function, which is actually just a jump table depending on what button has been pushed. And it allows it to be fairly stateless and be able to jump to whatever routine it needs to when you press whatever button you press.
So in the rest of this chapter, I will show you the database routines in this code. Understand that writing a CRUD application is a great way to learn about database operations. I suggest that you write your own. Or feel free to use this application as a starting place for your own applications. Or use it as a bases for further experimenting and learning about SQL relational databases. Or database applications in general. In the next lesson I'll start showing you how the SQL queries are integrated into this PHP application.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.