INTERMEDIATE TO ADVANCED MYSQL, HTML5, PHP and OO TECHNIQUES.
There are so many frameworks available for creating HTML forms and tables that it becomes difficult to know which to choose. Instead of concentrating and learning code, we have to turn our attentions to becoming experts in frameworks such as Angular, Laravel, Yii and so on.
If you’re like me and like to be in full control of what you are creating, then you need to know this simple and powerful technique to harness and unleash the ‘awesomeness’ of arrays! It’s actually quite simple and if you didn’t think of it yourself then you’ll be kicking yourself as you probably know how to do it already!
For this discussion, you will need to know MySql, HTML5 forms and PHP. CSS isn’t really necessary here as we are concentrating on a technique – the presentation can come later.
Firstly, let’s setup the MySql table, keeping it nice and simple:
CREATE TABLE `_people` (
`FIrst_Name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`Surname` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`_key` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci;
There is a reason for the underscores being in the column names. I will come on to those later. For now, they are important so please do not omit them.
Now that the table is created, we need a form. An HTML form will do nicely. Again, there are many predefined forms and frameworks available, so go ahead and use any of your choice. All we need is a simple form to demonstrate the point of this post. So:
<!DOCTYPE html>
<html>
<body><form action=”saveFormData.php”>
<p><label>Enter your first name:</label>
<input name=”First_Name”></p><p><label>Enter your surname:</label>
<input name=”Surname”></p></form>
</body>
</html>
(Sorry about the formatting, but i am not here to show off)!
Ok, we have a simple table and a simple form to capture the input. Notice how the <input> names match the column names from our MySql table? That was done for reasons that i will come on to in a while.
Now the really important part:
saveFormData.php
<?php
/** add your own requirements to match your environment
*@author Salman S Tahir <salman@happymail.com>
*more comments etc….
*/
//$object = new …….
$object->__set(‘First_Name’, filter_var($_POST[‘First_Name’], FILTER_SANITIZE_STRING));
$object->__set(‘Surname’, filter_var($_POST[‘Surname’], FILTER_SANITIZE_STRING));try {
$object->saveInputs($object->__get(‘First_Name’), $object->__get(‘Surname’));displayMessage(true, “Record Saved!”);
//displayMessage is my own function to display an onscreen bootstrap alert.
exit;
}
catch (Exception $e) {
$message =”We’re sorry – we couldn’t save that record. The error has been reported as : <br>”;
$message .= “<em><strong>.”.$e->getMessage().”</strong></em>”;$message .=”<br><br>Please check your input carefully and try again. <br>
If error persists, please <a href=\”mailto:support@happymail.com?Subject=error%20adding%20new%20record\” target=\”_top\” class=\”alert-link\”>send us </a>a
screen shot of this error and we will do what we can to help.”;
displayMessage(false, $message);
exit;
}//end catch
?>
Now, suppose we modify our table to include ‘Date of Birth’? What are the implications? At the database level, no problem. We just use the ‘alter table’ command to add the new column:
ALTER TABLE `_people` ADD `DOB` DATE NOT NULL AFTER `Surname`;
Now we modify the form to include an extra <input> to capture the data… and don’t forget we have to update saveFormData.php to capture the new input…. typo typo typo! It happens.
WHAT IF there were a method/technique we could employ so that we don’t have to worry about modifying either the HTML5 form and or the saveFormData.php file? Enter the power of ARRAYS!
So here is the logic:
Read MySql table;
Create HTML5 form based on data retrieved from table;
Display table;
Read data to array;
Save data to table.
COOL! We saved a lot load of work and headaches! Because now, if we update our table structure we DON’T have to update either the HTML OR the PHP.