New Feature: Playlist Center! Pick a topic and let our playlists guide the way.

Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using a stored funciton

From: MySQL Essential Training

Video: Using a stored funciton

The CRUD application uses a stored function

Using a stored funciton

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.

Show transcript

This video is part of

Image for MySQL Essential Training
MySQL Essential Training

60 video lessons · 4661 viewers

Bill Weinman
Author

 
Expand all | Collapse all
  1. 4m 22s
    1. Welcome
      1m 3s
    2. Using the exercise files
      1m 31s
    3. What is MySQL?
      1m 48s
  2. 45m 37s
    1. Installation overview
      3m 16s
    2. Installing XAMPP on Windows
      5m 55s
    3. Installing XAMPP on the Mac
      6m 38s
    4. Setting up MySQL users
      11m 31s
    5. Installing SID on Windows
      5m 43s
    6. Installing SID on the Mac
      6m 6s
    7. Installing time zone support in MySQL on Windows
      6m 28s
  3. 45m 43s
    1. The SELECT statement
      3m 57s
    2. Selecting rows
      4m 57s
    3. Selecting columns
      3m 8s
    4. Sorting results with ORDER BY
      2m 58s
    5. Filtering results with WHERE
      3m 52s
    6. Filtering results with LIKE and IN
      3m 41s
    7. Filtering results with regular expressions
      8m 21s
    8. Inserting rows
      4m 9s
    9. Updating rows
      2m 21s
    10. Deleting rows
      2m 25s
    11. Literal strings
      3m 12s
    12. Understanding NULL
      2m 42s
  4. 41m 47s
    1. Creating a database
      4m 30s
    2. Creating a table
      7m 18s
    3. Creating indexes
      6m 8s
    4. Controlling column behavior with constraints
      4m 46s
    5. Creating an ID column
      6m 58s
    6. Using foreign key constraints
      7m 58s
    7. Altering a table
      4m 9s
  5. 28m 56s
    1. What are data types?
      4m 1s
    2. Numeric types
      5m 21s
    3. String types
      2m 58s
    4. Date and time types
      7m 2s
    5. Bit type
      2m 26s
    6. Boolean values
      2m 15s
    7. Enumeration types
      4m 53s
  6. 32m 34s
    1. String functions
      6m 57s
    2. Numeric functions
      6m 2s
    3. Date and time functions
      4m 12s
    4. Time zones in MySQL
      3m 37s
    5. Formatting dates
      1m 51s
    6. Aggregate functions
      5m 45s
    7. Flow control with CASE
      4m 10s
  7. 7m 6s
    1. Maintaining database integrity with transactions
      4m 46s
    2. Using transactions for performance
      2m 20s
  8. 16m 49s
    1. Updating a table with a trigger
      5m 11s
    2. Preventing automatic updates with a trigger
      7m 29s
    3. Logging transactions with a trigger
      4m 9s
  9. 14m 11s
    1. Creating a simple subselect
      3m 23s
    2. Searching within a result set
      3m 53s
    3. Creating a view
      3m 32s
    4. Creating a joined view
      3m 23s
  10. 12m 26s
    1. Understanding MySQL stored routines
      2m 0s
    2. Creating a stored function
      4m 34s
    3. Creating a stored procedure
      5m 52s
  11. 14m 4s
    1. The multi-platform PDO interface
      3m 44s
    2. Executing the SQL
      4m 8s
    3. Implementing auto-increment IDs
      2m 3s
    4. Using a stored funciton
      4m 9s
  12. 1m 3s
    1. Goodbye
      1m 3s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold

Are you sure you want to delete this note?

No

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.