Tools and Services Blog Learn Quizzes Smile API Log In / Sign Up
Tools and Services Blog Learn Quizzes Smile API Log In / Sign Up
« Return to the tutorials list
We have updated our privacy policy to let you know that we use cookies to personalise content and ads. We also use cookies to analyse our traffic and we share information about your use of our site and application with our advertising and analytics partners. By using this website or our application you agree to our use of cookies. Learn more about the way this website uses cookies or remove this message.

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

May 26, 2014 Difficulty: 40 / 50 Tweet
child searching

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:

      `StudentID` int(11) NOT NULL,
      `ClassName` varchar(255) NOT NULL,
      KEY `StudentID` (`StudentID`)

      `StudentID` int(11) NOT NULL AUTO_INCREMENT,
      `FirstName` varchar(255) DEFAULT NULL,
      `LastName` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`StudentID`)

    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:

    CREATE PROCEDURE GetFullNameAndClass(IN class VARCHAR(255)) 

    IF class = 'ALL'
        SET @str = ' WHERE 1';
        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 // 
  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.

      $mysqli = new mysqli("localhost", "root", "", "test");
      $res = $mysqli->query("CALL GetFullNameAndClass('ALL')");
      while($row = $res->fetch_object())
        echo $row->FullName;
comments powered by Disqus