Basics of PHP – Querying a Database (Select)
May 28, 2010
Querying a database is where PHP can get fun. If you’re developing a site chances are you will need a database. Database’s are often used to hold user information (like login information) or to record visits to the site. Some of the most complex sites have hundreds of tables each recording vital information.
In this tutorial we will start by learning how to connect to the database and then how to retrieve some information from it (we’ll be creating a contacts database to record people’s phone numbers). To join in you will need to have access to a MySql database & phpMyAdmin, if you have installed WAMP then you will already have this on your computer.
The first thing we are going to do is create a new database and call it contacts, inside the contacts database create a table called people. Inside the table you will need to create three columns called contact_id, name & phone_number. contact_id should be set to INT and Auto Increment, name should be set to VARCHAR (200) and phone_number to VARCHAR (20).
Now that your database is set up it’s time to put some info in there, insert a few rows (leave the contact_id blank – auto increment will automatically assign an ID) of data into the name and phone number, just so that we have something to display in PHP.
Next we start with the PHP file, first we need to connect to the database. Database connection through MySql uses the mysql_connect() function.
<?php
$mysql_connection = mysql_connect('localhost', 'username', 'password');
?>
This should be reasonably straightforward, replace localhost with the IP address of your MySql database (or leave it as localhost if that is what you are using, remember to keep the quotes in place) and then replace username and password with your MySql login information, remember to keep the quotes in place. You may also be wondering why we have assigned the connection to the variable $mysql_connection, we have done this so that we can use the connection multiple times in the future in different functions like mysql_select_db() and mysql_error(), these two functions require the mysql_connect() details in order to work so we have made it easy on ourselves by storing mysql_connect() in a variable.
Now would be a good time to check have PHP check that those login details actually work, one way of doing this is by using mysql_error(). The mysql_error() function displays any MySql errors to the user.
<?php
$mysql_connection = mysql_connect('localhost', 'username', 'password');
//Check for errors
if(!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}
?>
A lot of the above is brand new so let me introduce it. Firstly you may have seen the (!) operator on the previous tutorial, when this operator is used it is asking PHP if the function has returned false instead of true. If the database details did not work then mysql_connect() will have returned false, as we have stored mysql_connect() inside the variable $mysql_connection, $mysql_connection will currently be set to either true or false. In the case that the MySql connection has failed then the code inside the curly brackets is executed. The die() function is used to display a message and then stop the execution of the rest of the page, when you use this PHP will only display what is shown between the brackets (in this case “Could not connect to db”… ect.) and then will not show or do anything else. mysql_error() displays the MySql error and the period (.) in between mysql_error and “Could not connect to db” is called a concatenation operator and is used to join things together, more on this later though (don’t worry about understanding it right now).
So, if no MySql errors appear then the script will move on to the next part, which is to select the database. Previously we have just connected to the MySql server, now we need to tell PHP what database we want to use.
<?php
$mysql_connection = mysql_connect('localhost', 'username', 'password');
//Check for errors
if(!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}
//Select database
mysql_select_db('contacts', $mysql_connection);
?>
Again pretty straightforward, replace ‘contacts’ with the name of your database and keep the quotes. We can also use mysql_error() here too if we want to check for errors.
Now that we have connected to the database it’s time to select some data from our ‘people’ table.
<?php
$mysql_connection = mysql_connect('localhost', 'username', 'password');
//Check for errors
if(!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}
//Select database
mysql_select_db('contacts', $mysql_connection);
//Query database
$res = mysql_query("select `name`, `phone_number` from people");
while($row = mysql_fetch_array($res)) {
echo 'Name: ';
echo $row['name'];
echo ' Phone Number: ';
echo $row['phone_number'];
echo '<br />';
}
?>
After running that you should hopefully see your database data being displayed on the website, if so congratulations but hold on, there’s more.
Just like with mysql_connect() we have stored mysql_query() as $res and mysql_fetch_array() as $row, mysql_fetch_array() loads all the rows into $row to be displayed. While() is a loop that keeps running until everything has been displayed (this will be covered in another tutorial).
Now there’s one piece of house keeping we can do to keep everything nice and easy, and save us some time. Remember the concatenation operator? Here is the operator in use to make everything much simpler.
<?php
$mysql_connection = mysql_connect('localhost', 'username', 'password');
//Check for errors
if(!$mysql_connection) {
die('Could not connect to db: ' . mysql_error());
}
//Select database
mysql_select_db('contacts', $mysql_connection);
//Query database
$res = mysql_query("select `name`, `phone_number` from people");
while($row = mysql_fetch_array($res)) {
echo 'Name: ' . $row['name'] . ' Phone Number: ' . $row['phone_number'] . ' <br />';
}
?>
The operator allows us to join a string together with a variable (as many times as we like) inside one echo function, this can be used pretty much everywhere to join string with integers, variables and functions.
