Overview
PHP has built-in functions that allow you to connect to your database server to retrieve data. This article details a few examples of using these functions using the mysqli extension.
The examples in this article should be placed in a separate PHP file. Your other PHP files would then reference this script to perform the connection. View the following links for further information:
View the phpMyAdmin article for details on how to find your database credentials.
Creating a file to store your database credentials
For security purposes, you should always avoid placing your database credentials directly into your PHP scripts.
Instead, create a separate file to store your database credentials. You can then call this file within your PHP script to connect to the database.
- Navigate into your user's home directory.
[server]$ cd ~
- Create a new directory named something like database.
[server]$ mkdir database
- Change into this new directory.
[server]$ cd database
- Create a new file named something like dbcredentials.php.
[server]$ nano dbcredentials.php
- Add the following code to this file.
Change the highlighted words to your database credentials.
<?php $DBhostname = "HOSTNAME"; $DBusername = "USERNAME"; $DBpassword = "PASSWORD";
$DBname = "DATABASE_NAME"; ?>
You can then call this file using require_once as shown below.
Creating a connection
In your website directory, create a file named connection.php with the following code. This script creates a MySQL resource named $link. This resource can then be used to create queries.
Change username to your Shell user.
database and dbcredentials.php are from the directions above.
<?php require_once "/home/username/database/dbcredentials.php"; $link = mysqli_connect($DBhostname, $DBusername, $DBpassword, $DBname); if (mysqli_connect_errno()) { die("Connect failed: %s\n" + mysqli_connect_error()); exit(); }
echo "Connected successfully"; ?>
Run the script and you should see Connected successfully.
[server]$ php connection.php
Other PHP scripts that require database access can then use the include() function to access this file.
Executing queries
Retrieving a ResultSet
The following example extracts data in the specified table and displays it in an HTML table for you to view.
This script is an addition to the script above, so make sure you already have your connection script created.
Add an include call at the top of your script to the connection.php file. The $link resource is then used to extract the data.
This script then extracts the data from that table:
<?php include 'connection.php'; $sql = "SELECT * FROM test_table"; $result = mysqli_query($link,$sql) or die("Unable to select: ".mysql_error()); print "<table>\n"; while($row = mysqli_fetch_row($result)) { print "<tr>\n"; foreach($row as $field) { print "<td>$field</td>\n"; } print "</tr>\n"; } print "</table>\n"; mysqli_close($link); ?>
