☰ ☰

PHP - MySQL databases

1. MySQL database

For large projects where an interactive website is being developed, it is recommended to use a database. In PHP, libraries have been made available for the various database systems.
This way you can use the MySQL functions. A distinction is made here between an object-oriented or a procedural approach. In both cases you use “Mysqli_” functions (the i stands for “improved”).
If you want to use a different database management system you can get the same result with the PDO (PHP Data Objects) functions.
To ensure that the website is protected against "SQL injection", it is best to make "prepared statements". The values of the entry fields are added to the SQL query (or SQL statement) at a later time.

2. Step-by-step plan

To retrieve information from a MySQL database you need to perform the following steps:
1. connect to the MySQL server;
2. select a MySQL database;
3. compose a query string;
4. run the query;
Depending on the type of query (a select query or an action query (insert, update, delete)) you will have to perform different steps.
With a select query you can use the result after the first 4 steps to display the records in a user interface.
With an action query you have to check the result of the query to find out if the action was successful. It is common to display an error message if the return value of the mysqli_query() function is not 1.
After the query you can close the connection to the MySQL server if necessary.

3. Examples

The examples below show 3 different ways to work with a MySQL database.
The first example uses an object of the MySQLi class.
The second example uses the (deprecated) functions of MySQLi.
The third example uses an object of the PDO class. The PDO class (PHP Data Objects) has many methods that can also be used with any other database management system.
The methods of the PDO class for making a prepared statement are important in the battle against SQL injection.

<?php
//MySQLi (object):
$conn = new mysqli($host, $user, $password,$dbname);
if ($conn->connect_errno) {
  echo "Toegang werd geweigerd.<br>"
	. "De combinatie gebruikersnaam en paswoord is (nog) niet bekend.<br>";
}
$sql = "select * from consultatie where consultatienummer=" . $_GET['id'];
if ($result = $conn->query($sql)) {
  $row = $result->fetch_assoc();
}
//MySQLi (procedure):
$conn = @mysqli_connect($host, $user, $password,$dbname) or die (
	"Toegang werd geweigerd.<br>"
	. "De combinatie gebruikersnaam en paswoord is (nog) niet bekend.<br>"
	);
$result = @mysqli_select_db($conn,$dbname) or die (
	"Database selectie mislukt !<br>"
	);
if ($conn == true) {
	$sql = "select * from patient where dossiernummer=" . $id;
	if ($result = @mysqli_query($conn,$sql)) {
		if (mysqli_num_rows($result) > 0) {
			$row = mysqli_fetch_assoc($result);
		}
	}
}

//PDO (object):
try {
$conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Toegang werd geweigerd.<br>"
. "De combinatie gebruikersnaam en paswoord is (nog) niet bekend.<br>";
}	
$sql = "INSERT INTO mutualiteit(naam, adres, gemeente, telefoonnummer, contactpersoon)"
	. " VALUES (:naam,:adres,:gemeente,:telefoonnummer,:contactpersoon)";
$result = $conn->prepare($sql);
$result->execute(array(':naam'=>$this->naam,
			  ':adres'=>$this->adres,
			  ':gemeente'=>$this->gemeente,
			  ':telefoonnummer'=>$this->telefoonnummer,
			  ':contactpersoon'=>$this->contactpersoon));
if ($result->rowCount() > 0) {
	return $result;
} else {
	return 0;
}

4. Additional Information

You can find more information on the websites below.
Create database and create table examples:
Create
Insert, update, delete examples (prepared statements!):
Insert Update Delete
prepared statements:
Prepared
In the PHP Mysql part of:
Coursesweb
In the PHP documentation of:
Docs
=> Choose English chm file. You must unblock the file in the properties of the file!
In the help texts (PHP documentation) you can find the Mysqli functions under:
- Function reference >> database extensions >> vendor specific database extensions >> MySQL
The PDO functions can be found in the help texts under:
- Function reference >> database extensions >> abstraction layers >> PDO

Pages

Paragraphs