You can now use any non-standard SQL Query to retrieve data i.e. you can use MySQL variables in your SQL Query or use stored procedures to retrieve data. Let's see how to work with stored procedures. In this example, we will work with MySQL and Northwind database.
First, let's create a stored procedure:
DELIMITER //
CREATE PROCEDURE categories_search
(IN a VARCHAR(50))
BEGIN
SELECT * from categories
where Description like concat('%', a, '%');
END //
DELIMITER ;
It doesn't do much but returns a list of categories that contain the word passed there as a parameter. We can test in in phpMyAdmin this way:
call categories_search('fish')
Once we made sure that stored procedure works we can create a SQL View based on this stored procedure call. We will use 'All fields search' variable as a parameter. Note that we added single quotes around the parameter since this is a text variable. And, of course, we didn't have to remember this variable name, we added it via Insert variable->All fields search.
Now we can run this procedure, get results back, add fields to the list and proceed to build the project.
Note: 'All fields search' parameter will be empty on the initial page load. You need to make sure that your stored procedure won't break if an empty parameter is passed there. This is, of course, not a problem, if your stored procedure doesn't take any parameters.
