Categories
HTML MVC PHP

Deeper in Models

In this post, we will explore more database transactions in model classes with the previous Item class example. We will also create views that can support these operations.

SQL DML (Data Modification Language) are these operations that we must implement in our model classes. They comprise all CRUD (Create, Read, Update, Delete) operations, i.e., respectively INSERT, SELECT, UPDATE, and DELETE.

Create

We add a create method to the Item model class as follows:

public function create(){
  $SQL = 'INSERT INTO Item(name) VALUES (:name)';
  $stmt = self::$_connection->prepare($SQL);
  $stmt->execute(['name'=>$this->name]);
  return self::$_connection->lastInsertId();
}

In the above, we first define the SQL command we will use, with a named placeholder, :name. We then get PDO to prepare the statement; the use of prepared statements has many advantages including reusability and SQL-injection prevention (this happens later).

Next, we run the prepared statement with the execute method. The parameter is an array ([...]) containing a dictionary of values for this statement, i.e., if there is a :name placeholder in the SQL, then there must be a name key-value pair in this dictionary. The execute method will take all this data and sanitize it, preventing any special character that could be used to perform SQL injection. In other words, if you are using prepared statements in all your models such that all data is passed into the statements through the execute method, then you have been diligent in preventing SQL injection.

The final step in the create method is to return the primary key value assigned to the created record. This value is fetched by PDO’s lastInsertId() method.

We now focus on the view used to create this record. We create a view, /app/view/home/create.php that will be used for data entry by the user, as follows:

<html>
  <head><title>Create an Item</title></head>
  <body>
    <h1>Create an Item</h1>
    <form action='' method='post'>
      <label>Name:<input type='text' name='name' /></label><br />
      <input type='submit name='action' value='Create' /> <a href='/home/index'>Cancel</a>
    </form>
  </body>
</html>

The form is submitted to the same URL from which it was called. This way, we can write the instructions to call the view and to add the record in the same controller method in HomeController:

public function create(){
  if(isset($_POST['action'])){
    $newItem = $this->model('Item');
    $newItem->name = $_POST['name'];
    $newItem->create();
    header('location:/Home/index');
  }else{
    $this->view('home/create');
  }
}

The create method is written in two branches, the first if the submit button named action is pressed. We instantiate an Item object and initialize its name attribute (its only attribute) before calling the create() method to store it. Finally, we send the browser headers to redirect to the /Home/index URL for the application, which will display the list of items. The user can also navigate back to the list of items by blicking Cancel.

Read

We now focus on a detail-view feature for the records. Bear in mind that this is especially useful in cases where records hold many fields. We first add a find(…) method to the model class:

public function find($item_id){
  $SQL = 'SELECT * FROM Item WHERE item_id = :item_id';
  $stmt = self::$_connection->prepare($SQL);
  $stmt->execute(['item_id'=>$item_id]);
  $stmt->setFetchMode(PDO::FETCH_CLASS, 'Item');
  return $stmt->fetch();
}

In contrast with the get() method we wrote in An Introduction to Models, the SQL includes a WHERE clause to choose the record based on the primary key value and this value is passed in the call to the execute() method. Finally, the is returned after a call to fetch() such that the single matching record is returned as an object, without being placed in an array.

To display the record, we build a detail view, /app/views/home/detail.php which should include all record fields in the display:

<html>
  <head><title>Item Details</title></head>
  <body>
    <h1>Item Details</h1>
    <dl>
      <dt>Name:</dt>
      <dd><?=$data->name ?></dd>
    </dl>
<a href='/home/index'>Back to list</a>
  </body>
</html>

Above, we are assuming the single record is passed to the view and that the name attribute is displayed by the <?=expression ?> tags which are shorthand for <?php echo expression; ?>.

We complete this part with method detail in HomeController as follows:

public function detail($item_id){
  $theItem = $this->model('Item')->find($item_id);
  $this->view('home/detail', $theItem);
}

On the first line, we invoke the method to find the wanted record and store it in $theItem, which is then passed to the newly-created view on the following line.

Update

Another important part of a data-centric Web application is the ability to modify data. In a MySQL database, this is done through UPDATE SQL statements, as in the following update method added to the Item model.

public function update(){
  $SQL = 'UPDATE Item 
          SET name = :name 
          WHERE item_id = :item_id';
  $stmt = self::$_connection->prepare($SQL);
  $stmt->execute(['name'=>$this->name,
                  'item_id'=>$this->item_id]);
  return $stmt->rowCount();
}

First we write a proper SQL UPDATE statement with named placeholders where the data is meant to go. Then we prepare the statement and execute it with the needed data, from the object. Finally, we return the rowCount as feedback about how many rows were modified.

The view to support this process must allow data entry from the user AND have the data entry fields prepopulated with the existing record field values. We add the view /app/views/home/edit.php as follows:

