Understanding Basic PDO in PHP - Expert web hosting guides- ExpertWebHost.TOP

PHP PDO (PHP Data Objects) is an easy to understand and use feature in PHP programming. To use PHP PDO in your local php-mysql installation or shared hosting based on CPanel, DirectAdmin or any other control panels, you need to follow these steps:

Establish a Database Connection with your database manager(like mysql, oracle etc):

   $host = "localhost";
   $dbname = "your_database";
   $username = "your_username";
   $password = "your_password";

   try {
       $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
       $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
       echo "Connected successfully";
   } catch (PDOException $e) {
       echo "PDO Connection failed: " . $e->getMessage();
   }

Executing Database Queries using PDO

You can choose to execute your queries using PDO Prepared Statements as follows :

	$stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
	$stmt->bindParam(':id', $id);
	$id = 1;	//sample variable value for testing
	$stmt->execute();
	$result = $stmt->fetch(PDO::FETCH_ASSOC);

Or, you can execute direct queries on your database tables :

	$sql = "SELECT * FROM users WHERE id = 1";
	$result = $conn->query($sql)->fetch(PDO::FETCH_ASSOC);

Fetching query execution results

Getting a single row is easy:

$row = $result->fetch(PDO::FETCH_ASSOC);

A bit more work for fetching multiple rows:

$rows = $result->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $row) {
 // Process each row
}

You can try with multiple parameters also with prepared statements:

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$name = "Jim Carter";
$email = "jim@example.com";
$stmt->execute();

Performing PDO transactions

try
{
	$conn->beginTransaction();
	// Execute multiple queries
	$conn->commit();
}
catch (PDOException $e) 
{
	$conn->rollback();
	echo "Transaction failed: " . $e->getMessage();
}

PDO Error handling is relatively simple

try {
   // Code with PDO queries and operations
} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}

Closing PDO database Connection

$conn = null;

Complete PHP program using PDO

You can find below a complete PHP program that demonstrates the use of all the concepts mentioned earlier, including :

establishing a database connection
executing queries
using prepared statements
handling errors
closing the connection

<?php
$host = "localhost";
$dbname = "your_database";
$username = "your_username";
$password = "your_password";

try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully<br>";

    // Execute a query
    $stmt = $conn->query("SELECT * FROM users");
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Display results
    echo "Users:<br>";
    foreach ($users as $user) {
        echo "ID: " . $user['id'] . ", Name: " . $user['name'] . ", Email: " . $user['email'] . "<br>";
    }
    echo "<br>";

    // Prepare and execute a query with parameters
    $stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':email', $email);

    $name = "Jim Carter";
    $email = "jim@example.com";
    $stmt->execute();

    $name = "Jane Smith";
    $email = "jane@example.com";
    $stmt->execute();

    // Commit the transaction
    $conn->beginTransaction();
    $stmt = $conn->query("UPDATE users SET email = 'updated@example.com' WHERE id = 1");
    $stmt->execute();
    $conn->commit();

    // Close the connection
    $conn = null;
    echo "Connection closed";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Before using above program, create a database and table with mentioned field names.

Also, You need to remember to change following values in your code with appropriate values for your database connection:

localhost
your_database
your_username
your_password

These are the basic concepts of using PHP PDO. You can always refer to the PHP manual for more details on specific PDO methods and their parameters based on your requirements and database operations.