Fetch a specific post
We do not want a list of titles for all our post but instead we sould like a uniqe link for each post. In order to do that we need to be able to provide a post_id in the url. Like this http://www.my_site.se/index.php?module=post&view=item&id=1. The easy way to do this is to add the WHERE id=$id to the $sql in our page, like this.
mysqli->query($sql);
while ($row = $res->fetch_array()) {
echo $row['title'] . '
';
}
?>
But no I start to get creative with the code. Why should I have to do this for every I create. Wouldn't it be better if the controller (index.php) could figure out what table I wanted to get data from and what post I wanted and look that up for me. So that I only had to write something like this for the same result.
title; ?>
Since we got the module in the url we could actually use that to connect to out table. And we have the id, so we know what post to look up. Now the only question is how do we do this? We could create a class Post that contain all functions for getting the requested post but I know that we are going to have to do the same for the Comments, Pages, Users and all the other classes connected to tables that we want to get data form. So I'm going to do a generic class for handeling the communication with the database. Im going to call that class Db and let the Post class and all the other related classes extend on that one, like this for classes/Post.php:
Now the class called Db is quite a mouthful, at least for a beginner. So I won't go in to too much details here. But I have tried to make as many useful comments in the code as possible so that you should be able to figure out what is going on. What is worth mentioning though, is that the variable $this->table is getting its value from the class that extends Db. In the case of Post we get the value "post". Here is Db.php:
className = get_class($this);
// If the input is typed to be an integer then we expect it to be the ID-value of the table, so we do a look up
if (is_int($input)) {
$this->fetchObjectById($input, $fields);
}
}
function fetchObjectById($id, $fields=' * ') {
$vals = $this->buildFieldString($fields);
$sql = 'SELECT ' . $vals . ' FROM `' . $this->table . '` WHERE id=' . $id . '';
$res = Db::query($sql);
if ($row = Db::fetch_array($res) AND $id != 0) {
$o = $this->fetchObject($row, $fields);
return $o;
} else {
$o = $this->fetchEmpty();
return $o;
}
}
// Create a string of columns that is requested and transform * to actual column names
// It is allot faster when running the sql query!
// Input either ' * ' or 'id, name, mail'
// Returns a string of columns like this: `id`, `name`, `mail`
function buildFieldString($fields=' * ') {
if ($fields == ' * ') {
$fields = $this->fetchFields();
$first = true;
foreach ($fields as $field) {
if (!$first) {
$vals .= ', ';
}
$first = false;
$vals .= $field->columnName;
}
$fields = $vals;
}
$itms = array();
$itms = explode(',', $fields);
$vals = '';
$first = true;
foreach ($itms as $itm) {
if (!$first) {
$vals .= ', ';
}
$first = false;
$vals .= '`' . trim($itm) . '`';
}
return $vals;
}
// Fetch the column names from the database. By doing this instead of just running with * we actually save time while querying larger sets of data.
// Returns: [0] => TableStructure Object ( [columnName] => id [varName] => id [realName] => Id [columnType] => int [realType] => int(11) )
function fetchFields() {
// TODO: Cache fields and return them directly if they are cached in order to speed up the process.
$ret = array();
$sql = 'SHOW columns FROM `' . $this->table . '`';
$res = Db::query($sql);
while ($row = Db::fetch_assoc($res)) {
$aDbName[] = $row['Field'];
$fieldName = $this->strToCamel($row['Field']);
$type = preg_replace('/(.*?)/', '', $row['Type']);
$string = array('text', 'date', 'time', 'datetime', 'tinytext', 'mediumtext', 'longtext');
$int = array('int', 'double', 'tinyint', 'smallint', 'mediumint', 'bigint', 'decimal');
if (in_array($type, $string)) {
$type = 'string';
}
if (in_array($type, $int)) {
$type = 'int';
}
if (preg_match("/^is_/", $row['Field'], $matches)) {
$type = 'chk';
}
$t = new TableStructure();
$t->columnName = $row['Field'];
$t->varName = $fieldName;
$t->realName = $this->getRealName($row['Field'], $row['Type']);
$t->columnType = $type;
$t->realType = $row['Type'];
$ret[] = $t;
}
return $ret;
}
// Turn a string like my_colum_name into "My column name"
function getRealName($field, $type='') {
$field = ucfirst($field);
$field = preg_replace('/_/', ' ', $field);
$field = preg_replace('/sid$/', '', $field);
$field = preg_replace('/stext$/', '', $field);
return $field;
}
// Turn a string like my_colum_name into myColumnName
function strToCamel($str) {
$str = explode('_', strtolower($str));
for ($i = 1; $i < count($str); $i++){
$str[$i] = strtoupper(substr($str[$i], 0, 1)) . substr($str[$i], 1);
}
$str = implode('', $str);
return $str;
}
// Take an array of the return data and files it out in our created objects variables
function fetchObject($object, $values=' * ') {
$cnt = 0;
if ($values == ' * ') {
$fields = $this->fetchFields();
$first = true;
foreach ($fields as $field) {
if (!$first) {
$vals .= ', ';
}
$first = false;
$vals .= $field->columnName;
}
$values = $vals;
}
$row = explode(',', $values);
foreach ($row as $item) {
$item = trim($item);
$fieldName = $this->strToCamel($item, true);
$this->$fieldName = $this->handleDataOut($item, ($object[$item])); // Used as: $obj->id
$this->data[$item] = $this->$fieldName; // Used as: $obj->data['id']
$this->table_data[$cnt] = $this->$fieldName; // Used as: $obj->table_data[0]
$cnt++;
}
return true;
}
// Return an object with all the parameters set to zero or an empty string
function fetchEmpty() {
$sql = 'SHOW columns FROM `' . $this->table . '`';
$res = Db::query($sql);
while ($row = Db::fetch_assoc($res)) {
$fieldName = $this->strToCamel($row['Field']);
$type = preg_replace('/(.*?)/', '', $row['Type']);
$string = array('text', 'date', 'time', 'datetime', 'tinytext', 'mediumtext', 'longtext', 'varchar', 'string');
$int = array('int', 'double', 'tinyint', 'smallint', 'mediumint', 'bigint', 'decimal');
if (in_array($type, $string)) {
$type = 'string';
}
if (in_array($type, $int)) {
$type = 'int';
}
if ($type == 'int') {
$this->$fieldName = 0;
} else {
$this->$fieldName = '';
}
}
return true;
}
function handleDataOut($field, $data) {
global $handleDataOutLog;
$arr = array();
if (isset($handleDataOutLog[$this->table.'_'.$field])) {
$arr = $handleDataOutLog[$this->table.'_'.$field];
} else {
$sql = 'SHOW columns FROM `' . $this->table . '` WHERE Field='' . $field . ''';
$res = Db::query($sql);
while ($row = Db::fetch_assoc($res)) {
$arr[] = $row;
}
$handleDataOutLog[$this->table.'_'.$field] = $arr;
}
foreach ($arr as $row) {
$fieldName = $this->strToCamel($row['Field'], true);
$type = preg_replace('/(.*?)/', '', $row['Type']);
$string = array('text', 'date', 'time', 'datetime', 'tinytext', 'mediumtext', 'longtext', 'varchar');
$int = array('int', 'double', 'tinyint', 'smallint', 'mediumint', 'bigint');
$decimal = array('decimal');
if (in_array($type, $string)) {
$data = stripslashes($data);
}
if (in_array($type, $int)) {
$data = (int)($data);
}
if (in_array($type, $decimal)) {
$data = str_replace('.', ',', $data);
}
if (preg_match("/^is_/", $field, $matches)) {
$data = (int)($data);
}
#print $type . ': '.$data .'
';
//print '$this->'.$fieldName.' = '.$this->$fieldName.'
';
}
return $data;
}
// Below area a list of functions for querying the database.
function query($qry, $handleErrors=true) {
$ad = ActiveData::getInstance();
if (!($res = $ad->mysqli->query($qry)) and $handleErrors) {
//TODO: Add error handling
}
return $res;
}
function insert_id() {
$ad = ActiveData::getInstance();
return $ad->mysqli->insert_id;
}
function error() {
$ad = ActiveData::getInstance();
return $ad->mysqli->error;
}
function real_escape_string($str) {
$ad = ActiveData::getInstance();
return $ad->mysqli->real_escape_string($str);
}
function fetch_assoc($res) {
return $res->fetch_assoc();
}
function fetch_array($res) {
return $res->fetch_array();
}
function fetch_row($res) {
return $res->fetch_row();
}
function num_rows($res) {
return $res->num_rows;
}
function close($db) {
$db->close();
}
function connect($dbHost, $dbUser, $dbPass, $dbName, $dbPort) {
$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName, $dbPort);
// Set the value in global.php
$ad = new ActiveData();
$ad = $ad->getInstance();
$ad->mysqli = $mysqli;
}
}
// This is used as a struct to contain all representations of a table column
class TableStructure {
public $columnName = '';
public $varName = '';
public $realName = '';
public $columnType = '';
public $realType = '';
}
Now you can try editing you views/post/item.php to this and se if you get the same result twice.
mysqli->query($sql);
while ($row = $res->fetch_array()) {
echo $row['title'] . '
';
}
// This is how we can get the same data using Db.php
$obj = new Post((int)$id);
echo $obj->title . '
';
?>
But we want to go even further. Because if we have an id in the url. We can load the object right away in index.php like this:
function printContent() {
$module = filter_input(INPUT_GET, 'module', FILTER_SANITIZE_URL);
$view = filter_input(INPUT_GET, 'view', FILTER_SANITIZE_URL);
$id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT);
$obj = new stdClass();
if ($module != '') {
$db = new Db();
$className = $db->className($module);
if (is_file('classes/' . $className . '.php')) {
$obj = new $className((int)$id);
}
}
// Get the page we want to display
if ($module == '') {
include('views/start/main.php');
} else {
include('views/' . $module . '/' . $view . '.php');
}
}
Because we want the script to do this for every class we throw at it we change the line
$obj = new Post((int)$id);
with the generic solution
$db = new Db();
$className = $db->className($module);
$obj = new $className((int)$id);
So now we can make a complete post item page by changing item.php to:
title; ?>
message; ?>
posted; ?> -
tags; ?>
As you might have noticed, I have also added a connect function in to our Db.php. So now I can modify the global.php as follows:
- framework, php, generic, software