Please note:
This material is adapted from Build Your Own Database Driven Website Using PHP and MySQL, by Kevin Yank. 2nd Edition, published by Sitepoint Pty. Ltd. ISBN 0-9579218-1-0. The book includes much more information, and you are encouraged to buy it!
Chapter 2 Notes, page 1
Getting started - Intro to Databases
Using a database on your website has at least two advantages: One, you can write a single PHP file that will get the information you need out of the database as needed, rather than having to write a separate HTML page for each piece; and two, adding a piece of information to your site just requires adding it to the database. The PHP file will automatically display the new information when it is retrieved.
Data in a database is stored in tables. Each table has one or more columns, or fields. Each field has a particular type of information - for instance, the date of the information in the row. Then there may be a field for the text of the information, and another for the specific identification number of the item. Each row is a group of fields, or record. So a table of song titles might look like this:
| ID no. | Title | Date |
| 1 | Light My Fire | 07-31-2004 |
| 2 | Bob Dylan's 115th Dream | 07-31-2004 |
| 3 | We Can Talk About It Now | 07-31-2004 |
Note that the ID column should contain a unique value, so that no item can be entered twice, and you can always tell each record from all the others. This gives you a way to keep track of the records and to refer to them. This will be vital later on.
Logging on to MySQL
At this point, we have to take note of the fact that the command line interface that the author describes will not work on the site www.databasedrivenwebsite.net, because our host wants us to use a graphical interface, phpMyAdmin, instead. However, the site developed by Ouida and Mark, who actually know what they are doing, can be accessed in this manner. For illustration purposes, I am going to use the database on my local computer.
To make the connection, you need to have the MySQL client program on your computer. The program is called mysql.exe, under Windows and it is located in the C:\mysql\bin directory. See the text for Linux information. You need to open a command prompt (cmd.exe under Windows) and navigate to that directory. Then type in the following:
C:\MySQL\bin>mysql -h localhost -u root -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.20a-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Ahem. Sorry. Now then. After the user name, you type in -p, which will cause you to be prompted for your password. You gave this to yourself back in Chapter 1, when you set up the database. At least you should have. If you did, you will see the asterisks when you type it in. If you are successful, you will see the message as you see it above. And you will be back at the command prompt.
The MySQL server can keep track of a number of databases, so you need to choose which database you are interested in. First, we'll get the list of databases on the server (that is, my computer.) Type this command, remembering that you always follow a command with a semicolon. (If you forget, MySQL will assume that you have not finished typing and will wait for you to enter that semicolon.)
mysql>show databases;
MySQL will show you a list of the databases on the server. (It includes one called "test," which we will ignore; it is just a sample.)We will also leave the "mysql" database alone; the MySQL server uses it to keep track of users, passwords, and what you ate for breakfast. Well, not that.
mysql> show databases;
+------------+ | Database | +------------+ | globalcar | | greatlines | | mysql | | test | +------------+ 4 rows in set (0.01 sec) mysql>
By the way, when I use quote marks here, it is for clarity in the text - they are not part of the syntax, so leave them out unless they are specifically required.
If you want to delete a database, you type "drop database." It will happen and you will get no warning or chance to confirm! So don't do this unless you want to wipe something out entirely.
If you get halfway through a command and realize that you want to type something else, use the Cancel command by typing \c and pressing Enter. MySQL will go back to the prompt. And if you want to exit MySQL, just type "quit" or "exit." For this, you don't need the semicolon.