One of PHP's fundamental strengths is how well it interacts with databases.
To paraphrase the book and movie character Forrest Gump, PHP and MySQL databases go together like "peas and carrots." So in this post, I will demonstrate how to use PHP to make a database connection, use that connection to add content to data tables, read content from data tables, and close a database connection.
Create the Database
For this post, I will be using PHPMyAdmin to create my database, but you can certainly use a terminal, if you prefer.
For this post, I will call my database example.
Next, I will use PHPMyAdmin's "SQL" terminal to add a table to my database.
CREATE TABLE contact (firstname TEXT, lastname TEXT, emailaddress TEXT);
Connecting With PHP
Now that I have a database with a data table, I can use PHP to connect to that database. I will need to know the host name, username, password, and database name. In my example, I am using localhost. This works as long as the PHP script and the MySQL database reside on the same web server.
<?php
$dblink = mysqli_connect('localhost', 'root', 'password', 'example')
or die('failed to connect');
?>
In the code above, I use one of PHP's built-in functions, mysqli_connect() to establish the link between my PHP script and the example database. Notice that I pass four parameters to the function. As mentioned above, these are the host name, username, password, and database name. These must be passed in this order. The first three are required. The specific database name is optional, but you will need to specify the database at some point in your script, so why not here.
Also notice that I used the PHP die() function. It will display an error message if the connect fails.
Adding Data to a Table
One of the most common reasons for linking a PHP script to a database is for the purpose of adding user submitted content to the database so that it can be recalled later or used on other pages. For example, this might happen when a user signs up for a newsletter or begins to fill out a shopping cart form.
To add data to a table, I will actually use a language called SQL, which will be stored as a string in the PHP variable and then sent to the database.
Here is the SQL code to add data to a table. Notice that I identify the table, "contact"; specify the fields I will be inserting into, "firstname, lastname, emailaddress"; and then provide values for each field in order.
INSERT INTO contact(firstname, lastname, emailaddress) VALUES('jane', 'doe', 'jane@doe.com')
In PHP, I will place this code inside of a variable.
$contact_to_insert = "INSERT INTO contact(firstname, lastname, emailaddress) VALUES('jane', 'doe', 'jane@doe.com')";
PHP's mysqli_query() function will pass my SQL string to the database.
mysqli_query($dblink, $contact_to_insert);
Notice that I reference the database connect with the variable $dblink, and then reference the string that I wish to pass to the database.
I could again use die() to create an error message if I wished.
Selecting Data from Tables
Next, I want to get some information back from the database, perhaps, to display on a page. I will again create an SQL string.
SELECT * FROM contact
The keyword "SELECT" gets data from a database. The symbol "*" represents "all," and "FROM" describes which table the data should come from.
I can also select only the email addresses.
SELECT emailaddress FROM contact
Here is the PHP.
$select_email = 'SELECT emailaddress FROM contact';
$result = mysqli_query($dblink, $select_email);
<?pre>
With the selection made, there are several techniques for placing the data on the page. Here, I simply display the first (and only) row of the field.
<pre>
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
printf ("%s \n", $row["emailaddress"]);
Close the Database
To close the database, I will use another built in PHP function, mysqli_close(), passing it to the variable for the connection.
mysqli_close($dblink);
Summing Up
In this post, I have demonstrated how to connect to, query, and close a MySQL database from PHP. While this may be a good start, I have only scratched the surface of what PHP can do.
