PHP Classes

Rasher PHP Data Access Layer Helper - Database Repository: Run queries to access data in any type of database

Recommend this page to a friend!
  Info   Example   Screenshots   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 58 All time: 10,535 This week: 455Up
Version License PHP version Categories
data_access_helper 2.41MIT/X Consortium ...8PHP 5, Databases, Data types, Design ...
Description 

Author

This package provides several classes that can connect to many kinds of databases and executes SQL queries to perform any type of data access (MySQLi, PDO) to database tables.

Currently, it can:

- Connect to a database server via MySQLi or PDO extensions

- Execute prepared queries and get the results as an array or by result column name

- Access a database like a repository with functions to access repository records by performing operations to create, read, update, and delete repository items

- Manipulate database record column values according to the respective data type

- DT_ITEM, DT_LIST complex attribute types and searching abilities

- Transaction handling

- Caching the database query results

- Comparison operators to define filtering conditions

Picture of Hernádi Tamás
  Performance   Level  
Name: Hernádi Tamás <contact>
Classes: 1 package by
Country: Hungary Hungary
Age: 44
All time rank: 448140 in Hungary Hungary
Week rank: 164 Up3 in Hungary Hungary Up

Example

<?php
namespace Rasher\Test;
use
Rasher\Data\PDO\DataManagement\{ConnectionData}; //PDO extension
//use Rasher\Data\MySQLi\DataManagement\{ConnectionData}; //MySQLi extension
use Rasher\Data\UserManagement\{DbUserRoleSettingRepository,DbUserRoleRepository,DbUserRepository};
use
Rasher\Data\Type\{LogicalOperator,Param,FilterParam,Operator,ItemAttribute};
use
Rasher\Common\{Common};

include_once
__DIR__."/user_data_repository.php";

class
LoginTest
{
    public
$dbUserRepository = null;
    public
$userLogin = null;

    public function
__construct($dbUserRepository)
    {
       
$this->dbUserRepository = $dbUserRepository;
    }

    public function
deleteUserData()
    {
        try
        {
           
$this->dbUserRepository->dbUserRoleRepository->beginTransaction();
           
$this->dbUserRepository->beginTransaction();

           
$this->dbUserRepository->dbUserRoleRepository->deleteAll_UserRole_UserRoleSettingsCollection();
           
$this->dbUserRepository->deleteAll_User_UserRolesCollection();
           
$this->dbUserRepository->deleteAll();
           
           
$this->dbUserRepository->dbUserRoleRepository->commitTransaction();
           
$this->dbUserRepository->commitTransaction();
        }
        catch (\
Throwable $e)
        {
           
$this->dbUserRepository->dbUserRoleRepository->rollbackTransaction();
           
$this->dbUserRepository->rollbackTransaction();
            throw
$e;
        }
    }

    public function
deleteUserRelatedBaseData()
    {
        try
        {
           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->beginTransaction();
           
$this->dbUserRepository->dbUserRoleRepository->beginTransaction();

           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->deleteAll();
           
$this->dbUserRepository->dbUserRoleRepository->deleteAll();

           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->commitTransaction();
           
$this->dbUserRepository->dbUserRoleRepository->commitTransaction();
        }
        catch (\
Throwable $e)
        {
           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->rollbackTransaction();
           
$this->dbUserRepository->dbUserRoleRepository->rollbackTransaction();
            throw
$e;
        }
    }

    public function
createUserRelatedBaseData()
    {
        try
        {
           
//UserRoleSetting
           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->beginTransaction();

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Name", "ACTIVE");
            if (!
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->getNewItemInstance();
               
$item["Name"]->value = "ACTIVE";
               
$item["DefaultValue"]->value = 0;
               
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->save($item);
            }

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Name", "LOGLEVEL");
            if (!
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->getNewItemInstance();
               
$item["Name"]->value = "LOGLEVEL";
               
$item["DefaultValue"]->value = 1;
               
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->save($item);
            }

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Name", "ACCESS_READ");
            if (!
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->getNewItemInstance();
               
$item["Name"]->value = "ACCESS_READ";
               
$item["DefaultValue"]->value = 0;
               
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->save($item);
            }

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Name", "ACCESS_WRITE");
            if (!
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->getNewItemInstance();
               
$item["Name"]->value = "ACCESS_WRITE";
               
$item["DefaultValue"]->value = 0;
               
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->save($item);
            }

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Name", "ACCESS_DOWNLOAD");
            if (!
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->getNewItemInstance();
               
$item["Name"]->value = "ACCESS_DOWNLOAD";
               
$item["DefaultValue"]->value = 0;
               
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->save($item);
            }

           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->commitTransaction();
        }
        catch (\
Throwable $e)
        {
           
$this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->rollbackTransaction();
            throw
$e;
        }

        try
        {
           
//UserRole
           
$this->dbUserRepository->dbUserRoleRepository->beginTransaction();

           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Code", "BASE_USER");
            if (!
$this->dbUserRepository->dbUserRoleRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->getNewItemInstance();
               
$item["Code"]->value = "BASE_USER";
               
$item["Name"]->value = "Base user";
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACTIVE", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("LOGLEVEL", "3");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_READ", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_WRITE", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_DOWNLOAD", "1");
               
$this->dbUserRepository->dbUserRoleRepository->save($item);
            }
   
           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Code", "GUEST");
            if (!
$this->dbUserRepository->dbUserRoleRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->getNewItemInstance();
               
$item["Code"]->value = "GUEST";
               
$item["Name"]->value = "Guest";
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACTIVE", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("LOGLEVEL", "10");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_READ", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_WRITE", "0");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_DOWNLOAD", "1");
               
$this->dbUserRepository->dbUserRoleRepository->save($item);
            }
   
           
$filters = array();
           
$filters[] = new Param("IsDeleted", 0);
           
$filters[] = new Param("Code", "ADMIN");
            if (!
$this->dbUserRepository->dbUserRoleRepository->checkItemInDB($filters, $item))
            {
               
$item = $this->dbUserRepository->dbUserRoleRepository->getNewItemInstance();
               
$item["Code"]->value = "ADMIN";
               
$item["Name"]->value = "Admin";
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACTIVE", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("LOGLEVEL", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_READ", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_WRITE", "1");
               
$item["UserRoleSettingsCollection"]->value[] = $this->getNewUserRoleSettingCollectionItem("ACCESS_DOWNLOAD", "1");
               
$this->dbUserRepository->dbUserRoleRepository->save($item);
            }

           
$this->dbUserRepository->dbUserRoleRepository->commitTransaction();
        }
        catch (\
Throwable $e)
        {
           
$this->dbUserRepository->dbUserRoleRepository->rollbackTransaction();
            throw
$e;
        }
    }

    private function
