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.
If you want to provide content coming from a database, you will at least have to know the following functions:
There are different scenarios of data readout we want to take a look at.
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.
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.
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.