I have a function selectEntry(), however with my current implementation, I cannot use ORDERBY to order the results by a column ASC or DESC.
index.php
Code: Select all
<?php
require('sql.php');
$sql = new sqlInstance();
$sql->connect('localhost','aw_modules','root','');
require('blog.php');
$blog = new blog($sql,'blog_entries','blog_settings');
/* List entries */
for($i=1; $i <= $blog->getTotalEntries(); $i+=1){
$blog->selectEntry($i);
$blog->getEntryValue('title');
echo '<br/>';
}
/* Add a new entry */
$blog->addEntry(array(
'title'=>'My Title',
'content'=>'My Content',
'tags'=>'tags, go, here',
'custom_url'=>'custom-url',
'publish_date'=>'2012-01-17',
'publish_time'=>'00:00:00',
'expire_date'=>'0000-00-00',
'expire_time'=>'00:00:00',
'visible'=>'1'
));
/* Update an entry */
$blog->updateEntry(null,array(
'title'=>'My Updated Title',
'content'=>'My Updated Content',
'tags'=>'tags, go, here',
'custom_url'=>'custom-url',
'publish_date'=>'2012-01-17',
'publish_time'=>'00:00:00',
'expire_date'=>'0000-00-00',
'expire_time'=>'00:00:00',
'visible'=>'1'
));
/* Delete an entry */
//$blog->deleteEntry();
/* Retrieve the value of a setting */
//$blog->getSetting('allow_comments');
/* Update a setting */
//$blog->updateSettings(array('allow_comments'=>'0'));
?>Code: Select all
<?php
/**
* Blog Module
*
* @version 1.0.0
* @updated 17 January 2012, 06:15 GMT+2
*/
class blog{
/**
* __construct()
* Constructor of the blog object
*
* @param dbObject The object of the open database connection
* @param entriesTable The entries table name for the particular blog module
* @param settingsTable The settings table name for the particular blog module
*/
public function __construct($dbObject = null, $entriesTable = null, $settingsTable = null)
{
$this->dbObject = $dbObject;
$this->entriesTable = $entriesTable;
$this->settingsTable = $settingsTable;
$this->selectedEntry = null;
if ($this->dbObject != null && $this->entriesTable != null && $this->settingsTable != null){
$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."`");
$this->totalEntries = $this->dbObject->numRows($sqlResult);
$this->dbObject->freeResult($sqlResult);
$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->settingsTable."`");
$this->settings = $this->dbObject->fetchArray($sqlResult);
$this->dbObject->freeResult($sqlResult);
} else {
return false;
}
}
/**
* selectEntry(id, onlyVisible)
* Selects a blog entry by its ID
*
* @param id The ID of the post to update its contents, when null the current selected post is affected
* @param onlyVisible Select only visible=1 entries from the database table
* @return result Returns true if successful, otherwise false if an error has occured
*/
public function selectEntry($id, $onlyVisible = true)
{
if ($onlyVisible == true)
{
$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' AND `visible`='1' LIMIT 1");
} else {
$sqlResult = $this->dbObject->query("SELECT * FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
}
$this->selectedEntry = $this->dbObject->fetchArray($sqlResult);
$this->dbObject->freeResult($sqlResult);
if ($this->dbObject->getErrorCode()!=0){
return false;
} else {
return true;
}
}
/**
* getEntryValue(column)
* Gets the column value of the currently selected entry
*
* @param column The column name e.g. 'title'
* @return result Returns the value of the specified column
*/
public function getEntryValue($column)
{
echo $this->selectedEntry[$column];
}
/**
* addEntry()
* Adds a new blog entry
*
* @param params The columns to update, accepts an array e.g. array('title'=>'My Title', 'content'=>'My Content')
* @return result Returns the last inserted ID if successful, otherwise false if an error has occured
*/
public function addEntry($params)
{
$queryRow = "";
foreach($params as $key => $value)
{
$queryRow .= "`".$key."`,";
}
$queryRow = rtrim($queryRow,',');
$queryValues = "";
foreach($params as $key => $value)
{
$queryValues .= "'".$this->dbObject->escapeString($value)."',";
}
$queryValues = rtrim($queryValues,',');
$sqlResult = $this->dbObject->query("INSERT INTO `".$this->entriesTable."` (`id`,".$queryRow.") VALUES(NULL,".$queryValues.")");
if ($this->dbObject->getErrorCode()!=0){
return false;
} else {
return $this->dbObject->getInsertID();
}
}
/**
* updateEntry(id, params)
* Updates a blog entry
*
* @param id The ID of the post to update its contents, when null the currently selected entry is affected
* @param params The columns to update, accepts an array e.g. array('title'=>'My Title', 'content'=>'My Content')
* @return result Returns true if successful, otherwise false if an error has occured
*/
public function updateEntry($id = null, $params)
{
if ($id == null){
$id = $this->selectedEntry['id'];
}
$query = "";
foreach($params as $key => $value)
{
$query .= "`".$key."`='".$this->dbObject->escapeString($value)."',";
}
$query = rtrim($query,',');
$sqlResult = $this->dbObject->query("UPDATE `".$this->entriesTable."` SET ".$query." WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
if ($this->dbObject->getErrorCode()!=0){
return false;
} else {
return true;
}
}
/**
* deleteEntry(id)
* Updates a blog entry
*
* @param id The ID of the post to update its contents, when null the currently selected entry is affected
* @return result Returns true if successful, otherwise false if an error has occured
*/
public function deleteEntry($id = null)
{
if ($id == null){
$id = $this->selectedEntry['id'];
}
$sqlResult = $this->dbObject->query("DELETE FROM `".$this->entriesTable."` WHERE `id`='".$this->dbObject->escapeString($id)."' LIMIT 1");
if ($this->dbObject->getErrorCode()!=0){
return false;
} else {
return true;
}
}
/**
* getTotalEntries()
* Retrieves the total blog entries in the database
*
* @return totalEntries The number of the total blog entries in the database
*/
public function getTotalEntries()
{
return $this->totalEntries;
}
/**
* getSetting(setting)
* Retrieves a setting for the blog interface
*
* @param setting The name of the column that keeps the particular setting value
* @return result Returns the setting value
*/
public function getSetting($setting){
return $this->settings[$setting];
}
/**
* updateSettings(setting)
* Updates the settings for the blog interface
*
* @param setting The columns to update, accepts an array e.g. array('allow_comments'=>'1')
* @return result Returns true if successful, otherwise false if an error has occured
*/
public function updateSettings($params)
{
$query = "";
foreach($params as $key => $value)
{
$query .= "`".$key."`='".$this->dbObject->escapeString($value)."',";
}
$query = rtrim($query,',');
$sqlResult = $this->dbObject->query("UPDATE `".$this->settingsTable."` SET ".$query."");
if ($this->dbObject->getErrorCode()!=0){
return false;
} else {
return true;
}
}
}
?>Code: Select all
<?php
/**
* SQL Class
*
* @version 1.0.0
* @updated 28 November 2011, 13:30 GMT+2
*/
class sqlInstance{
public function __construct( $db_engine = 'mysql', $db_charset = 'utf8' )
{
$this->db_engine = $db_engine;
$this->db_charset = $db_charset;
}
public function connect( $db_host = 'localhost', $db_database = 'test', $db_username = 'root', $db_password = '' )
{
$this->db_host = $db_host;
$this->db_database = $db_database;
$this->db_username = $db_username;
$this->db_password = $db_password;
switch($this->db_engine)
{
case 'mysql':
//start database connection for mysql
$this->db_connection = mysql_connect($this->db_host, $this->db_username, $this->db_password);
if (!$this->db_connection){return false;}
//set the connection charset for mysql
if (!mysql_query("SET NAMES '".$this->db_charset."'")){return false;}
//connect to the database for mysql
if (!mysql_select_db($this->db_database, $this->db_connection)){return false;}
else{return true;}
break;
case 'mysqli':
//start database connection for mysqli
$this->db_connection = mysqli_connect($this->db_host, $this->db_username, $this->db_password);
if (!$this->db_connection){return false;}
//set the connection charset for mysqli
if(!mysqli_query($this->db_connection, "SET NAMES '".$this->db_charset."'")){return false;}
//connect to the database for mysqli
if(!mysqli_select_db($this->db_connection, $this->db_database)){return false;}
else{return true;}
break;
default:
return false;
}
}
public function escapeString($string)
{
switch($this->db_engine)
{
case 'mysql':
//escape string for query
if (ini_get('magic_quotes_gpc')) {
$string = stripslashes($string);
}
if (!is_numeric($string)) {
$string = mysql_real_escape_string($string, $this->db_connection);
}
return $string;
break;
case 'mysqli':
//escape string for query
if (ini_get('magic_quotes_gpc')) {
$string = stripslashes($string);
}
if (!is_numeric($string)) {
$string = mysqli_real_escape_string($this->db_connection, $string);
}
return $string;
break;
default:
return false;
}
}
public function query($db_query)
{
$this->db_query = $db_query;
switch($this->db_engine)
{
case 'mysql':
//execute query in mysql
return mysql_query($this->db_query, $this->db_connection);
break;
case 'mysqli':
//execute query in mysqli
return mysqli_query($this->db_connection, $this->db_query);
break;
default:
return false;
}
}
public function fetchArray($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//fetch array in mysql
return mysql_fetch_array($db_result);
break;
case 'mysqli':
//fetch array in mysqli
return mysqli_fetch_array($db_result);
break;
default:
return false;
}
}
public function fetchRow($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//fetch row in mysql
return mysql_fetch_row($db_result);
break;
case 'mysqli':
//fetch row in mysqli
return mysqli_fetch_row($db_result);
break;
default:
return false;
}
}
public function numRows($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//num rows in mysql
return mysql_num_rows($db_result);
break;
case 'mysqli':
//num rows in mysqli
return mysqli_num_rows($db_result);
break;
default:
return false;
}
}
public function affectedRows($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//affected rows in mysql
return mysql_affected_rows($db_result);
break;
case 'mysqli':
//affected rows in mysqli
return mysqli_affected_rows($db_result);
break;
default:
return false;
}
}
public function numFields($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//num fields in mysql
return mysql_num_fields($db_result);
break;
case 'mysqli':
//num fields in mysqli
return mysqli_field_count($this->db_connection);
break;
default:
return false;
}
}
public function getFieldName($db_result, $field_number)
{
switch($this->db_engine)
{
case 'mysql':
//field name in mysql
return mysql_field_name($db_result, $field_number);
break;
case 'mysqli':
//field name in mysqli
mysqli_field_seek($db_result, $field_number);
$finfo = mysqli_fetch_field($db_result);
return $finfo->name;
break;
default:
return false;
}
}
public function getFieldType($db_result, $field_number)
{
switch($this->db_engine)
{
case 'mysql':
//field type in mysql
return mysql_field_type($db_result, $field_number);
break;
case 'mysqli':
//field type in mysqli
mysqli_field_seek($db_result, $field_number);
$finfo = mysqli_fetch_field($db_result);
return $finfo->type;
break;
default:
return false;
}
}
public function getFieldTable($db_result, $field_number)
{
switch($this->db_engine)
{
case 'mysql':
//field table in mysql
return mysql_field_table($db_result, $field_number);
break;
case 'mysqli':
//field table in mysqli
mysqli_field_seek($db_result, $field_number);
$finfo = mysqli_fetch_field($db_result);
return $finfo->table;
break;
default:
return false;
}
}
public function getInsertID()
{
switch($this->db_engine)
{
case 'mysql':
//insert ID in mysql
return mysql_insert_id();
break;
case 'mysqli':
//insert ID in mysqli
return mysqli_insert_id();
break;
default:
return false;
}
}
public function getEncoding()
{
switch($this->db_engine)
{
case 'mysql':
//get encoding in mysql
return mysql_client_encoding($this->db_connection);
break;
case 'mysqli':
//get encoding in mysqli
return mysqli_client_encoding($this->db_connection);
break;
default:
return false;
}
}
public function setEncoding($encoding)
{
switch($this->db_engine)
{
case 'mysql':
//set encoding in mysql
if(!mysql_set_charset($encoding, $this->db_connection)){return false;}
else{return true;}
break;
case 'mysqli':
//set encoding in mysqli
if(!mysqli_set_charset($this->db_connection, $encoding)){return false;}
else{return true;}
break;
default:
return false;
}
}
public function freeResult($db_result)
{
switch($this->db_engine)
{
case 'mysql':
//free result in mysql
if(!mysql_free_result($db_result)){return false;}
else{return true;}
break;
case 'mysqli':
//free result in mysqli
if(!mysqli_free_result($db_result)){return false;}
else{return true;}
break;
default:
return false;
}
}
public function getVersion()
{
switch($this->db_engine)
{
case 'mysql':
//get mysql version number
return mysql_get_server_info();
break;
case 'mysqli':
//get mysqli version number
return mysqli_get_server_info($this->db_connection);
break;
default:
return false;
}
}
public function ping()
{
switch($this->db_engine)
{
case 'mysql':
//ping in mysql
return mysql_ping($this->db_connection);
break;
case 'mysqli':
//ping in mysqli
return mysqli_ping($this->db_connection);
break;
default:
return false;
}
}
public function getUptime()
{
switch($this->db_engine)
{
case 'mysql':
//uptime in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[0] = ltrim($stats[0],'Uptime: ');
return $stats[0];
break;
case 'mysqli':
//uptime in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[0] = ltrim($stats[0],'Uptime: ');
return $stats[0];
break;
default:
return false;
}
}
public function getThreads()
{
switch($this->db_engine)
{
case 'mysql':
//threads in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[1] = ltrim($stats[1],'Threads: ');
return $stats[1];
break;
case 'mysqli':
//threads in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[1] = ltrim($stats[1],'Threads: ');
return $stats[1];
break;
default:
return false;
}
}
public function getQuestions()
{
switch($this->db_engine)
{
case 'mysql':
//questions in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[2] = ltrim($stats[2],'Questions: ');
return $stats[2];
break;
case 'mysqli':
//questions in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[2] = ltrim($stats[2],'Questions: ');
return $stats[2];
break;
default:
return false;
}
}
public function getSlowQueries()
{
switch($this->db_engine)
{
case 'mysql':
//slow queries in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[3] = ltrim($stats[3],'Slow queries: ');
return $stats[3];
break;
case 'mysqli':
//slow queries in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[3] = ltrim($stats[3],'Slow queries: ');
return $stats[3];
break;
default:
return false;
}
}
public function getOpenTables()
{
switch($this->db_engine)
{
case 'mysql':
//get open tables in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[6] = ltrim($stats[6],'Open tables: ');
return $stats[6];
break;
case 'mysqli':
//get open tables in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[6] = ltrim($stats[6],'Open tables: ');
return $stats[6];
break;
default:
return false;
}
}
public function getQueriesPerSecond()
{
switch($this->db_engine)
{
case 'mysql':
//queries per second in mysql
$stats = explode(' ', mysql_stat($this->db_connection));
$stats[7] = ltrim($stats[7],'Queries per second avg: ');
return $stats[7];
break;
case 'mysqli':
//queries per second in mysqli
$stats = explode(' ', mysqli_stat($this->db_connection));
$stats[7] = ltrim($stats[7],'Queries per second avg: ');
return $stats[7];
break;
default:
return false;
}
}
public function getError()
{
switch($this->db_engine)
{
case 'mysql':
//display error in mysql
return mysql_error($this->db_connection);
break;
case 'mysqli':
//display error in mysqli
return mysqli_error($this->db_connection);
break;
default:
return false;
}
}
public function getErrorCode()
{
switch($this->db_engine)
{
case 'mysql':
//display error in mysql
return mysql_errno($this->db_connection);
break;
case 'mysqli':
//display error in mysqli
return mysqli_errno($this->db_connection);
break;
default:
return false;
}
}
public function disconnect()
{
switch($this->db_engine)
{
case 'mysql':
//close the connection for mysql
if (!mysql_close($this->db_connection)){return false;}
else{return true;}
break;
case 'mysqli':
//close the connection for mysqli
if(!mysqli_close($this->db_connection)){return false;}
else{return true;}
break;
default:
return false;
}
}
}
?>Code: Select all
-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 17, 2012 at 01:27 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `aw_modules`
--
-- --------------------------------------------------------
--
-- Table structure for table `blog_entries`
--
CREATE TABLE IF NOT EXISTS `blog_entries` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content` longtext NOT NULL,
`tags` varchar(256) NOT NULL,
`custom_url` varchar(255) DEFAULT NULL,
`publish_date` date NOT NULL,
`publish_time` time NOT NULL,
`expire_date` date DEFAULT NULL,
`expire_time` time DEFAULT NULL,
`visible` tinyint(3) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `blog_entries`
--
INSERT INTO `blog_entries` (`id`, `title`, `content`, `tags`, `custom_url`, `publish_date`, `publish_time`, `expire_date`, `expire_time`, `visible`) VALUES
(1, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(2, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(3, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(4, 'My Updated Title', 'My Updated Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1),
(5, 'My Title', 'My Content', 'tags, go, here', 'custom-url', '2012-01-17', '00:00:00', '0000-00-00', '00:00:00', 1);
-- --------------------------------------------------------
--
-- Table structure for table `blog_settings`
--
CREATE TABLE IF NOT EXISTS `blog_settings` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`allow_comments` tinyint(11) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
--
-- Dumping data for table `blog_settings`
--
INSERT INTO `blog_settings` (`id`, `allow_comments`) VALUES
(1, 1);
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;