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: (source)

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.

See some working examples for mi-linux here:

• https://mi-linux.wlv.ac.uk/~in9352/db/

The code

Here is all the code you'll need to connect to the server, run an SQL query, and display the data returned:

<?php

// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR_LOGIN", "YOUR_PASSWORD", "YOUR_DATABASE");
if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
} else {
 echo "Connected to the database successfully.";
}


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

// Are there any errors in my SQL statement?
if(!$res) {
  print("MySQL error: " . mysqli_error($mysqli));
  exit;
}

// 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) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
} else {
 echo "Connected to the database successfully.";
}

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

The mysqli_connect() function takes 4 parameters:

• 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 mysqli_connect() function returns a connection object ($mysqli in this case) that we will use again later (see below).

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");

Once connected, the mysqli_query() function is used to RUN queries, and takes the following 2 parameters:

• A connection object, as returned by mysqli_connect() (see above)
• An SQL string

It returns a result-set as an object ($res in this case), i.e. a collection of 1 or more records.

// Are there any errors in my SQL statement?
if(!$res) {
  print("MySQL error: " . mysqli_error($mysqli));
  exit;
}

The above simply display any errors returned from the MySQL server, for example if you have made a mistake in your SQL statement, if you are selecting fields that don't exist etc... very useful for debugging!

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

Once you have obtained a result-set from the database, the first thing you can do is count how many records it contains, using the mysqli_num_rows() function. It takes a valid result-set as a parameter, and returns an int value (i.e. a number).

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

Displaying the number of records can be useful, but what we are really after here is the data itself, i.e. the records. You can fetch ONE record from a result-set using the mysqli_fetch_assoc() function, passing the result-set as a parameter. It returns ONE record from the set, i.e. 1 pet in this case.

If you put the mysqli_fetch_assoc() function in a while loop (as is the case here), it will keep fetching records until you reach the end of the result-set.

Inside the loop, we can access the current record's fields like this:

$row['field_name']

So for example in our case:

$row['name']
$row['species']
$row['age']

In this case, we simply display the name and species on a single line, separated by a - character.

You try it

• Implement the code above
• Amend the code above so that it displays all 3 fields selected (name, species and age)
• Amend the code above so that it displays all 3 fields in an HTML table, like this

Mini task

Let's take a look at this week's mini task.