1
\$\begingroup\$

I'm using a slim router and have made a folder model controller view. Below is one of my model files. I made one database with one model.

I have some problem in my project: there are several databases like read_0, read_1, read_2, etc. and each have some different fields:

  1. I don't know how to make crud function in model class match every database, I need to hardcore change every model and controller, I'm calling in controller like this:

    $data_select_all_from_thumbnail_where_thumbnail_id_equal_max_thumbnail_id = $this->read_0_model->select_all_from_thumbnail_where_thumbnail_id_equal_max_thumbnail_id($response_message);
    $response_message = $data_select_all_from_thumbnail_where_thumbnail_id_equal_max_thumbnail_id['response_message'];
    

    Is this a normal way to do it? I don't want to use ORM, is there some library plugin to help make it reusable? If so, does that mean I need to move table name, fields, etc. in the controller? Then what should model class do?

  2. I'm putting one query in one function to collect in model class, so e.g., if I need to get 1 article I need to call each function like below; is this normal way to do this?

    $data_select_all_from_read_0_order_by_sequence_desc = $this->read_0_model->select_all_from_read_0_order_by_sequence_desc();
    $response_message = $data_select_all_from_read_0_order_by_sequence_desc['response_message'];
    
    $data_select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc = $this->read_0_model->select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc($param_0, $response_message);
    $response_message = $data_select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc['response_message']; 
    ....
    

Database

