Recently, I had to use the super handy Wordpress Calendar Plugin. After adding a few repeating events and taking a look at the output it generated, I noticed the large number of queries that it executed! It executed at least 3 queries per day of the month being displayed! I was doing development that required displaying two months at a time, and the total number of queries was over 150!!

The plugin was fetching the events per-repetetition (weekly, monthly, yearly, etc) per-day. This would add up to more than 3 queries per day in the month.

Well, I decided to step in and take some action. I spent well over ten hours optimizing the event-retrieval process and came up with a query that fetched the events and returned them in chronological order, allowing for filtering of past or future events. I had never done this type of system before, so I took a look at the author’s code and converted it to a very large mysql query. I’m posting it bellow for anyone who might be interested.

The following function fetches the latest events and sorts them using an additional column “diff“. which indicates the freshness of the event. A negative number indicates a past event. A positive number indicates a future event, and a value of 0 indicates an ongoing event. For infinitely repeating events, the diff will indicate the number of days until the next occurrence; but, for infinitely recurring monthly events, it will return a positive value only if the event ocurred more than 15 days ago.

function grab_latest_events($howmany = 3)
{
 global $wpdb;

 $events = $wpdb->get_results("SELECT *, if(event_recur = 'Y',
 if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats YEAR) >= CURDATE()) AND
 (CURDATE() BETWEEN DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR) AND
 DATE_ADD(event_end, INTERVAL YEAR(CURDATE())-YEAR(event_end) YEAR)),
 0,
 if(event_repeats = 0,
 DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE())
 ,if(event_repeats>=(YEAR(CURDATE())-YEAR(event_end)),
 DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE()),
 DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats YEAR), CURDATE()))
 )
 )
 ,if(event_recur IN ('M'),
 if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats MONTH) >= CURDATE()) AND # withn range
 ((MONTH(event_end) = MONTH(event_begin) AND DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin) AND DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end)
 ) OR (MONTH(event_end) > MONTH(event_begin) AND
 (DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end) OR DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin)))
 OR (MONTH(event_end) < MONTH(event_begin) AND (MONTH(CURDATE()) >= MONTH(event_end) OR MONTH(CURDATE()) <= MONTH(event_begin))
 AND (DAYOFMONTH(CURDATE()) > DAYOFMONTH(event_end) OR
 DAYOFMONTH(CURDATE()) < DAYOFMONTH(CURDATE()))
 )
 ),
 0,    #IF MONTHLY AND WITHIN RANGE, RETURN 0 DENOMINATING AN OCURRING EVENT
 if(event_repeats = 0 OR DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE()) > 0,
 DATEDIFF(DATE_ADD(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), INTERVAL
 MONTH(CURDATE())-MONTH(event_begin)+((DAYOFMONTH(CURDATE())-14>DAYOFMONTH(event_begin))) MONTH), CURDATE())
 ,
 DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE())
 )
 )
 ,if(event_recur = 'W' AND
 (event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats WEEK) >= CURDATE())
 AND if(DAYOFWEEK(event_begin) <= DAYOFWEEK(event_end),
 DAYOFWEEK(event_begin) < DAYOFWEEK(CURDATE()) AND DAYOFWEEK(event_end) >= DAYOFWEEK(CURDATE()),
 DAYOFWEEK(CURDATE()) >= DAYOFWEEK(event_begin) OR DAYOFWEEK(CURDATE()) <= DAYOFWEEK(event_end))
 , 0, #else

 if(event_recur = 'W' AND
 event_repeats = 0,
 abs(DAYOFWEEK(CURDATE())-DAYOFWEEK(event_begin)),
 if(event_recur = 'S' AND (CURDATE() <= event_end AND event_begin <= CURDATE()),
 0, DATEDIFF(event_begin, CURDATE()))
 )
 ))
 ) as diff FROM " . WP_CALENDAR_TABLE .
 " WHERE  (CURDATE() <= event_end AND event_begin <= CURDATE()) OR
 (if(event_recur = 'Y',
 if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats YEAR) >= CURDATE()) AND
 (CURDATE() BETWEEN DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR) AND
 DATE_ADD(event_end, INTERVAL YEAR(CURDATE())-YEAR(event_end) YEAR)),
 0,
 if(event_repeats = 0,
 DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE())
 ,if(event_repeats>=(YEAR(CURDATE())-YEAR(event_end)),
 DATEDIFF(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), CURDATE()),
 DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats YEAR), CURDATE()))
 )
 )
 ,if(event_recur IN ('M'),
 if((event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats MONTH) >= CURDATE()) AND # withn range
 ((MONTH(event_end) = MONTH(event_begin) AND DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin) AND DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end)
 ) OR (MONTH(event_end) > MONTH(event_begin) AND
 (DAYOFMONTH(CURDATE()) <= DAYOFMONTH(event_end) OR DAYOFMONTH(CURDATE()) >= DAYOFMONTH(event_begin)))
 OR (MONTH(event_end) < MONTH(event_begin) AND (MONTH(CURDATE()) >= MONTH(event_end) OR MONTH(CURDATE()) <= MONTH(event_begin))
 AND (DAYOFMONTH(CURDATE()) > DAYOFMONTH(event_end) OR
 DAYOFMONTH(CURDATE()) < DAYOFMONTH(CURDATE()))
 )
 ),
 0,    #IF MONTHLY AND WITHIN RANGE, RETURN 0 DENOMINATING AN OCURRING EVENT
 if(event_repeats = 0 OR DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE()) > 0,
 DATEDIFF(DATE_ADD(DATE_ADD(event_begin, INTERVAL YEAR(CURDATE())-YEAR(event_begin) YEAR), INTERVAL
 MONTH(CURDATE())-MONTH(event_begin)+((DAYOFMONTH(CURDATE())-14>DAYOFMONTH(event_begin))) MONTH), CURDATE())
 ,
 DATEDIFF(DATE_ADD(event_end, INTERVAL event_repeats MONTH), CURDATE())
 )
 )
 ,if(event_recur = 'W' AND
 (event_repeats = 0 OR DATE_ADD(event_end, INTERVAL event_repeats WEEK) >= CURDATE())
 AND if(DAYOFWEEK(event_begin) <= DAYOFWEEK(event_end),
 DAYOFWEEK(event_begin) < DAYOFWEEK(CURDATE()) AND DAYOFWEEK(event_end) >= DAYOFWEEK(CURDATE()),
 DAYOFWEEK(CURDATE()) >= DAYOFWEEK(event_begin) OR DAYOFWEEK(CURDATE()) <= DAYOFWEEK(event_end))
 , 0, #else

 if(event_recur = 'W' AND
 event_repeats = 0,
 abs(DAYOFWEEK(CURDATE())-DAYOFWEEK(event_begin)),
 if(event_recur = 'S' AND (CURDATE() <= event_end AND event_begin <= CURDATE()),
 0, DATEDIFF(event_begin, CURDATE()))
 )
 ))
 ) >= 0)" . //*/
 " ORDER BY diff ASC, event_begin=event_end DESC, event_begin ASC LIMIT $howmany");
 return $events;
}

