PHP MySQL
One essential component of creating dynamic web applications using PHP is interacting with databases. For this, PHP is frequently utilised, and many PHP programmers wish to use a database.Relational database systems like MySQL simplify, secure, and speed up online data storage and retrieval compared to flat files. PHP supports MySQL, SQLite, Oracle, PostgreSQL, Sybase, MS-SQL, DB2, ODBC-compliant databases, CouchDB, and MongoDB.
You have to connect before you can work with a database from PHP. After linking, you can query, insert, update, and delete data. PHP offers many database connection and interaction techniques, including MySQL. MySQLi and PHP Data Objects (PDO) for database interaction.
Connecting to a Database using PHP
PHP scripts must first connect to the database server to obtain data. This requires inputting the hostname, username, password, and sometimes the database name you want to connect to immediately.
Using the MySQLi Extension
The simplest PHP library for connecting to MySQL is the MySQLi (Improved MySQL) extension. It facilitates connecting to a MySQL database server and running queries on it. Both an object-oriented and a procedural syntax are available in MySQLi.
The procedural style makes use of functions such as
mysqli_connect() to establish a connection:
<?php
// open connection
$connection = mysqli_connect('localhost', 'user', 'pass', 'db1')
or die ("ERROR: Cannot connect"); //
// [32]
print "connected to " . mysqli_get_host_info($connection) . "\n"; //
// close connection
mysqli_close($connection); //
?>
This example uses the provided username, password, and database name, db1, to connect to the server localhost. A connection link identifier is returned by the mysqli_connect() function. Mysqli_close() can be used to terminate the connection.
You create a new instance of the mysqli class using the object-oriented style:
<?php
// open connection
$mysqli = new mysqli('localhost', 'user', 'pass', 'db1'); //
if (mysqli_connect_errno()) { //
die("ERROR: Cannot connect. " . mysqli_connect_error()); //
}
// [39]
print "connected to " . $mysqli->host_info . "\n"; //
// close connection
$mysqli->close(); //
?>By instantiating the mysqli class and passing in the host, username, password, and database name as constructor inputs, the connection is created in this case. Mysqli_connect_error() and mysqli_connect_errno() can be used to handle errors.
Using PHP Data Objects (PDO)
A lightweight, standardised interface for database access in PHP is offered by PHP Data Objects (PDO), a database access abstraction layer or extension. Its primary benefit is that you may use the same functions to access nearly any database, including MySQL, Oracle, Microsoft SQL Server, PostgreSQL, and SQLite, for which there is a PDO driver. Writing more portable database programming is encouraged by this.
PDO is an extension that is object-oriented. You must instantiate the PDO class in order to connect. A Data Source Name (DSN) string is usually used to convey the connection information.
<?php
$dsn = 'mysql:dbname=database_name;host=localhost'; //
$dbuser = 'database_user'; //
$dbuserpw = 'database_user_password'; //
try { //
$connection = new PDO($dsn, $dbuser, $dbuserpw); //
// set the PDO error mode to exception (recommended)
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //
echo "Connected successfully"; //
} catch (PDOException $e) { //
echo 'There was a problem connecting to the database: ' . $e->getMessage(); //
//
}
//
// To close the connection, you typically unset the PDO object:
unset($connection); //
?>You must supply the DSN, username, and password in order to create the PDO object. Try catch blocks are frequently used in PDO error handling to address PDOException issues, particularly during connection.
Choosing between MySQLi and PDO
Database independence is the primary distinction between PDO and MySQLi. For MySQL and MariaDB, MySQLi was created especially, whereas PDO provides a standardised API for accessing different database systems. MySQLi is an excellent option if your application only uses MariaDB or MySQL. However, PDO is the recommended approach because of its portability if you expect to need to change database systems in the future or if you want to develop code that can readily adapt to many database types. Important security features like prepared statements are supported by both extensions.
Executing SQL Queries from PHP
After establishing a connection, PHP can be used to run SQL instructions. The fundamental procedures entail creating a SQL query string and then sending the query to the database server using the function or method that the database extension (MySQLi or PDO) provides.
MySQLi supports procedural and object-oriented $mysqli->query():
<?php
// Assuming $mysqli is an established MySQLi connection (object-oriented style)
$query = "SELECT * FROM users"; //
$result = $mysqli->query($query); //
if ($result) { //
// Process results (explained in Retrieving Data section)
} else {
echo "Error executing query: " . $mysqli->error; //
}
// Assuming $connection is an established MySQLi connection (procedural style)
$sql_query = "SELECT * FROM products"; //
$rslt = mysqli_query($connection, $sql_query); //
if ($rslt) { //
// Process results (explained in Retrieving Data section)
} else {
die("ERROR: " . mysqli_error($connection)); //
}
?>SELECT, SHOW, DESCRIBE, and EXPLAIN return an object, while INSERT, UPDATE, and DELETE return a Boolean value.
When utilising PDO, you can use prepared statements ($pdo->prepare() followed by $stmt->execute()) or query(). For security reasons, prepared statements are typically advised, particularly when working with user input:
<?php
// Assuming $pdo is an established PDO connection
$sql = "SELECT id, name FROM products"; //
$rslt = $pdo->query($sql) or die("ERROR: " . implode(":", $pdo->errorInfo())); //
// Process results (explained in Retrieving Data section)
// Using a prepared statement (safer for variable input)
$stmt = $pdo->prepare("SELECT name from Products where id = :id"); //
$id = 1; // Example variable
$stmt->bindParam(':id', $id); //
$stmt->execute(); //
$row = $stmt->fetch(); //
// Process results
?>Simple enquiries without variable input can be handled with the $pdo->query() method. Prepared statements entail binding the values, executing, and creating the query structure independently of the values. For results, PDO methods return PDOStatement objects.
Basic CRUD Operations
CRUD, or Create (Insert), Retrieve (Select), Update, and Delete, are the four basic processes that are frequently involved in database transactions. These are equivalent to the verbs INSERT, SELECT, UPDATE, and DELETE in the Data Manipulation Language (DML).
Data Retrieval (SELECT): The SELECT statement is used to retrieve data. Usually, a result set or object is returned following the execution of a SELECT query. The next step is to retrieve individual rows by iterating through the result set.
Using the procedural MySQLi:
<?php
// Assuming $rslt holds the result from mysqli_query()
if (mysqli_num_rows($rslt) > 0) { //
while ($row = mysqli_fetch_array($rslt)) { //
echo $row . " = " . $row[85] . "\n"; //
}
} else {
echo "No records found!"; //
}
mysqli_free_result($rslt); // Free the result set
?>
Using PDO:
<?php
// Assuming $rslt holds the result from $pdo->query()
while ($row = $rslt->fetch()) { //
echo $row . " = " . $row[85] . "\n"; //
}
// Or using prepared statements
$stmt = $pdo->prepare("SELECT name FROM Products WHERE id = :id"); //
$id = 1;
$stmt->bindParam(':id', $id); //
$stmt->execute(); //
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { // Fetch row as associative array
print_r($row); // Print the row
?>There are various fetching modes to obtain results as objects, associative arrays, or numeric arrays.
Inserting Data (INSERT): The INSERT command is used to add new records. The execute function/method usually returns a boolean indicating success or failure, or the number of rows impacted, for INSERT, UPDATE, and DELETE queries. To avoid SQL injection, prepared statements are strongly advised when adding user-provided data.
Using MySQLi (object-oriented):
<?php
// Assuming $mysqli is an established MySQLi connection
$sql = "INSERT INTO products (id, name) VALUES ('5', 'pears')"; //
if ($mysqli->query($sql) === TRUE) { //
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $mysqli->error; //
}
?>Making use of object-oriented MySQLi:
<?php
// Assuming $handle is an established SQLite connection
$sql = "INSERT INTO products (id, name) VALUES ('5', 'pears')"; //
sqlite_query($handle, $sql) or die ("ERROR: " . sqlite_error_string(sqlite_last_error($handle)) . " (query was $sql)"); //
echo "Record inserted successfully";
?>Explicit code examples for UPDATE and DELETE using MySQLi or PDO are less common than INSERT examples. However, the idea remains the same: use the query or prepared statement methods/functions of the selected extension to generate the relevant SQL string (UPDATE table SET column = value WHERE condition or DELETE FROM table WHERE condition) and run it. It is best practice to use prepared statements for UPDATE and DELETE in order to ensure security and dependability, particularly when user input is involved.