Selecting 30 days or older entries using MySQL DateTime field

I’ve been doing quite a bit of Date and Time manipulations lately, and have grown to enjoy MySQL’s DateTime fields just as much as UNIX timestamps. Particularly its built-in functions for selecting any range of fields based on a time frame.

Needing to remove all entries from a table that are older than 30 days, at first I thought I would need to iterate over the very large table (90+k) rows of data and convert each MySQL DateTime field to a UNIX time stamp to do the calculations myself with PHP. However after a bit of research I’ve learned of a few things with MySQL DateTime that make this a lot easier.

SELECT fields FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= timestamp;

The “timestamp” field is the MySQL DateTime field. Using the DATE_SUB, CURDATE and INTERVAL one can easily grab all records that are 30 days old without the headache of converting to UNIX timestamps in PHP and doing the grunt work yourself.

idea Also as a side note, if you wish to convert a MySQL DateTime field to a UNIX timestamp you can easily do this by:

$mysql_timestamp = gmdate("Y-m-d H:i:s",$mysql_timestamp_value);

If anyone has any better more efficient means to accomplish this please feel free to leave a comment. :D

Combine H.264 MP4 Files and Add Audio via MP4Box and FFMPEG

Some late night fun… Since I can’t sleep… I’ve successfully found a means to combine multiple video files and add audio to a combined video progmatically and documenting my findings, thought I would share…

First I had to install GPAC which contains MP4Box, using it allows to combine the video files pre-existing as we have them now into one single file (1.mp4 and 2.mp4 are video files captured with an AXIS IP Camera in H.264):

MP4Box -cat 1.mp4 -cat 2.mp4 out.mp4

Next we determine the length in seconds of the video, this is where a custom PHP Script is involved that will determine the length:

$output = shell_exec("/usr/local/bin/ffmpeg -i {$path}");
preg_match('/Duration: ([0-9]{2}):([0-9]{2}):([^ ,])+/', $output, $matches);
$time = str_replace("Duration: ", "", $matches[0]);
$time_breakdown = explode(":", $time);
$total_seconds = round(($time_breakdown[0]*60*60) + ($time_breakdown[1]*60) + $time_breakdown[2]);

With the example file, here’s what the PHP script would see:

ffmpeg -i out.mp4 2>&1 | grep Duration
Duration: 00:00:13.60, start: 0.000000, bitrate: 875 kb/s

And then we cut the audio file accordingly, the above PHP script returned 14 seconds for our two combined videos, since 13.60 is rounded to 14 from MP4Box, so it starts at 00:00:00 and takes a total time of 14 seconds of the audio file and creates a new file to be used next.

ffmpeg -ss 00:00:00 -t 14 -i music.mp3 final.mp3

Take the combined videos from MP4Box and add the newly created 14 second final.mp3 file and create the final file for viewing.

ffmpeg -vcodec copy -i final.mp3 -i out.mp4 final.mp4

If your wanting to have progressive downloading for web playback:

MP4Box -inter 0.5 final.mp4

Core Action Automated Video System for Action Sports

I have been doing a lot of developing lately particularly for the release of Core Action‘s Automated Video system that is patent pending. Basically it is a system that was developed that allows kids from skate parks to have video of them skating and doing tricks and having them identified, recorded and uploaded to the web for later retrieval.

Currently the system has been successfully running at Eisenbergs skate park in Plano, Texas for over two weeks and utilized by over 200 kids riding at the park.  Over 7k Videos have been recorded so far, and over 100 kids have claimed their videos.

For those curious on some of the technical details. Every kid in the park that wants to have their videos recorded are handed a Core Action sticker that contains an Radio Frequency ID (RFID), placing the sticker on their helmet. There are hot zones setup within the park where an RFID reader and antennas are setup to detect when someone enters the area of an IP Camera mounted with a fish eye lens that is called upon to start recording and capture the persons tricks and or bail. Uniquely identifying this person with their tag for later retrieval online. After it has been captured the persons video is queued and played back on a big screen projector on the wall so they can see the videos they just created. While this is going on the video that is captured is then taken and uploaded processed so that it can be view-able/shared online at the Core Action website with anyone.

The middle-ware is compromised of many different systems. Including VLC and C# which makes up the bulk of the actual “cool process” of Reading RFID tags, Capture the IP Camera Streams in H.264 via RTSP and saving the data when the RFID tags are detected. VLC is also used to playback the videos the big screen at the park. PHP, FFMPEG, and MySQL are utilized on the server end to process the video files and create the proper entries based on the RFID tags recorded into a file system and MySQL database to be retrieved, via an online social network written as well in PHP and MySQL.

Myself and one other developer/programmer, Matt Stapleton are responsible for developing the system.

Overall the system took over a year of Research and Development to make it a reality, there are still a few minor things we are still fine tuning but obviously it is doing what we had set out to do.



Build a Forum in Flash / Forums

While experiementing with Flash 5 and ASP/Access around 2001 I wrote a pretty simplistic based forum with Flash as a frontend.

Still seeing some traffic coming from that I had simply experimented with, I’ve since moved away from old ASP, and Access Databases.  It is merely for learning purposes, A Novice person may find it interesting as for more advanced may find it quite amusing :)

I make no warranty with the zip and there isn’t any “install” instructions.

Download it here:
arrow mb-forums05b

Build forum in flash – Forums.

.net C# API to Magento via XML-RPC

Since I’m starting to get back to writing .Net C# again, I decided to dig around for anything Magento related and found a nice XML-RPC based library, that will allow you to utilize Magento’s API. Samples are provided and seem to be pretty straight forward, however they do note a lot of the returns even though stated as an integer or such, but tend to most always return a string. Which leads me to believe its something with the XML-RPC in between the two, and PHP’s love to turn most any type into a string. Here is one of the examples to get your gears turning:

#region Product Image Examples
//// gets the product image current store view (doc says int returned, but is string)
string myProductImageCurrentStore = ProductImage.CurrentStore(apiUrl, sessionId, new object[] { });

You can find the full API here, along with some notes:
arrow .NET C# Object Library for Magento

arrow .net C# API to Magento via XML-RPC

Direct download link to the API: