Curriculum

PHP Basic

PHP Intro

0/1

PHP Install

0/1

PHP Syntax

0/1

PHP Comments

0/2

PHP Variables

0/4

PHP echo / print

0/3

PHP Data Types

0/7

PHP Strings

0/5

PHP Numbers

0/2

PHP Casting

0/1

PHP Math

0/1

PHP Constants

0/1

PHP Magic Constants

0/1

PHP Operators

0/1

PHP if...Else...Elseif

0/5

PHP switch

0/1

PHP Loops

0/7

PHP Functions

0/6

PHP Arrays

0/11

PHP Superglobals

0/7

PHP RegEx

0/1

PHP Form

0/9

PHP Advanced

0/15

PHP OOP

0/14

MySQL Database

0/14

PHP XML

0/5

PHP Reference

0/276

Text lesson

MySQL onnect

PHP Connect to MySQL

PHP 5 and later versions can interact with a MySQL database using:

The MySQLi extension (where “i” stands for improved)
PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension, which was deprecated in 2012.

Should I Use MySQLi or PDO?

For a brief answer, it’s “Whatever you prefer.”

Both MySQLi and PDO have their strengths:

PDO supports 12 different database systems, making it easier to switch databases. You only need to update the connection string and adjust a few queries. In contrast, MySQLi is limited to MySQL databases, and switching to another database would require rewriting the entire code, including queries.
Both PDO and MySQLi are object-oriented, but MySQLi also provides a procedural API.
Both support Prepared Statements, which are crucial for protecting against SQL injection and ensuring web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this chapter and the following ones, we will demonstrate three methods for working with PHP and MySQL:

MySQLi (object-oriented)
MySQLi (procedural)
PDO

MySQLi Installation

For both Linux and Windows: The MySQLi extension is usually installed automatically when you install the php5-mysql package.

For installation details, visit: http://php.net/manual/en/mysqli.installation.php

PDO Installation

For installation details, visit: http://php.net/manual/en/pdo.installation.php

Open a Connection to MySQL

Before accessing data in the MySQL database, we must first establish a connection to the server.

Example (MySQLi Object-Oriented)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
die(“Connection failed: “ . $conn->connect_error);
}
echo “Connected successfully”;
?>

Note on the object-oriented example above:

The $connect_error variable was unreliable before PHP 5.2.9 and 5.3.0. For compatibility with PHP versions earlier than 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) {
  die("Database connection failed: " . mysqli_connect_error());
}

Example (MySQLi Procedural)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
die(“Connection failed: “ . mysqli_connect_error());
}
echo “Connected successfully”;
?>

Example (PDO)

<?php
$servername = “localhost”;
$username = “username”;
$password = “password”;

try {
  $conn = new PDO(“mysql:host=$servername;dbname=myDB”, $username, $password);
  // set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo “Connected successfully”;
} catch(PDOException $e) {
  echo “Connection failed: “ . $e->getMessage();
}
?>

Note: In the PDO example above, we also specified a database (myDB). PDO requires a valid database to connect to; if no database is specified, an exception will be thrown.

Tip: One of the key advantages of PDO is its exception handling capability. It includes an exception class to manage issues that arise during database queries. If an exception occurs within the try { } block, the script halts and immediately transfers control to the first catch { } block.

Close the Connection

The connection will close automatically when the script ends. To close it earlier, use the following:

MySQLi Object-Oriented:

$conn->close();

MySQLi Procedural:

mysqli_close($conn);

PDO:

$conn = null;