Building your own myspace.com with PHP Part II: Designing the database

Posted by Stefan on July 31st, 2007 at 08:57pm

In this part of the tutorial we are going to create the back end database that will hold all the information that we need to save for our script. We will create the table structure to prepare the database for the information we want it to hold. We will also create a wrapper class for MySQL that we will use to make our database calls as painless and clean as possible.

Pre requirements

In order to complete this tutorial you will need a MySQL database. Some basic knowledge of the SQL syntax would’nt hurt as well.

Creating the database tables

For our basic community script we are going to need three different tables in our database.

• members - Holds user information (username, password etc).
• guestbook - Used by our guestbook class to hold posts in our members guestbooks.
• friends - Keeps a list of friends.

The structure of the table members looks like this:


CREATE TABLE `members` (
   `id` int(11) NOT NULL auto_increment,
   `username` varchar(32) NOT NULL,
   `password` varchar(32) NOT NULL,
   `presentation` mediumtext NOT NULL,
   PRIMARY KEY  (`id`)
)  ;

If you open up PHPMyAdmin or your database administration tool of choice and paste the code above in the query window you should have the first table loaded into the database. The fields should be pretty self explanatory, basically we save the username and password of the current member along with his personal presentation.

Moving on to the next table “guestbook” the structure looks like this:


CREATE TABLE `guestbook` (
  `id` int(11) NOT NULL auto_increment,
  `owner` varchar(32) NOT NULL,
  `postedby` varchar(32) NOT NULL,
  `post` varchar(1024) NOT NULL,
   `time` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ;


What all the fields in the table does will become clear once we get to implement the guest book so don’t get discouraged if you are not sure how this is supposed to work. Anyway the idea is to save all posts by all users on every guest book in a single table for the simple reason that it is the easiest way to do it. The field “owner” identifies who the current post belongs to.

The simplest table of them all is the table used to hold the relationships between members.


CREATE TABLE `friends` (
  `id` int(11) NOT NULL auto_increment,
  `member` varchar(32) NOT NULL,
  `friendwith` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ;


The field “member” identifies a current member of the community and “friendwith” tells us who he has added as a friend to his presentation.

The database class

Now when we got the database up and running we will need a way to put some data into it. PHP comes with a nice set of functions by default that is quite easy to use. However i prefer to use my own wrapper class around the standard PHP functions to make things a bit easier to use and to improve readability of the code.


<?php
////////////////////////////////////////////////////////////////////////////////////////
// Class: DbConnector
///////////////////////////////////////////////////////////////////////////////////////
require_once 'DbVars.php';
 
class DbConnector extends Dbvars {
 
        var $theQuery;
        var $link;
  var $dbname;
  var $host;
  var $user;
  var $pass;
    
   function DbConnector(){
        // Load settings from parent class
        $settings = Dbvars::getSettings();
        // Get the main settings from the array we just loaded
        $this->host = $settings['dbhost'];
        $this->dbname = $settings['dbname'];
        $this->user = $settings['dbusername'];
        $this->pass = $settings['dbpassword'];
    }
  
   function setDatabase($ndbname)
  {
    $this->dbname = $ndbname;
  }
  
  function connect()
  {
                 // Connect to the database
                 $this->link = mysql_connect($this->host, $this->user, $this->pass)or die(mysql_error());
                 mysql_select_db($this->dbname);
  }
 
    function query($query) {
 
        $this->theQuery = $query;
        $res = mysql_query($query, $this->link)or die(mysql_error());
  return $res;
 
    }
 
    function fetchArray($result) {
 
       return mysql_fetch_array($result);
 
    }
 
}
?>


We also need to create a file that will hold our database information.


<?php
// DbVars.php
class Dbvars {
 
    var $settings;
    
    function getSettings() {
 
        // Database variables change accordingly
        $settings['dbhost'] = 'localhost';
        $settings['dbusername'] = 'username';
        $settings['dbpassword'] = 'password';
        $settings['dbname'] = 'community';
 
        return $settings;
    }
}
?>
 


We now have everything we need to start reading and writing to the database. To set up a connection and get some data from the database we simply do the following.


require_once("DbConnector.php");   // Include the database class
$db = new DbConnector();               // Create an instance of the database class
$db->connect();                               // Connect to the database
$query = "SELECT * FROM table;    // Perform a query to the database
$result = $db->query($query);
$rows = $db->fetchArray($query);  // Get an array with the results
echo $rows["data"];                          


Next part: Register and login with sessions

Under MySQL+ PHP+ Tutorials