PHP and MySQL
PHP has the ability to connect to and manipulate databases. The most popular database system that is used with PHP is called MySQL. This is a free database system, and comes with the Wampserver software you may have installed at the start of the course. We will be working with MySQL databases throughout these lessons.
We'll create our databases with phpMyAdmin, which is part of MySQL. If you installed Wampserver then you can go to the phpMyAdmin section quite easily. Click the icon in the bottom right and select phpMyAdmin from the menu:
Another way to bring up phpMyAdmin is to simply type the following address in your browser:
http://localhost/phpMyAdmin/
You should see the phpMyAdmin page display:
We're going to be creating databases using this. But if you can see the page OK, then you have everything you need. You might want to bookmark the mysql page, though!
If you can't see the page, then either MySQL isn't installed, or it's not configured correctly. Again, you need to refer to the documentation to fix this.
However, we have a few databases amongst the files you downloaded. So you can still follow along with the later tutorials.
Create a database with phpMyAdmin
You can create all of your database tables and queries using PHP code. But before doing that, it's a good idea to get an understanding of just what it is you'll be creating. If you're new to the world of databases, then here's a simple primer.
What is a database and what do they look like?
A database is a way to store lots of information. You might want to store the names and addresses of all your contacts, or save usernames and passwords for your online forum. Or maybe customer information.
When you create a database, you're creating a structure like this:
The columns (ID, Title, First_Name, Surname) are called Fields. The rows are called Records. Each record is a separate entry.
In a database, you save the information in a Table. A single database can contain many tables, and they can be linked together. When the tables are linked together, it's said to be a relationaldatabase. If you just have a single table in your database, then it's called a flat-file database. Flat-file database are easier to create and understand, so we'll start by creating one of these using phpMyAdmin.
So bring up phpMyAdmin, if you haven't already done so.
Although it looks a bit muddled, the part to concentrate on is the textbox under the words create new database, as in the next image:
This is where you type a name for your database. We're going to create a simple Address Book, so type that into the textbox:
After you have typed a name for your new database, click the "Create" button. You will be taken to a new area:
In this new area, you can create a Table to go in your database. At the moment, as it says, there are No tables found in the database. But the database itself has been created.
To create a new table, type a name for it in the box at the bottom. You can also type a number for the Fields textbox. The fields are the columns, remember, and will be things like first_name, surname, address, etc. You can always add more later, but just type 4 in there. In fact, type it out exactly as it is below:
When you've finished, click the Go button. Another, more complex, area will appear:
In this new area, you set up the fields in your database. You can specify whether a field is for text, for numbers, for yes/no values, etc.
phpMyAdmin Database Fields
You have four Fields in your table from the previous section. Although they are set out in rows in the images, the rows are actually the Columns you saw earlier – the Fields. Each Field needs a name. So go ahead and type the following for your Field names:
So we have given each column in our table a name: ID, First_Name, Surname, and Address. The next thing to set is what type of data will be going in to each field - do you want to store text in this field, numbers, Yes/No value, etc?
To set the type of data going into a field, you select an item from the Type drop down list. Click the down arrow to see the following list you can choose from:
A List of the Field Types (opens in a new window - 21K)
As you can see from the image above, there's quite a lot! But you won't use most them. For the values we have in our four fields, we want to hold these Types:
ID – A number, used just to identify each record. This needs to be unique for each record
First_Name – Text
Surname – Text
Address – Text
If you look at the list, there is an INT but no Number; and there are four different Text Types to choose from. We can use INT (meaning integer) for the numbers, but again, there are a few Integer Types to choose from. And that's leaving out things like float and double. Here's the difference between them, though.
Integer Values
TINYINT Signed: -128 to 127. Unsigned: 0 to 255
SMALLINT Signed: -32768 to 32767. Unsigned: 0 to 65535
MEDIUMINT Signed: -8388608 to 8388607. Unsigned: 0 to 16777215
INT Signed: -2147483648 to 2147483647. Unsigned: 0 to 4294967295
BIGINT Signed: -9223372036854775808. Unsigned: 0 to 18446744073709551615
The signed and unsigned are for minus and non minus values. So if you need to store negative values, you need to be aware of the signed ranges. If you were using a TINYINT value, for example, you can go from minus 128 to positive 127. If you didn't need the minus value, you can go from 0 to positive 255.
For our address book, we have an ID field. We're using this just to identify a record (row). Each record will be unique, so it will need a different number for each. We can set it to one of the INT values. But which one?
If we set ID to TINYINT, then you'd run in to problem if you tried to store more than 255 records. If you used SMALLINT, you'd have problems if you tried to stored the details of friend number 65536. IF you have more than 65 and half thousand friends, then you need a different INT type. We'll assume that you don't, so we'll use SMALLINT.
Text Types
The length for the text types can be quite confusing. The MySQL manual says this about the various lengths that each text type can hold:
TINYTEXT L+1 byte, where L < 2^8
TEXT L+2 bytes, where L < 2^16
MEDIUMTEXT L+3 bytes, where L < 2^24
LONGTEXT L+4 bytes, where L < 2^32
This in not terribly helpful for beginners! So what does it mean. Well, the L + 1 part means, "The length of the string, plus 1 byte to store the value." The translated values for each are approximately:
TINYTEXT 256 bytes
TEXT 64 KiloBytes
MEDIUMTEXT 16 MegaBytes
LONGTEXT 4 GigaBytes
To confuse the issue even more, you can also use CHAR and VARCHAR to store your text. These are quite useful, if you know how many characters you want to store. For example, for a UK postcode you don't need more than 9 characters, and one of those will be a blank space. So there's no sense in setting a postcode field to hold 4 gigabytes! Instead, use CHAR or VARCHAR.
CHAR
You specify how many characters you want the field to hold. The maximum value is 255. For example:
CHAR(10)
This field can then hold a maximum of ten characters. But if you only use 4 of them, the rest of the 10 characters will be blank spaces. The blank spaces get added to the right of your text:
"TEXT "
"TENLETTERS"
VARCHAR
Like CHAR, but the rest of the characters are not padded with blank spaces. The maximum value before MySQL 5.0.3 was 255. After this it's jumped to 65, 535. With VARCHAR, there is also an extra byte that records how long your text is.
For our fields, then, we'll use the following Types:
ID SMALLINT
First_Name VARCHAR
Surname VARCHAR
Address TINYTEXT
So select these from your Types drop down list:
We've only set Lengths for the VARCHAR TYPES. If you leave it blank for VARCHAR, you'll get a default value of 1 character.
The other Field settings we'll take a look at are these:
Null
This is an important field in database terminology. It essentially means, "Should the field contain anything?" If you set a field to NOT NULL, then you can't leave it blank when you come to adding records to your database. Otherwise you'll get errors.
Default
Do you want to add anything to the field, just in case it's left blank when adding a record? If so, type it in here.
Extra
This is where you can set an auto increment value. This means adding one to the previous record number. This is ideal for us, as we have an ID field. Then we don't have to worry about this field. MySQL will take care of updating it for us.
The three icons are Primary Key, Index, and Unique. Primary keys are not terribly important for flat-file databases like ours. But they are important when you have more than one table, and want to link information. They are set to unique values, like our ID field. An index is useful for sorting information in your tables, as they speed things up. Unique is useful for those fields when there can't be any duplicate values.
So, set a primary key for the ID field by selecting the radio button, and choose Auto Increment from the Extra drop down list:
Your field screen then, minus the parts we've ignored, should look like this:
Bear in mind what we've done here: we've just set up the fields for our table, and specified the kind of information that will be going into each field (the columns). We haven't yet added any information to the table.
Click the Save button on the fields screen. You'll be taken back to the Structure screen. There should be a lot more information there now. Don't worry if it looks a bit confusing. All we want to do is to add one record to the table. We'll then use PHP code to add some more.
phpMyAdmin Tables - Adding Records
To insert a new record to the table you created in the previous section, select the Insert link at the top of the page:
When you click on Insert, you'll be taken to a new area. This one:
As you can see, our four fields are there: ID, First_Name, Surname, and Address. But look at the lengths of the textboxes under the Value. The sizes are determined by the length of the Fields. The address area is a lot bigger, because we used TINYTEXT.
To enter a new record in your table, you type your data in the textboxes under the Value heading. Go ahead and enter the following information for the Value textboxes:
ID: 1
First_Name: Test
Surname: Name
Address: 12 Test Street
Your screen should then look like this:
Finally, click the Go button at the bottom of the screen to create the Row in your table. You will be returned to the Structure screen.
And that's it - you now have a database to work with. To see where it has been saved, navigate to your Wamp folder on your hard drive. Double click the folder called bin\mysql\mysqlx.x.x. Inside this folder will be one called data. This is where all of your databases are stored:
Notice the folder name in the image above: testDB. This is the same as the database name, and is automatically created for you for all new databases. When you double click this folder, you should see a few files there:
Notice the files names - they are the same as the tables you create. In other words, they ARE the tables.
If you have PHP web space, you can upload this folder and its contents to your data folder, and you should then be able to access the tables in the database with PHP code.
We can move on to doing just that - accessing this database with some PHP code.
Comments
Post a Comment