This has nothing to do with Frameworks because it is an external project, I am currently developing to learn, some things about database management or how databases behave...
I need help with some php and PDO... Previously I dev this script:
<?php
class DataBaseManager
{
public function GetData($query, $user, $pass)
{
try {
$db_result = [];
$conn = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("set names utf8");
reset($query);
$db_name = key($query);
$conn->exec('USE ' . $db_name);
$db_result['r'] = $conn->query($query[$db_name], PDO::FETCH_ASSOC);
$count = $db_result['r']->rowCount();
$db_result['c'] = $count;
if (0 == $count) {
$db_result['r'] = null;
} elseif (1 == $count) {
$db_result['r'] = $db_result['r']->fetch();
}
return $db_result;
} catch (PDOException $e) {
echo $e->getMessage();
return false;
}
}
public function DeleteData($query, $user, $pass)
{
try {
$conn = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
$conn->exec("set names utf8");
foreach ($query as $db_name => $query_arr) {
$conn->exec('USE ' . $db_name);
foreach ($query_arr as $key => $query_string) {
$conn->exec($query_string);
++$ct;
}
}
$conn->commit();
$conn = null;
return '<b>' . $ct . ' Records Deleted Successfully.</b>';
} catch (PDOException $e) {
$conn->rollback();
echo $e->getMessage();
return false;
}
}
public function SetData($query, $user, $pass)
{
try {
$db_result = [];
$conn = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
$conn->exec("set names utf8");
$count = ['u' => 0, 'i' => 0];
foreach ($query as $db_name => $query_arr) {
$conn->exec('USE ' . $db_name);
foreach ($query_arr as $key => $query_string) {
$cq = $conn->exec($query_string);
if (strpos($query_string, 'UPDATE') !== false) {
$count['u'] += $cq;
}
if (strpos($query_string, 'INSERT') !== false) {
$count['i'] += $cq;
}
}
}
$conn->commit();
$db_result['r'] = true;
$db_result['t'] = 'Updates: ' . $count['u'] . ', Inserts: ' . $count['i'];
return $db_result;
} catch (PDOException $e) {
$conn->rollback();
echo $e->getMessage();
return false;
}
}
}
I would like to be able to insert data by volumes in multiple tables in a single commit...
The idea of doing it that way is because if something fails I want it to be automatically rolled back in all table instances...
So I have a data structure in an array with the following content in my new class:
$dbquery =[
'test'=>[
'0' =>[
0 => 'INSERT INTO table_name_1(column1,column2) VALUES (?,?)', //mean it is the query
1 =>[value11,value12], // mean it is a row
2 =>[value21,value22], // mean it is a row
],
'1' =>[
0 => 'INSERT INTO table_name_2(column1,column2,column3,column4) VALUES (?,?,?,?)', //mean it is the query
1 =>[value11,value12,value13,value14], // mean it is a row
2 =>[value21,value22,value23,value24], // mean it is a row
],
],
];
but i have my first try with bindValue, this is my new class mentioned:
<?php
class DataBase
{
private static ?DataBase $instance = null;
public static function getInstance(): DataBase
{
if (!self::$instance instanceof self) {
self::$instance = new self();
}
return self::$instance;
}
private array $config;
public function __construct()
{
$this->config = ['DB_HOST'=>'test','DB_NAME'=>'test','DB_USER'=>'test','DB_PASSWORD'=>''];
$this->setConnection(new PDO("mysql:host=" . $this->config['DB_HOST'] . ";dbname=" . $this->config['DB_NAME'], $this->config['DB_USER'], $this->config['DB_PASSWORD']));
}
private PDO $connection;
/**
* @return PDO
*/
private function getConnection(): PDO
{
return $this->connection;
}
/**
* @param PDO $connection
*/
private function setConnection(PDO $connection): void
{
$this->connection = $connection;
}
public function changeConnectionServer(string $host, string $db_name, string $user, string $password): void
{
$this->setConnection(new PDO("mysql:host=" . $host . ";dbname=" . $db_name, $user, $password));
}
private array $query;
public function setDataBaseTarget(string $db_name)
{
if (empty($this->query)) {
$this->query = [];
}
$this->query[$db_name] = [];
}
public function buildQuery(string $query)
{
if (empty($this->query)) {
$this->query = [];
$this->query[$this->config['DB_NAME']] = [];
}
$target = array_key_last($this->query);
$this->query[$target][] = [$query];
}
public function addQueryData($data)
{
$target = array_key_last($this->query);
$key = array_key_last($this->query[$target]);
$this->query[$target][$key][] = $data;
}
private function getQuery(): array
{
return $this->query;
}
/**
* @throws Exception
*/
public function setData(): array
{
try {
$time = -microtime(true);
$con = $this->getConnection();
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->beginTransaction();
$con->exec("set names utf8;");
foreach ($this->getQuery() as $db_name => $query_arr) {
$con->exec('USE `' . $db_name . '`;');
$ct = 0;
// on this section have proble with code and logic .... i dont know what i need to dev to insert the data
foreach ($query_arr as $query_structure) {
foreach ($query_structure as $key => $raw) {
if ($key === 0) {
$ct++;
$stmt[$ct] = $con->prepare($raw);
} else {
if (is_array($raw)) {
$c = 0;
foreach ($raw as $value) {
$c++;
$stmt[$ct]->bindValue($c, $value, $this->getParamType($value));
}
}
}
}
$stmt[$ct]->execute();
}
//end section
}
//$con->commit();
return true;
} catch (PDOException $e) {
$con->rollback();
echo $e->getMessage();
return false;
}
}
private function getParamType($value)
{
if (is_int($value)) {
return PDO::PARAM_INT;
} elseif (is_bool($value)) {
return PDO::PARAM_BOOL;
} elseif (is_null($value)) {
return PDO::PARAM_NULL;
} elseif (is_string($value)) {
return PDO::PARAM_STR;
} else {
return false;
}
}
}
$db_handler = DataBase::getInstance();
$db_handler->buildQuery("INSERT INTO `client_list`(`email`,`mobile`) VALUES ('?','?');");
$db_handler->addQueryData(['mail1@test.com', '35634634636546']);
$db_handler->addQueryData(['mail2@test.com', '35634634636546']);
$db_handler->addQueryData(['mail3@test.com', '35634634636546']);
$db_handler->setData();
I can't figure out, develop the part that allows me to package everything in a single transaction... what I have is a stm[] ...
Can someone help me with this development?
I can't make heads or tails of all the things your class is doing, but here's a generalized version according to the bits that seem obvious:
public function runMultipleQueries() {
$dbh = $this->getConnection();
// ... setup stuff
$dbh->beginTransaction();
try {
foreach($this->queries as $query) {
$stmt->prepare($query->queryString);
$param_id = 1;
foreach($query->params as $param) {
$stmt->bindValue($param_id++, $param);
}
$stmt->execute();
}
} catch( \Exception $e ) {
$dbh->rollback();
throw $e;
// IMO if you cannot fully/meaningfully recover, just re-throw and let it kill execution or be caught elsewhere where it can be
// otherwise you're likely having to maintain a stack of if(foo() == false) { ... } wrappers
}
$dbh->commit();
}
Additionally, singleton DB classes have the drawbacks of both being limiting if you ever need a second DB handle, as well as boiling down to being global state, and subject to the same "spooky action at a distance" problems.
Consider using dependency inversion and feeding class dependencies in via constructor arguments, method dependencies via method arguments, etc. Eg:
interface MyWrapperInterface {
function setQueries(array $queries);
function runQueries();
}
class MyDbWrapper implements MyWrapperInterface {
protected $dbh;
public function __construct(\PDO $dbh) {
$this->dbh = $dbh;
}
public function setQueries(array $queries) { /* ... */ }
public function runQueries() { /* ... */ }
}
class MyOtherThing {
protected $db;
public function __construct( MyWrapperInterface $db ) {
$this->db = $db;
}
// ...
}
$wrapper1 = new MyDbWrapper(new PDO($connstr1, $options));
$wrapper2 = new MyDbWrapper(new PDO($connstr2, $options));
$thing1 = new MyOtherThing($wrapper1);
$thing2 = new MyOtherThing($wrapper2);
$thing1_2 = new MyOtherThing($wrapper1);
// etc