Minor Triumphs and Set Backs

Whoops, this has been in drafts for a while now.  Guess I forgot to publish it.

I finished the surgery entry page. It works exactly as I want it to, which is a great feeling. Turned out to be easier than i thought it would be. I think I was psyching myself out. That was the triumph.

Now, for the set backs. The only thing I have left to do before I can consider this complete in terms of features is presenting the data to the user in the form of reports. I knew this would be tricky, which is part of why I saved it for last. I'm trying to mimic the structure they use for the excel document, but it is proving to be more of a challenge than I anticipated. I may have to abandon that in favor of something that plays more to the strengths of the data structure I'm using. I'm sure this could have been avoided with better planning, but then this is how one learns to do that, right?

The other set back was one I knew was on the horizon. I initially developed this using EasyPHP, which is a simple web development app. It combines the webserver, database, and scripting engine in a single, preconfigured packaged. The only problem is that it uses Apache for the webserver, and MySQL for the database, but we use Microsoft solutions exclusively at work, which means migrating it. I decided to do this now to possibly take advantage of some of the Microsoft database tools. So what's the set back, you ask? I'm a little embarrassed to admit this, but I don't have a good backup of the database. I was using a MySQL admin tool, and I guess I hit the option to back up that tool's databases, and not my own. Its not a big deal. The structure is pretty simple, and it was all test and junk data, but it's frustrating. That's where I am now.


Sleepy Time Revelation

I think I had another one of those fits of inspiration after sleeping.  A sleepy time revelation, if you will.  I was complaining in the last post about how I may have mucked up the database by trying to fix a perceived problem.  It wasn't until I got into the implementation that it looked like a problem, and then trying to fix it may have made it worse, etc, etc.

What's weird is I didn't fully realize it was actually a problem until I started writing it out.  When I write these things, it's usually just dumping out whatever I'm thinking about.  I usually stick to the topic, but there's very little planning in what I'm going to say or how I'm going to approach it.  So it didn't become fully clear that I had created a problem until I was writing it out and thought to myself "Wait, that sounds worse".  Then the thought of having to change the way it currently works just seems like it would be complicated.  I didn't really give it a lot of thought, though.

I'm pretty sure that I don't have to change anything in the database, and make minimal changes to the PHP.  My thought was I would update entries that already exist in the database instead of maintaining multiple entries, but there's nothing to stop me from just not doing that, and inserting those updates as separate entries.  The database was set up this to work this way originally, and I didn't modify any of the keys or relationships to prevent multiple entries.  I didn't think it would be necessary to change the database since the PHP would be so constrained that the user wouldn't be able to mess it up.

I have to do some testing to make sure I'm right about this, but it feels right in my head.  I think the lesson from all this is that all of those CompSci textbooks that recommend thoroughly planning your project out before starting might be on to something.  Who knew?


Surgery Tracker Database Integration


As I continue to work on and evolve this little project of mine, I am reminded of a bonus featurette on one of the Lord of the Rings DVDs.  They recounted how a very simple line in the script, something like "The fellowship ran down the stairs" turned into this 5 minute long special effects laden sequence, with crumbling stairs, and stunts, and added dialogue.
Now my version of this is, perhaps, not as visually stimulating as that.  Basically, I had planned this out in general terms.  The user would interact with a form, there would be some massaging of the form data, and that data would populate the database, etc.  I knew exactly what information they need to track, so making the database wasn't a problem.  I always question if there's a better way, but I don't think I can avoid that kind of self-doubt at this point.
What keeps happening, though, is I approach one piece of the project.  I will use creating the form as an example.  I know the broad strokes of what I want to accomplish, but then as the implementation happens, it ends up being so much more complex than I originally planned.  Now, I believe this is necessary complexity.  It would be very easy to do a simple form that handled a single entry at a time, but that would be a big hit to ease of use for the people I'm making this for.  So it ends up being more complicated than I originally thought.

Now I'm up to the point where it's time to stick these entries in the database, and again I'm surprised at how much more complicated the implementation is from my original expectations.  Part of it is PHP, I think.  Maybe it's just inexperience, but I don't find the way PHP interacts with databases very intuitive.  As far as I can tell, every query has to be extracted as a row in an array, even when you're performing some function and not retrieving data.  This came up because I was doing a count of records to figure out what the next record's ID number should be.
Another unexpected complexity was whether to use an insert or update query.  This one is my fault.  Early on, I had decided that every addition to the database would be a new entry, which would use an insert.  When I wanted to find the total number of a given surgery, for a given doctor, in a given month of the given year, I would pull all the records that met that criteria, and add them up.  This struck me as unnecessarily complicated, and potentially more time consuming.  Changing the 'Amount' field of a single record seemed a much better solution than maintaining several rows for each entry.  I know I give up some detail this way, but it's detail that they won't care about.

