web development

some neat SQL i learned recently. and mootools ajax hurdles

There’s a lot of techie-acronyms in this post, so if you’re not technically inclined … you know the deal.

I just recently discovered the “INSERT INTO … ON DUPLICATE KEY UPDATE” syntax while doing database inserts. While probably not the most efficient, this totally changed my thought process while I was coding some PHP to parse a CSV file handed to me at work.

What I was originally doing was iterating through each line and checking the database to see if the item existed. If it did, I would continue processing; if it didn’t I’d insert it. So on and so forth, but it got pretty complex when I had to start correlating the data together throughout multiple tables. Then I stumbled on the above SQL, and it became clear that I should let the RDBMS (MySQL) do the processing instead of waste lines of code and patience to do it myself. So now I just iterate through each line, and pass a variable to a setInfo($vars) function that goes through and pieces together the SQL. If there is a dupe, no big deal … the data is simply updated to match the particulars for that line. This should be good in case the data changes for a particular host in that CSV file, also …

My next hurdle is automagically generating MooTools effects in an AJAX call … right now the size of the data set I’m pulling out of the MySQL database is causing the MooTools effects to be really slow in the browser. This is because when the DOM is ready, I set up my JavaScript/MooTools code to apply effects to each of those rows. Kinda sloppy, I know. I have to read up on AJAX design patterns.

But an interesting thought I had was to automatically generate some JavaScript based on parameters sent via a POST operation. I found a decent article that suggests using “document.createElement(‘script’)” and injecting the JavaScript tags directly into the DOM. I think what I’ll do is tinker with the idea of only triggering an effect event when the user clicks on an element and then I’ll attach the effect(s) to it. What first drew me to MooTools was it’s Flash-like responsiveness, and unfortunately I managed to hose that with a huge set of data and poor JS/MooTools coding on my part.

Hopefully this other approach is a more elegant and better performing solution … ? We’ll see …

One thought on “some neat SQL i learned recently. and mootools ajax hurdles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.