One list to rule them all
Now we are going to create a list that will work for any table. A generic list view. Since we want to be able to list data from all our tables without having to write code for every single one. And we are going to start from a copy of the items page from the previous post. Then we are going to rewrite it so that it does not have any references to the Post class left. Here we go:
<div>
<?php $list = $obj->fetchArray(); ?>
<?php $count = $obj->fetchCount(); ?>
<h1>Post list</h1>
<?php echo $count; ?> posts found<br>
<table class="list">
<tr><th>Title</th><th>Posted</th><th>Tags</th></tr>
<?php foreach($list as $item) { ?>
<tr>
<td><a href="index.php?module=post&view=item&id=<?php echo $item->id; ?>"><?php echo $item->title; ?></a></td>
<td><?php echo $item->posted; ?></td>
<td><?php echo $item->tags; ?></td>
</tr>
<?php } ?>
</table>
</div>
The fetchCount function is generic so we keep that one just to figure out just how many posts there are in the set. We are going to use this number for the navigation later.
Then we use the fetchArray. But we want to be able to limit the amount of posts shown. And it would be nice to be able to sort the different columns right out of the box. So we
add some code to care for that. In order to move through pages we also need to track what page we are on, so we add code for that as well. And while we are at it, why not add
search function as well.
In order to pull all of this off, the most important thing we need to know is what columns are there in the table we are currently looking at. Therefore we start by calling the
function fetchFields() from our Db.php.
<?php
$fields = $obj->fetchFields();
$colCount = count($fields);
$searchText = filter_input(INPUT_GET, 'search_text', FILTER_SANITIZE_URL);
$srchQry = '';
if ($searchText != '') {
$first = true;
foreach ($fields as $field) {
if (!$first) {
$srchQry .= ' OR ';
}
$first = false;
// Use UPPER to ignore case sensitive searches
$srchQry .= 'UPPER(`' . $field->columnName . '`) LIKE UPPER('%' . $searchText . '%')';
}
} else {
$srchQry = '1';
}
if (filter_input(INPUT_GET, 'orderby', FILTER_SANITIZE_URL) == '') {
$orderBy = 'id';
} else {
$orderBy = filter_input(INPUT_GET, 'orderby', FILTER_SANITIZE_URL);
}
$desc = '';
if (filter_input(INPUT_GET, 'desc', FILTER_SANITIZE_URL) == 1) {
$desc = 'DESC';
}
$order = 'orderby=' . $orderBy . '&desc=' . filter_input(INPUT_GET, 'desc', FILTER_SANITIZE_URL);
$orderBy = ' ORDER BY ' . $orderBy . ' ' . $desc;
$postsPerPage = 20; // How many posts do you want to show per page
$currentPage = (int)filter_input(INPUT_GET, 'current_page', FILTER_SANITIZE_NUMBER_INT);
if ($currentPage == 0) { $currentPage = 1; } // We are on page 1 as default when current_page is not set.
$index = ($currentPage * $postsPerPage) - $postsPerPage;
$limit = '';
if ($page != 0) {
$limit = " LIMIT $index, $postsPerPage";
}
$total = $obj->fetchCount(' WHERE ' . $srchQry);
$list = $obj->fetchArray(' WHERE ' . $srchQry . ' ' . $orderBy . ' ' . $limit);
$pages = ceil((($total-1)/$postsPerPage));
$query = '&search_text=' . $searchText
;
$pData = '&module=' . $module
. '&view=' . $view
. '&id=' . $id
;
$params = $pData
. $query
;
?>
Now we got all the data, so the now we only need to display it.
<div>
<h1><?php echo $obj->realClassName() . ' list'; ?></h1>
<a href="?module=<?php echo $module; ?>&view=edit" class="button"><?php echo 'New post'; ?></a>
<form method="GET" action="">
<input type="hidden" name="current_page" value="1">
<input type="hidden" name="module" value="<?php echo $module; ?>">
<input type="hidden" name="view" value="<?php echo $view; ?>">
<input type="text" name="search_text" value="<?php echo $searchText; ?>">
<input type="submit" class="button" name="search" value="Search">
</form>
<?php echo $total; ?> post<?php if ($total > 1) { echo 's'; } ?> found<br>
<table class="list">
<tr>
<th width="40"><?php echo 'Edit'; ?></th>
<th width="60"><?php echo 'Delete'; ?></th>
<?php foreach ($fields as $t) { ?>
<th>
<?php
if (preg_match("/_id$/", $t->columnName, $matches)) {
$columnArr = explode('-', $t->columnName);
// if the column name is owner-user_id then show Owner as the column name
echo Db::realClassName(str_replace('_id', '', $columnArr[0]));
} else {
echo Db::realClassName($t->columnName);
}
?>
<a href="?page=<?php echo $page; ?><?php echo $params; ?>&orderby=<?php echo $t->columnName; ?>&desc=1" class="nav">-</a>
<a href="?page=<?php echo $page; ?><?php echo $params; ?>&orderby=<?php echo $t->columnName; ?>" class="nav">+</a>
</th>
<?php } ?>
</tr>
<?php foreach ($list as $item) { ?>
<tr>
<td>
<a href="?module=<?php echo $module; ?>&view=edit&id=<?php echo $item->id; ?>">
<?php echo 'Edit'; ?>
</a>
</td>
<td>
<a href="?module=<?php echo $module; ?>&view=list&id=<?php echo $item->id; ?>&cmd=del">
<?php echo 'Delete'; ?>
</a>
</td>
<?php
$cnt = 0;
foreach ($fields as $t) {
$name = $t->varName
?>
<td>
<?php
// if the column name ends with id, look up the name of that object
if (preg_match("/_id$/", $t->columnName, $matches)) {
// If the column name is owner-user_id find the id in the user table.
$columnArr = explode('-', $t->columnName);
if (count($columnArr) < 2) {
$columnArr[1] = $t->columnName;
}
$table = preg_replace("/_id$/", '', $columnArr[1]);
$className = preg_replace('/s/', '', $obj->className($table));
$sc = new $className((int)$item->$name);
?>
<span class="noedit"><?php echo $sc->table_data[1]; ?></span>
<?php } else { ?>
<?php if ($cnt < 2) { ?>
<a href="?module=<?php echo $module; ?>&view=edit&id=<?php echo $item->id; ?>">
<?php } ?>
<?php echo $item->$name; ?>
<?php if ($cnt < 2) { ?></a><?php } ?>
<?php
$cnt++;
}
?>
</td>
<?php } ?>
</tr>
<?php } ?>
<tr><td colspan="<?php echo $colCount + 2; ?>"><?php echo Html::paging($page, $pages, '&module=' . $module . '&id=' . $obj->id . '&view=' . $view . '&' .
$query . '&' . $order); ?></td></tr>
</table>
</div>
In the end we have a new function Html::paging(). We need to add a class called Html with the function paging(). We will use the Html class later if we want to create other html segments that we want to reuse.
<?php
class Html {
function paging($page, $pages, $params='') {
if ($pages < 2) {
return '';
}
$items = 10;
$totalPages = $pages;
$startPage = 1;
$average = floor($items / 2);
if ($page > $average) {
$startPage = $page - $average;
}
$menuItem = 0;
if ($startPage != 1) {
$navigation .= '...';
}
for ($index=$startPage; $index<=$totalPages; $index++) {
$menuItem++;
if ($page == $index) {
$navigation .= ' <span class="paging_nav selected">' . $index . '</span>';
} else {
$navigation .= ' <a href="?current_page=' . $index . '&' . $params . '" class="paging_nav">' . $index . '</a> ';
}
if ($menuItem == $items) { break; }
}
if ($index < $totalPages) {
$navigation .= '...';
}
if ($page > 1) {
$navigation = ' <a href="?current_page=' . ($page - 1) . '&' . $params . '" class="paging_nav"><<</a> ' . $navigation;
} else {
$navigation = ' <span class="paging_nav selected"><<</span> ' . $navigation;
}
if ($page < $pages) {
$navigation = $navigation . ' <a href="?current_page=' . ($page + 1) . '&' . $params . '" class="paging_nav">>></a>';
} else {
$navigation = $navigation . ' <span class="paging_nav selected">>></span>';
}
return $navigation;
}
}
Now let's have some fun. Earlier I used to have a script called make_file.php that automatically generated the list.php for all tables if it did not exist. This time I want to go a step further. I don't want a file at all. In order to do this we are going to move the list.php to a folder called views/generic and then do the following modifications in index.php:
if ($module == '') {
include('views/start/main.php');
} else {
if (!is_file('views/' . $module . '/' . $view . '.php')) {
if (is_file('views/generic/' . $view . '.php')) {
include('views/generic/' . $view . '.php');
} else {
echo '<div class="warning">The file ' . $view . '.php' . ' is missing!</div>';
}
} else {
include('views/' . $module . '/' . $view . '.php');
}
}
Now if the file is not found in the folder where we are currrently looking then the index.php will try to find the file in the generic folder. Lets also style our buttons in the main.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 #962;
color: #962;
-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;
}
.button:hover {
background: #a73;
color: #fff;
}
- Framework, PHP, Generic