Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The CRUD application uses a stored function for a feature that's not readily visible. In order to see it, we'll need to update the permissions for the web user in phpMyAdmin. So, if we come over here to phpMyAdmin, and I'm just going to select Users and the web at local host user, and we'll come out here. And this user has been set up with minimal permissions. Just these data permissions, but in order to actually run and create a stored function, it needs to have these three permissions over here: create routine, alter routine, and execute routine.
So, we'll add those, and go ahead and press the Go button, and those privileges have now been updated. And now, we're going to reload this, but before we do that, I want to come out here to the XAMPP Manager and just restart the database. It may or may not be necessary. Sometimes it is. Sometimes it isn't, but when you update the Permissions table, it's always a good idea. So, I'm going to restart the database. I've got MySQL Database selected and I'll press Restart. And that's restarted and now I'll come back out here to CRUD, and you'll notice this column here, the title column, there's going to be some extra information in there when I reload this.
So, I'll just press Reload, and you notice that now we have the cumulative duration of the entire album, and this is done with a stored function in the code. So if I come out here to text wrangler and we'll come down to the init, and where we set up MySQL, you notice that here it calls this function set_mysql_album-length function, and I can scroll down here to that function, and here we have a query that actually creates a stored function.
And this stored function we've seen it before. We've seen something like it before here. This takes the duration and it formats it as hours, minutes and seconds. And this one's a little bit flexible. The seconds to time function that comes with MySQL. I don't like the way that it works. It always shows hours, minutes and seconds and it shows lots of leading zeroes. And I just like the leading zeroes within the fields to the right of the colon. I don't like that first field to have leading zeroes. It's just me.
For example here, if I come out here to the Hendrix in the West album and I've got a little fake dummy track in there just for this purpose. And I make that say 29 minutes instead of 9 minutes and I'll select Update and Done. And you notice that now it says one hour and nine minutes. And that one in the hours column does not have a leading zero. And yet, the nine in the minutes column does, and that's the way that I like to see time formatted and that's the beauty of a stored function is that I can have it exactly the way that I want it.
And so can you. You can take this function and modify it or you can write your own. So, once this function is created and, of course, if we don't have permissions for that, it never happens. Then down here, in the get_album_length function for MySQL, you see it calls that album length function and it calls it on the aggregate sum for this query. And if you remember from our stored functions lesson, that's the way we get around the limitation of a stored function not being able to operate in an aggregate context.
You notice here, in SQL Lite, this sum sect to time does operate in an aggregate context, and that's actually defined in PHP. So, this use of a stored function provides a string version of the time duration formatted exactly how I like it. In many cases, I would have put this code for the stored function in the SQL file that defines the database. In this case, however, I chose to put it here in the PHP to ensure that the function is defined, even if it gets deleted by the student in SID while experimenting with the database.
Get unlimited access to all courses for just $25/month.Become a member