CRUD stands for Create Read Update Delete.
The PDO version of the CRUD operations
First, you need to initiate the DB connection:
<?php
try
{
$DBcon = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
$DBcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo 'ERROR : '.$e->getMessage();
die('Something went wrong.');
}
And then use it! View MySQL CRUD operation for details.
<?php
/**
* Class PDO_Actions
* A class for performing common PDO database actions.
*/
class PDO_Actions {
/**
* @var PDO $DBcon The PDO instance used for database operations.
*/
private $DBcon;
/**
* Constructor method that initializes the PDO_Actions object with a PDO instance.
*
* @param PDO $DBcon The PDO instance to be used for database operations.
*/
public function __construct(PDO $DBcon) {
$this->DBcon = $DBcon;
}
/**
* Inserts records into the specified database table.
*
* @param string $t_table Name of the target database table.
* @param array $t_records Associative array of column-value pairs to be inserted.
*/
function insert_2_db($t_table, $t_records)
{
$count = 0;
$yet_another_array = array();
foreach ($t_records as $key => $val)
{
if ($count === 0) {
$t_db_fields = $key;
$t_db_fields_with_colon = ':'.$key;
if ($val === '') $t_values = "''"; else $t_values = "'" . $val . "'";
}
else {
$t_db_fields .= ", " . $key;
$t_db_fields_with_colon .= ', ' . ':'.$key;
if ($val === '') $t_values .= ", ''"; else $t_values .= ", '" . $val . "'";
}
$count++;
$yet_another_array[':'.$key] = $val;
}
$sql = "INSERT INTO " . $t_table . " (" . $t_db_fields . ") VALUES (" . $t_db_fields_with_colon . ")";
$pdo_statement = $this->DBcon->prepare( $sql );
$pdo_statement->execute( $yet_another_array );
}
/**
* Retrieves and returns a single value from the database based on the specified conditions.
*
* @param string $co Column name from which to retrieve the value.
* @param string $t_table Name of the target database table.
* @param string $t_condition (Optional) Additional conditions for the SQL WHERE clause.
* @param string $ekstra (Optional) Additional query options.
* @return mixed The retrieved value.
*/
function print_from_db($co, $t_table, $t_condition='', $ekstra='')
{
$q = $this->select_from_db($co, $t_table, $t_condition, $ekstra);
foreach($q as $row) {
return $row[$co];
}
}
/**
* Retrieves and returns selected data from the database based on the specified conditions.
*
* @param string $co Column name(s) to be selected.
* @param string $t_table Name of the target database table.
* @param string $t_condition (Optional) Additional conditions for the SQL WHERE clause.
* @param string $ekstra (Optional) Additional query options.
* @return array An array of associative arrays representing the selected rows.
*/
function select_from_db($co, $t_table, $t_condition='', $ekstra='')
{
$t_condition = ($t_condition !== '') ? ' WHERE ' . $t_condition : '';
$ekstra = ($ekstra !== '') ? ' ' . $ekstra : '';
$pdo_statement = $this->DBcon->prepare("SELECT " . $co . " FROM " . $t_table . $t_condition . $ekstra);
$pdo_statement->execute();
return $pdo_statement->fetchAll();
}
/**
* Updates records in the specified database table based on the given conditions.
*
* @param string $t_table Name of the target database table.
* @param array $t_records Associative array of column-value pairs to be updated.
* @param string $t_condition Conditions for the SQL WHERE clause.
*/
function update_db($t_table, $t_records, $t_condition)
{
$count = 0;
foreach ($t_records as $key => $val)
{
if ($count === 0) {
$t_db_fields = $key . " = '" . $val . "'";
}
else {
$t_db_fields .= ", " . $key . " = '" . $val . "'";
}
$count++;
}
$pdo_statement = $this->DBcon->prepare("UPDATE " . $t_table . " SET " . $t_db_fields . " WHERE " . $t_condition . "");
$pdo_statement->execute();
}
/**
* Deletes records from the specified database table based on the given conditions.
*
* @param string $t_table Name of the target database table.
* @param string $t_condition Conditions for the SQL WHERE clause.
*/
function delete_from_db($t_table, $t_condition)
{
$pdo_statement = $this->DBcon->prepare("DELETE from " . $t_table . " WHERE " . $t_condition . "");
$pdo_statement->execute();
}
}
Somewhere in your code you need to activate the class:
<?php
$action = new PDO_Actions($DBcon);
Example usage:
<?php
$q = $action->select_from_db('*', 'table', 'column = ' . $variable );
And that's all!