Can you turn this excel spreadsheet into a web application?

The phrase above basically sums up my existence at my job. There’s all sorts of important information that people have been storing in an Excel Spreadsheet and now, they have no easy way of extracting that data and/or connecting that data to other data sources.

In my continuing quest to figure out a better way to display a spreadsheet-like interface on a webpage, I found this article which was published a couple months ago.

http://roberto.open-lab.com/2010/01/18/javascript-grid-editor-i-want-to-be-excel/

It’s pretty much a roundup of all these different grid/spreadsheet javascript libraries which all have their pros and cons… I’m hoping to find something I can seriously use now. Something that will drastically reduce the load-time due to the number of DOM elements on these gigantic grids which my application is asked to handle.

Anyway, I’ll let you know what I come up with.

Nested Left Joins?!

I didn’t know you could write MySQL queries like this, but I just did:

SELECT it.*,a.name parent_primary_attribute,d.value primary_value FROM itemtype it
LEFT JOIN (primaryattribute pa
LEFT JOIN (itemtypeattribute_default d, itemtypeattribute a)
ON (pa.attribute_id=d.attribute_id AND pa.attribute_id=a.id)
) ON (it.parent_id=pa.type_id AND d.type_id=it.id)
WHERE it.id=’$id'”;

MySQL Workbench 5.2

For those of you who like to see things visually, the newest release of MySQL workbench enables you to connect to your existing MySQL database and reverse engineer it so that you can see the full ERR (Entity Relationship R_____) Diagram in all its glory. For me this is helpful to see when I’m taking over a project I know nothing about. As long as the foreign keys are set up correctly, the tool should be able to do all the work for you. You can also make changes to the Diagram and then have it synchronize with your development/production database. This feature used to be some sort of premium feature that you had to upgrade and pay for, but I just discovered that it’s all included in the free download. I like free stuff.
http://dev.mysql.com/downloads/workbench

Screenshot:
MySQL Workbench Screeshot
After reverse-engineering the existing MySQL database, you can get an ERR diagram that looks like this. Now your job is to nicely organize the tables.

PHP Makes You a Lazy Programmer

The fact that you don’t have to initialize your variables in PHP makes me a very lazy programmer.  With a large scale project that I’ve been working on for the past 6 months, I’ve really paid the penalty for dumb mistakes like not initializing and resetting my variables.  So just because PHP doesn’t make you do that stuff, it’s always best practice to do so.

PHP String Concatentation Performance

I’m not sure if this is totally correct and I have no fixed way of measuring this (other than that I don’t get any timeout errors anymore) but I’ve noticed a significant difference when choosing between two different methods of string concatenation:

Slow way:

foreach($oranges as $orange){
$orange_str = $orange_str . $orange;
}

Better way:

foreach($oranges as $orange){
$orange_str .= $orange;
}

This is obviously simplified for example’s sake, but I had a situation where I was concatenating several hundred (maybe a thousand) elements to a string and I was receiving a “Fatal error: Maximum execution time of 30 seconds exceeded…”.  My best guess is that with the first method, an actual copy of that string is being made in memory so you’re probably taking up twice the amount of memory than you should be.  That’s just my guess.