Asked recently on Yahoo! Answers:
10 Points…Help MYSQL?
i have a table named ‘Students’ and it has the coulumns ‘Name’,’DateBirth’,’Age’ how do i do to know how many years, months, and days do all the students united have, THANKS
What the questioner wants to do is fairly complicated in MySQL. If we were working with a program, such as a PHP script or C++ program, we might well simply grab all the user records and do our calculations in the program. But in MySQL itself, this sort of query isn’t easy to write; it really requires a stored procedure.
Stored Procedures In MySQL
Stored procedures (also called stored routines) are, as the name suggests, a way to save your queries on the database server, so you can call them over and over again without having to rewrite them in your code. Another benefit they offer is the ability to run several queries in sequence, and / or process data; in some cases, you can conditionally run a query based on the results of a previous query, or “roll back” the results of previous queries if some portion of the stored procedure didn’t properly work.
Stored procedures are also more secure than text-based queries, as a rule, because you can control permissions to the stored procedure itself — that is, limit only certain database users or roles to being able to execute (or call) the procedure. Also, stored procedures use parameters — that is, the variables you send to a stored procedure are given a type and are often sanitized by the database, which help prevents some of the worries about SQL injection attacks.
You will occasionally hear tell of functions in MySQL. They’re effectively the same thing as a stored procedure; the difference is that a function always returns a value, while a stored procedure may not. Think of stored procedures as null / void functions (C-like languages) or subroutines (Visual Basic).
Stored procedures are fairly unusual to most MySQL users; just about everyone works in text queries. MySQL has far more power than that, including robust support for stored procedures.
However, the management tools for MySQL tend to be weak in terms of support for stored procedures and functions, and that is especially true of phpMyAdmin. Generally speaking, you can get phpMyAdmin to create, alter, delete and call stored procedures via SQL queries; but you cannot visually manage them through the interface. **
The MySQL Administrator GUI better manages stored procedures and functions, but you need to be able to remotely connect to your MySQL server to use it, and that’s often not possible with most shared Web hosting. (It is possible with GoDaddy and Dreamhost, my preferred LAMP shared hosting providers.)
With that described, let’s proceed with the stored procedure that handles this request.
Why A Stored Procedure?
In our case, we really need a stored procedure because we cannot easily aggregate the data this questioner has into the results she wants. We might well be able to pull off a query that does what’s wanted, but it would be needlessly complex and probably exceedingly inefficient.
Instead, we will use a stored procedure that effectively handles what we need done in steps:
- It retrieves the number of days between the birth date of each person and today’s date, using the DATEDIFF() function. That effectively gives us the total number of days each person has been alive
- We iterate through each record, adding the days alive for each person to a total days variable, called xdays.
- After we have gone through all the records, we then divide xdays by 365, to give us the total number of years all persons have been alive.
- We subtract the number of days represented by xyears * 365, to give us the remainder.
- xdays is then divided by 30, to give us the number of months.
- We then reduce xdays by xmonths * 30. This gives us the number of days.
- Once we call this stored procedure, we will get back three parameters representing the years, months and days of life for all the people in the database, added together.
CREATE PROCEDURE getagesums(OUT years INT, OUT months INT, OUT days INT) BEGIN -- local variables DECLARE done INT DEFAULT 0; DECLARE xtemp, xdays INT; -- get a local recordset and declare its cursor -- we're getting how old each person is in days DECLARE cs CURSOR FOR SELECT DATEDIFF(CURDATE(), DateBirth) FROM Students; OPEN cs; -- add each person's days of age to days variable REPEAT FETCH cs INTO xtemp; xdays = xdays + xtemp; UNTIL done END REPEAT; CLOSE cs; -- divide days by 365 for years, 30 for months -- set values of output parameters SET years = FLOOR(xdays / 365); SET xdays = xdays - (FLOOR(xdays / 365) * 365); SET months = FLOOR(xdays / 30); SET days = xdays - (FLOOR(xdays / 30) * 30); END
In the procedure above, I have generalized a year to last 365 days and a month to be 30 days. This fudging is going to introduce some error; provided we’re dealing with a limited number of fairly young people, it shouldn’t be a wild error. If we have 100,000 octogenarians in the database, we’re going to have some significant sway in the results, toward an undercounting of years and overcounting of months; if we have 100 or so people aged 10 to 50, the results should all come out in the wash.
To invoke the stored procedure, we simply use CALL, adding three names for the output parameters, so we can work with them after the procedure runs:
CALL getagesums(@years, @months, @days);
Since the questioner didn’t state how or where the results would be used, I’ll leave it up to him to figure out.
**: To be 100 percent accurate, all stored routines in MySQL are in the information_schema database, in the ROUTINES table. Therefore, you can manage your stored routines in phpMyAdmin via SQL statements.
Here are a few examples of helpful stored procedure SQL statements:
-- get a list of all routines for a database named dbname: SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'dbname'; -- there are two ways to see the code of a procedure named proc_name -- this one only works if you have enabled debugging in MySQL SHOW PROCEDURE CODE proc_name; -- this will work in most MySQL installs SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'proc_name' AND ROUTINE_SCHEMA = 'dbname'; -- if you have enabled debugging, you can get metadata for proc_name with SHOW PROCEDURE STATUS LIKE 'proc_name';