This tutorial is split among three sections in this post. This is how the tutorial is organized:
- Introduction to databases
- Creating our database
- PHP Code
1. Introduction to databases
Databases are ways of storing structured and organized data, which is extracted using Standard Query Language (SQL). Each database has database tables that contain fields (or columns), which can be filled in to create a record (or row). When creating a database, you must choose what type of data each field will contain. Here are several different types of data, possible values for each type, and a good use for each one:
TINYINT
Values: integer from -128 to 127, or 0 to 255
Logical use: a bool of 0 or 1 to show if something is on or off
INT
Values: integer from -2147483648 to 2147483647, or 0 to 4294967295
Logical use: storing a Unix timestamp of the date
FLOAT
Values: number from -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38
Logical use: a dollar amount (or any number that is not an integer)
VARCHAR
Values: a string ranging from 1 to 255 characters in length
Logical use: a name
TEXT
Values: text with maximum length of 65,535 (2^16 - 1) characters
Logical use: articles
BLOB
Values: text with maximum length of 65,535 (2^16 - 1) bytes
Logical use: articles
Note: Most people don’t know the difference between BLOB and TEXT, but (as shown above) their maximum values are determined differently: a BLOB’s maximum value is determined in byte size, while a TEXT’s maximum value is determined by the actual number of characters.
Once you have created a database table with fields, records of data may now be inserted into the table. A record can store data in one or all of the fields depending on how you set the fields to require information.
The four most popular databases to use in conjunction with PHP are MySQL, Oracle, PostgreSQL, and Microsoft SQL Server. For this tutorial (and all others that I write), I will be using MySQL, which is open-source and also the most popular database at the time of writing. It is a free database that is incredibly fast, but lacks some sophisticated features that some developers need, but that’s nothing you should be worried about right now.
Note: For this database tutorial, and any others following it, you will need to enter in some SQL statements to create and alter information. I’m going to assume that if you are reading this, you have MySQL installed on a web server. If you are unsure of how to enter SQL statements into your MySQL database, contact your web host or look into phpMyAdmin (
http://phpmyadmin.net), an open-source database management tool written entirely in PHP with a GUI.
2. Creating our database
For our example of extracting information from a database, we will be creating a link manager. Our database table will only consist of two fields to retain simplicity and not overwhelm the beginner. One field will be for the name of the site and the other field will be for the URL. The optimal type of data we want these to be stored as is VARCHAR, since they are small strings.
Here is the SQL query to create our database:
Code:
CREATE TABLE `link_manager` (
`name` VARCHAR( 50 ) NOT NULL ,
`url` VARCHAR( 100 ) NOT NULL
);
The above query creates a table with the name “link_manager” that contains two fields: “name” and “url.” The maximum amount of characters allowed to be entered in the “name” field is 50 and 100 for “url” (leaving room for more characters than that is unnecessary).
Below is the query to insert two records in our database to test with:
Code:
INSERT INTO `link_manager` ( `name` , `url` ) VALUES ( 'Yahoo!', 'yahoo.com');
INSERT INTO `link_manager` ( `name` , `url` ) VALUES ( 'Google', 'google.com');
3. PHP Code
Now I will show you how you can access all of this data from your database. First, we will need to create a file to put our php code, for now, we will use "links.php" (remember, any page on your website that you want to have PHP code must be have the ".php" file extension, unless PHP is set differently on your server).
Below is the entire script with comments to show you how each part works:
PHP Code:
<?php
$hostname = 'localhost'; // This variable is your host or the server your database is on. On most servers it is "localhost."
$username = 'yourusername'; // Your username that you log onto your database server with.
$password = 'yourpassword'; // Your password that goes with your username.
$dbname = 'mydb'; // The name of your database. In this case, it will be "mydb".
if (!@mysql_connect($hostname, $username, $password)) // This checks to see if we cannot connect to the database (the "@" suppresses any error from the function as we will be handling the error ourselves in the script)
{
echo 'Error, unable to connect to database!'; // If we are unable to connect, we print an error and stop the script with "exit"
exit;
}
if (!@mysql_select_db($dbname)) // This checks to see if we can select the proper database
{
echo 'Error, unable to select database!';
exit;
}
// Now we need to select the relevant data from our database,
// this is done with Standard Query Language (SQL)
$sql = 'SELECT url, name
FROM link_manager';
$result = mysql_query($sql); // This runs the SQL query we just stored as a variable and grabs the information we requested.
// This loops through each record in our database and stores
// its data as an array in $row
while ($row = mysql_fetch_array($result))
{
// This line of code will display our data for each record selected
// as the name of the entry as a url to its website.
echo '<a href="http://' . $row['url'] . '">' . $row['name'] . '</a><br />' . "\n";
}
// Now we close the connection to our database; however, this
// is optional. You do not need to close the connection to the
// database, it will do it automatically at the end of the
// script; it is just here to show you how to do it.
mysql_close();
?>
Here is what should output on the page:
<a href="http://yahoo.com">Yahoo!</a>
<a href="http://google.com">Google</a>
If that's the case, then that means you have successfully extracted information from a database!
Comments? Questions? Post 'em!
Tutorial > PHP > Displaying Database Information
.
