Handling Contents with MySQL for a Content Management System Built with Prototype - Inserting, updating and deleting articles
(Page 3 of 4 )
As I stated in the previous section, all the operations required to insert, update and delete the contents of a given article will be directly performed by a simple PHP script, which obviously will have the capacity to interact directly with MySQL.
Having explained that, the signature of this PHP snippet is shown below, so take some time to look it over. It is as follows:
<?php
// define 'MySQL' class
class MySQL{
private $conId;
private $host;
private $user;
private $password;
private $database;
private $result;
const OPTIONS=4;
public function __construct($options=array()){
if(count($options)!=self::OPTIONS){
throw new Exception('Invalid number of connection
parameters');
}
foreach($options as $parameter=>$value){
if(!$value){
throw new Exception('Invalid parameter '.$parameter);
}
$this->{$parameter}=$value;
}
$this->connectDB();
}
// connect to MySQL
private function connectDB(){
if(!$this->conId=mysql_connect($this->host,$this-
>user,$this->password)){
throw new Exception('Error connecting to the server');
}
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
}
}
// run query
public function query($query){
if(!$this->result=mysql_query($query,$this->conId)){
throw new Exception('Error performing query '.$query);
}
return new Result($this,$this->result);
}
// escape string
public function escapeString($string){
return mysql_escape_string($string);
}
}
// define 'Result' class
class Result {
private $mysql;
private $result;
public function __construct($mysql,$result){
$this->mysql=$mysql;
$this->result=$result;
}
// fetch row
public function fetchRow(){
return mysql_fetch_assoc($this->result);
}
// count rows
public function countRows(){
if(!$rows=mysql_num_rows($this->result)){
throw new Exception('No rows were found to display!');
}
return $rows;
}
// count affected rows
public function countAffectedRows(){
if(!$rows=mysql_affected_rows($this->mysql->conId)){
throw new Exception('Error counting affected rows');
}
return $rows;
}
// get ID form last-inserted row
public function getInsertID(){
if(!$id=mysql_insert_id($this->mysql->conId)){
throw new Exception('Error getting ID');
}
return $id;
}
// seek row
public function seekRow($row=0){
if(!is_int($row)||$row<0){
throw new Exception('Invalid result set offset');
}
if(!mysql_data_seek($this->result,$row)){
throw new Exception('Error seeking data');
}
}
}
try{
// prevent browser caching
header('Cache-Control: no-cache');
header('Pragma: no-cache');
// connect to MySQL
$db=new MySQL(array
('host'=>'host','user'=>'user','password'=>'password',
'database'=>'database'));
$command=$db->escapeString($_GET['command']);
// add new article to database table
if(!$command||$command=='upload'){
if($_GET['title']&&$_GET['author']&&$_ GET['content']){
$title=$db->escapeString($_GET['title']);
$author=$db->escapeString($_GET['author']);
$content=$db->escapeString($_GET['content']);
$db->query("INSERT INTO articles
(id,title,author,content) VALUES
(NULL,'$title','$author','$content')");
}
}
// update article into database table
elseif($command=='update'){
$id=$db->escapeString($_GET['id']);
$title=$db->escapeString($_GET['title']);
$author=$db->escapeString($_GET['author']);
$content=$db->escapeString($_GET['content']);
$db->query("UPDATE articles SET
title='$title',author='$author',content='$content' WHERE
id='$id'");
}
// delete article from database table
elseif($command=='delete'){
$id=$db->escapeString($_GET['id']);
$db->query("DELETE FROM articles WHERE id='$id'");
}
$result=$db->query("SELECT * FROM articles ORDER BY id");
// display list of articles
while($row=$result->fetchRow()){
echo '<div id="article"><h2>'.$row
['title'].'</h2><p>Author: '.$row['author'].'</p><p>'.$row
['content'].'</p><a href="#" onclick="editArticle(''.$row
['id'].'',''.$row['title'].'',''.$row['author'].'',''.$row
['content'].'')";>Edit</a> <a href="#" onclick="deleteArticle
('.$row['id'].')";>Delete</a><p></p></div>';
}
}
catch(Exception $e){
echo $e->getMessage();
exit();
}
?>
As you can see, the above PHP script uses two independent classes that I already used in some of my previous articles. The first one, called "MySQL," is tasked with connecting to MySQL and running queries, while the second one is responsible for handling all the returned result sets.
However, the most interesting part of the script is the one that runs the proper SQL queries against a sample "articles" database table. This is where the content management system really does its work.
In this case, according to the value of a "command" variable, which is passed via an AJAX request, the PHP script performs the insertion, update or deletion of a specific article. If working with basic queries and MySQL is a familiar process to you, then the logic implemented by the pertinent script shouldn't be hard to grasp at all.
Finally, the script in question finishes its execution by displaying the full list of articles stored in the respective "articles" database table, and also shows two additional links, useful for updating and deleting the contents of a particular article. Wasn't that easy to understand? I bet it was!
Now that you know how the content management system works, please go ahead and read the following section. I'm going to show you the complete source code of this application, in case you want to copy it and paste it into your favorite text editor.
Next: Assembling the modules of the application >>
More JavaScript Articles
More By Alejandro Gervasio
