Basic usage of stored procedures in MySQL for loading filtered result sets

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.

Why is this useful?

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
  ...
  

Enough talking, give me the code!

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 ;
  

1. The 'GetFullNameAndClass' procedure accepts an 'IN' parameter. Please don't forget to specify its Data Type.
2. The DELIMITER part of a stored procedure is something you define. The reason delimiters exist is because using the default semicolon as a delimiter would cause a syntax error - A stored procedure can have multiple statements and therefore multiple semicolons.
3. The procedure itself is very easy - we define a @str variable that is created dynamically depending on what is passed in the 'class' parameter. In other words, the conditional block adds the 'class' to the where clause unless the value of 'class' is 'ALL'.
4. The next step is to construct the actual query through concatenation and then prepare and execute the resulted query string.

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;
    ?>