Select Page

I have been working on a website that required a script that imitates the status updates a la Twitter, Facebook status, MySpace status, etc. This post explains how I programmed that part of the website.

First, as part of a backend CMS, I created a form that submits the status update via PHP to a MySQL database. Then I wrote a PHP class that includes a method to query the database for the most recent status update and another method that calls a MySQL stored procedure to get the time of that status update (i.e. “a few minutes ago”, “32 minutes ago”, “5 days ago”, etc.). The following is the code for the MySQL stored procedure that gets the time of the user’s most recent status update:

DROP FUNCTION IF EXISTS `my_DB`.`getStatusTime`;

DELIMITER $$
/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE=” */ $$
CREATE FUNCTION `my_DB`.`getStatusTime`(User VARCHAR(25)) RETURNS VARCHAR(25)
BEGIN
DECLARE TodaysDate TIMESTAMP;
DECLARE StatusDate TIMESTAMP;
DECLARE TimeDifference TIME;
DECLARE Status_Time VARCHAR(25);

SELECT NOW()
INTO TodaysDate;

SELECT s.Stat_Updated
INTO StatusDate
FROM T_Status s
INNER JOIN T_Profile p ON p.Profile_ID=s.Stat_Who
WHERE p.Pr_Name_First=User
ORDER BY s.Stat_Updated desc
LIMIT 1;

SELECT TIMEDIFF( TodaysDate, StatusDate )
INTO TimeDifference;

SELECT TIMEDIFF( MAKETIME(47,59,59), TimeOfStatus )
INTO YesterdayTimeDifference;

/*////// Find the time of status update formatted as a string /////// */
IF HOUR(TimeOfStatus) < 12 THEN SET TimeOfStatusString = CONCAT( ‘ at ‘, HOUR(TimeOfStatus), ‘:‘, MINUTE(TimeOfStatus), ‘ AM‘ );
ELSEIF HOUR(TimeOfStatus) < 13 THEN SET TimeOfStatusString = CONCAT( ‘ at ‘, HOUR(TimeOfStatus), ‘:‘, MINUTE(TimeOfStatus), ‘ PM‘ );
ELSE SET TimeOfStatusString = CONCAT( ‘ at ‘, HOUR( DATE_SUB( StatusDate, INTERVAL 12 HOUR ) ), ‘:‘, MINUTE(TimeOfStatus), ‘ PM‘ );
END IF;

/*////// Find the amount of time since most recent status update /////// */
/*
Status options:
‘a moment ago’
‘less than 1 minute ago’
‘1 minute ago’
‘{n} minutes ago’
‘1 hour ago’
‘{n} hours ago’
‘yesterday at {time}’
‘on {day} at {time}’
‘last {day} at {time}’
‘on {date} at {time}’
*/

IF HOUR( TimeDifference ) < 1 AND MINUTE( TimeDifference ) < 1 THEN SET Status_Time = CONCAT( SECOND( TimeDifference ), ‘ seconds ago‘ );
ELSEIF HOUR( TimeDifference ) < 1 AND MINUTE( TimeDifference ) < 2 THEN SET Status_Time = ‘1 minute ago;
ELSEIF HOUR( TimeDifference ) < 1 AND MINUTE( TimeDifference ) < 60 THEN SET Status_Time = CONCAT( MINUTE( TimeDifference ), ‘ minutes ago‘ );
ELSEIF HOUR( TimeDifference ) < 2 THEN SET Status_Time = ‘1 hour ago;
ELSEIF HOUR( TimeDifference ) < 24 THEN SET Status_Time = CONCAT( HOUR( TimeDifference ), ‘ hours ago‘ );
ELSEIF HOUR( TimeDifference ) < ( YesterdayTimeDifference
) AND MINUTE( TimeDifference ) < MINUTE( YesterdayTimeDifference ) AND SECOND( TimeDifference ) < SECOND( YesterdayTimeDifference ) THEN SET Status_Time = CONCAT(yesterday, TimeOfStatusString );
ELSEIF HOUR( TimeDifference ) < 144 THEN SET Status_Time = CONCAT( on, DAYNAME( StatusDate ), TimeOfStatusString );
ELSEIF HOUR( TimeDifference ) < 168 THEN SET Status_Time = CONCAT(last, DAYNAME( StatusDate ), TimeOfStatusString );
ELSE SET Status_Time = CONCAT(on, MONTHNAME( StatusDate ), ‘ ‘, DAY( StatusDate ), TimeOfStatusString );
END IF;

RETURN Status_Time;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$

DELIMITER ;

The script works like a charm! My next project on this site is to create a Flash image slideshow where the images can be managed from the backend…