So since the other cool kids in my class are blogging their progress I thought maybe I should do the same. I figure it would also be a good way to track what I’ve done, what I have left to do and keep track of some of the places where I found my code.
Up until last week I’ve felt very behind the rest of my classmates. While they were already playing with data sets that already existed (and this does not mean they had it easier than me as Jeri’s post can attest to) I had to first figure out how to get my data into a database to begin with. I have almost 3,000 cartoons that exist in pdf format with no OCR and no searchable data besides their date. For years I’ve dreamed of building a database that contained juicy meta data like the names of the characters represented and the specific events and themes that the cartoons discuss.
To do this I took advantage of this week’s homework assignment to build a form using PHP that would insert my data into a MySQL database. Easy, I thought. Oh how wrong the ignorant are. My first experiment was with an example tweeted to us by my prof, Fred Gibbs. After spending hours trying to replace its variables with my own and failing miserably, I decided I needed to start from scratch. The problem was, I still didn’t quite understand what the heck was going on in the PHP to make it look the way it should.
I did a few google searches and found this tutorial at teamtutorials.com which goes through the process step by step to just get basic input field entries into a database. Once I understood those fundamentals, it was time to get complex. I combined what I knew from the teamtutorials post with tizag.com’s HTML forms tutorial to get my basic form.php functioning and then struggled the rest of last weekend with getting those references into my database but more importantly for me…getting them to populate multiple tables.
I started small. First, create a dropdown menu populated from information from my database. This was a crucial first step as each cartoon needs to have an artist and since I only have three to choose from, a dropdown select menu grabbing the artist’s respective primary key was crucial. I finally found some code that I was able to manhandle into working.
Once that was done I had to figure out how to get the auto incremented Primary Key created in MySQL from my “new cartoon” that I created from combining the PK of my artist, the cartoon date (which I figured out how to successfully explode and reformat into proper MySQL format) and the caption of the cartoon. This would then be combined in a joiner table that would need to pull the Primary Key from my Characters table of either an existing character that was created new from another cartoon, or a new character that I created simultaneously from my form. Make sense? Good, because it didn’t to me at first either.
There were four things I desperately needed to figure out.
- How do I get the Primary Key from a cartoon that’s being created instantaneously when I hit the submit button on my form? (subsequently this applied to any new character I wanted to create)
- How do I get those Primary Keys into their respective Foreign Key columns of my joiner table?
- How do I do this for an existing character I’m selecting from a dropdown menu I’ve populated similarly to my artists dropdown?
- How do I do this for multiple new characters, multiple existing characters and any combination thereof?
The first through third problems were figured out once I stumbled upon mysql_insert_id(). Once I found that and figured out how it worked I was able to create a variable that all it was was the primary key I had just created for my new cartoon and new character. I could then easily write a query that inserted those new variables into my joiner table and for my third problem combine the new cartoon id variable with the Primary Key from my existing character dropdown and plug those into my joiner table as well. Problem was, I could only do that for one new character, or one existing character, but I could envision a situation where I would have multiple new characters, and/or multiple existing characters.
I knew there was a way to use arrays and a foreach loop to solve this problem but for the life of me I couldn’t figure out how to do it. It was here that my brain officially broke and I enlisted the help of my fellow CHNMer and go to code guy, Patrick Murray-John. Who, on a Saturday night, looked at my code that I had posted on GitHub and rewrote my existing character code to make it an array and wrote my foreach loop so that I could populate my joiner table from multiple existing characters.
Hallelujah! My problem has always been, especially with code, that I have trouble translating something from theory into practice with my own work. I understood how arrays functioned. And I understood how the foreach loop functioned. I could not conceptualize how that would work with how my database was structured and what I needed to do. After Patrick had built me an example it was like a light bulb went off in my head. I understood EXACTLY what an array was and what I could do with it.
It didn’t take me too much longer to figure out how to combine the explode command I had previously used in my date restructuring to explode the entries in my new character box (with the deliminator being a ‘, ‘ – can’t forget the space or things wouldn’t alphabetize nicely when they become existing characters in my dropdown) and run a foreach loop on my newly created array.
After that, it was just a matter of cleaning up the code a little bit to make sure that my new character array played nicely with mysql_insert_id() and to test the bejezus out of it to make sure that any combination of new or existing characters would work, and it did!
So now that I have figured out how to do this for one of my meta data fields, I can easily replicate it to work with new and existing themes, events and keywords.
Next step: Editing a cartoon’s meta data. How do I structure it if I want/need to add a character/theme/event/keyword I forgot, change one that’s already there and do this simply and quickly for any combination of my meta data fields at once?
You can find the form here. Feel free to populate it as it’s a dummy database and will not corrupt my “real” data.
The code for it is up on GitHub here.
*Update 9/26/12 – The form and code that this post previously linked to has been replaced with an upgraded version. The links are for the current form and the related code.