CREATE TABLE `read_0` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sequence` int(11) unsigned NOT NULL,
  `visible` tinyint(1) NOT NULL COMMENT '1:visible,0:invisible',
  `subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `intro` text COLLATE utf8_unicode_ci,
  `content` text COLLATE utf8_unicode_ci,
  `create_at` datetime NOT NULL,
  `create_by` int(11) unsigned NOT NULL,
  `update_at` datetime NOT NULL,
  `update_by` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `thumbnail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `format` varchar(255) COLLATE utf8_unicode_ci DEFAULT '',
  `thumbnail_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `thumbnail_id` (`thumbnail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `read_0_x_thumbnail` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `read_0_id` int(11) unsigned NOT NULL,
  `thumbnail_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `read_0_id` (`read_0_id`),
  KEY `read_0_x_thumbnail_ibfk_2` (`thumbnail_id`),
  CONSTRAINT `read_0_x_thumbnail_ibfk_1` FOREIGN KEY (`read_0_id`) REFERENCES `read_0` (`id`) ON DELETE CASCADE,
  CONSTRAINT `read_0_x_thumbnail_ibfk_2` FOREIGN KEY (`thumbnail_id`) REFERENCES `thumbnail` (`thumbnail_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Model

<?php
namespace usr\local\project\model;

use \PDO;
use usr\local\project\lib as lib;


class read_0_model {
  protected $connect_db;
  protected $response_message;

  public function __construct() {
    $this->connect_db = new lib\connect_db();
    $this->connect_db = $this->connect_db->name('read_0');
    $this->response_message = array(
      "debug_message" => $debug_message =array(),
      "error_message" => $error_message =array(),
      "success_message" => $success_message =array(),
      "warning_message" => $warning_message =array()
    );
  }


  public function insert_into_read_0($data, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "INSERT INTO read_0 (sequence, visible, subject, intro, content, create_at, create_by, update_at, update_by) VALUES (:sequence, :visible, :subject, :intro, :content, :create_at, :create_by, :update_at, :update_by)";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->bindValue(':sequence', $data['sequence']);
      $stmt->bindValue(':visible', $data['visible']);
      $stmt->bindValue(':subject', $data['subject']);
      $stmt->bindValue(':intro', $data['intro']);
      $stmt->bindValue(':content', $data['content']);
      $stmt->bindValue(':create_at', $data['create_at']);
      $stmt->bindValue(':create_by', $data['create_by']);
      $stmt->bindValue(':update_at', $data['update_at']);
      $stmt->bindValue(':update_by', $data['update_by']);
      $stmt->execute();

      $success_message = 'success_message: read_0, insert_into_read_0';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_id_equal_param_0($param_0, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE id = $param_0";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $row = $stmt->fetch(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_id_equal_param_0';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['row'] = $row;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_sequence_equal_max_sequence($response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE sequence =(SELECT MAX(sequence) as sequence FROM read_0)";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $row = $stmt->fetch(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_sequence_equal_max_sequence';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['row'] = $row;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_order_by_sequence_desc($response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 ORDER BY sequence DESC";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_order_by_sequence_desc';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_visible_equal_param_0_order_by_sequence_desc($param_0, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    // data structure: $param_0, controller>>
    // tinyint(1)

    try {
      $sql = "SELECT * FROM read_0 WHERE visible = $param_0 ORDER BY sequence DESC";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_visible_equal_param_0_order_by_sequence_desc';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_order_by_sequence_desc_limit_param_0_param_1($param_0, $param_1, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 ORDER BY sequence DESC LIMIT $param_0, $param_1";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_order_by_sequence_desc_limit_param_0_param_1';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_visible_equal_param_0_order_by_sequence_desc_limit_param_1_param_2($param_0, $param_1, $param_2, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE visible = $param_0 ORDER BY sequence DESC LIMIT $param_1, $param_2";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_visible_equal_param_0_order_by_sequence_desc_limit_param_1_param_2';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc($param_0, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE sequence < $param_0 ORDER BY sequence DESC";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_visible_equal_param_0_where_sequence_smaller_param_1_order_by_sequence_desc($param_0, $param_1, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE visible = $param_0 AND sequence < $param_1 ORDER BY sequence DESC";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_visible_equal_param_0_where_sequence_smaller_param_1_order_by_sequence_desc';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc_limit_param_1($param_0, $param_1, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE sequence < $param_0 ORDER BY sequence DESC LIMIT $param_1";
      $stmt = $this->connect_db->prepare($sql);   
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_sequence_smaller_param_0_order_by_sequence_desc_limit_param_1';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_read_0_where_visible_equal_param_0_and_sequence_smaller_param_1_order_by_sequence_desc_limit_param_2($param_0, $param_1, $param_2, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM read_0 WHERE visible = $param_0 AND sequence < $param_1 ORDER BY sequence DESC LIMIT $param_2";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_read_0_where_visible_equal_param_0_and_sequence_smaller_param_1_order_by_sequence_desc_limit_param_2';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['rows'] = $rows;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function update_read_0($data, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "UPDATE read_0 SET visible = :visible, subject = :subject, intro = :intro, content = :content, update_at = :update_at, update_by = :update_by WHERE id = :id";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->bindValue(':id', $data['id']);
      $stmt->bindValue(':visible', $data['visible']);
      $stmt->bindValue(':subject', $data['subject']);
      $stmt->bindValue(':intro', $data['intro']);
      $stmt->bindValue(':content', $data['content']);
      $stmt->bindValue(':update_at', $data['update_at']);
      $stmt->bindValue(':update_by', $data['update_by']);
      $stmt->execute();

      $success_message = 'success_message: read_0, update_read_0';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }


  public function update_read_0_set_sequence_equal_param_0_where_id_equal_param_1($param_0, $param_1, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "UPDATE read_0 SET sequence = $param_0 WHERE id = $param_1";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();

      $success_message = 'success_message: read_0, update_read_0_set_sequence_equal_param_0_where_id_equal_param_1';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }


  public function delete_from_read_0_where_id_equal_param_0($param_0, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "DELETE FROM read_0 WHERE id = $param_0";
      $stmt = $this->connect_db->prepare($sql);
      $status = $stmt->execute();


      if ($status) {
        $row_count = $stmt->rowCount(); // 0=ignore, 1=insert
        if ($row_count == 0) {
          $warning_message = 'warning_message: read_0, delete_from_read_0_where_id_equal_param_0, not exist in database';
          array_push($response_message['warning_message'], $warning_message);
        } else if ($row_count == 1) {
          $success_message = 'success_message: read_0, delete_from_read_0_where_id_equal_param_0';
          array_push($response_message['success_message'], $success_message);
        }
      }
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }


  // thumbnail
  public function insert_into_thumbnail($data, $response_message = null) {
    if( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "INSERT INTO thumbnail (name, format, thumbnail_id) VALUES (:name, :format, :thumbnail_id)";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->bindValue(':name', $data['name']);
      $stmt->bindValue(':format', $data['type']);
      $stmt->bindValue(':thumbnail_id', $data['thumbnail_id']);
      $stmt->execute();

      $success_message = 'success_message: read_0, insert_into_thumbnail';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_thumbnail_where_thumbnail_id_equal_max_thumbnail_id($response_message = null) {
    if( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM thumbnail WHERE thumbnail_id =(SELECT MAX(thumbnail_id) as thumbnail_id FROM thumbnail)";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $row = $stmt->fetch(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_thumbnail_where_thumbnail_id_equal_max_thumbnail_id';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['row'] = $row;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function select_all_from_thumbnail_where_thumbnail_id_equal_param_0($param_0, $response_message = null) {
    if ( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "SELECT * FROM thumbnail WHERE thumbnail_id = $param_0";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();
      $row = $stmt->fetch(PDO::FETCH_ASSOC);

      $success_message = 'success_message: read_0, select_all_from_thumbnail_where_thumbnail_id_equal_param_0';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['row'] = $row;
    $data['response_message'] = $response_message;


    return $data;
  }


  public function delete_from_thumbnail_where_thumbnail_id_equal_param_0($param_0, $response_message = null) {
    if( $response_message === null ) {
      $response_message = $this->response_message;
    }

    try {
      $sql = "DELETE FROM thumbnail WHERE thumbnail_id = $param_0";
      $stmt = $this->connect_db->prepare($sql);
      $stmt->execute();

      $success_message = 'success_message: read_0, delete_from_thumbnail_where_thumbnail_id_equal_param_0';
      array_push($response_message['success_message'], $success_message);
    } catch (PDOException $e) {
      $debug_message = $e->getMessage();
      array_push($response_message['debug_message'], $debug_message);
      $error_message = "error_message: ? database";
      array_push($response_message['error_message'], $error_message);
    }

    $data = array();
    $data['response_message'] = $response_message;


    return $data;
  }

 ...

}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.