PHP/MySQL Tutorial: Classifieds Software In 1 Hour

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.


12 thoughts on “PHP/MySQL Tutorial: Classifieds Software In 1 Hour

  1. Earl Gile

    I am really enjoying this tutorial. I have been searching forums and the internet for days for a tutorial on building an classifieds software because I was unhappy with all the prebuilt plugins I was seeing and other classifieds software to work with. Thank you Codin

  2. Mak

    I cant seem to get this to work. Home page says..
    Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Users/Amish/Sites/basicclassifieds/models/category.php on line 63

    I dont know how to resolve this issue. Any advice?

  3. Neilson

    This is exactly what im looking for…
    A plain classifieds scripts!!

    Tho Im having the same bug as Mak said above..

    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;
    }

    The while statement is the line that output error.
    I still dont know how to resolve it so far

    Also i noticed a few typo error that you may want to update on your page:

    #1
    CREATE TABLE admin (
    ` id` TINYING UNSIGNED
    //Should be TINYINT

    #2
    <?php
    class Category()
    {
    // these () shouldnt be there

    #3
    submit.html

    // value=”1” is having an incorrect double quote symbols

    Thanks for this nice plain script and awaiting your advice concerning the bug i cant resolve

  4. Neilson

    Ive added a or die(mysql_error() on the $result=mysql_query($q);

    Ive came across this error : Unknown column ‘tC.name’ in ‘field list’

    Any thought on this error?

  5. Rocks

    could someone please give me a clue?!!
    -do i have to create an index page in the root directory,which handles the form?
    i am confused about the steps that i should follow!!
    i like the idea of creating a php classified ads since there is no other tutorials available out there.

    great tut 🙂

  6. Jorge Torres

    Very nice and simple tutorial. Although it is an old post, it is still useful. As for the error Neilson and others are getting, don’t know if you already solved it, but it might be within the SQL Statement. Try changing the following:
    FROM categories tC
    To:
    FROM categories as tC

    And changing:
    JOIN classifieds tA
    To:
    JOIN classifieds as tA

    Hope that helps.

  7. nikk

    ve came across this error : Unknown column ‘tC.name’ in ‘field list’

    Any thought on this error?

Leave a Reply

Your email address will not be published. Required fields are marked *