My Web Training

📖 PHP Connecting to a Database

W3Schools shows 2 methods to connect to MySQL. They are mysqli and PDO. PDO is the object oriented method which we will use in the advanced course. In this course, we will use mysqli.

mysqli_connect()

The built-in PHP mysqli_connect() function creates a connection between a php program and a MySQL database. The user account in the database must have the appropriate permissions set for these scripts to work. At a minimum, the user permissions should include SELECT, INSERT, UPDATE and DELETE. Add the SHOW GRANTS; command to also return the current user's permissions.

The mysqli_connect() function is so ubiquitous in PHP database-driven sites, it is a best practice to have it available to all your pages. A good practice is to create a PHP file to hold these site configuration settings. So, let's create a file called config.php and add the MySQL connect script to it. Then you can simply include 'config.php'; at the top of any pages that will be accessing your database.

Example

/* Contents of config.php */
$conn = mysqli_connect("localhost", "user", "password","database");
// where $conn is the name you assign to the connection,
// user is the authorized user name (your avatar name),
// password is the password for the user (your server password), and
// database is the default database (your avatar name).

Add config.php to PHP Scripts

/* Include the config.php file in your scripts */
include_once 'config.php';

mysqli_connect_error()

To check to see if the connection worked, use this code. Paste this into a new PHP script and run it on the same server with your MySQL.

It will provide error messages if it fails to make a connection. Read the error message carefully to help determine the source of the error. Errors at this point are usually related to login credentials. Check to make sure your login credentials are exactly correct including case sensitivity.

Making a good connection to the databse is critical to the next development phase as our scripts will rely on accessing the databse. You can use a version of this script on every script you write that uses a databse connection.

Example

if (!$conn) {
	echo "Failed to connect to MySQL: ".mysqli_connect_error();
} else {
	echo "Connection ok.";
}

mysqli_query()

This function passes a SQL command to a database through a connection.

Format

$result = mysqli_query(connName, query);
// where query is a SQL query and 
// connName is the connection to the database.
// $result is a pointer to the data returned by the query - also called a result set.

To check for errors in a query, check the result set to see if it is false.

if (!$result) {
	die (mysqli_error(connName));
}

To check user permissions, use this code.

$query = "SHOW GRANTS;";
$result = mysqli_query($conn,$query);
if (!$result) {
	die(mysqli_error($conn));
}
while ($row = mysqli_fetch_array($result)) {
	// retrieve data from record
	$permissions = $row[0];
	echo "$permissions<br>";
}

You should get a confirmation of permissions for the user listed in the config.php file. The permissions should grant usage and other privileges similar to this.

GRANT USAGE ON *.* TO `EasterBunny`@`%` IDENTIFIED BY PASSWORD '*FADE61D1C4F63B83AC02D3B29E93B384237CAB14'
GRANT ALL PRIVILEGES ON `easterbunny`.* TO `EasterBunny`@`%`

If your permissions are not set correctly, you can edit them using the phpMyAdmin app. Choose the Privileges button on the menu bar and create new user (if not created) and grant all permissions for database-specific privileges.

mysqli_num_rows()

This function returns the number of rows in the resultset. Can be used to see if there are results to display.

Format

if (mysqli_num_rows($result) > 0) {
	// output data of each row
} else {
	echo "0 results";
}

mysqli_fetch_assoc()

This function retrieves the next record from the result as an associative array.

Format

while ($row = mysqli_fetch_assoc($result)) {
	echo $row['fieldName'];
}
// where $result is a pointer to the result set.
// $row will contain the next record from the result or false if there are no more records.

$row is actually an array of the fields in the record. Use the fieldname as the key: $row['last_name'] would return the value in the last_name column from the row. The index must match the name of the column in MySQL.

Example

Here is an example of a simple php program to display the information from the playlist table.

<?php
// load config.php to connect to the database
include_once 'config.php';

// initialize variables
$pageContent = NULL;

// create the query
$query = "Select * from playlist";

// run the query
$result = mysqli_query($conn,$query);

// check for errors
if (!$result) {
	die(mysqli_error($conn));
}
$pageContent = <<<EOF
<table>
	<tr><th>ID</th><th>Name</th></tr>\n
EOF;
// check for results output the table rows
if (mysqli_num_rows($result)> 0) {
	// loop through results and display
	while ($row = mysqli_fetch_assoc($result)) {
		$playlistID = $row['playlistID'];
		$Name = $row['Name'];
		$pageContent .= <<<EOF
	<tr><td>$playlistID</td><td>$Name</td></tr>\n
EOF;
	}
}
$pageContent .= <<<EOF
</table>\n
EOF;

$pageTitle = "MySQL Connection Test";
include 'template.php';
?>

The Database

The databse table used for the PHP script above is a simple playlist table. The code below can be run in phpMyAdmin to generate the table and data to use for testing the PHP script.

-- Table structure for table `playlist`
--

DROP TABLE IF EXISTS `playlist`;

CREATE TABLE `playlist` (
	`playlistID` int NOT NULL AUTO_INCREMENT,
	`Name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	PRIMARY KEY (`playlistID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `playlist`
--

INSERT INTO `playlist` VALUES (1,'Music'),(2,'Movies'),(3,'TV Shows'),(4,'Audiobooks');

Pulling It All Together

To get the databse working with your scripts, review this article and begin by creating the config.php script that will hold the database connection script. You will need to add your databse login credentials that are specific to your system.

Next, open phpMyAdmin and paste the SQL script above into the SQL form and run the script. The script should run successfully and create the playlist table.

Lastly, create your main PHP page script with the include to the config.php file and code to access the database and display the results of the query in a table layout as shown below.

Sample Table Output

ID	Name
1	Music
2	Movies
3	TV Shows
4	Audiobooks