Loading, please wait...

ARTDESIGN FRAMEWORK

DATABASE

Description

ArtDesign FrameWork DataBase - Database Management
Prepared Statements (prevent SQL injection)
DataBase - Select, Insert, Update, Delete

Implementation

//01. SetUp DataBase Settings - /Path/To/ArtDesign/PHP/Settings/DataBase.php
$ConfigurationData = array(
    "SQLDefault" => array(
        "SQLType" => "mysql",
        "SQLHost" => "localhost",
        "SQLDataBase" => "data_base_name",
        "SQLUser" => "mysql_user",
        "SQLPassword" => "mysql_password",
        "SQLPDO" => array(
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'",
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    )
);
return $ConfigurationData;

//02. SetUp DataBase test1 table
CREATE TABLE IF NOT EXISTS `test1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(100) NOT NULL,
  `Family` char(100) NOT NULL,
  `Age` smallint(3) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

//03. Run DataBase Class
$ArtDesign = \ArtDesign\ArtDesign::GetInstance();
$DataBase = \ArtDesign\Engine\DataBase\SQL::GetInstance("SQLDefault");

Select

$ArtDesign = \ArtDesign\ArtDesign::GetInstance();
$DataBase = \ArtDesign\Engine\DataBase\SQL::GetInstance("SQLDefault");

$SQLQuery =
"SELECT *
FROM test1
WHERE Age > 18
AND Age < 60
ORDER BY ID DESC
LIMIT 3";

$AffectedRows = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLGetAffectedRows();
//return rowCount()

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchAllAssoc();
//return fetchAll(\PDO::FETCH_ASSOC)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchRowAssoc();
//return fetch(\PDO::FETCH_ASSOC)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchAllNum();
//return fetchAll(\PDO::FETCH_NUM)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchRowNum();
//return fetch(\PDO::FETCH_NUM)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchAllObj();
//return fetchAll(\PDO::FETCH_OBJ)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchRowObj();
//return fetch(\PDO::FETCH_OBJ)

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchAllColumn($SQLColumn);
//return fetchAll(\PDO::FETCH_COLUMN, $SQLColumn);

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchRowColumn($SQLColumn);
//return fetchAll(\PDO::FETCH_BOUND, $SQLColumn);

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchAllClass($SQLClass);
//return fetchAll(\PDO::FETCH_CLASS, $SQLClass);

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLFetchRowClass($SQLClass);
//return fetchAll(\PDO::FETCH_BOUND, $SQLClass);

$DBScript = $DataBase->SQLPrepare($SQLQuery)->SQLExecute()->SQLGetSTMT();
//return query

Insert

$ArtDesign = \ArtDesign\ArtDesign::GetInstance();
$DataBase = \ArtDesign\Engine\DataBase\SQL::GetInstance("SQLDefault");
$Input = $ArtDesign->GetInput();

$DataBase->SQLPrepare(
"INSERT INTO test1
(Name, Family, Age)
VALUES(?, ?, ?)",
    array(
        $Input->Post("name", "string|xss|trim", "N/A"),
        $Input->Post("family", "string|xss|trim", "N/A"),
        $Input->Post("age", "number|xss|trim", 0),
    )
)->SQLExecute();

Update

$ArtDesign = \ArtDesign\ArtDesign::GetInstance();
$DataBase = \ArtDesign\Engine\DataBase\SQL::GetInstance("SQLDefault");
$Input = $ArtDesign->GetInput();

$DataBase->SQLPrepare(
"UPDATE test1
SET
    Name = ?,
    Family = ?,
    Age = ?
WHERE
    ID = ?",
    array(
        $Input->Post("name", "string|xss|trim", "N/A"),
        $Input->Post("family", "string|xss|trim", "N/A"),
        $Input->Post("age", "number|xss|trim", 0),
        $Input->Post("update-id", "number|xss|trim"),
    )
)->SQLExecute();

Delete

$ArtDesign = \ArtDesign\ArtDesign::GetInstance();
$DataBase = \ArtDesign\Engine\DataBase\SQL::GetInstance("SQLDefault");
$Input = $ArtDesign->GetInput();

$DataBase->SQLPrepare(
"DELETE FROM test1
WHERE
    ID = ?",
    array(
        $Input->Post("delete-id", "number|xss|trim")
    )
)->SQLExecute();