PHP
PDO Connection
<?php
// Database configuration
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_database_username';
$password = 'your_database_password';
try {
// Create a new PDO instance
$pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
// Set PDO to throw exceptions on error
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Optionally, set character encoding to UTF-8
$pdo->exec("set names utf8");
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>
OOP Connection
<?php
class Database {
private $host = 'localhost';
private $dbname = 'your_database_name';
private $username = 'your_database_username';
private $password = 'your_database_password';
private $pdo;
public function __construct() {
try {
$this->pdo = new PDO("mysql:host=$this->host;dbname=$this->dbname", $this->username, $this->password);
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->pdo->exec("set names utf8");
} catch(PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
}
public function getConnection() {
return $this->pdo;
}
}
?>
insert data
<?php
require_once 'Database.php'; // Include the database class file
class DatabaseOperations extends Database {
public function insertData($tableName, $data) {
try {
// Prepare the SQL statement
$keys = implode(', ', array_keys($data));
$values = ':' . implode(', :', array_keys($data));
$sql = "INSERT INTO $tableName ($keys) VALUES ($values)";
$stmt = $this->getConnection()->prepare($sql);
// Bind parameters
foreach ($data as $key => $value) {
$stmt->bindValue(':' . $key, $value);
}
// Execute the statement
$stmt->execute();
return true; // Insert successful
} catch(PDOException $e) {
return false; // Insert failed
}
}
}
// Usage example
$databaseOps = new DatabaseOperations();
// Data to be inserted
$data = array(
'name' => 'John Doe',
'email' => 'john@example.com',
'age' => 30
);
// Insert data into a table called 'users'
$tableName = 'users';
if ($databaseOps->insertData($tableName, $data)) {
echo 'Data inserted successfully.';
} else {
echo 'Failed to insert data.';
}
?>
Insert data with HTML Form
form.html
<!DOCTYPE html>
<html>
<head>
<title>Insert Data</title>
</head>
<body>
<h2>Insert Data</h2>
<form action="insert_data.php" method="post">
<label for="name">Name:</label><br>
<input type="text" id="name" name="name"><br>
<label for="email">Email:</label><br>
<input type="email" id="email" name="email"><br>
<label for="age">Age:</label><br>
<input type="number" id="age" name="age"><br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
Insert_data.php
<?php
require_once 'DatabaseOperations.php'; // Include the DatabaseOperations class file
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Get form data
$name = $_POST['name'];
$email = $_POST['email'];
$age = $_POST['age'];
// Data to be inserted
$data = array(
'name' => $name,
'email' => $email,
'age' => $age
);
// Insert data into the 'users' table
$tableName = 'users';
$databaseOps = new DatabaseOperations();
if ($databaseOps->insertData($tableName, $data)) {
echo 'Data inserted successfully.';
} else {
echo 'Failed to insert data.';
}
}
?>
Select data from table
<?php
require_once 'Database.php'; // Include the Database class file
class DatabaseOperations extends Database {
public function selectData($tableName) {
try {
// Prepare and execute the SQL query
$stmt = $this->getConnection()->query("SELECT * FROM $tableName");
// Check if there are any results
if ($stmt->rowCount() > 0) {
// Fetch data using a while loop
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Output or process each row of data
echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . "<br>";
}
} else {
echo "No records found";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
}
// Usage example
$databaseOps = new DatabaseOperations();
// Select data from a table called 'users'
$tableName = 'users';
$databaseOps->selectData($tableName);
?>
Select Data and Display on Table
<?php
require_once 'Database.php'; // Include the Database class file
class DatabaseOperations extends Database {
public function selectData($tableName) {
try {
// Prepare and execute the SQL query
$stmt = $this->getConnection()->query("SELECT * FROM $tableName");
// Check if there are any results
if ($stmt->rowCount() > 0) {
// Output table headers
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";
// Fetch data using a while loop and output table rows
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
// Close table
echo "</table>";
} else {
echo "No records found";
}
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
}
// Usage example
$databaseOps = new DatabaseOperations();
// Select data from a table called 'users' and display in table format
$tableName = 'users';
$databaseOps->selectData($tableName);
?>
Update Data
<?php
require_once 'Database.php'; // Include the Database class file
class DatabaseOperations extends Database {
public function updateData($tableName, $id, $data) {
try {
// Generate SET clause for updating columns
$setClause = '';
foreach ($data as $key => $value) {
$setClause .= "$key = :$key, ";
}
$setClause = rtrim($setClause, ', '); // Remove the trailing comma
// Prepare the SQL statement
$sql = "UPDATE $tableName SET $setClause WHERE id = :id";
$stmt = $this->getConnection()->prepare($sql);
// Bind parameters
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
foreach ($data as $key => $value) {
$stmt->bindValue(":$key", $value);
}
// Execute the statement
$stmt->execute();
return true; // Update successful
} catch(PDOException $e) {
return false; // Update failed
}
}
}
// Usage example
$databaseOps = new DatabaseOperations();
// Data to be updated
$id = 1;
$data = array(
'name' => 'Updated Name',
'email' => 'updated@example.com'
);
// Update data in the 'users' table
$tableName = 'users';
if ($databaseOps->updateData($tableName, $id, $data)) {
echo 'Data updated successfully.';
} else {
echo 'Failed to update data.';
}
?>
Procedure Connection