PHP Diary | PHP Housekeeping | PHP Scripts | TD Scripts.com
TD Keno - Offer your site visitors an engaging Keno game without the monetary risk

[back] 01/10/00 "mySQL basics" [next]

mySQL

It is time to get into mySQL databases. I am going to copy to these pages my reference notes on mySQL because I am not entirely new to mySQL so that you and I can have some sort of online reference material. However I am completely new to using PHP and its interface with a mySQL database. When is it appropriate to use a mySQL database as opposed to flat file database systems? That often comes down to the amount of information you will be storing and/or how often you will be accessing the information. My rating box below, for example, is only tracking one piece of information (votes) and even though every time a page visit happens the average is calculated, this would not be a situation where setting up a mySQL database would bring any significant performance or efficiency boost. However, what about the database we just finished that searches through the meta tags of every diary page?  That is something that might be more suited for a mySQL database rather than opening and closing dozens of files every time a search is conducted. Before I could build any kind of database I needed to understand better what a relational database is.

Relational databases

A relational database seeks to form relations between pieces of information. Let's say I want to start a database of music (I am a music afficiando, btw), and this is actually something I would like to do someday. I would first break down the individual pieces of information I would want to store into fields.

Field 1: Band/Artist
Field 2: Song Title
Field 3: CD/Album Title where song comes from
Field 4: URL to the band's website
Field 5: URL to buy the CD containing the song

All these fields would then be stored in a table from left to right like this:

ID | Artist | Song Title | CD Title | Band URL | Buy CD

You may notice I added the uniqe primary key called ID. In order to manipulate records there need to be some unique key to keep data separated. Every time I inserted a new song into the database I would be inserting a new record.  When I had thousands of songs if I wanted to find every song by say Led Zeppelin, I could simply do a query for Zeppelin and the mySQL database would compile the results for me.  The way you input the records isn't necessarily important, but having all the information you want to query is, because you can make new tables out of existing data once you have your database filled with records.

For example, let's say I wanted to know all the songs from Led Zeppelin's first CD? I could make a new table out of the existing database just for the artist Led Zeppelin. Now instead of searching through every artist, I would be limiting my search to one artist, since I wouldn't be interested in looking through songs by The Beatles. This is how relational databases work. You make the number of fields you will be using, you set a primary key, and then you query the information in the most logical and efficient way (if possible). If you are going to be searching specific pieces of information you can speed up your search by making new tables out of the database which already have the basic information so that your endusers aren't trying to query the entire database to find something specific. Obviously these databases can grow quite large, but they can also be quite useful for storing hundreds of thousands of records.

Now before you can actually use PHP to interface with a database, you have to create a mySQL database.  However, before you can create the database you need to know what fields of information and types of information you will be using. You can insert fields or rows at a later date, but the best planning beforehand is almost always better when setting up databases. So the first thing you should do is sit down with your pen and paper and write all the different fields of information you will want to keep track of or search for like I did with my example above. This will probably be a small number of fields for most projects. For instance, for my diary search engine it would go like this:

field 1: diary filename or URL
field 2: meta tag description
field 3: meta tag keywords
field 4: extra keywords

As you can see I added an additonal field called "extra" keywords. This would be for extra words I wanted to use, but not as meta keywords so when people query the mySQL database the page may come up. Why would I do this? Because maybe I don't want to overflow the meta tags with keywords (some search engines frown upon excess meta keywords), yet I still want to make a useful database of information for people to be able to search.

Now that you have the fields you want to use, you need to classify each field into a specific data type. mySQL has many different data types and I will list some of the more useful ones and provide an explanation for their usage in my next diary entry.

Everquest sidetrack

I have been playing the role-playing game Everquest a lot lately http://www.everquest.com and thought I'd mention it here if you like RPG like Dungeons and Dragons and the like, this might be a good entertainment sidetrack for you. The 3D worlds are fantastic and there are thousands of players online to play with at all hours of the day. All work and no play is no fun :)

Please vote on what you think of this diary entry :)

[back] 01/10/00 "mySQL basics" [next]