getNewUserRoleSettingCollectionItem($userRoleSettingName, $value)
    {
       
$userRoleSettingCollectionItem = $this->dbUserRepository->dbUserRoleRepository->getNewItemInstance($this->dbUserRepository->dbUserRoleRepository->getUserRoleUserRoleSettingsCollectionItemAttributes());
       
$filters = array();
       
$filters[] = new Param("IsDeleted", 0);
       
$filters[] = new Param("Name", $userRoleSettingName);
       
$userRoleSetting = $this->dbUserRepository->dbUserRoleRepository->dbUserRoleSettingRepository->loadByFilter2($filters);
       
$userRoleSettingCollectionItem["UserRoleSetting"]->value = $userRoleSetting[0];
       
$userRoleSettingCollectionItem["Value"]->value = $value;
        return
$userRoleSettingCollectionItem;
    }


    public function
login($loginName, $password)
    {
       
$returnValue = false;
       
$currentDate = date('Y-m-d H:i:s');

       
$filters = array();
       
$filters[] = new Param("IsDeleted", 0);
       
$filters[] = new Param("LoginName", $loginName);
       
$filters[] = new Param("Password", sha1($password));
        if (
$this->dbUserRepository->checkItemInDB($filters, $this->userLogin))
        {
           
$this->userLogin = $this->userLogin[0];
           
$this->userLogin["IsLogged"]->value = 1;
           
$this->userLogin["LastLoginDateTime"]->value = $currentDate;
           
$this->dbUserRepository->saveWithTransaction($this->userLogin);
           
$returnValue = true;
            echo
"Login success!".LINE_SEPARATOR;
        }
        else
        {
            echo
"Login failed!".LINE_SEPARATOR;
        }
        return
$returnValue;
    }

    public function
logout()
    {
        if (
$this->userLogin !== null)
        {
           
$this->userLogin["IsLogged"]->value = 0;
           
$this->dbUserRepository->saveWithTransaction($this->userLogin);
           
$this->userLogin = null;
            echo
"Logout success!".LINE_SEPARATOR;
        }
    }
   
    public function
