Tag: Database

I write my own PHP framework and I want to respect the SOLID principles.

I made this interface:

    & # 39; test & # 39;]* /
public function insertRecords (string $ table, array $ data);

/ **
* Update records in a table
* @param string $ table Name of the table
* @param array $ changes Table with the fields and values ​​of the table - Ex: ['name' => 'test']
     * @param array $ conditions Requirements to run it Ex: ['id' => 1]
     * /
public function updateRecords (string $ table, array $ changes, array $ conditions);

/ **
* Delete records in a table
* @param string $ table Name of the table
* @param string $ conditions Requirements to run it - Ex: "id =: id"
* @param array $ params Parameters to replace in conditions
* @return int Affected line
* /
public function deleteRecords (string $ table, string $ conditions, array $ params = []): int;

/ **
* Returns the last ID inserted
* @return int ID
* /
public function lastInsertId (): int;

/ **
* Close the connection
* /
public function closeConnection ();

}
?>

Implemented by this class:

get (& # 39; base_server). & # 39 ;; port = & # 39; $ config-> get (& # 39; base_port & # 39;). & # 39 ;; basename = & # 39 ;. $ config-> get (& # 39; base_name & # 39;);

try{
$ this-> pdo = new PDO (
$ connectionString,
$ config-> get (& # 39; db_username & # 39;),
$ config-> get (& # 39; db_password & # 39;)
)
# We can now save all exceptions in case of fatal error.
$ this-> pdo-> setAttribute (PDO :: ATTR_ERRMODE, PDO :: ERRMODE_EXCEPTION);

# Disable the emulation of prepared instructions, rather use REAL prepared statements.
$ this-> pdo-> setAttribute (PDO :: ATTR_EMULATE_PREPARES, true);

$ this-> connected = true;
return true;

// Error handling
} catch (PDOException $ e) {
throw new  Exception ("Failed to connect to database:". $ e-> getMessage (), 1);
}
}

public function prepare (string $ sql, array $ params = [])
{
$ this-> stmt = $ this-> pdo-> prepare ($ sql);
if (! empty ($ params))
{
$ this-> bindParams ($ params);
}
}

/ **
* Link the param value to the prepared SQL query
* @param string $ param
* @param $ value
* @param $ type
* /
private function bind (string $ param, $ value, $ type = null)
{
if (is_null ($ type))
{
switch (TRUE) {
case is_int ($ value):
$ type = PDO :: PARAM_INT;
Pause;
case is_bool ($ value):
$ type = PDO :: PARAM_BOOL;
Pause;
case is_null ($ value):
$ type = PDO :: PARAM_NULL;
Pause;
default:
$ type = PDO :: PARAM_STR;
}
$ this-> stmt-> bindValue (& # 39 ;: $ param, $ value, $ type);
}
}

/ **
* Link a group of parameters
* @param array $ params Array with parameters and values ​​Ex: ['name' => 'test']
     * @param string $ prefix Prefix to add to the parameter name
* /
private function bindParams (array $ parameters, string $ prefix = & # 39;)
{
foreach ($ params as $ key => $ value) {
$ this-> bind ($ prefix. $ key, $ value);
}
}

/ **
* Eseque the query preparata
* /
public function execute () {
returns $ this-> stmt-> execute ();
}

public service results ()
{
$ mode = PDO :: FETCH_ASSOC;
$ this-> execute ();
$ this-> stmt-> fetchAll ($ mode);
}

public service single ()
{
$ mode = PDO :: FETCH_ASSOC;
$ this-> execute ();
$ this-> stmt-> fetch ($ mode);
}

public function rowCount (): int
{
return $ this-> stmt-> rowCount ();
}

/ **
* Elimina database record dal. Es: (users, where id =: id, ['id' => 1])
* @param string tabella
* @param string $ conditions campi e condizione
* @param array $ params valori delle condizioni
* @return int affected lines
* /
public function deleteRecords (string $ table, string $ conditions, array $ params = []): int
{
$ delete = "DELETE FROM {$ table} WHERE {$ conditions}";

$ this-> prepare = $ delete;
if (! empty ($ params))
{
$ this-> bindParams ($ params);
}
$ this-> execute ();

return $ this-> rowCount ();
}

/ **
* Aggiorna a record of the database
* @param string $ table
* @param array $ changes with the modifiche [field => value]
     * @param array $ conditions condizioni [id => 1]
     * /
public function updateRecords (string $ table, array $ changes, array $ conditions)
{
$ changesStr = & # 39;
$ whereStr = & # 39;
$ cond_array = [];

foreach ($ change as $ field => $ value) {
$ changesStr. = "{$ field} =: param _ {$ field},";
}
// rimuovo the ultiam, in eccesso
$ changesStr = substr ($ changesStr, 0, -1);

foreach ($ conditions as $ condition => $ value) {
$ cond_array[] = "{$ condition} =: where _ {$ condition}";
}
$ whereStr = implode (& # 39 ;, $ cond_array);

$ this-> prepare ("UPDATE {$ table} SET {$ changesStr} WHERE {$ whereStr}");

// uso i prefissi per evitare sovrapposizioni tra parametri e condizioni
$ this-> bindParams ($ changes, __parameters);
$ this-> bindParams ($ conditions, where _ & # 39;);

$ this-> execute ();
}

/ **
* Inserisce record nel database
* @param string $ table tabella
* @param array $ data dati da inserire field => value
* @return bool
* /
public function insertRecords ($ table, $ data)
{
$ fieldStr = & # 39 ;;
$ valuesStr = & # 39;

// genero the request
foreach ($ data as $ f => $ v) {
$ fieldStr. = $ f;
$ valuesStr. = ": {$ f}";
}

// rimuovo la, in eccesso
$ fieldStr = substr ($ fieldsStr, 0, -1);
// rimuovo la, in eccesso
$ valuesStr = substr ($ valuesStr, 0, -1);

$ this-> prepare ("INSERT INTO {$ table} {{fieldsStr}) VALUES ({$ valuesStr})");
$ this-> bindParams ($ data);
$ this-> execute ();
return true;
}

// The clone of the magic method is empty to avoid the duplication of the connection
private function __clone () {
returns false;
}
private function __wakeup () {
returns false;
}

public function lastInsertId (): int {
return $ this-> pdo-> lastInsertId ();
}

public service closeConnection () {
$ this-> pdo = null;
}

// Get the connection
public function getConnection () {
returns $ this-> pdo;
}
}

?>

Is this correct for SOLID principles? Insert the insertRecords, updateRecords, and deleteRecords methods here, or is it better to insert them into another class like DataMapper?