The conditional column “diff” cannot be used in the WHERE statement, which forced me to copy the code and paste it inside the where statement for filtering out the past events.  Until Next Time!!

This is a quick class in C++ that I slapped together from an example I found at http://www.luckyspin.org/?p=28.

I’ve been writing a php bot using curl for one of my favorite online games and I decided to make it public. This would be one of my latest public releases in a while. It turned out that PHP is not the best language to use due to poor portability. I played around with bambalam (PHP ‘compiler’) and also WinBinder, but both packages are severely outdated. All my code was written in PHP5, and bambalam only supports php 4. So, Now I’m going back to hardcore programming and coming up with a solution soon. I’ve already got libcurl, and boost hooked up and ready to go!

#include <iostream>
#include "curl/curl.h"

class MyCurl
{
private:

public:
// Write any errors in here

static int writer(char *data, size_t size, size_t nmemb, std::string *buffer_in)
{

// Is there anything in the buffer?
if (buffer_in != NULL)
{
// Append the data to the buffer
buffer_in->append(data, size * nmemb);

// How much did we write?
return size * nmemb;
}

return 0;
}

static std::string get(const char* url)
{
CURL *curl;
CURLcode result;

// Create our curl handle
curl = curl_easy_init();

char errorBuffer[CURL_ERROR_SIZE];
// Write all expected data in here
std::string buffer;

if (curl)
{
// Now set up all of the curl options
curl_easy_setopt(curl, CURLOPT_ERRORBUFFER, errorBuffer);
curl_easy_setopt(curl, CURLOPT_URL, url);
curl_easy_setopt(curl, CURLOPT_HEADER, 0);
curl_easy_setopt(curl, CURLOPT_FOLLOWLOCATION, 1);
curl_easy_setopt(curl, CURLOPT_COOKIEJAR, "cookies.txt");
curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, MyCurl::writer);
curl_easy_setopt(curl, CURLOPT_WRITEDATA, &buffer);

// Attempt to retrieve the remote page
result = curl_easy_perform(curl);

// Always cleanup
curl_easy_cleanup(curl);
}

if(result == CURLE_OK)
return buffer;
return std::string();
}
};

I’m currently available for hire. If you would like to get a quote or a free estimate on your projects, please see the contact or free estimate page and I will get in touch with you then so we can discuss.

I migrated the site from the old static html site to a wordpress installation with a custom theme. The site is now search engine friendly and makes it easier to posts updates. I also think it looks a lot more clean. On the right side of the page, there are links to my getafreelancer and linkedin profile (also new.)

I had been working on a new website saltforkoutfitters.org for a couple of weeks and it’s finally finished. As with all my clients, this site is subject to my 30 day free client support.