Software and Small Business

In this tutorial we will build a basic classifieds software in PHP and MySQL. The software will do the following:

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 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”);?>

<?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”);?>

<?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.

Your event calendar software now has an extended version called Eventy Plus. Instead of upgrading the original calendar and raising its price I decided to split it in two versions. Many customers like the calendar as is and there is no point in overloading it with features they don’t need. I know there are others however who need the few features of Eventy Plus:

• Multiuser interface. There is one superadmin and sub-admins. The sub-admins can add/edit/delete only their own events.
• Rich text editor CKEDITOR
• Sending email notifications for events with optional ICal support.

If you want to see other features, let me know! Feel free to comment on the prices as well.

A.M.Y. is our new ad management software for small to medium publishers. The software automates selling and maintaining advertising banners, text links and rich HTML ads for web publishers. There are detailed reports for both admin and advertisers, different types of ads, campaigns, pricing zones, plans etc.

We have started the product with a price of $29 as it’s still in beta stage. Once I get the first feedback from customers and add the most demanded extra features, the price will probably be  raised to $39.

You can check out the demo and let me know what you think. Any feedback is highly appreciated.

In this tutorial we will create a simple PHP shoutbox with Ajax based messaging. It will ask the user to choose a nickname and then allow him/her to use it (no registration and password will be required).

The Database

Given these requirements, we will only need 2 tables. I am giving them in pseudo-SQL code:

Table users:
id INT PRIMARY KEY
username VARCHAR(30)
last_action DATETIME

Table messages:
id INT PRIMARY KEY
user_id INT FOREIGN KEY to users.id
message TEXT

We will not require user registration but will still need to ask them to choose username. At the time of selecting it we will insert it into users table and will store it in session.

The Chat Box

The chat box contains two main parts – the text box where you enter messages and the area which shows the messages posted.

Here is the input code:

<p><textarea id=”myMessage”></textarea>

<input type=”button” value=”Send” onclick=”sendMessage();”></p>

I’m leaving the formatting to you. Then there will be also a div where the latest 10 messages will be displayed:

<div id=”chatArea”></div>

The Javascript Functions

There are 2 javascript functions needed: one will send messages, the other will retrieve them from the DB. The second function will be called when the document is loaded and refreshed in 10 seconds interval. It will also be called immediately when you post a message so you can see your own message without any delay.

For easy work with Ajax and DOM, download and install JQuery. We will use the library in this tutorial.

function sendMessage()
{
     $.ajax({
          type: "POST",
          url: "post.php",
          async: false,
          data: "message="+$("#myMessage").val(),
           success: function(msg){
                refreshChat();
           }
      });
}

This simple function just sends your message. Note that the function has async:false. This is because we clearly want posting to happen prior to refreshing the chat.

And this one is loading the chats:

function refreshChat()
{
      $.ajax({
          type: "POST",
          url: "chat.php",
           success: function(msg){
                $("#chatArea").html(msg);
           }
      });
}

This function is pretty simple too: it gets the chats from chat.php and displays the returned HTML in chatArea.

You need also to ensure this function runs all the time even if the user performs no action. To do this add the following javascript somewhere at the bottom of the HTML page with the chat:

window.setInterval(“refreshChat()”,10000);

The PHP Code

The script for posting chat message should be something like this:

<?php
session_start();

