Learn PHP : MySQL Databases
Whilst there’s plenty more I could go into with regards to PHP I realise that most people want to use PHP to access a database, store information, run a site off one page etc. So I figured that the easiest way to do this is to go over databases and then run through a whole CMS (content management system) which is pretty much the standard one I tend to use/alter for my web sites.
Why not go and just download and install one of the available CMSs out there such as Mambo? Well you could, but a: where would the fun be, b: it’s easier to learn by example and c: at least if you’ve written it it’s easier for you to alter and update 😉
So first things first, MySQL databases. I’m not going to go into the raw code used from the command prompt, if you want to learn hardcore SQL then you’ll be waiting a while as I’m still a beginner with it. However for PHP at first, there are about 4 commands you need to understand and that’s about all (I’m talking the basics here before anyone points anything else out ;)). So you will now need, if you don’t already have them
MySQL and PHPMyAdmin. The first is the database server which you need to install as per the instructions given. The second is a graphical user interface for setting up and interacting with databases. Again, it comes with instructions of how to set it up.
Once running you should be able to navigate to localhost/phpMyAdmin/ where ‘phpMyAdmin’ is the folder that it is stored in (I tend to change the given folder name to something simpler to type in) and it should load phpMyAdmin for you. On the left is a little drop down menu of existing databases and on the right is your main work area. Currently with a box to create a new database.
A quick run down of phpMyAdmin – I’m not going into detail as their web site can do this for you. This site is for learning PHP, I don’t know enough whatsoever to try and write about MySQL too!
So enter a name into the new database box eg. mydb. This will create a completely empty database. Then on the next page it will ask for the name of a table and how many fields it will have. For a quick explanation, a database can hold many tables. Each table holds the rows/records of information. The table is organised into fields/column headers so to speak. So create a table called ‘content’ with 5 fields. On the next page it will list a form for 5 field inputs. Put the following where it fits in the form
rowid – INT – 5 – autoincrement – Primary key
firstname – VARCHAR – 25
surname – VARCHAR – 35
address – TINYTEXT
postcode – VARCHAR – 10
Then click Go. Now you have a table set up to store a person’s name and address. To explain each option listed above:
The first field is the rowid. It’s always handy to have a number, that autoincrements on the table for easy admin and to keep uniqueness in the table. I’ve set it to be the Primary key ie. a unique entry. INT stands for Integer and 5 means 5 bytes long (which subsequently works out to 5 integers long ie. up to 99999).
The second, third and fifth fields use VARCHAR which stands for Variable Character. I always use this field type for alphanumeric fields over CHAR. Finally the address field type is TINYTEXT which accepts up to 255 characters of text. This is perfect for short phrases or descriptions.
So there is a table in a database. For further information on using phpMyAdmin take a look on their web site where there is good documentation. From here on in with my posts a basic understanding of databases will either be needed or else you’ll just have to take what I say and do it exactly. I’ll explain the whys as I go along but I’ll be assuming a level of knowledge. Have a play in phpMyAdmin, it’s quite easy to get to grips with.