showUserSomeData()
    {
        if (
$this->userLogin !== null)
        {
           
//We will load the user's all data
           
$user = $this->dbUserRepository->loadByIdWithTransaction($this->userLogin["Id"]->value);
            echo
LINE_SEPARATOR;
            echo
"User:";
           
$this->dbUserRepository->writeOutSimpleData($user);

            echo
"UserRolesCollection.UserRole.Code:".LINE_SEPARATOR;
           
$attribute = ItemAttribute::getItemAttribute($user[0], "UserRolesCollection.UserRole.Code");
            foreach(
$attribute as $val)
            {
                echo
$val->value.LINE_SEPARATOR;
            }
            echo
LINE_SEPARATOR;
           
Common::writeOutLetter("-", 50, LINE_SEPARATOR);

            echo
"UserRolesCollection.UserRole.UserRoleSettingsCollection.UserRoleSetting.Value".LINE_SEPARATOR;
           
$attribute = ItemAttribute::getItemAttribute($user[0], "UserRolesCollection.UserRole.UserRoleSettingsCollection.Value");
            foreach(
$attribute as $val)
            {
                echo
$val->value.LINE_SEPARATOR;
            }

            echo
LINE_SEPARATOR;
           
Common::writeOutLetter("-", 50, LINE_SEPARATOR);
            echo
"UserRolesCollection:";
           
$this->dbUserRepository->writeOutSimpleData($user[0]["UserRolesCollection"]->value);
            echo
"UserRolesCollection UserRole codes with settings and values:";
            echo
LINE_SEPARATOR;
           
            foreach(
$user[0]["UserRolesCollection"]->value as $userRoleCollectionItem)
            {
                echo
LINE_SEPARATOR;
                echo
$userRoleCollectionItem["UserRole"]->value["Code"]->value.":";
                echo
LINE_SEPARATOR;
                foreach(
$userRoleCollectionItem["UserRole"]->value["UserRoleSettingsCollection"]->value as $userRoleUserRoleSettingCollectionItem)
                {
                    echo
$userRoleUserRoleSettingCollectionItem["UserRoleSetting"]->value["Name"]->value." -> ".$userRoleUserRoleSettingCollectionItem["Value"]->value;
                    echo
LINE_SEPARATOR;
                }
            }
            echo
LINE_SEPARATOR;
        }
        else
        {
            echo
"User is not logged in!".LINE_SEPARATOR;
        }
    }

    public function
getNewUserRoleCollectionItem($userRoleCode)
    {
       
$userRoleCollectionItem = $this->dbUserRepository->getNewItemInstance($this->dbUserRepository->getUserUserRolesCollectionItemAttributes());
       
$filters = array();
       
$filters[] = new Param("IsDeleted", 0);
       
$filters[] = new Param("Code", $userRoleCode);
       
$userRole = $this->dbUserRepository->dbUserRoleRepository->loadByFilter2($filters);
       
$userRoleCollectionItem["UserRole"]->value = $userRole[0];
        return
$userRoleCollectionItem;
    }

    public function
registerNewUser($loginName, $password, $userRoleCollectionItemArray)
    {
       
$newUser = null;
       
$filters = array();
       
$filters[] = new Param("IsDeleted", 0);
       
$filters[] = new Param("LoginName", $loginName);
        if (
$this->dbUserRepository->checkItemInDB($filters, $newUser))
        {
            echo
"$loginName user is already existed!".LINE_SEPARATOR;
        }
        else
        {
           
//Set user's base data
           
$passwordsha1 = sha1($password);
           
$newUser = $this->dbUserRepository->getNewItemInstance();
           
$newUser["LoginName"]->value = $loginName;
           
$newUser["Password"]->value = $passwordsha1;
       
           
//Set user's UserRolesCollection
           
$newUser["UserRolesCollection"]->value = $userRoleCollectionItemArray;

           
$this->dbUserRepository->saveWithTransaction($newUser);
            echo
"$loginName user registered!".LINE_SEPARATOR;
        }
    }

    public function
deleteUser($loginName)
    {
       
$currentUser = null;
       
$filters = array();
       
$filters[] = new Param("LoginName", $loginName);
        if (!
$this->dbUserRepository->checkItemInDB($filters, $currentUser))
        {
            echo
"$loginName user is not existed!".LINE_SEPARATOR;
        }
        else
        {
           
//We will load the user's all data
           
$currentUser = $this->dbUserRepository->loadByIdWithTransaction($currentUser[0]["Id"]->value);
           
$this->dbUserRepository->deleteWithTransaction($currentUser[0]);
            echo
"$loginName user deleted!".LINE_SEPARATOR;
        }
    }

    public function