<html>
  <head><title>Edit an item</title></head>
  <body>
    <h1>Edit an item</h1>
    <form action='' method='post'>
      <label>Name:<input type='text' name='name'
           value='<?= $data->name ?>' /></label><br />
      <input type='submit name='action' value='Save' /> <a href='/home/index'>Cancel</a>
    </form>
  </body>
</html>

For the view to work properly, it must receive the record as its $data parameter. We put all this together in the HomeController edit method, as follows:

public function edit($item_id){
  $theItem = $this->model('Item')->find($item_id);
  if(isset($_POST['action'])){
    $theItem->name = $_POST['name'];
    $theItem->update();
    header('location:/Home/index');
  }else{
    $this->view('home/edit', $theItem);
  }
}

This method is very similar to create, in that it handles displaying a form, processes the submitted data, and redirects back to the list of all items. The distinctions are that edit operates on an existing record and thus must find and present it in the view prior to calling for updates with the user input. Naturally, if a user clicks the Cancel hyperlink, they navigate back to the index method, so this case need not be addressed here.

Delete

To complete the basic data management for the Item table, we must have the ability to delete records. We will implement the Item model class delete method as follows:

public function delete(){
  $SQL = 'DELETE FROM Item WHERE item_id = :item_id';
  $stmt = self::$_connection->prepare($SQL);
  $stmt->execute(['item_id'=>$this->item_id]);
  return $stmt->rowCount();
}

It is easy to also implement this method to take the item_id as a parameter and forego using the actual object attributes.

The pattern to process this request is familiar by now, but in a nutshell: define the DELETE statement with an :item_id placeholder; prepare the statement; execute the statement with the data to replace the placeholders; and return the number of rows affected by the change.

To make this process go smoothly, there are two possibilities: with or without a deletion confirmation view. We write a deletion confirmation view /app/views/home/delete.php as follows:

<html>
  <head><title>Delete an item</title></head>
  <body>
    <h1>Delete an item</h1>
    <form action='' method='post'>
      <label>Name:<input type='text' name='name'
        value='<?= $data->name ?>' disabled />
        </label><br />
      <input type='submit name='action' value='Delete' /> <a href='/home/index'>Cancel</a>
    </form>
  </body>
</html>

So the user will click on the Delete button to confirm deletion and the Cancel link to cancel the deletion.

To complete the deletion implementation, we add the delete method to the HomeController as follows:

public function delete($item_id){
  $theItem = $this->model('Item')->find($item_id);
  if(isset($_POST['action'])){
    $theItem->delete();
    header('location:/Home/index');
  }else{
    $this->view('home/delete', $theItem);
  }
}

In the above, we only handle the cases when the method is called with or without the action button being clicked. These cases happen upon submitting the form and navigating to delete, respectively.

Tying it All Together

The only aspect missing from this application is the set of hyperlinks that allow navigation. Since all views have links back to /home/index and since all methods redirect to /home/index as well, the links to go to access create, detail, update, and delete methods must be implemented in the view for the index method of the HomeController class; we modify it as follows:

<html>
<head><title>Item List</title></head>
<body>
<h1>List of items</h1>
<a href='/home/create/'>Add an item</a>
<table>
<tr><th>Name</th><th>Actions</th></tr>
<?php
  foreach($data['items'] as $item){
    echo "<tr><td>$item->name</td><td>
      <a href='/home/detail/$item->item_id'>Details</a>
      <a href='/home/edit/$item->item_id'>Modify</a>
      <a href='/home/delete/$item->item_id'>Delete</a>
      </td></tr>";
  }
?>
</table>
</body>
</html>

Notice the hyperlinks all lead to URLs which will be used to invoke controller methods with their parameters. The controller methods will, in turn, call the appropriate views and process the responses to finally redirect as appropriate.

By Michel

My name is Michel Paquette. I currently teach my students how to create data-driven Web applications at Vanier College, in Montreal.

My GitHub page contains a few examples of Web applications. Also consult my YouTube channels: @CSTutoringDotCa and @MichelPaquette.

2 replies on “Deeper in Models”

Getting error Read section, error is
Notice: Trying to get property ‘name’ of non-object in F:\wamp64\www\Login-System-PHP-MVC-PDO\app\views\home\detail.php on line 11
Call Stack
# Time Memory Function Location
1 0.0003 362696 {main}( ) …\index.php:0
2 0.0004 362856 App->__construct( ) …\index.php:3
3 0.0005 364408 HomeController->detail( ) …\App.php:25
4 0.0033 418208 HomeController->view( ) …\HomeController.php:23
5 0.0034 418464 include( ‘F:\wamp64\www\Login-System-PHP-MVC-PDO\app\views\home\detail.php’ ) …\Controller.php:15

pelase help me on this

Hi Sudha,
Most of the time, this type of bug is caused by errors in the Model subclass written: the object returned is not an instance of a class. Have you used PDO and returned objects of an existing class by using code similar to the following?
$stmt->setFetchMode(PDO::FETCH_CLASS, ‘fully qualified class name’);

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.