This article will focus on the object-oriented methods for connections and queries in MySQLi, including code examples to illustrate their use.
Establishing a Connection
To begin using MySQLi in object-oriented PHP, the first step is to establish a connection to the database. This is done using the mysqli class, which is the main class for MySQLi operations.Code Example: Establishing a Connection
<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "mydatabase";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
In the above code:
- $servername, $username, $password, and $dbname are the parameters required to establish a connection.
- The new mysqli function creates a new instance of the mysqli class.
- The connect_error property checks if there is an error in the connection process.
Executing Queries
Once the connection is established, you can execute SQL queries. The mysqli class provides several methods to interact with the database, such as query(), prepare(), bind_param(), and execute().Code Example: Executing a Simple Query
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
$conn->close();
?>
In this example:
- $sql contains the SQL query.
- The query() method executes the SQL query and returns the result.
- num_rows property checks the number of rows in the result set.
- fetch_assoc() method fetches the result row as an associative array.
Prepared Statements
Prepared statements are a feature used to execute the same or similar SQL statements repeatedly with high efficiency. They also help prevent SQL injection attacks by separating SQL logic from data.Code Example: Using Prepared Statements
<?php
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);
// Set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john.doe@example.com";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary.moe@example.com";
$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();
?>
In this example:
- prepare() method prepares an SQL statement for execution.
- bind_param() method binds variables to the prepared statement as parameters.
- execute() method executes the prepared statement.
Handling Errors
Handling errors effectively is crucial in database interactions to ensure smooth execution and to debug issues. MySQLi provides several methods to handle errors, such as error, errno, and sqlstate.Code Example: Handling Errors
<?php
$sql = "SELECT id, firstname, lastname FROM NonExistentTable";
$result = $conn->query($sql);
if (!$result) {
echo "Error: " . $conn->error . "<br>";
echo "Error Number: " . $conn->errno . "<br>";
echo "SQLSTATE: " . $conn->sqlstate . "<br>";
} else {
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
} else {
echo "0 results";
}
}
$conn->close();
?>
In this example:
Using Transactions
Transactions in MySQLi allow you to execute a series of SQL statements as a single unit of work. They ensure that either all the statements are executed successfully, or none of them are executed, maintaining the integrity of the database.
- error property returns the last error description.
- errno property returns the last error number.
- sqlstate property returns the SQLSTATE error code for the last error.
Using Transactions
Transactions in MySQLi allow you to execute a series of SQL statements as a single unit of work. They ensure that either all the statements are executed successfully, or none of them are executed, maintaining the integrity of the database.
Code Example: Using Transactions
<?php
// Start transaction
$conn->begin_transaction();
try {
$conn->query("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', 'john.doe@example.com')");
$conn->query("INSERT INTO MyGuests (firstname, lastname, email) VALUES ('Jane', 'Doe', 'jane.doe@example.com')");
// Commit transaction
$conn->commit();
echo "New records created successfully";
} catch (Exception $e) {
// Rollback transaction if an error occurs
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
$conn->close();
?>
In this example:
- begin_transaction() method starts a new transaction.
- commit() method commits the current transaction.
- rollback() method rolls back the current transaction if an error occurs.
Fetching Results
MySQLi offers multiple methods to fetch results from a query, including fetch_assoc(), fetch_array(), and fetch_object().Code Example: Fetching Results
<?php
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
}
$result->free(); // Free result set
} else {
echo "0 results";
}
$conn->close();
?>
In this example:
- fetch_assoc() method fetches a result row as an associative array.
- free() method frees the memory associated with a result.
Closing Connections
It’s essential to close the database connection once all the database operations are done. This releases the resources used for the connection.Code Example: Closing Connections
<?php
$conn->close();
?>