How to separate logic from database interaction?

Question

PHP

Hello!
Please tell me how to properly organize the architecture of classes &
I write in PHP.
I want to separate logic from database interaction.
For example, I have a class for working with some entity that creates a collection

class Items implements Iterator {
  // здесь методы для работы с коллекцией
  
  // возможно здесь и методы для фильтрации, сортировки по коллекции ?

}

and a class for working with the database.

class DbItems {
  // здесь методы , внутри которых будут запросы к БД

}

How to properly connect these classes with each other?
You also need the ability to cover these classes with autotests.

Answer 1

Read about Doctrine, ORM, DDD architecture

Answer 2

Create a class for working with the database in a separate file and fill it with static methods. and call them from the previous class (object) only connect the file with the class according to the database in the file where the class that will use it. This is how I write the first class, it makes requests to the youtube API, the second DBdriver does everything related to the

<?php
/**
 * Created by PhpStorm.
 * User: pantene
 * Date: 13.02.2017
 * Time: 14:15
 */

namespace ytd;

use \PDO;

class DBdriver
{


    private static function getConnection()
    {

  // Получаем параметры подключения из файла
        $paramsPath = array(
            'host' => 'localhost',
            'dbname' => 'kamios_mbet',
            'user' => 'root',
            'password' => '',
        );

        $params = $paramsPath;

        // Устанавливаем соединение
        $dsn = "mysql:host={$params['host']};dbname={$params['dbname']}";
        $db = new PDO($dsn, $params['user'], $params['password']);

        // Задаем кодировку
        $db->exec("set names utf8");

        return $db;
    }   // get Connection with DataBase




    public static function Add_Video_To_DataBase($options)
    {



        // Соединение с БД
        $db = DBdriver::getConnection();

        // Текст запроса к БД
        $sql = 'INSERT INTO videos '
            . '(name, description, date, views, videos_id)'
            . 'VALUES '
            . '(:name, :description, :date, :views, :videos_id)';

        // Получение и возврат результатов. Используется подготовленный запрос
        $result = $db->prepare($sql);



        $result->bindParam(':name', $options['name'], PDO::PARAM_STR);
        $result->bindParam(':description', $options['description'], PDO::PARAM_STR);
        $result->bindParam(':date', $options['date'], PDO::PARAM_STR);
        $result->bindParam(':views', $options['views'], PDO::PARAM_INT);
        $result->bindParam(':videos_id', $options['id'], PDO::PARAM_STR);

        if ($result->execute()) {
            // Вертаємо ІД останнього запису
            return $db->lastInsertId();
        }
        // Вразі помилки 0
        return 0;
    }



    public static function clear_table(){

        $db = DBdriver::getConnection();

        $sql = 'TRUNCATE TABLE videos';

        $result = $db->prepare($sql);

        if ($about_result = $result->execute()) {
            // Если запрос выполенен успешно, возвращаем id добавленной записи
            return $about_result;
        }
        // Иначе возвращаем 0
        return 0;


    }


    public static function get_clicks_sum(){

        $db = DBdriver::getConnection();

        $sql = "SELECT `meta_value` FROM `wp_postmeta` WHERE `meta_key` = '_linker_count'";

        // echo $sql;  for debug

        $result = $db->query($sql);

        $assocArray = $result->fetchAll();

     //   var_dump($assocArray);    for debug

        $sum = 0;

        for ($i = 0; $i <= count($assocArray); $i++) {

           $sum = $sum + $assocArray[$i][meta_value];


        }


        return $sum;


    }


    public static function get_all_views_in_playlist($num_int = false) {


        $db = self::getConnection();

        $sql =  'SELECT `views` from `videos`';

        $result = $db->query($sql);

        $assocArray = $result->fetchAll();

        $sum = 0;

        for ($i = 0; $i <= count($assocArray); $i++) {

            $sum = $sum + $assocArray[$i][views];


        }


        if ($num_int) {

            return $sum;

        } else {

            return number_format($sum);

        } //else


    }   // get_all_views_in_playlist



    public static function get_views_in_last_30_days() {


        $db = self::getConnection();

        $sql =  'SELECT `views` from `videos` WHERE `date` > CURDATE() - INTERVAL 30 DAY AND CURDATE()';

        $result = $db->query($sql);

        $assocArray = $result->fetchAll();


        $sum = 0;

        for ($i = 0; $i <= count($assocArray); $i++) {

            $sum = $sum + $assocArray[$i][views];


        }


        return   number_format($sum);



    } // get_views_in_last_30_days


    public static function get_quantity() {

        $db = self::getConnection();

        $members=$db->query("SELECT COUNT(*) as count FROM videos")->fetchColumn();



        return $members;

    }  //  get_quantity_of_videos_in_play_list




    public static function get_average_views_per_video() {



              $average = self::get_all_views_in_playlist(true) / self::get_quantity();

              $average = round($average, 0);

              return $average;



    }



}