What I did not fully consider when making that change was that this didn't really eliminate complexity so much as shift it.  This change means that I can't simply use an insert for each addition.  I have to check whether the entry I'm trying to add it already there first.  So now I'm querying for the existence of the entry, then either inserting it or updating it depending on whether it exists.  So every entry now requires two queries.  This is what I would call sub-optimal behavior.  I could probably shave down on the number of repeats if I just have it try either inserting or updating, then doing the other one if that fails, but I don't like that solution.  
Right now I'm trying to weigh whether it would be better to go back and change the database again, making it behave more like how I originally planned, or just continue on and try to finish this.  It's not like this will be used 24/7 by a few hundred people at a time, so the inefficiency probably won't be noticeable, but the fact that it's there irks me.  Is it better to delay this thing even more to fix a problem no one will notice, or finish it sooner, get it to the people who want it, and feel like I actually accomplished something?  I've come close to giving up a few times already, and I'm concerned another setback will torpedo any remaining enthusiasm I have for this project.  I know what I should do, I'm just not sure if it's worth it at this point.  Stay tuned, I'm sure I'll be revisiting this soon.




Processing Form Input for my Tracker Project

I was stuck on something when making this little web app.  You know, calling it a web app feels generous.  It's a fairly simple database with a form-based front end.  I don't think there's anything particularly complex about it, but it's been challenging for me.  I'm going to chalk it up to inexperience.  Really, the only thing that makes it challenging is that I want to allow the end user to add multiple surgeries at once.  This thing is supposed to replace a spreadsheet, and it feels like it would be a huge step backwards to ask the users to enter one procedure at a time.  This will still be more cumbersome than a spreadsheet, but I think the gains in upkeep will offset the inconvenience of using web forms.

Making the form was the easy part.  The challenge came in processing the form input after it is submitted.  If the user wanted to enter 4 procedures, for example, I had the form use unique names for each entry, which was 'Proc' for procedure, followed by an incrementing number.  There was also a matching 'Amt' for amount that incremented the same way.  For this part of the form, all I really need to capture is the name of the procedure and the number of times it was done.  The doctor and date are entered elsewhere.  I'll probably post the form here or somewhere when it's closer to being done.

So the form content is all stuck in an array, in order.  It looks something like this (using 2 procedures):


The problem I ran into is how to add that to the database?  I'm still new to database stuff, so I wanted to keep it simple and just insert each Proc/Amt pair one at time using a loop.  There might be better ways to do this, but this will only be used occasionally by a few people, so I'm too concerned about efficiency right now.  I just want it to work.  But as I started to write it out, I couldn't figure out how to reference the names correctly since each group would have a different name.  Proc0, Proc1, etc.  I tried various ways of adding a variable at the end that would resolve to the correct number, and incrementing it each time through the loop, but I had a hell of a time getting that to work.  There was either a problem with  my syntax, or PHP doesn't want variable names changed like that.  I decided to look at other options.

I've heard that dreams act as unconscious problem solvers.  If you're struggling with something one day, then go to sleep, and suddenly the thing you were having trouble with makes sense, or is easier, it was your brain tackling the problem through dreams, and coming up with possible solutions.  I'm not saying I was dreaming code.  I'm not that bad.  Yet.  But it did just sort of come to me a couple days after messing around with this and thinking about it: multi-dimensional arrays.

So instead of there just being the one array with all the form data, I would split that data into smaller chunks, and store each in its own array.  Then I would have an array, filled with arrays, filled with the actual data.  That doesn't really sound like an improvement, but I'll explain why it works for me soon.

So I had my epiphany, but I still had to figure out how to manipulate the data in those sub-arrays.  Fortunately, the developers of PHP have a function designed to break up arrays into smaller chunks.  It's called:


Aren't they clever? This does the heavy lifting for me.  The problem that I was seeing with having everything in one array is that I needed unique names for each piece of data.  That made it tough when I wanted to run a loop that would insert each piece in the right place.  I could never get it to work right, and even if I did figure out a way to do that, I had a feeling it would be sloppy.  This way, I didn't have to rely on everything having a number at the end.  I could have the number in the array, and identical names for each piece of data.  It ended up looking like this: 


 Being able to name each surgery code "Code" instead of "Proc1" would make it much easier to reference that in the loop structure.  I'm skipping some steps here.  array_chunk() turns it into a numeric array by default.  This would have been fine, since the form data was sorted correctly by default, but I prefered to have some connection to the names I set up in the form.  I had to mess around a bit to rename things, but it wasn't too bad.  When I had figured all this out, I wrote the loop, and had it output some fake SQL code to make sure the entries and amounts were inserting where I wanted them to.  It was all good, and in the end it was a far more elegant solution than my original plans.  Can't ask for more than that, right?

As I moved on writing the code to actually add these things to the database I ran into another snag, but that's a tale for another day.  I'm still working on that one.


