In this tutorial we will build a basic classifieds software in PHP and MySQL. The software will do the following:
| [ad#square-250] |
1. Allow everyone to submit classified (no registration required)
2. Classifieds will be listed in categories
3. The main page will list all categories along with the number of classifieds in them
4. Each category will list the classifieds in it ordered by the time of posting, descending order
5. A small administration will be provided where the administrator will add/edit categories. This page is not included in the tutorial as for testing you can just use phpMyAdmin and add/edit/delete categories from it.
This software will use the MVC concept (although very simple one).
|
Database Structure
The database structure will be simple. We need 3 tables: classifieds, categories and admin. Here are the CREATE TABLE statements with some short explanation:
CREATE TABLE `classifieds` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
`category_id` INT UNSIGNED NOT NULL,
`title` VARCHAR(255) NOT NULL,
`content` TEXT NOT NULL,
`contact_details` TEXT NOT NULL,
`date` DATE NOT NULL
);
Optionally you can make category_id a foreign key to the next table so when a category is deleted, the classifieds in it are deleted too (or use RESTRICT clause and disallow deleting a category that has classifieds in it).
CREATE TABLE `categories` (
` id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
` name` VARCHAR(255) NOT NULL
);
This categories table is very simple as categories contain only name. You may decide to add description, some kind of status field (active/inactive) and so on.
The following table will contain the login information for the administrator:
CREATE TABLE admin (
` id` TINYING UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
` email` VARCHAR(255) NOT NULL,
` pass` VARCHAR(100) NOT NULL
);
Most probably you will use only one admin (superadmin), but having a table for the administrator’s login will allow you to add and use more than 1 admin in the future if that becomes a requirement.
Models
As we follow the MVC concept we’ll delegate the business logic of this software to the model layer. Models are PHP classes that control the database operation for creating, editing and showing classifieds and categories.
For simplicity in this tutorial I will use SQL queries inside the models for every database operation. However it’s highly recommended that you use some database class rather than the mysql_query calls. It’s also a good idea to use some sort of ActiveRecord/ORM implementation so add/edit/delete operations can be handled without typing SQL. For example see PHP ActiveRecord or the basic model in Celeroo framework. Discussing ORM itself is out of the scope of this tutorial so let’s continue with the other things.
We will use two models – one for categories and one for classifieds. At this point we will not build model for admin as the only place where we’ll deal with the admin table will be authentication. I am leaving authentication out of this tutorial. You can see how to build simple PHP/MySQL user authentication here.
Model Category:
<?php
class Category()
{
function __construct()
{
// for this tutorial there will be nothing inside this method
// in the real app you could possibly add some initialization
}
function add($name)
{
$this->filter($name);
$q="INSERT INTO categories (name) VALUES (\"$name\")";
mysql_query($q) or die(mysql_error());
}
function edit($name, $id)
{
$this->filter($name);
// add some basic security
if(!is_numeric($id)) exit;
$q="UPDATE categories SET name=\"$name\" WHERE id='$id'";
mysql_query($q);
}
// category name allows only alphanumeric
private function filter($name)
{
$name=preg_replace("/\W/","",$name);
return $name;
}
function delete($id)
{
if(!is_numeric($id)) exit;
$q="DELETE FROM categories WHERE id='$id'";
mysql_query($q);
}
// method to list the categories for admin edit/delete
function list()
{
$q="SELECT * FROM categories ORDER BY name";
$result=mysql_query($q);
$cats=array();
while($cat=mysql_fetch_array($result)) $cats[]=$cat;
return $cats;
}
// method to return categories along with number of ads in them
function list_count()
{
$q="SELECT COUNT(tA.id) as classifieds, tC.name as name, tC.id as id
FROM categories tC LEFT JOIN classifieds tA ON tA.category_id=tC.id
ORDER BY tC.name";
$result=mysql_query($q);
$cats=array();
while($cat=mysql_fetch_array($result)) $cats[]=$cat;
return $cats;
}
// method to return full category data for a single category
function select($id)
{
if(!is_numeric($id)) exit;
$q="SELECT * FROM categories WHERE id='$id'";
$category=mysql_fetch_array(mysql_query($q));
return $category;
}
}
?>
Few things to notice here: in the methods that receive $id we are checking whether it is numeric to prevent eventual SQL injections.
Another thing to pay attention to is the LEFT JOIN in list_count method. If we don’t use LEFT only the categories which have classifieds in them will be returned. But we want to show all, even those with zero ads inside.
Then we need a simple class to add a classified and list classifieds:
<?php
class Classified
{
function __construct()
{
// empty in our basic version
}
function add($vars)
{
if(!is_numeric($vars['category_id'])) exit;
$vars['title']=mysql_real_escape_string($vars['title']);
$vars['content']=mysql_real_escape_string($vars['content']);
$vars['contact_details']=mysql_real_escape_string($vars['contact_details']);
$q="INSERT INTO classifieds (category_id,title,content,contact_details,date)
VALUES ('$vars[category_id]',\"$vars[title]\",\"$vars[content]\",
\"$vars[contact_details]\",CURDATE())";
mysql_query($q);
$id=mysql_insert_id();
return $id;
}
// list classifieds in a category
function list($category_id)
{
if(!is_numeric($category_id)) exit;
$q="SELECT * FROM classifieds WHERE category_id='$category_id'
ORDER BY id DESC";
$result=mysql_query($q);
$ads=array();
while($ad=mysql_fetch_array($result)) $ads[]=$ad;
return $ads;
}
}
?>
The code here is pretty straightforward. Notice the security checks and mysql_real_escape_string calls to prepare the data for inserting in the DB.
Controllers
The controllers are parts of the program which handle the interaction with the user input and prepare the output. In our case the controllers will be simple.
Home page:
This will be index.php so it gets loadedby default. It needs to list all the categories along with the number of classifieds.
<?php
// this may not be required if you use php autoload function
require_once("models/category.php"); // assuming we have put models in models/ folder
$_category=new Category();
$cats=$_category->list_count();
require_once("views/index.html");
?>
As you can see once you’ve built model layer, controllers are pretty simple. This one just creates a model instance $_category and calls the method list_counts to receive array of categories.
Submit classified
We need a page that will allow people to submit classifieds. It will be linked from each category page and the category ID will be passed in the URL. Let’s call this page submit.php:
<?php
// again this may be unnecessary
require_once("models/classified.php");
$_classified=new Classified();
if(!empty($_POST['submit']))
{
$id=$_classified->add($_POST);
// redirect to see the posted classified
header("Location: classified.php?id=$id");
exit;
}
require("views/submit.html");
?>
This page initially displays the posting form. When the form is submitted, it uses the model add() method to insert the classified, to get its ID and to redirect to the page where the classified is shown. Simple, isn’t it?
Category page
This page will be reached when the visitor clicks a link on the homepage. The links on the homepage will contain the category_id in the URL, for example like this: category.php?id=X. When you see the views everything will become clear.
So here is category.php controller:
<?php
require_once("models/category.php");
require_once("models/classified.php");
$_category=new Category();
$_classified=new Classified();
// select category
$category=$_category->select($_GET['id']);
$classifieds=$_classified->list($category['id']);
require_once("views/category.html");
?>
Note that for simplicity I have omitted the DB connection part from the controllers. It’s highly recommended that you create a file which will create the database connection and then include/require this file in all controllers so the connection is available in them.
Views
Views are the HTML pages that display the content and web forms to the users. They complete the entire software.
You need to create header and footer HTML files. The header will contain not only the visible header but also the HTML head tag with CSS, javascripts etc. Let’s call these files header.html and footer.html and put them in folder views/ where will be all the other views as well.
Home page:
This will be called index.html and is included from index.php controller. Here is the content:
<?php require(“views/header.html”);?>
<h1>Home Page</h1>
<?php foreach($cats as $cat):?>
<div style=”float:left;”>
<h2><a href=”category.php?id=<?=$cat[‘id’]?>”><?=$cat[‘name’]?></a></h2>
<p><?=$cat[‘classifieds’]?> ads</p>
</div>
<?php endforeach;?>
<?php require(“views/footer.html”);?>
Note that in the views I use the alternative PHP control structure syntax. It clutters the HTML code less and doesn’t confuse so much designers that may eventually need to work with your views.
Now this page goes to category.php which lists the ads in a category. Here is the associated view category.html:
Category page:
<?php require(“views/header.html”);?>
<h1><?=$category[‘name’]?></h1>
<p><a href=”submit.php?category_id=<?=$category[‘id’]?>”>Submit classified</a></p>
<?php foreach($classifieds as $classified):?>
<div style=”clear:both;border:1px solid black;padding:5px;margin:5px;”>
<p><b><?=$classified[‘title’]?></b></p>
<p><?=nl2br($classified[‘content’]);?></p>
<p>Contact details: <?=nl2br($classified[‘contacts’]);?></p>
<p>Published at: <?=$classified[‘date’]?></p>
</div>
<?php endforeach;?>
<?php require(“views/footer.html”);?>
Finally we need the web form for submitting new classified. Let’s call this page submit.html:
Submit classified:
<?php require(“views/header.html”);?>
<h1>Submit classified</h1>
<form method=”post” action=”submit.php”>
<input type=”hidden” name=”submit” value=”1″>
<input type=”hidden” name=”category_id” value=”<?=$_GET[‘category_id’]?>”>
<p><label>Title:</label> <input type=”text” name=”title”></p>
<p><label>Content:</label> <textarea name=”content”></textarea></p>
<p><label>Contact details:</label> <textarea name=”contacts”></textarea></p>
<p><input type=”submit” value=”Submit classified”></p>
</form>
<?php require(“views/footer.html”);?>
That’s it! Once you create the database connection and include the file in all controllers, your software will be ready to run.
You can build this software for just one hour.
How to improve the software
Of course this is a very basic and simple classifieds software. Here are some ideas how you can improve it:
– Require registration and allow users login and edit/delete their ads
– Make sure classifieds automatically expire after some time
– Add validations on the submit classified page
– Add contact form to allow visitors contact the classified posters
– Add pagination in the category page
– Format the classified post date to some better human readable format
– Check for duplicate ads
– Allow unlimited levels of subcategories
– and so on
This tutorial is giving you the basic and then you can continue and elaborate the software as much as you wish.
Any comments are welcome.
