In today's tutorial, we're going to learn a little bit about MySQL stored procedures and how they can be used to load filtered data from a database.
Let's imagine the following scenario: You're working on a School Management application. One of its most used functions is the search module which loads students filtered by different criteria. (Ex: Students who study math, Students that didn't miss any classes, Students who earned the highest grades, etc...)
Taking into consideration the above, what I've seen in many applications, is programmers would choose to dynamically build the query through a series of concatenations based on the search criteria chosen by the end-user.
In this post, we're going to try a different approach and we'll learn how to do this using a stored procedure in MySQL 5.5. We're assuming the following table structure:
CREATE TABLE IF NOT EXISTS `classes` (
`StudentID` int(11) NOT NULL,
`ClassName` varchar(255) NOT NULL,
KEY `StudentID` (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `students` (
`StudentID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(255) DEFAULT NULL,
`LastName` varchar(255) DEFAULT NULL,
PRIMARY KEY (`StudentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
INSERT INTO `classes` (`StudentID`, `ClassName`) VALUES (2, 'Math'), (3, 'English'), (4, 'Computer Science'), (5, 'English'), (6, 'Math');
INSERT INTO `students` (`StudentID`, `FirstName`, `LastName`) VALUES (2, 'Michael', 'Jackson'), (3, 'John', 'Smith'), (4, 'Mary', 'Anderson'), (5, 'Johnny', 'Cash'), (6, 'Keanu', 'Reaves');
So the resulting tables would look like this:
'students' table:
StudentID | FirstName | LastName
2 | Daniel | G
3 | Joh | Smith
...
---------------------------------
'classes' table:
StudentID | ClassName
2 | Math
3 | English
4 | Computer Science
...
For the sake of simplicity and clarity we're only going to have one parameter in our stored procedure - the class parameter. Depending on the value passed to it, the result set will filter out the students by the name of the class they're attending. For example, calling the procedure with the "Math" parameter will fetch all students that attend the math class. Also, I've added a condition that fetches all students, regardless of the classes they're attending. This is achieved by passing the 'ALL' value to the class parameter.
Now let's have a look at the stored procedure:
DELIMITER //
CREATE PROCEDURE GetFullNameAndClass(IN class VARCHAR(255))
BEGIN
IF class = 'ALL'
THEN
SET @str = ' WHERE 1';
ELSE
SET @str = CONCAT (' WHERE c.ClassName = ', QUOTE(class));
END IF;
SET @query = CONCAT ('SELECT CONCAT (s.LastName, ", ", s.FirstName) as FullName, c.ClassName FROM students AS s LEFT JOIN classes AS c ON s.StudentID = c.StudentID ', @str);
PREPARE stmt FROM @query;
EXECUTE stmt;
END //
DELIMITER ;
In your PHP file, assuming you're using mysqli you could use the code below. Please note that if your procedure returns multiple result sets you need to use mysqli_multi_query() or mysqli_real_query(). See PHP's official documentation for reference.
<?php
$mysqli = new mysqli("localhost", "root", "", "test");
$res = $mysqli->query("CALL GetFullNameAndClass('ALL')");
while($row = $res->fetch_object())
echo $row->FullName;
?>