Lesson 12: How to connect to MySQL using PHP

This lesson describes step-by-step how to connect to a MySQL database using PHP. It is a necessary step to enable your PHP script to read data out of your database. Otherwise WordPress could not read the content of your post and would only present an empty theme to your user. We are bridging the worlds of PHP and MySQL.

Required functions for PHP to interact with MySQL servers

If you want to provide content coming from a database, you will at least have to know the following functions:

• mysqli_connect($db_server, $db_user, $db_password, $db_name) — opens a connection to a database server (returns $db_connection)
• mysqli_query($db_connection, $sql_query_string) — sends a SQL statement to the DBS (returns $statement)
• mysqli_fetch_row($statement) — creates a result set with data coming from the DBS (returns $result)
• mysqli_insert_id($db_connection) — gets the last generated ID coming from an auto_increment attribute (returns $last_id)
• mysqli_error() — gives the error string coming from the DBS in case of an error (returns $error string)
• mysqli_num_fields($statement) — gets the column count of a statement

There are different scenarios of data readout we want to take a look at.

Database connection and simple data readout

The following code is an example on how to do a full PHP – database interaction roundtrip:

1
2
3
4
5
6
7

<?php
$server = "dbs.your-domain.com";
$user = "bob";
$password = "P4ssW0rD!";
$database = "wordpress";
$conn = mysqli_connect($server, $user, $password, $database);
?>

The code above enables you to build a working database connection from your PHP script. However we have not read any data yet.

1
2
3
4
5

<?php
$stmt = mysqli_query($conn, "SELECT TEXT FROM TEST") or die ( mysql_error() );
$row = mysqli_fetch_row($stmt);
echo $row[0]; // gives the first item of TEXT in TEST
?>

The last two lines will allow data readout, but only for the first row of one attribute.

Reading one attribute and all rows of a table using WHILE loop

If you wanted to read all rows of table TEST for attribute TEXT you’d have to use a WHILE loop as discussed earlier:

1
2
3
4
5
6

<?php
$stmt = mysqli_query($conn, "SELECT TEXT FROM TEST") or die ( mysql_error() );
while ($row = mysqli_fetch_row($stmt)) {
     echo $row[0]; // gives the current item of TEXT in TEST
}
?>

The WHILE loop above enables you to echo all values of attribute TEXT in TEST.

Reading several attributes and all rows of a table using WHILE and FOR loops

If you wanted to read all rows of table TEST for more than one attribute you can do the following:

1
2
3
4
5
6
7
8
9

<?php
$stmt = mysqli_query($conn, "SELECT ID, TEXT FROM TEST") or die ( mysql_error() );
$column_count = mysqli_num_fields($stmt);
while ($row = mysqli_fetch_row($stmt)) {
     for ($j = 0; $j < $column_count; $j++) {
          echo $row[$j]; // gives the current item of the current attribute in TEST
     }
}
?>

You notice that we put a FOR loop inside the WHILE loop which counts through every column (attribute) of the table we are fetching.

The advantage of the code above is that you can re-use it for any table and any attribute you like.

I hope this part of our PHP MySQL tutorial article helps you in getting started with understanding the bridge between PHP and MySQL. We will now focus on actual data readouts, by using a common problem: a login script with database user data.

The next lessons:

• Lesson 13: PHP Login Script Tutorial
• Lesson 14: Dynamic PHP based MySQL data management with HTML output