And the class that uses it...

<?php
/**
 * Created by PhpStorm.
 * User: pantene
 * Date: 13.02.2017
 * Time: 13:27
 */

namespace ytd;

use ytd\DBdriver;


class YtData
{

//свойства

public $last_number_of_array_on_the_last_pagination = 0;
public $all_items_ids = array();
public $array_of_full_information_about_video = array();
public $playlist_id = "";

//свойства





    public function add_videos_to_bd(){

            for ($i = 0; $i <= count($this->array_of_full_information_about_video); $i++) {

                DBdriver::Add_Video_To_DataBase($this->array_of_full_information_about_video[$i]);

            }


        }







        public function get_array_of_full_information_about_video(){

                   //  echo "</br></br>ITEMS_IDS</br></br>";

                     $i = 0;

                     foreach ($this->all_items_ids as $key=>$value){

                       $this->array_of_full_information_about_video[$i] = $this->get_data_about_video_by_id($value);

                       $i++;
                     } //foreach

            return $this->array_of_full_information_about_video;
        } // METHOD   get array of full information about video


        public function get_all_items($is_page_token = false)
        {


            if ($is_page_token != false) {

                $pgt = "&pageToken=" . $is_page_token;

            } else { $pgt = ""; }


            $request = "https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&playlistId=PLbZV_PJvMcy8x0n6Bx3QsWdSABYTtM0ph&key=AIzaSyC6N_rx9GXbitgE1qTnMmDafv1lVa0Rtvk&maxResults=9&order=date".$pgt;

            $response = file_get_contents($request);

            $response = json_decode($response, true);


            for ($i = 0; $i < count($response[items]); $i++) {

                $this->all_items_ids[$this->last_number_of_array_on_the_last_pagination] = $response[items][$i][snippet][resourceId][videoId];

                $this->last_number_of_array_on_the_last_pagination++;

            } //FOR


            if (isset($response[nextPageToken])) {   $this->get_all_items($response[nextPageToken]);    }   else { return true; }



        }



    public function get_data_about_video_by_id($videoID){


        $response = file_get_contents('https://www.googleapis.com/youtube/v3/videos?id='.$videoID.'&key=AIzaSyC6N_rx9GXbitgE1qTnMmDafv1lVa0Rtvk%20&part=statistics,snippet');

        $response = json_decode($response, true);



        $data_about_video = array();

        $data_about_video[date] = $response[items][0][snippet][publishedAt];           // вихідний формат дати

        $data_about_video[date] = $this->date_in_right_order($data_about_video[date]); // получили дату роліка. Звернулися до метода, що зробить її в потрібному фоматі

        $data_about_video[name] = $response[items][0][snippet][title];    // получили назву роліка

        $data_about_video[views] =  $response[items][0][statistics][viewCount];

        $data_about_video[description] = $response[items][0][snippet][description];    // получили описання роліка

        $data_about_video[id] = $response[items][0][id];




        return $data_about_video;


    }  // METHOD - get video's data by ID


    private function date_in_right_order($date_in_false_order){


           $date_in_false_order = explode("T", $date_in_false_order);
           $date_in_false_order = explode("-",$date_in_false_order[0]);

           $string_data = implode("-", $date_in_false_order);

         //  $array_right_order = array();

           /*$ar = 2;
           $it = 0;
        for ($i = 1; $i <= count($date_in_false_order); $i++) {

            $array_right_order[$it] = $date_in_false_order[$ar];
            $ar--;
            $it++;

        } //for*/


     //   $string_data = implode("-", $array_right_order);

        return "$string_data";

    } //get date in right order  response date in need FORMAT

}   //class YtData;

Here is the method in the DB driver class

private static function getConnection()
    {

  // Получаем параметры подключения из файла
        $paramsPath = array(
            'host' => 'localhost',
            'dbname' => 'kamios_mbet',
            'user' => 'root',
            'password' => '',
        );

        $params = $paramsPath;

        // Устанавливаем соединение
        $dsn = "mysql:host={$params['host']};dbname={$params['dbname']}";
        $db = new PDO($dsn, $params['user'], $params['password']);

        // Задаем кодировку
        $db->exec("set names utf8");

        return $db;
    }   // get Connection with DataBase

I call it in other methods of this class with this code...
$db = DBdriver::getConnection(); ---- and I already have an interface for working with PDO Mysql in the database variable.
You can also write self::fetConnection(); when one method uses another within the same class

Answer 3

There is a standard architecture for separating the logic of the program from working with data and appearance. This architecture is called MVC(model, view, controller). Model is responsible for working with the database (receiving data, adding data to the database, etc.), view is responsible for the appearance (layout in websites, for example), controller is responsible for the logic of the program (for example, get data from the database through model and draw in view).
For a better understanding of this architecture, you can try any php framework, such as laravel, yii2, symfony2.
If you only need a convenient database layer, then Doctrine is enough . Then you won't need to invent iterator, dbitems, etc.