generateUsers($count, $startIndex = 1)
    {
        for(
$i = $startIndex; $i < ($startIndex + $count); $i++)
        {
           
$userRoleCollectionItemArray = array(
           
$this->getNewUserRoleCollectionItem("BASE_USER"),
           
$this->getNewUserRoleCollectionItem("GUEST"));
           
$this->registerNewUser("user_$i", "123", $userRoleCollectionItemArray);
        }
    }

}

try
{
   
//TEST

   
error_reporting(0); //only production environment
    //error_reporting(E_ALL); //for detail error reporting

    //Fill connectionData out before using
    //$connectionData = new ConnectionData("localhost", "userName", "password", "test"); // use it with MySQLi extension
    //$connectionData = new ConnectionData("mysql:host=localhost;dbname=test", "userName", "password"); // use it with PDO extension (MySQL)
   
$connectionData = new ConnectionData("sqlsrv:server=(local);Database=test","",""); //PDO MSSQL

    //DbUserRoleSettingRepository single instance
   
$dbUserRoleSettingRepository = new DbUserRoleSettingRepository($connectionData, true, "Name"); //Caching by Name

    //DbUserRoleRepository single instance
   
$dbUserRoleRepository = new DbUserRoleRepository($connectionData,$dbUserRoleSettingRepository, true, "Code"); //Caching by Code
   
    //DbUserRepository single instance
   
$dbUserRepository = new DbUserRepository($connectionData, $dbUserRoleRepository); //default no cache

   
$loginTest = new LoginTest($dbUserRepository);
   
//Comment out if you want to modify this class for several times!
    //unset($_SESSION["LoginTest"]);

   
if (!isset($_SESSION["LoginTest"]))
    {
       
$_SESSION["LoginTest"] = json_encode($loginTest);
    }
    else
    {
       
$loginTest = json_decode($loginTest);
    }


   
$loginTest->deleteUserData(); //User and related data deleting
   
$loginTest->deleteUserRelatedBaseData(); //UserRole + UserRoleSetting deleting

   
$loginTest->createUserRelatedBaseData(); //UserRole + UserRoleSetting creating

    //TEST DATA generation: creating 500 users
   
$loginTest->generateUsers(500, 3);


   
//Caching will be slow when you have much more data!!!
    //It is important to overthink you really need caching.
   
$dbUserRepository = new DbUserRepository($connectionData, $dbUserRoleRepository, true);
   
   
//Build cache
   
echo "Using cached items:".LINE_SEPARATOR;
   
$dbUserRoleRepository->buildCache(true);
   
$dbUserRoleSettingRepository->buildCache(true);

   
$userRoleItem = $dbUserRoleRepository->getItemFromCache("GUEST");
    echo
LINE_SEPARATOR;
    echo
$userRoleItem["Name"]->value.LINE_SEPARATOR;
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getItemFromCache("ACTIVE");
    echo
$userRoleSettingItem["Name"]->value.LINE_SEPARATOR;

   
//Add new userRoleSetting items to itemCache
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getNewItemInstance();
   
$userRoleSettingItem["Name"]->value = "TEST1";
   
$userRoleSettingItem["DefaultValue"]->value = "TEST1";
   
$dbUserRoleSettingRepository->addItemToCache($userRoleSettingItem);
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getItemFromCache("TEST1");
    echo
$userRoleSettingItem["Name"]->value."(Id: ".$userRoleSettingItem["Id"]->value.")".LINE_SEPARATOR;

   
$userRoleSettingItem = $dbUserRoleSettingRepository->getNewItemInstance();
   
$userRoleSettingItem["Name"]->value = "TEST2";
   
$userRoleSettingItem["DefaultValue"]->value = "TEST2";
   
$dbUserRoleSettingRepository->addItemToCache($userRoleSettingItem);
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getItemFromCache("TEST2");
    echo
$userRoleSettingItem["Name"]->value."(Id: ".$userRoleSettingItem["Id"]->value.")".LINE_SEPARATOR;
   
   
//Save new userRoleSetting items to DB
   
$dbUserRoleSettingRepository->saveCache();
   
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getItemFromCache("TEST1");
    echo
$userRoleSettingItem["Name"]->value."(Id: ".$userRoleSettingItem["Id"]->value.")".LINE_SEPARATOR;
   
$userRoleSettingItem = $dbUserRoleSettingRepository->getItemFromCache("TEST2");
    echo
$userRoleSettingItem["Name"]->value."(Id: ".$userRoleSettingItem["Id"]->value.")".LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #1
   
echo "Finding item test #1";
    echo
LINE_SEPARATOR;
   
$param = array();
   
$param[] = new Param("Name","Gues%", Operator::OP_LIKE);
   
$param[] = new Param("Code","AA", Operator::OP_NOT_LIKE);
   
$filterParam = new FilterParam($param, LogicalOperator::LO_AND);
   
$foundItems = $dbUserRoleRepository->find($dbUserRoleRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);
    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #2
   
echo "Finding item test #2";
    echo
LINE_SEPARATOR;
   
$param = array();
   
$param[] = new Param("Name","Gues%", Operator::OP_LIKE);
   
$param[] = new Param("Code","%ASE%", Operator::OP_LIKE);
   
$filterParam = new FilterParam($param, LogicalOperator::LO_OR);
   
$foundItems = $dbUserRoleRepository->find($dbUserRoleRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);
    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;


   
$userRoleCollectionItemArray = array(
   
$loginTest->getNewUserRoleCollectionItem("BASE_USER"),
   
$loginTest->getNewUserRoleCollectionItem("GUEST"));
   
$loginTest->registerNewUser("user_1", "123", $userRoleCollectionItemArray);
   
$loginTest->login("user_1", "123");
   
$loginTest->showUserSomeData();
   
$loginTest->logout();

   
$userRoleCollectionItemArray = array(
   
$loginTest->getNewUserRoleCollectionItem("BASE_USER"));
   
$loginTest->registerNewUser("user_2", "123", $userRoleCollectionItemArray);
   
$userRoleCollectionItemArray = array(
   
$loginTest->getNewUserRoleCollectionItem("ADMIN"));
   
$loginTest->registerNewUser("admin", "admin", $userRoleCollectionItemArray);


    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
$dbUserRepository->buildCache(true);
   
//Find test #3
   
echo "Finding item test #3";
    echo
LINE_SEPARATOR;
   
$param = array();
   
$param[] = new Param("LastLoginDateTime", date('Y-m-d H:i:s', strtotime("2024-08-08")), Operator::OP_LESS_THAN);
   
$filterParam = new FilterParam($param);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #4
   
echo "Finding item test #4";
    echo
LINE_SEPARATOR;
   
$param = array();
   
$param[] = new Param("LastLoginDateTime", date('Y-m-d H:i:s', strtotime("2023-08-08")), Operator::OP_GREATER_THAN_OR_EQUAL);
   
$param[] = new Param("LastLoginDateTime", null, Operator::OP_EQUAL);
   
$filterParam = new FilterParam($param, LogicalOperator::LO_OR);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);
    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #5: any user who has "BASE_USER" UserRole and has "LOGLEVEL" = 3 setting
   
