SiteCrafting

Dumping Duplicates

Deleting all semi-matching rows in MySQL

One of the more recent additions to the SiteCrafting CMS arsenal is a comprehensive error logger, tracking all PHP and MySQL errors (by default... other error types can be created on a case by case basis) that occur in new sites we build. Errors are stored in our own intranet system with a timestamp, error body and a site ID (assigned to each client at a different stage of our project workflow). The table looks something like this:

+----+---------------------+---------------+---------+
| id | logTime             | text          | project |
+----+---------------------+---------------+---------+
|  2 | 2008-05-14 14:42:15 | A PHP Error   |       1 |
|  3 | 2008-05-14 14:42:26 | A PHP Error   |       1 |
|  4 | 2008-05-14 14:42:34 | A PHP Error   |       1 |
|  5 | 2008-05-14 14:42:47 | A MySQL Error |       1 |
|  6 | 2008-05-14 14:42:56 | A MySQL Error |       1 |
|  7 | 2008-05-14 14:43:05 | A PHP Error   |       2 |
|  8 | 2008-05-14 14:43:10 | A PHP Error   |       2 |
|  9 | 2008-05-14 14:43:21 | A MySQL Error |       2 |
+----+---------------------+---------------+---------+
8 rows in set (0.00 sec)

Obviously sometimes we get duplicate errors coming through. Aside from being mere mortals who aren't always fast enough to correct an error before it recurs, one of the first stages of debugging is to try and replicate the error. These are often pretty easy to manage. It's pretty trivial to search for all matching errors, check them all, and delete them. Sometimes, though, this just doesn't cut it. Like when there are a few different errors with 10,000 occurrences apiece.

Comments (0) | Posted in Coding Techniques, MySQL by Joe Izenman

Nov. 14, 2007 at 12:01am

Android: The REAL Next Gen of Web Apps

If the iPhone is web apps to go, Google and the Open Handset Alliance's new open source mobile phone API, dubbed Android, is The Joy Of Cooking Webapps. Once again Google has seen a good idea (packing a cell phone full of convenient little apps and widgets that sync with websites and external systems) and figured out a way to make it a great one.

Comments (1) | Posted in Critiques, Odds 'n Ends, Software Engineering by Joe Izenman

Sep. 6, 2007 at 4:45pm

Adventures in Database Migration Pt. I

Fie on commas! Fie!

SiteCrafting is in the process of phasing out some of our older servers, and as an added bonus, the clients hosted on those servers are getting a MySQL jumpstart, leapfrogging over 4.1 to go straight from 4.0.24 to 5.0.32. Tragically, it's not quite as simple as dump | import. This is what I get for bothering my bosses for a few weeks not long after coming aboard about how nice stored procedures, updatable views, and triggers could be.

"The wonderful thing about standards," a wiser person than me once said, "is that there are so many of them." That's not the whole of it, though. One good thing about standards is that there are certain features one can generally rely upon to work, translate, port, etc. Assuming one works within them, rather than taking advantage of loopholes allowed by their not-entirely-compliant-but-we're-getting-there-and-anyway-isn't-this-way- easier-and-faster software. When people don't (and I'm not entirely innocent here), you run the risk of turning your simple upgrade into a serious project when your favorite software decides it's time to comply a bit more.

Comments (5) | Posted in MySQL, Web Hosting by Joe Izenman

Dec. 18, 2006 at 10:37am

The Right People For Your Job

I'm in a band. Well, two of them, but only one that matters for this discussion. I've noticed that the progression of a musical group that wants to be professional on some scale in many ways closely mirrors that of any product-based business. You start out spending a certain amount of time in development. You come up with ideas, assemble the best team available to execute those ideas, and then do your level best to refine and perfect your product before you release it to the public.

Comments (1) | Posted in Deep Thoughts, Marketing by Joe Izenman

Oct. 27, 2006 at 11:34am

Cross Table Content Search

One of the more interesting adventures working with data storage is trying to aggregate information meaningfully from multiple very different data structures. Imagine you've got a website filled with content (say, a few hundred pages). All the content is stored and output dynamically - who wants to create and maintain 500 static html pages, anyway? And of course, you need a bit of variety, so all this content is spread across five different page designs, each requiring its own template and data structure.

Now you say you want to search your site? All the content? And you want the results all together in one big happy sorted-by-relevancy list? How on earth am I going to do that?

Well... like this.
Read more

Comments (0) | Posted in MySQL, PHP by Joe Izenman

Oct. 23, 2006 at 10:50am

Firefox 2.0

Mozilla releases new browser when it's good and ready.

Hot on the heels of the Internet Explorer 7 release, Mozilla Firefox 2.0 is slated for official release tomorrow, but, as often happens, they have posted the full release version up on the mirrors already, so you can find what you're looking for at ftp://ftp.mozilla.org/pub/mozilla.org/firefox/releases/2.0

Comments (2) | Posted in Critiques by Joe Izenman

Oct. 18, 2006 at 10:46am

Navigation Nightmare pt. 1

Something no developer wants to see...

foreach ($left_elements AS $le) { ?> <table class="leftnav_<?=$le['class']?>"> <tr> <td class="leftnav_<?=$le['class']?>"> <a href="<?=$le['target']?>" class="<?=$le['theme']?>"><?=$le['name']?></a> </td> </tr> </table> <? }

Nobody wants to see that. Especially not seven different times with seven tiny variants covering seven different if conditions.

Each higher 'class' number is a deeper indentation. Apparently it's necessary for this class identification to be repeated for the table, table cell and link. Converting this to a nested list format (part of my current task) is going to be an adventure.

Well... at least it's indented nicely.