My trigger I wrote yesterday actually worked!

This morning I went on Animal Crossing, then went to Church. After dinner I went on Animal Crossing again, then watched 'If I had a Million' with Moccle.

After that I checked Deviant art, The Luminous Landscape, Andy Rouse's blog, Moose Peterson's blog, and John K's blog.

Then I started trying to write a mysql stored procedure to insert records into my location table if the record didn't already exist, since my attempt at writing a trigger to control record insertion yesterday didn't seem to work.

I had a bit of trouble getting my stored procedure working, and I couldn't find a PDF that I had used before that had a good tutorial on writing stored procedures and functions in mysql. Eventually I did get it working, I had made a few mistakes with my IF statements -

• I had put IF condition SET instead of IF condition THEN SET
• I had put ELSE IF instead of ELSEIF
• and I had put ENDIF; instead of END IF;

After getting that working I tried inserting rows normally with the trigger in effect again, to make sure that I could insert rows okay with the trigger, just that it was causing the LAST_INSERT_ID() to be incorrect. It inserted a row again, then when I re-ran the INSERT statement, I got the id previous to the row that had just been inserted, rather than just 0 that I was getting yesterday.

I changed my trigger to assign the id that LAST_INSERT_ID() should have been returning to a variable (@id), then re-ran my INSERT operation followed by SELECT LAST_INSERT_ID(), @id; and it worked okay, LAST_INSERT_ID() was actually giving the correct id.

Anyway, after lots more testing, I found that even with my original trigger, if I ran my INSERT statement followed by SELECT LAST_INSERT_ID(), it would actually give the correct id/key. When I had been getting zero or the id/key previous to the row just inserted, I had just been running the INSERT statement, and then looking at the result that phpMyAdmin gave e.g.
INSERT INTO location SET City = 1, ProvinceState = 1, WorldRegion = 1 ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
phpmyadmin would say

0 row(s) inserted.
Inserted row id: 25 ( Query took 0.0006 sec )

(the existing row id is actually 26)

However, running the query like
INSERT INTO location SET City = 1, ProvinceState = 1, WorldRegion = 1 ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
SELECT LAST_INSERT_ID();
phpmyadmin would return the correct result (26).

I just tried it out on my actual website, and it seemed to work okay, so I guess it's just some weird bug in phpmyadmin where it doesn't print the correct inserted row id.

Also, Ubuntu updated phpMyAdmin again today, and again I had the same problem as when I upgraded Ubuntu - first the UID and GID of the page were of the wrong level so suPHP wouldn't allow PHP to parse the page, then the dreaded blank page problem. Luckily I had my previous blog post to refer back to: Fixing blank page problem in phpMyAdmin, and so fixing the white page problem was quite easy.

After getting that working I checked Macro Art In Nature, then did some more website stuff, but got stuck with some jQuery that wasn't working in IE.

The weather today was mainly cloudy with quite a few showers (some very heavy). There was a nice pinky sunset around the horizon where the sun sets, but I couldn't really see it due to the houses between me and the horizon. I could have gone out to try and see it, but by the time I had got to an open field past the houses, it probably would have finished (plus it was wet from all the rain so far today and looked like it might rain some more).

Food
Breakfast: Honey loops; cup o' tea.
Dinner: Nasi Goreng; Noodles; Chicken Nuggets. Pudding was a couple of pancakes with golden syrup and squirty cream. Coffee.
Tea: Mature cheddar cheese with salad cream and iceberg lettuce sandwich; apple; slice of Ludlow Food Centre Shropshire Fruit cake; Orange Trophy; cup o' tea.