Simple PHP MySQL Blog Example

Posted by on . 2 comments.

OK, this is just one of many PHP ad MySQL implementation, it is my course which I’m taking actually. So in this post is what my lecturer gave to us. Lets dive into our first step. OK, no more talk, lets practice.

Developing a web application based on PHP and MySQL as its database can be done by many tools, you can use EclipsePDT, Adobe® Dreamweaver®, Netbeans, and many more. We ‘ll not cover many PHP IDE feature, so we can focus on simplicity of this tutorial. To proceed to the next step we’ll use these tools:

  • Notepad++ (free text editor with php syntax highlighting)
  • SRWare (free web browser)
  • XAMPP 1.7.3 with PHP 5.3.1 and MySQL 5.1.41
  • This sample database

and now, we can try to cook from some PHP and MySQL,

Run your XAMPP, so apache(php web server) and MySQL server started.

  1. Create a MySQL database, we named it simpleblog, and import the sample database, (If you need detail you can always ask by commenting below).
  2. Prepare your web folder in xampp htdocs folder. We named it simpleblog.
  3. Create MySQL connection file to connect PHP to MySQL. Open Notepad++, create new file, save as MysqlConn.php and save it inside simpleblog/lib folder(note: create lib folder inside simpleblog folder). Copy and paste the next code:
  4. Set user, password, database name, match to your XAMPP setting.
<?php
/* @created     nov 10 2007 13:00
* @lastmodified
* @package
* @file         obj/MysqlConn
* @desc         extends class Connection
*               support get single / multiple array
*               - use getArrayAssoc / getArrayNum -> single data
*               - use getDataAssoc / getDataNum -> mltiple data
*               Assoc --> association array,
*               Num --> numeric array
*
* copyright (c)2001 - 2007 arif laksito
*/
Class Connection {
var $host;
var $dbname;
var $user;
var $pass;
var $error;

function Connection() {
$this->host   = 'localhost';
$this->dbname = 'simpleblog';
$this->user   = 'root';
$this->pass   = 'pass';
}

}

Class MysqlConn extends Connection {

var $db;    // var db conn
var $str;   // var statatement query
var $rst;   // var result query

// constructor class
// assign variable connection
function MysqlConn() {
$this->Connection();
}

// open connection MsSQL DB
function openConnection() {
$this->db = mysql_connect($this->host,$this->user,$this->pass)
or die('connection error..');
mysql_select_db($this->dbname, $this->db)
or die('conect to db error..');
}

// execution query statement
function execute($str) {
$this->openConnection();
$this->str = $str;
if($this->rst = mysql_query($this->str, $this->db))
return true;
else{
die( 'sql error : '.mysql_error() );
return false;
}

$this->closeConnection();
}

// get array association from execution query
// return false on error
function getArrayAssoc() {
if($this->rst) {
if($this->row = mysql_fetch_array($this->rst, MYSQL_ASSOC))
return $this->row;
else
return false;
}
}

// get array numeric from execution query
// return false on error
function getArrayNum() {
if($this->rst) {
if($this->row = mysql_fetch_array($this->rst, MYSQL_NUM))
return $this->row;
else
return false;
}
}

// return data execution query on object data
// association object
function getObjectAssoc() {
if($this->rst) {
if($this->row = mysql_fetch_object($this->rst, MYSQL_ASSOC))
return $this->row;
else
return false;
}
}

// return data execution query on object data
// numeric object
function getObjectNum() {
if($this->rst) {
if($this->row = mysql_fetch_object($this->rst, MYSQL_NUM))
return $this->row;
else
return false;
}
}

// get set array from query result
// 'select' tipe query
// data --> association array
function getDataAssoc() {
$dataSet = array();
$i = 0;
while($data = $this->getArrayAssoc()) {
for($j=0; $j < $this->getNumFields(); $j++) {
$field = mysql_field_name($this->rst, $j);
$dataSet[$i][$field] = $data[$field];
}
$i++;
}

return $dataSet;

}

// data --> numeric array
function getDataNum() {
$dataSet = array();
$i = 0;
while($data = $this->getArrayNum()) {
for($j=0; $j < $this->getNumFields(); $j++) {
$dataSet[$i][$j] = $data[$j];
}
$i++;
}

return $dataSet;
}

function getNumRows() {
if($this->numRows = mysql_num_rows($this->rst))
return $this->numRows;
else
return false;
}

function getNumFields() {
if($this->numFields = mysql_num_fields($this->rst))
return $this->numFields;
else
return false;
}

function getAffRows() {
if(mysql_affected_rows()>0)
return true;
else
return false;
}

function closeConnection() {
mysql_close($this->db);
}
}
?>

Create index.php file inside simpleblog folder. copy and paste this:

<?php
include_once 'lib/MysqlConn.php';
$sql = new MysqlConn();
$str = "SELECT beritaId, beritaTitle, beritaDetail, beritaTime, nama
from berita left join alumni On berita.beritaSender = alumni.id_alumni;";

$sql->execute($str);
$data = $sql->getDataAssoc();

print '<pre>';
print_r($data);
print '</pre>';

?>

Ok, for now lets try open the url, for me it will be http://localhost/simpleblog. It should be printing some words like this:

<pre>Array
(
    [0] => Array
        (
            [beritaId] => 3
            [beritaTitle] => Internet Semakin diminati Wanita
            [beritaDetail] => San Fransisco, Kamis

Meskipun, pria dan wanita punya minat yang berbeda dalam menggunakan jaringan internet, lambat laun jumlahnya semakin seimbang.
Gambaran ...

For the next step lets make it more web look. Since this post don’t cover xhtml/css theme creating, so we’ll take some free theme from the net, for example freecsstemplates.org. We’ll take sparkling.
Copy unzipped theme to simpleblog folder. So the index.php and index.html which came from theme will be in the same folder.
Rename index.php to something else, index.old.php for example. Rename index.html to index.php. Copy and paste code below into your new index.php, before the html tag, you can paste it on line 13 :

<?php
include_once 'lib/MysqlConn.php';

$sql = new MysqlConn();

$str = "SELECT beritaId, beritaTitle, beritaDetail, beritaTime, nama
from berita left join alumni On berita.beritaSender = alumni.id_alumni;";

$sql->execute($str);
$data = $sql->getDataAssoc();
?>

on line 58 change the code into this:

<div class="post">
<?php foreach($data as $itm) {?>
<h2 class="title"><a href="#"><?php echo "$itm[beritaTitle]";?></a></h2>
<p class="meta"><span class="date"><?php echo "$itm[beritaTime]";?></span><span class="posted">Posted by <a href="#"><?php echo "$itm[nama]";?></a></span></p>
<div style="clear: both;">&amp;nbsp;</div>
<div class="entry">
<p><?php echo "$itm[beritaDetail]";?></p>
<p class="links"><a href="#">Read More</a></p>
</div>
<?php } ?>
</div>

As you can see from the code above, the line which write the content of the post (post body) was line 7. The code just barely write all content body. We’ll change the code, so just some of the content will shown on the front page. Replace with this one:

<p><?php echo substr($itm[beritaDetail], 0, strrpos(substr($itm[beritaDetail], 0, 300), " ")); ?></p>

When you click read more, still nothing happen yet. We’ll make one php file again to handle full post body.