How to create iCRUD

#1

The following just shows how to create a basic interface that Creates, Updates, Reads and Deletes. This is obviously done in Object-Oriented Programming style and is still a work-in-progress for me. However, there are benefits in creating an interface for one it is extremely portable, easy to use and easy to maintain. Two it keeps your code structured and forces you to stay structured.

Here is the interface:

<?php
/* The iCrud interface.
 * The interface identifies four methods:
 * - create()
 * - read()
 * - update()
 * - delete()
 */
interface iCrud {

  public function create($data);

  public function read();

  public function update($data);

  public function delete($id=NULL);
  
}
[/php]

I think that is pretty straight forward and really hardly any explanation is needed. 

This is how I implement the interface for my blog/forum(s).
[php]<?php

class OnlineJournal implements iCrud {
  
  protected $query = NULL;
  protected $stmt = NULL;
  protected $result = NULL;
  protected $query_params = NULL;
  protected $row = NULL;
  protected $status_message = NULL;
  protected $category = 'home_page';

  public function __construct(array $data = NULL) {
    if (is_array($data) && !empty($data['title']) && !empty($data['message_post'])) {
      $db = Database::getInstance();
      $pdo = $db->getConnection();

      /* Set the query variable */
      $this->query = 'INSERT INTO blog (category, client_id, created_by, title, message_post, date_added) VALUES (:category, :client_id, :created_by, :title, :message_post, NOW())';

      /* Set the query variable */
      $this->stmt = $pdo->prepare($this->query);

      /* Execute the Query */
      $this->result = $this->stmt->execute(array(':category' => $this->category, ':client_id' => $data['client_id'], ':created_by' => $data['created_by'], ':title' => $data['title'], ':message_post' => $data['message_post']));

      return 'Data Successfully Inserted!';
    }
  }

  public function create($data) {
    return self::__construct($data);
  }

  public function setCategory($category) {
    $this->category = $category;
  }
  
  public function getCategory() {
    return $this->category;
  }

  public function read() {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    $this->query = 'SELECT id, client_id, created_by, title, message_post, date_updated, DATE_FORMAT(date_added, "%M %e, %Y") as date_added FROM blog WHERE category=:category ORDER BY date_updated ASC';
    $this->stmt = $pdo->prepare($this->query);
    $this->stmt->execute([':category' => $this->category]);
    if ($this->stmt && $this->stmt->rowCount() > 0) {
      return $this->stmt; // if there is data in the database table then return:
    } else {
     return FALSE;
    }
  }

  public function update($data) {

    $db = Database::getInstance();
    $pdo = $db->getConnection();
    /* Update the edited blog */
    $this->query = 'UPDATE blog SET title=:title, message_post=:message_post, date_updated=NOW() WHERE id=:id';
    /* Prepare the statement */
    $this->stmt = $pdo->prepare($this->query);
    /* Execute the statement */
    $this->result = $this->stmt->execute(array(':title' => $data['title'], ':message_post' => $data['message_post'], ':id' => $data['id']));

    return ($this->result) ? true : false;
  }

  public function delete($id = NULL) {
    $db = Database::getInstance();
    $pdo = $db->getConnection();
    $this->stmt = $pdo->prepare('DELETE FROM blog WHERE id=:id');
    $this->result = $this->stmt->execute(array(':id' => $id));
    return ($this->result) ? true : false;
  }

}

This is how new data is inserted into the database table:

<?php
require 'lib/includes/utilities.inc.php';
if (!$user) {
  header("Location: forums.php");
  exit();
} elseif ($user->getSecurityLevel() == 'public') {
  header("Location: forums.php");
  exit();
}
$category = $_SESSION['category']; // Retreive the category in order to set:
$newThread = new OnlineJournal(); // Create a new instance of C.R.U.D. system:
$newThread->setCategory($category); // Set Category for proper forum:

/* Insert blog into database table */
$journal = filter_input(INPUT_POST, 'submitBlog', FILTER_SANITIZE_SPECIAL_CHARS);

if (isset($journal) && $journal == 'Enter') {
  $data['client_id'] = filter_input(INPUT_POST, 'client_id', FILTER_SANITIZE_NUMBER_INT);
  $data['created_by'] = filter_input(INPUT_POST, 'created_by', FILTER_SANITIZE_SPECIAL_CHARS);
  $data['title'] = filter_input(INPUT_POST, 'title', FILTER_DEFAULT);
  $data['message_post'] = filter_input(INPUT_POST, 'message_post', FILTER_DEFAULT);
  $message = $newThread->create($data); // insert data into database table:
  unset($journal);
}
include 'lib/includes/header.inc.php';
?>
<div class="container blogContainer">
  <div class="blogFormContainer">
    <form id="" class="blogFormStyle m-span5" action="<?php echo $phpSelf ?>" method="post"> 
      <fieldset class="fieldsetStyle">
        <legend class="legendStyle">Insert Blog</legend>
        <input type="hidden" name="update_id" value="">
        <input type="hidden" name="client_id" value="<?php echo $user->getUserId(); ?>">
        <input type="hidden" name="created_by" value="<?php echo $user->getFirstName() . ' ' . $user->getLastName(); ?>">
        <input id="title" type="text" name="title" placeholder="Title Goes Here..." value="">
        <textarea id="message_post" placeholder="Blog Text Goes Here..." name="message_post"></textarea>
        <input class="blogBtn" type="submit" name="submitBlog" value="Enter"> 
        <button class="blogBtn" name="reset" >Cancel</button>
      </fieldset>
    </form>
  </div>  
</div>
<?php
include 'lib/includes/footer.inc.php';

I think it’s pretty straight forward and like I said it’s still a work-in-progress meaning there’s still some data validation that needs to be performed on the scripts. However, I think if you know OOP then that should be a walk-in-the-park for you. :wink:

#2

Good start!

I like that your example includes filtering input.

#3

Good start indeed, but why are we filtering input? I always prefer to store the actual data the user have submitted, without any escaping or filters done to it. Makes it much easier to avoid double escaping or other problems later on.

#4

Actually I had another post on this subject on how I spent 4 hours trying to figure why my code was acting up, I meant to change this.

I just do this now for example.
[php] $content = $badWordFilter->setText(filter_input(INPUT_POST, ‘message_post’, FILTER_DEFAULT));[/php]

It really doesn’t filter the input, but I guess it “might” catch something really crazy (but I doubt it).An according to PHP.NET - “ID of default (“string”) filter. This is equivalent to FILTER_UNSAFE_RAW.”. I liken it to passing water through a tube unfiltered. It doesn’t catch html special characters or HTML (I know for I have tested myself). That I leave to the output with htmlspecialchars and the like. :wink: I just do the above now to get my IDE (NetBeans) from warning me about “unfiltering” $_POST (It annoys me seeing warning symbols on the side). LOL ;D

#5

I am of the garbage in garbage out school of thought. Therefore I clean my data going in.

#6

Fair enough, but I still wouldn’t recommend it

This page explain it pretty well
http://lukeplant.me.uk/blog/posts/why-escape-on-input-is-a-bad-idea/

Other than that, just google “escape input or output”


I see no good reason to change the users data when storing it in the database, at all.

#7

Being open to improving my skills, I am looking into it.