Episode Guide

[Ted Varnson]

Hello everyone! I run a Superman/Smallville site and I have neglected my Smallville episode guide long enough. The main reason I have fallen behind on updating it is because it is such a hassle.

I had the idea of creating a form that would insert the data in a mySQL database, then pulling it out using php of course. The thing is, I am a novice when it comes to this. I was wondering if you all could give me pointers on where to start or how to go about it. I'm reading a few tutorials right now.

Basicly what I have now is a form that is going to have fields for
Episode ID
Episode Name
Episode Description
Original Air Date
Music
Guest Stars
Written by
Directed By

My biggest problem right now is figuring out how to set up my table. I am used to working on windows servers and using ASP and access databases. how similar is this? Thanks a bunch for your help.

[sad69]

Well MySQL has a command-line interface by default. There are a few programs out there that provide a graphical interface, the more popular being phpMyAdmin (http://www.phpmyadmin.net/home_page/).

It will let you see what databases you have in MySQL (you can have more than one..), and what tables are in each particular database. You can create new databases and tables and alter, drop, insert, update, and just about everything else.

It's web-based, so it runs in your browser. Your webserver needs to have PHP installed, and you also need to have MySQL installed and running (it's a server). You also need to install/unzip phpMyAdmin (http://www.phpmyadmin.net/home_page/) in your web directory (.www, or htdocs or whatever..), so that you can open it up in your browser.

That's usually a good place to start so that you've got the table(s) created and whatever. Then to pull the data out of the database using PHP, here are a few good tutorials:
http://www.freewebmasterhelp.com/tutorials/phpmysql/
http://www.createafreewebsite.net/phpmysql/phpmysql_introduction.html
http://webmonkey.wired.com/webmonkey/programming/php/tutorials/tutorial4.html

If you have any other questions, just post back. (Create new threads in the PHP forum if your questions are regarding PHP. for the most part, once you've got your database setup, the remainder of the questions are normally regarding PHP, unless you have questions about queries and what-not).

Good luck!
Sadiq.

[Ted Varnson]

Thanks a lot for your help. Now that I have that going I can work on getting it on my website. to the PHP forum!!

[Kiwi]

With fields like "Music" and "Guests" where there may be zero, one or more tied to one or more episodes, it makes sense to put them in seperate tables, then use a linking table to connect them. This means you are not restricted to a fixed number of songs, guests per episode and you can handle repeated entries without data errors.

[sad69]

With fields like "Music" and "Guests" where there may be zero, one or more tied to one or more episodes, it makes sense to put them in seperate tables, then use a linking table to connect them. This means you are not restricted to a fixed number of songs, guests per episode and you can handle repeated entries without data errors.

This is true, but if he could set those fields up to be TEXT or LONGTEXT and he could just type them in, and only be restricted by the number of characters.. it's simpler if redundancy is not an issue. It might be slightly slower, but.....

Sadiq.

[Kiwi]

It's not about speed: it's about good design and data integrity. Using free text fields for values that are replicated through-out the database opens the door for errors and reduces the power and flexibility of the design. Given that it's easiest to fix at the initial design stage, it's worth spending a bit of time thinking about it then, rather than later.

I've had to go through too many databases cleaning up data that could have easily been prevented by a decent design. The fact that you miss the principle reason for it, pretty much makes the point.

[raf]

Kiwi is absolutely right.

One of the first steps in normalising a db is taking out composed values and making sure that each cell only contains a single value. (as with any rule , there are situations where this isn't the best sollution, but not for this particular case).

Unless you are absolutely sure that you will never use the guest stars for anything else as just a simple descriptive textfield, then you'll need to take it out of this table and set up a table like

episodeID_FK
startID_FK

which then contains the pimary key value of your episode table and your star table. For each start is an episode, you'll have 1 line in this table.

If the number of stars is limited and fixed (not very likely) then you could keep the gueststars inside your episode table as an ENUM field to simplify your design. But normalising your db and setting up the linking tables offers much more flexbility.