echo "Finding item test #5";
    echo
LINE_SEPARATOR;
   
$param = array();

   
$param[] = new Param("UserRolesCollection.UserRole.Code","BASE_USER", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.UserRoleSetting.Name", "LOGLEVEL", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.Value", "3", Operator::OP_EQUAL);

   
$filterParam = new FilterParam($param, LogicalOperator::LO_AND);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #6: any user who has "BASE_USER" UserRole and has "LOGLEVEL" = 10 setting
    //DbUserRepository.depth = 2, if you set it to 0 you cannot get the correct result
   
echo "Finding item test #6";
    echo
LINE_SEPARATOR;
   
$param = array();

   
$param[] = new Param("UserRolesCollection.UserRole.Code", "BASE_USER", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.Value", "10", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.UserRoleSetting.Name", "LOGLEVEL", Operator::OP_EQUAL);

   
$filterParam = new FilterParam($param, LogicalOperator::LO_AND);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #7:
   
echo "Finding item test #7";
    echo
LINE_SEPARATOR;
   
$param = array();

   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.Value", "10", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.Code", "GUEST", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.UserRoleSetting.Name", "LOGLEVEL1", Operator::OP_EQUAL); //no LOGLEVEL1

   
$filterParam = new FilterParam($param, LogicalOperator::LO_AND);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Find test #8:
   
echo "Finding item test #8";
    echo
LINE_SEPARATOR;
   
$param = array();

   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.Value", "10", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.Code", "GUEST", Operator::OP_EQUAL);
   
$param[] = new Param("UserRolesCollection.UserRole.UserRoleSettingsCollection.UserRoleSetting.Name", "LOGLEVEL1", Operator::OP_EQUAL); //no LOGLEVEL1

   
$filterParam = new FilterParam($param, LogicalOperator::LO_OR);
   