Toilets Suck

I had two things to talk about. One was a minor breakthrough in my surgery tracking project. The other is my hatred for plumbing. Not plumbing itself. But when it goes wrong and I have to fix it. I'm going to talk about this because it's fresher in my mind, and also because I found out my sister is reading this now, and I don't want the first new thing she reads to be about how using multi-dimensional arrays will let me isolate and organize the form data just the way I want.  Hi Kathy.  You're welcome.

So here's what happened.  We discovered a leak in our bedroom's bathroom toilet.  When his happened, we threw a towel down there, and I made a mental note to check it out later when I had time.  I promptly lost that mental note, and the situation worsened.  It was pretty ghastly back there by the time we noticed it again, so my project over the past 2 weeks was fixing this leak.  It's fixed, and there doesn't appear to be any permanent damage to the floor, but I had a time getting it back to normal.

The first problem was figuring out where it was leaking.  There are 3 bolts holding the tank to the bowl, and then the drain valve.  I guessed that it was the seal between the tank and the bowl, and went to my handy dandy home improvement store for a replacement. Now, I've done enough of this stuff to know that it's all pretty much standard.  I didn't realize there are two standard sizes, though.  Can you guess if I got the right one my first time out?  Of course not.  But that's my fault for not doing some very simple research.  The gasket was too big, and the leak persisted.

Ok, second trip out there I get some replacement bolts, and 2 different universal seals, just to be safe.  I also got this weird dual flush thing, where if you lift the handle to flush it does a half flush, and if you push it down it does a full flush.  I figured I was going to be elbow deep in the guts of the toilet anyway, why not upgrade and save some money?

The dual flush thing was actually pretty easy to install, but after replacing the bolts and everything else, the damn thing is still leaking.  At this point the only thing I haven't replaced is the actual flush valve thing itself, where the the water drains into the bowl.  It just seemed like the least likely thing to fail.  All of the other pieces might get moved or stressed as you sit and apply pressure by leaning on the tank, but I couldn't see that affecting this particular seal.  Oh well, it's the only thing left.

Now here's where my own ambition got me into trouble.  Like Icarus flying too close to the sun and plummeting to his death when his wings melted, I decided to keep the dual flush mechanism and try to install it on the replacement flush valve I was going out (again) to get.  There are 2 types of these things, one where opening of the hole is flat and level.  The other type is slightly slanted.  I don't know why, but there are.  Loews was out of the flat type, which is what I had before and wanted to get again.

The dual flush thing works best with the flat type.  For the slanted type you have to use an attachment to get it seated correctly.  The attachment is held in place with this stuff that looked like silly putty, only stickier.  That made me nervous right there.  I'm already have seal problems, and now I'm introducing yet another seal into the equation?  But no, I had to have my stupid low-flow flush thing.

Suffice it to say, when I get everything all bolted back together, it was still leaking.  I'm still not even positive at this point where the problem is.  I think I was on to something with replacing that piece where that dumps the tank water into the bowl.  Was the problem with that adapter I had to use?

I'm at the end of my rope at this point.  I can't tell you how close I came to just giving up and calling a plumber at that point.  But I just couldn't without giving it one more try.  Now, I would like to think that my next move was inspired by my years of technical support, but it could just as easily be from frustration.  Much like a misbehaving computer, I tried to eliminate every possible contributing factor, and bring it down to the bare essentials required for the toilet to function.  If I couldn't get it working at that point, then my only recourse would be to contact a professional.

So I went back to Loews (I've honestly lost count of the number of times I went there during this whole thing) to get a complete toilet repair/replacement kit.  Of course, they were out.  They had about 20 of them the last 8 times I was there, but they were completely sold out today.  Why wouldn't they be?  So I got everything I would need ala carte.  I made sure to get a fllush valve with a nice thick seal, since I think the problem with the last one (the slanted one) was that it was thin, and maybe wasn't making a water tight seal.  Now at this point, I have completely replaced every single component that goes in the toilet at least twice.  I double checked every piece twice, tightened everything until I was afraid I might crack the porcelain, and gingerly turned the water back on.  Everything seemed fine.  I put some paper towels down behind the toilet and checked a few hours later, and they were dry.  The leak was finally fixed.

To say I was relieved would be an understatement.  I can finally cross this off my todo list, and not worry about the floor rotting away beneath us.  Then I realized that when I got all the toilet stuff ala carte instead of in a complete kit, I focused on the stuff that connects the tank to the bowl only, and forgot to get a handle!  The dual flush system had a special handle that wouldn't work with a normal setup, and I had thrown the old handle out when I got the dual flush thing.  So right now the chain that lifts the flapper and starts the whole flush in motion is connected with wire to a plunger handle I have sitting on top of the tank.  But the important thing is it's fixed!  FINALLY!