Edit on the fly
No we are going to do some real magic. Let's build an edit form that work regardless of which table we are working on. Start by creating a edit.php in /views/generic/.
<?php
$fields = $obj->fetchFields();
$colCount = count($fields);
if (filter_input(INPUT_POST, 'save', FILTER_SANITIZE_URL) != '') {
foreach ($fields as $f) {
$name = $f->varName;
if ($t->columnName == 'id') {
// do nothing;
} else {
$obj->$name = filter_input(INPUT_POST, $f->columnName);
}
}
$obj->update();
?>
<div class="information">
Changes are saved
</div>
<?php
}
?>
<div>
<h1>
<?php if ($obj->id == 0) { ?>
New
<?php } else { ?>
Edit
<?php } ?>
<?php echo $obj->realClassName(); ?>
</h1>
<a href="index.php?module=<?php echo $module; ?>&view=list" class="button">Back</a>
<form method="post" action="">
<table class="form">
<?php foreach ($fields as $t) {
$name = $t->varName;
?>
<tr>
<th>
<?php if (preg_match("/_id$/", $t->columnName, $matches)) {
$columnArr = explode('-', $t->columnName);
echo Db::realClassName(str_replace('_id', '', $columnArr[0]));
} else {
echo Db::realClassName($t->columnName);
} ?>
</th>
<td>
<?php if ($t->columnName == 'id') { ?>
<?php echo $obj->$name; ?>
<input type="hidden" name="id" value="<?php echo $obj->$name; ?>">
<?php } else if (preg_match("/_id$/", $t->columnName, $matches)) {
$columnArr = explode('-', $t->columnName);
if (count($columnArr) < 2) {
$columnArr[1] = $t->columnName;
}
$table = preg_replace("/_id$/", '', $columnArr[1]);
$objClassName = preg_replace('/s/', '', '' . $obj->className($table));
//print $table.' : '.$objClassName;
$sc = new $objClassName();
$scFields = $sc->fetchFields();
$scs = $sc->fetchArray(' ORDER BY `' . $scFields[1]->columnName . '`, `id`');
?>
<select name="<?php echo $t->columnName; ?>" style="width: 145px;">
<option value="">Select</option>
<?php
foreach ($scs as $item) {
?>
<option value="<?php echo $item->id; ?>"
<?php if ($item->id == $obj->$name) { ?>
selected="selected"
<?php } ?>
>
<?php echo $item->table_data[1]; ?>
</option>
<?php
}
?>
</select>
<?php } else if ($t->realType == 'text') { ?>
<textarea name="<?php echo $t->columnName; ?>" style="width: 150px; height: 80px;"><?php echo $obj->$name; ?></textarea>
<?php } else { ?>
<input <?php if ($t->columnType == 'chk') { ?>
type="checkbox"
value="1"
<?php if ($obj->$name) { ?>
checked="checked"
<?php } ?>
<?php } else { ?>
<?php if (($t->realType == 'datetime' or $t->realType == 'timestamp') and $obj->$name == '') {
$obj->$name = date('Y-m-d H:i:s');
} ?>
type="text"
value="<?php echo $obj->$name; ?>"
<?php } ?>
name="<?php echo $t->columnName; ?>"
<?php if ($t->realType == 'date') { ?>
class="datepicker"
<?php } ?>
>
<?php } ?>
</td>
</tr>
<?php } ?>
<tr>
<th></th>
<td>
<input type="submit" name="save" value="Save" class="button">
</td>
</tr>
</table>
</form>
</div>
One of the most important parts of this code is the segment where we take care of the different types of database values and displays them in different kinds of input form fields. For instance columnType "chk" that is the type of all columns starting with "is_". This means that if you name a column is_active, then that will automatically generate a checkbox in the input form. The same for columns of type date. Her we will also add a datepicker later, that will apear automatically for all date fields. And if the column name ends with "_id" then the script will try to find a class with the name of this id and get a list of items from this class table. In order to generate items for the select list. This is a really neat feature.
In this code there is one function we have not yet implemented. The $obj->update(). This function will create a new post (INSERT) if the id is zero, otherwise it will (UPDATE) the post with the same id in the database. Let's create this function in Db.php.
function update() {
$fields = $this->fetchFields();
foreach ($fields as $field) {
$columnName[] = $field->columnName;
$fieldName = $this->strToCamel($field->columnName);
$data[] = $this->handleDataIn($field->columnName, $field->columnType, $this->$fieldName);
}
$indexId = 0;
$i = 0;
if ($this->id == 0) {
$sql = 'INSERT INTO `' . $this->table . '` (';
foreach ($columnName as $sItem) {
if ($sItem != 'id') {
$sql .= '`' . $sItem . '`, ';
} else {
$indexId = $i;
}
$i++;
}
$sql = trim($sql, ', ');
$sql .= ') VALUES (';
$i = 0;
foreach ($data as $sItem) {
if ($indexId != $i) {
$sql .= "'" . $sItem . "', ";
}
$i++;
}
$sql = trim($sql, ', ');
$sql .= ')';
Db::query($sql);
$this->id = Db::insert_id();
} else {
$sql = 'UPDATE `' . $this->table . '` SET ';
$found = true;
$indexId = 0;
$i = 0;
while ($found) {
if ($columnName[$i] != 'id') {
$sql .= "`" . $columnName[$i] . "`='" . $data[$i] . "', ";
} else {
$indexId = $i;
}
if (is_null($columnName[($i+1)])) {
$found = false;
}
++$i;
}
$sql = trim($sql, ", ");
$sql .= " WHERE id = " . (int)$data[$indexId] . "";
Db::query($sql);
}
$this->fetchObjectById((int)$this->id);
return $this->id;
}
As you can see it is a restriction for every table using the Db.php class that it has a column called "id" that is uniqly indexed and set up to autoincrement. In this code we also have a new function we have not implemented yet "handleDataIn". This function will prepare data to be stored in the database. It is your last resort to manipulate data before it is saved to the database.
// Before saving data to the database, we want to check it for sql-injection and stuff
private function handleDataIn($field, $type, $data) {
$aString = array('text', 'date', 'time', 'datetime', 'tinytext', 'mediumtext', 'longtext');
$aInt = array('int', 'double', 'tinyint', 'smallint', 'mediumint', 'bigint');
$aDecimal = array('decimal');
$tmp = explode('(', $type);
$type = $tmp[0];
if (in_array($type, $aString)) {
$data = String::slashText($data);
}
if (in_array($type, $aInt)) {
$data = (int)$data;
}
if (in_array($type, $aDecimal)) {
$data = str_replace(',', '.', $data);
}
if (preg_match("/^is_/", $field, $aMatches)) {
$data = (int)$data;
}
return $data;
}
And here wa have a function call to String::slashText(). That will take a string and slash the text and prevent any atempts of SQL-injection. So we also need to add the String.php in out classes library.
<?php
class String {
static function slashText($text, $removeBlank=true) {
// Remove all slashes so that we do not doubleslash at the end of this function
$text = stripcslashes($text);
// Clear all untrusted html-tags
$text = String::stripDangerousHtml($text);
// General cleanup of spaces, if demanded
if ($removeBlank) {
$text = preg_replace('/ /', ' ', $text);
}
// Escape the string with the standard mysql function and then add slashes for even more security
$text = addcslashes(Db::real_escape_string($text), '%_');
return $text;
}
static function stripDangerousHtml($text) {
// Remove all areas of code with potentially dangarous code
$text = preg_replace('/<script[^>]*?>.*?</script>/is', '', $text);
$text = preg_replace('/<!--.*?-->/is', '', $text);
// Secure all safe tags, only allowing tags and slashes. No other information inside tags.
$text = preg_replace('/(<(/?)(b|p|i|u|s|span|li|lu|table|tr|th|td|cite|wbr|strong|em|italic|code|pre)(/?)>)/i', '#$2$3$4¤', $text);
$text = preg_replace('/<(a).*?(href="[^"]*?").*?(target="[^"]*?")>/i', '#$1 $2 $3¤', $text);
$text = preg_replace('/<(a).*?(href="[^"]*?")>/i', '#$1 $2¤', $text);
$text = preg_replace('/(<(/a)>)/i', '#$2¤', $text);
$text = preg_replace('/<br.*?>/', '#br#', $text);
// Remove all tags that are left
$text = preg_replace('/<[^>]*?>/', '', $text);
// Restore all safe tags
$text = preg_replace('/#br#/', '<br>', $text);
$text = preg_replace('/(#(/a)¤)/i', '<$2>', $text);
$text = preg_replace('/(#(a.*?)¤)/i', '<$2>', $text);
$text = preg_replace('/(#(/?(b|p|i|u|s|span|li|lu|table|tr|th|td|cite|wbr|strong|em|italic)/?)¤)/i', '<$2>', $text);
return $text;
}
}
The only thing left for this first draft of a generic edit form is to add som styling to our css.
.button {
height: 30px;
font-size: 1em;
line-height: 1.2em;
display: inline;
margin-top: 0px;
margin-bottom: 5px;
padding: 5px;
padding-left: 10px;
padding-right: 10px;
background: #fff;
border: 1px solid #333;
color: #333 !important;
-webkit-border-radius: 4px;
-moz-border-radius: 4px;
-o-border-radius: 4px;
-ms-border-radius: 4px;
-khtml-border-radius: 4px;
border-radius: 4px;
cursor: pointer;
text-transform: uppercase;
}
.button:hover {
background: #999;
color: #fff !important;
}
input, select, textarea {
font-size: 1em;
line-height: 1.2em;
}
table.form th, table.form td {
padding: 5px;
}
table.form th {
text-align: left;
font-weight: normal;
font-size: 0.8em;
vertical-align: top;
}
em {
color: #333;
}
.information {
background: #cfc !important;
}
.warning {
background: #ff9 !important;
}
.error {
background: #fcc !important;
}
- framework, php, generic, software