PHP111

Main Page >> Web Application Development >> Workbook >> PHP and Databases

PHP and Databases – Making the Connection

How to connect

There are 3 ways to connect to a database in PHP:

1. (DEPRECATED) The mysql functions are procedural and use manual escaping.
2. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. We will use this option on this module
3. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.

(source)

The code

<?php

// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR_LOGIN", "YOUR_PASSWORD", "YOUR_DATABASE");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Run SQL query
$res = mysqli_query($mysqli, "SELECT name, species, age FROM pet");

// How many rows were returned?
echo("<p>" . mysqli_num_rows($res) . " record(s) were returned...</p>");

// Loop through resultset and display each field's value
while($row = mysqli_fetch_assoc($res)) {
  echo $row['name']. " - ". $row['species'] ."<br>";
}

?>

See the result here.

The code explained

// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR_LOGIN", "YOUR_PASSWORD", "YOUR_DATABASE");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

The first line of code is very important as it establishes and connection to our database server, using your credentials, and specifies which database we want to use on the server:

• server: "localhost" works fine here, since both PHP and MySQL are installed on the same server (mi-linux)
• login: your MySQL login
• password: your MySQL passord
• databse: the name of YOUR database (usually "db" + your student number, e.g. db0123456)

The if statement deals with errors, i.e. if the connection fails, it provides basic information and error messages (e.g. "Access denied" if your login/password is wrong).

// Run SQL query
$res = mysqli_query($mysqli, "SELECT name, species, age FROM pet");

// How many rows were returned?
echo("<p>" . mysqli_num_rows($res) . " record(s) were returned...</p>");

// Loop through resultset and display each field's value
while($row = mysqli_fetch_assoc($res)) {
  echo $row['name']. " - ". $row['species'] ."<br>";
}

Ready to try some more PHP/MySQL?

A simple example of using a HTML form to capture search requirements and execute a custom query can be found at PHP112 - Simple HTML/PHP/MySQL example