Here we go! Off to create a completely database driver News script system. This is my first ever tutorial, there will more than likely be some edits along the way. Everything I write will be in OOP (Object Oriented Programming). Basically, we're going to use classes and functions to make sure everything works the way it should. I've posted a display, showing you how I structure my folders while I work on websites.
ROOT or PUBLIC_HTML -
|
images (Folder) -
|
All of my images.
|
inc (Folder) -
|
Any of my class files and includes will go here.
|
styles (Folder) -
|
Any styles I have will go here.
We'll start off by making sure you have a few bits of code that are very usefully. I'll start by showing you a simple Mysql Class file. You can follow the comments in the code for more help. I'll go over everything after the code. Make sure you save this file (Mysql.php) in the inc folder. If you choose not to make sure you keep up with your paths.
<?php
define( DBHOST, "your host goes here", true ); // The true makes it so you have to type in DBHOST
define( DBUSER, "your username", true ); // in caps to retreive the string.
define( DBPASS, "your password", true );
define( DBNAME, "your database", true );
class Mysql
{
/****************************************
* function __construct()
****************************************/
public function __construct()
{
mysql_connect( DBHOST,DBUSER,DBPASS ) or die( mysql_error() );
// This says connect to the database or exit and
// give me the reason I couldn't connect.
mysql_select_db( DBNAME ) or die( mysql_error() );
// This will select the db you require or exit and
// give a reason why it couldn't
}
// Now we'll create a few functions that will help us retreive
// information from the database.
/****************************************
* function select()
* params - $fields = The fields you want to have in the array.
* - $table = The table you want to return information from.
* - $where = This allows you to pick with database records you want to return.
* - $orderby = This determinds the return order of the select.
* - $limit = this will control the amount of records the select returns.
****************************************/
public function select($fields,$table,$where='',$orderby='',$limit='')
{
if($where != '') $where = " WHERE $where";
// If a $where variable is pass into the
// function set the $where variable.
if($orderby != '') $orderby = " ORDER BY $orderby";
// If a $orderby variable is pass into the
// function set the $orderby variable.
if($limit != '') $limit = " LIMIT $limit";
// If a $limit variable is pass into the
// function set the $limit variable.
$recordSet =
mysql_query(
"SELECT $fields FROM $table" . $where . $orderby . $limit // Set the SELECT for the query
) or die(
"Selecting $table - SELECT $fields FROM $table" . $where . $orderby . $limit .
" - " . mysql_error()
// If the query fails, we'll exit the function and
// print this string to the screen.
);
if (!$recordSet) // A quick check to see if the query failed. This is a backup to the previos die()
{
return "Record Set Error";
}
else
{
$recordSet = new MysqlRecordSet( $recordSet );
// MysqlRecordSet lets you control the query resource a better. I'll explain it later in
// this code section.
}
return $recordSet; // Return the $recordSet whether it passed or now.
}
// The rest of the functions work very similiar and are coded the same way. If you have any questions
// about them leave a comment and I'll get with you about them.
public function insert($table, $fields, $where='')
{
if($where != '') $where = " WHERE $where";
$query =
mysql_query(
"INSERT INTO $table SET $fields" . $where
) or die(
"Insert Error - INSERT INTO $table SET $fields" . $where . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
public function update($table,$fields,$where='')
{
if($where != '') $where = " WHERE $where";
$query =
mysql_query(
"UPDATE $table SET $fields" . $where
) or die(
"Update Error - UPDATE $table SET $fields" . $where . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
public function delete($table,$where)
{
$query =
mysql_query(
"DELETE FROM $table WHERE $where"
) or die(
"Delete Error - DELETE FROM $table WHERE $where" . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
// This function gets the last mysql insert Id.
public static function getInsertId()
{
return mysql_insert_id();
}
}
class MysqlRecordSet // Allows you to utilize the resourses of the returned mysql_query().
{
var $recordSet;
function MysqlRecordSet( &$recordSet ) { $this->recordSet = $recordSet; return; }
function __construct( &$recordSet ) { $this->recordSet = $recordSet; return; }
function getRecordCount() { return mysql_num_rows($this->recordSet); }
// Returns the record count
function seek( $recordIndex ) { return mysql_data_seek( $this->recordSet, $recordIndex ); }
// Seek to a specific record
function getFirstRecord() {
mysql_data_seek( $this->recordSet, 0 );
return mysql_fetch_array( $this->recordSet ); }
// Seek to the first Record
function getNextRecord() { return mysql_fetch_array( $this->recordSet ); }
// Go to the next record
function getLastRecord() {
mysql_data_seek( $this->recordSet, mysql_num_rows($this->recordSet)-1 );
return mysql_fetch_array( $this->recordSet ); }
// Seek to the last record
function free() { return mysql_free_result( $this->recordSet ); }
// free the result.
}
?>
Ok, now to explain some of the functions in simple terms.
public function select()
This function works just the same way mysql_query("SELECT ....") would work. It return a MysqlRecordSet which allows you to iterate and control the mysql resource better than using mysql_fetch_array() throughout your code. I Have to give credit to My Boss and fellow developer for the MysqlRecordSet class. You can visit his website here at mmckinneysystems.com.
public function insert()
public function update()
public function delete()
All of these functions work just like the function name says. They run the according mysql command, returning true or false.
The next step in this process is to create your news class. I'll place the code here and put comments in it. I'll explain more after the code. Make sure you save the file (News.php) in the inc folder.
<?php
require_once 'Mysql.php'; // include this file so we can use the Mysql Class inside this class as well.
class News
{
// In this class we'll have a few variables to be used by the class.
var $mysql; // the mysql variable that will hold the Mysql Object.
public function __construct()
{
$this->mysql = new Mysql(); // Connect to mysql and the database so we can use it.
}
// This function does what it says, it gets all of the current news records from the db.
public function getNews()
{
$rset = $this->mysql->select('*',"cs_news","postDateTime <= NOW()","postDateTime DESC");
return $rset;
}
// You should update this function how you see fit. You could add more params to return only
// the fields you want.
// This will get a single record in the database.
public function getNewsById($id)
{
$rset = $this->mysql->select('*',"cs_news","newsId='$id'");
return $rset;
}
// Your going to need some way to add and edit your news entries right?
// well here ya go :P
// This function will save you lots of time writing out the fields variable everytime.
public function buildFields($post, $sep=" ") // $post comes in as an array of variables.
{
$fields = ""; // This makes sure we don't run into any past fields.
foreach($post as $key => $value)
{
// foreach will take each element of the $post array and seperate
// each of the values with its key $post[key] = value;
$value = mysql_escape_string($value); // We'll do a small security check here.
// I'll explain that in another tutorial. Basically it protect mysql from hackers.
if($i == 0)
$fields .= "$key='$value'";
else
$fields .= $sep . "$key='$value'";
// This will create your fields string based on each element in the post array.
$i++;
}
return $fields; // Return the string, $fields.
}
public function addNews($post)
{
$fields = $this->buildFields($post, ", "); // take the post array and break it into a string.
if( $this->mysql->insert("cs_news",$fields) ) // This is pretty basic. Inserts the new news record.
{
return true;
}
else
{
return false;
}
}
// This function is just the same as addnews, except that it updates an existing record.
public function editNews($post)
{
$fields = $this->buildFields($post, ", ");
$newsId = $post['newsId']; // retreive the newsId we need to update
if( $this->mysql->update("cs_news",$fields,"newsId='$newsId'") )
{
return true;
}
else
{
return false;
}
}
public function deleteNews($newsId)
{
if( $this->mysql->delete("cs_news","newsId='$newsId'") )
{
return true;
}
else
{
return false;
}
}
}
?>
Alright there is your news class. Its fairly simple. Gives you five different options. You can get all of the records, one of the record, add, edit, and delete. All of the basic functions you would likely need. Now all we need is some databases to retreive and add this information too. So in this next section i'll explain how to create your database.
First we'll begin by creating a database. I'll show you three different ways you could do it. The first way is using a php file. Below is the code for that. Save the file in your root directory. You can name it createDb.php, you won't use it after this.
<?php
// TO save yourself some time you can use your mysql class to connect.
require_once 'inc/Mysql.php';
$mysql = new Mysql();
// This is the create statement I used to create the database.
$sql = "
CREATE TABLE `cs_news` (
`newsId` int(10) unsigned NOT NULL auto_increment,
`companyId` int(10) unsigned NOT NULL,
`userId` int(10) unsigned NOT NULL,
`newsTitle` varchar(45) NOT NULL,
`newsSummary` varchar(100) NOT NULL,
`newsArticle` longtext NOT NULL,
`postDateTime` datetime NOT NULL,
`currentStatus` enum('Enabled','Disabled') collate latin1_general_ci NOT NULL default 'Disabled',
PRIMARY KEY USING BTREE (`newsId`)
) ENGINE=MyISAM AUTO_INCREMENT=7;
";
mysql_query( $sql ) or die( mysql_error() );
// If the page doesn't die we know the database got updated.
// If you want to make sure that it worked you can use the next bit of code to do that.
$sql = "
SHOW COLUMNS FROM cs_news
";
$rset = mysql_query( $sql );
$record = mysql_fetch_array( $rset );
?><pre><?php print_r( $record ); ?></pre><?php
?>
If you have access to a script called phpMyAdmin, which can be found on most web servers now. You can use phpMyAdmin to create the database. Either using that create statement from the previous code section or using there functions. The last way you can create this database is by downloading the free Mysql Software tools. They have a set of tools that are designed to help the average and professional developers. You can find the software here. Once, you download it and have installed it, its fairly simple to use. When you have it up the first thing its going to ask you the information for your Mysql server. Fill out the information and press connect. Once you've connected towards the right side of the program, you'll right click and and say create new database. Fill out the name of your database. Then right clicking in the same area and select create new table. Here you can fill in the table name and each one of the fields.
This tool is a lot to figure out your first time around. I'll create more tutorials on this program and mysql at a later date. If you feel like this method is to complex or you dont have remote access to your server use the first method.
The next part we are going to touch on is going to be the php pages that will display your new found new and mysql classes. The first page we are going to create is going to be our index or home page. You can name it which ever you want. I am going to use index for this example. Make sure you save it in your root directory. I am going to show you the code, then explain it as we go. I'll explain it in detail after the code.
<?php
require_once 'inc/News.php'; // Make sure we include the news class file
$news = new News(); // create a new news object
$newsRecordSet = $news->getNews(); // set newsRecordSet to a MysqlRecordSet
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style></head>
<?php
// Here you can create your self any sort of table to display your news article.
?>
<body>
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="3" align="center" valign="middle" bgcolor="#000000"><span class="style1">News</span></td>
</tr>
<?php
// This while will loop through all of the records as long as there is another record left.
while( ($record = $newsRecordSet->getNextRecord()) !== false )
{ // Basically as long as $record isn't false, we'll keep looping.
// You'll see below here the short hand for echoing php strings.
// <?=$record[key] - will display the value for that array.
?>
<tr>
<td colspan="2">
<strong><?=$record['newsTitle']?></strong>
</td>
<td width="36" align="center" valign="top">
<a href="editNews.php?id=<?=$record['newsId']?>">Edit</a> </td>
</tr>
<tr>
<td colspan="3" align="left" valign="top">
<?=$record['newsArticle']?>
</td>
</tr>
<tr>
<td colspan="3" align="left" valign="top">
<?=$record['userId']?>
</td>
</tr>
<?php
}
?>
</table>
<br /><br />
<a href="addNews.php">Add News</a>
</body>
</html>
This code is extremely simple. This is the part where you're creativity comes in hand. Make your own table or site to display the news information. All you have to make sure you have is the php and php echos in there html somewhere. Remember.
<?=$variable[KEY]?>
That code will display the $variable's VALUE for that KEY.
Now we have at least one page finished. For now you wont see anything on that page. You're going to need to create the addNews.php page and add a record to the database before something shows up. This next page will do just that. As with every other page in this tutorial i'll explain as much as I can in this code and go over it again afterwards.
<?php
require_once 'inc/News.php'; // Include The News Class
$news = new News(); // Create a new News Object
if(isset($_POST['addNews'])) // If the submit button was clicked
{
$post['newsTitle'] = $_POST['newsTitle'];
// add the news title to the $post array
$post['newsArticle'] = $_POST['newsArticle'];
// add the news article to the $post array
$post['postDateTime'] = date("Y-m-d H:i:s", time());
// add the current time and date to the $post array
$post['userId'] = 1;
// You can ignore userId, unless you have mutilple users
$post['currentStatus'] = 'Enabled';
// This will make sure its displayed
if($news->addNews($post))
{
echo 'The News was Added Succesfully'; // if adding was successful display this message
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style>
</head>
<body>
<a href="index.php">Index<a><br />
<form id="form1" name="form1" method="post" action="addNews.php">
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" align="center" valign="top" bgcolor="#000000"><span class="style1">Add News</span></td>
</tr>
<tr>
<td width="92">News Title :</td>
<td><label>
<input type="text" name="newsTitle" id="newsTitle" />
</label></td>
</tr>
<tr>
<td>News Article :</td>
<td><label>
<textarea name="newsArticle" cols="50" id="newsArticle"></textarea>
</label></td>
</tr>
<tr>
<td colspan="2" align="center" valign="top"><label>
<input type="submit" name="addNews" id="addNews" value="Add News" />
</label></td>
</tr>
</table>
</form>
</body>
</html>
Very very very simple. Not a whole lot to explain on this one. There isn't a lot to explain with the editNews.php page either. Its exactly the same page except at the beginning of the page you check to see if an id is passed to you in the url. Take the id do getNewsById($id) and display the newsTitle and newsArticle in the approprieate fields. Here is the code for editNews.php
<?php
require_once 'inc/News.php'; // Include The News Class
$news = new News(); // Create a new News Object
$id = $_GET['id']; // This is an unsafe way to get the information. But its the easiest in our case.
if(isset($_POST['editNews'])) // If the submit button was clicked
{
$post['newsId'] = $id;
// add the news Id
$post['newsTitle'] = $_POST['newsTitle'];
// add the news title to the $post array
$post['newsArticle'] = $_POST['newsArticle'];
// add the news article to the $post array
$post['postDateTime'] = date("Y-m-d H:i:s", time());
// add the current time and date to the $post array
$post['userId'] = 1;
// You can ignore userId, unless you have mutilple users
$post['currentStatus'] = 'Enabled';
// This will make sure its displayed
if($news->editNews($post))
{
echo 'The News was Edited Succesfully'; // if adding was successful display this message
}
}
if(isset($_POST['deleteNews']) and $news->deleteNews($id)) // This if statement says that if the user
// clicked the Delete button, we'll delete
// the news and return to the index page.
{
header("Location: index.php"); // This php function is used to redirect the user to the index page.
}
$newsRecordSet = $news->getNewsById($id); // get the record set for this Id.
$record = NULL; // This will make sure that we dont have the same record when we refresh the page.
if($newsRecordSet->getRecordCount() == 1)
{
$record = $newsRecordSet->getNextRecord();
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style>
</head>
<body>
<a href="index.php">Index</a><br />
<form id="form1" name="form1" method="post" action="editNews.php?id=<?=$record['newsId']?>">
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" align="center" valign="top" bgcolor="#000000"><span class="style1">Edit News</span></td>
</tr>
<tr>
<td width="92">News Title :</td>
<td><label>
<input type="text" name="newsTitle" id="newsTitle" value="<?=$record['newsTitle']?>"/>
</label></td>
</tr>
<tr>
<td>News Article :</td>
<td><label>
<textarea name="newsArticle" cols="50" id="newsArticle"><?=$record['newsArticle']?></textarea>
</label></td>
</tr>
<tr>
<td colspan="2" align="center" valign="top"><label>
<input type="submit" name="editNews" id="editNews" value="Edit News" /> -
<input type="submit" name="deleteNews" id="deleteNews" value="Delete" />
</label></td>
</tr>
</table>
</form>
</body>
</html>
The edit news pages is almost exactly the same as the add news pages, with exception to the id is used to update the news record. Also you'll notice a button next to the Edit news button that says delete. Clicking this will delete the current newsId and return you to the index page. Be careful with this however, if you dont come up with something to make sure the user wanted to delete this record. Thats another tutorial all together.
Thats basically it. Make sure you have your database set up and open up the index page in your browser. Nothing should be there except a black bar with News in the middle of it. Below that should be a link to the add news page. Click on it. It will bring you to a page that has 3 things. (1) A title bar that says add news. (2) A input form for adding the news Title. (3) A section to write out your news article.
If anything doesn't look right to you, you can leave me some comments and I'll try and answer you question as soon as possible. Thanks for reading in on my tutorial.
Thanks,
Arsenal19
