In my new portal content delivery system I use content files as a storage medium. I have only one set of database tables on the whole site which are used to display and store data from forms. There are presently three forms on the site:

• my personal details are wrong
• have your say
• report bullying

There are several functions in the interface class devoted to handling forms (retrieving form definitions, storing data etc.).

I have decided to use Stored Procedures to do all of the data handling in this form system as it’s fairly small and straightforward giving me a gentle introduction to stored procedures in MySql.

I have a table which holds the basic information about a form – form_headers so I created an SP to bring back the data based on a form shortname.

CREATE DEFINER=`dbuser`@`localhost` 
PROCEDURE `sp_form_headers`(IN `formshort` VARCHAR(25))
    READS SQL DATA
SELECT * from formheaders where shortname=formshort$$

This SP expects one parameter ‘formshort’ and returns all field values (seems lazy to use * but there are only 6 fields and I need to display or use 5 of them).

In php the code to retrieve the data looks like this:

$learnnetobject = new InterfaceClass(); 
$link=$learnnetobject->db_connect(); 
$temparray=array();
$link->query("SET @formshort = 'hys'”);
$result = mysqli_query($link, "CALL sp_form_headers(@formshort) ");
while ($row =mysqli_fetch_array($result) ) {
    array_push($temparray,$row);
}
$learnnetobject->db_disconnect($link);
return $temparray;

The array returned from this looks something like this:

Array{  “id”=>1,
        ”shortname”=>”hys”,
        “title”=>”Have your say”,
        “recipients”=>”joebloggs@oursite.com”,
        “description”=>”We aim to provide a.....”,
        “custommessage”=>”Thank you for having your say” 
     }

Input parameters are automatically cleaned to prevent injection attacks which makes stored procedures the safest way to interact with your database.