$foundItems = $dbUserRepository->find($dbUserRepository->getAllItemsFromCache(true), $filterParam, false);
    echo
"count: ".count($foundItems);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;
   
    echo
"DB - IS NULL Test #1";
    echo
LINE_SEPARATOR;
   
$filters = array();
   
$filters[] = new Param("LastLoginDateTime", null, Operator::OP_IS_NULL);
   
$users = $dbUserRepository->LoadByFilter2($filters);
    echo
"count: ".count($users);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;
   
    echo
"DB - IS NOT NULL Test #2";
    echo
LINE_SEPARATOR;
   
$filters = array();
   
$filters[] = new Param("LastLoginDateTime", null, Operator::OP_IS_NOT_NULL);
   
$users = $dbUserRepository->LoadByFilter2($filters);
    echo
"count: ".count($users);

    echo
LINE_SEPARATOR;
    echo
LINE_SEPARATOR;

   
//Testing user's delete
   
$loginTest->deleteUser("user_1"); //physically delete
   
$loginTest->deleteUser("user_2"); //physically delete
   
}
catch(\
Throwable $e)
{
    echo
$e->getMessage();
}
?>


Details

# DataAccessLayerHelper # DBRepository Rasher PHP Data Access Layer Helper & DataBase Repository This package provides several classes that can connect to many kinds of databases and executes SQL queries to perform any type of data access (MySQLi, PDO) to database tables. Currently, it can: - Connect to a database server via MySQLi or PDO extensions - Execute prepared queries and get the results as an array or by result column name - Access a database like a repository with functions to access repository records by performing operations to create, read, update, and delete repository items - Manipulate database record column values according to the respective data type - DT_ITEM, DT_LIST complex attribute types and searching abilities - Transaction handling - Caching the database query results - Comparison operators to define filtering conditions - Execute stored procedure + test (with PDO - MSSQL), data generation test //LATER DEVELOPMENT PHASE - Database table script generation by the described attributes - Concurrent data managing - Historical data handling with DBHistoricalRepository - Better function and parameters description - Documentation

Screenshots (2)  
  • example_running.jpg
  • example_running_2.jpg
  Files folder image Files (25)  
File Role Description
Files folder imagesrc (8 files)
Files folder imagetest (6 files, 2 directories)
Accessible without login Plain text file LICENSE Lic. licence
Accessible without login Plain text file README.md Doc. read_me

  Files folder image Files (25)  /  src  
File Role Description
  Plain text file common_static_helper.php Class Class source
  Plain text file data_access_layer_helper_base.php Class Class source
  Plain text file data_access_layer_helper_mysqli.php Class Class source
  Plain text file data_access_layer_helper_pdo.php Class Class source
  Plain text file data_type_helper.php Class Class source
  Plain text file db_repository_base.php Class Class source
  Plain text file db_repository_base_mysqli.php Class Class source
  Plain text file db_repository_base_pdo.php Class Class source

  Files folder image Files (25)  /  test  
File Role Description
Files folder imagescript_mssql (2 files)
Files folder imagescript_mysql (5 files)
  Accessible without login Plain text file login_test.php Example Class source
  Accessible without login Plain text file stored_procedure_test.php Example Example script
  Accessible without login Plain text file stored_procedure_test_simple_pdo.php Example Example script
  Plain text file userrolesetting_data_repository.php Class Class source
  Plain text file userrole_data_repository.php Class moving files
  Plain text file user_data_repository.php Class moving files

  Files folder image Files (25)  /  test  /  script_mssql  
File Role Description
  Accessible without login Plain text file getUser.storedProcedure.sql Data Auxiliary data
  Accessible without login Plain text file test.sql Data Auxiliary data

  Files folder image Files (25)  /  test  /  script_mysql  
File Role Description
  Accessible without login Plain text file test_user.sql Data Auxiliary data
  Accessible without login Plain text file test_userrole.sql Data Auxiliary data
  Accessible without login Plain text file test_userrolesetting.sql Data Auxiliary data
  Accessible without login Plain text file test_userrole_user...tingscollection.sql Data Auxiliary data
  Accessible without login Plain text file test_user_userrolescollection.sql Data Auxiliary data

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
 Version Control Unique User Downloads Download Rankings  
 92%
Total:58
This week:0
All time:10,535
This week:455Up
User Comments (1)
Jehhhhh
1 year ago (Róbert Tandari)
77%StarStarStarStar