$q=”INSERT INTO messages (user_id,message) VALUES (‘$_SESSION[user_id]‘,
\”".addslashes($_POST['message']).”\”)”;

mysql_query($q);
?>

And the script which retrieves the last 10 chat messages:

<?php

$q=”SELECT tM.*, tU.username as username
FROM messages tM JOIN users tU
ON tU.id=tM.user_id
ORDER BY id DESC LIMIT 10″;
$result=mysql_query($q);

while($row=mysql_fetch_array($result))
{
echo “<p>”.$row['username'].” says: “,nl2br($row['message']).”</p>”;
}
?>

Here in the query we join the two tables to get the username along with the message. The output of the script is sent directly to the javascript function.

What To Do Further

This is a very simple php ajax chat. The code is missing few things which you can add once you understand it and make it working:

• Add date/time to messages
• Add error checking in case some query fails
• Make sure the user does not post empty message (you can add validation code to the javascript functions.
• Make sure the user does not enter malicious code in the chats
• The tutorial does not include the user registration part. You will need to do it yourself.

If you want to see similar script in action, check my free php chat software.

The latest version of the PHP Autoresponder is available. Besides some nice UI updates, the most important new feature is the ability to assign one autoresponder to several mailing lists. This will give you more flexibility for your email marketing campaigns.

Normally I don’t ask people for testimonials neither I did it this time. I consider my customer service very good, but I know I sometimes answer the support requests as a typical geek. So it always surprises me nicely when customer say such a nice thing.

Mike Moyer is using the software for his Trade Show Samurai site in a live environment so this has been a great test for me as well. He even discovered a bug which is useful for everyone who will buy the autoresponder.

Thanks Mike, and wish you success with your project.

Some people are just pain and you can’t satisfy them. I’ve had customers who lost hours of my time asking how to install software, how to use it, what other services to use etc., and at the end they asked for refund because they can’t manage with the information. You can do very little with such freetards. The best you can do is either to recognize them at the beginning and refund them before they lost your time or to clearly state that they won’t get a refund and why (you need to have a refund policy or TOS on your site). Of course some can even file a chargeback, but that’s an exception.

Photo by drinksmachine

However there are many genuine customers who may also end up asking for refund because they are unsatisfied with your product or support. In this post I’ll tell you what I do to keep the percentage of such requests very low.

Delivery Quickly

The more your client has to wait, the bigger is the risk she will be unsatisfied and ask for refund. Sometimes the client will not do it immediately but will get dissatisfied when other small problems add up (some minor glitch in the software, hard to understand manual etc). Most people expect instant delivery on digital items and you should aim for that. If for some reason you can’t delivery instantly, it is a good idea to explain how long will it take to ship during the order process and/or in the thankyou email you send after the purchase.

Make Clear and Detailed Descriptions of Your Products

You don’t want people who don’t understand your product to buy it. Don’t think that you’ll make more sales by using superlatives and hiding the disadvantages of your product. The customers who don’t like it will ask for refund and will lose your time complaining that it is not what they expected.

At Calendarscrtips I place online demos of all the products I sell. This is the only way to be sure that customers can see exactly what they are buying. (Well some will not bother to look at the demo but buy and then scream it’s not what they expected. You can send such people to hell).

Offer Good Support

This is a common sense but it’s underestimated by many sellers. Many online products and especially software need to have technical support for bugs, problems or just to help people who can’t manage with the installation and usage.
Instead of trying to test in every possible platform and hope you’ve covered them all, just offer good responsive support to these who have troubles. 99% of them will be happy after you help them and will not even think about refund.

Manuals For Free

If you are selling software, it’s a good idea to offer the installation and usage manual for free download even to non-customers. Many people will check it prior to purchasing to ensure they can manage. Don’t worry about those who will not buy because the manual looked scary to them. You don’t want them as customers. They will waste your time and at the end can still request a refund.

Have a Forum

If you have a forum where customers can talk to each other, discuss the software and the technical aspects of it, this will make them and the visitors a lot more confident that there is someone to help them.

I still don’t have a forum on CalendarScripts but I have a neat comment system for every product which seems to do the job of a forum at this time.

Don’t Overprice and Don’t Go Too Cheap

People are a lot more inclined to complain and ask for refund when they think your product is overpriced. On the other hand, too cheap products attract freetards who expect that you will be their tech support for free.

Try to price your products fairly – accordingly to the effort put in them and to the price of similar products in the market. This will reduce the refund requests.

Be Reachable

If you offer something more than just a contact form people will feel they are dealing with real person and will be more confident they can reach you when they have inquiries. The best thing is to give a toll free phone, although I don’t do it as I am not a big company and do my support myself (I don’t want to be called in the night). However I have published my Facebook visit card and Twitter account and that seems to work great.

These are the most important strategies I can think of at this moment. If you have other ideas, please share!

This is just a quick update about the latest version of our php mass mailer. After I got several requests for SMTP in the autoresponder and added that it resulted in more satisfied customers. So I decided to do it for Broadfast as well. And here is the new version – with SMTP support and bounced back tracking.

The price is now $17 – I believe it’s a fair price for the software.

Its name is CodeLobster and it’s the editor used for most of the recent upgrades to the software on CalendarScripts (except those that was made under Linux). It’s a Windows editor for editing text files, CSS, Javascript, PHP, HTML and XML.

The basic version of the editor is free. There are some plugins form Drupal, Joomla, Smarty, WordPress, JQuery and CodeIgniter at reasonable price. I never needed those so I haven’t tried them, but for someone who us coding a lot on these platforms it might be a good option.

Before CodeLobster I used CrimsonEditor for years but it really had a lot of problem and even it’s inheritor Emerald Editor still has most of them. CodeLobster is far more advanced, has Class and Functions view, good cross-file search, auto-complete, excellent syntax highlight and good auto-indentations.

At this point the main problem with it is that it crashes often without any reason. Hopefully the guys will fix that in some of the next version.

Overall rating: 8 out of 10.

There were some requests to make our exam testing software work with images, so here it is: in the latest version there is a “Image Gallery” popup which allows you to upload unlimited number of images. Under each uploaded image the HTML code that can be used for exam questions, answers and grades is displayed. You can copy this code anywhere to create content with pictures.

The other interesting new feature is the ability to add open end questions. I had to think some time how to solve these, because the initial idea of this software is to calculate grade based on the user’s answers. Apparently this functionality is not required by everyone but I still wanted to allow calculating grade for open end questions. So here is what i came up with.

These questions will only optionally have answers. You can add unlimited number of answers to those questions and the system will not show them to the exam taker. Instead of that it will compare the freely entered text, case insensitively, to every answer. If answer is matched (assuming you have answers), the system will assign the points you have given to that answer. Simple and very efficient.