Month: April 2020

Basic picture file uploads are handled on the client-side by a form and on the server-side by a script moving a cached temporary file to a permanent location while recording the appropriate path information to database. In this post, we will elaborate on each of these portions.

The HTML Form

For forms to upload files correctly, 2 requirements must be met: a named file input must be included in the form and the encoding type must be appropriately. We show below an example proper form:

First, we see on line 1 that the enctype attribute for the form is set to multipart/form-data. This encodes the submitted form data with different parts for different data, much like an email with attachments included. If the form enctype attribute to this value, the file is simply not received by the server application.

Second, we see on line 4 an input with the type attribute set to the value file. This input also has the attribute name set to newPicture so that we may refer to it by this name in the server script.

The submit URL for this form will be the same as its calling URL.

The PHP Script

Now, a server-side script must receive the form data, check the data and save the file as appropriate:

This method is meant to accept files associated to products in a product catalog. Therefore, on line 1, we need to pass in the product_id foreign key value for the picture record to refer to the appropriate product record.

On lines 2 and 3, we verify that the picture is indeed sent (line 2) to the receiving script without any error (line 3). If not, the script branches to line 27 and presents the submission form.

On lines 4 and 8, we extract image file information (line 4) that will allow us to validate (on line 8) that the received file is indeed an image. If not, an error message is sent to the user on line 9.

At line 10, the image information (from line 4) is used again against an image type whitelist (produced on lines 5-7). If the file is not of the proper type, an error message is sent to the user, on lines 11-12.

Finally, if all checks are good, the script continues on to lines 15-24. Lines 15, 16, 17 handle saving the file to the filesystem as follows:

15. Define the saving path in the OS, based on the current working directory. The file will be saved in the images folder under the server document root.
16. Define the file name to a random string with the extension from the whitelist defined on lines 5-7.
17. Move (save) the file from its temporary location to the images folder.

Lines 19-24 record the data as follows:

19. Get an instance of the Picture class.
20. Populate the product_id foreign key from the method parameter.
21. Populate the filename, since other computers don’t have access to the full path structure. We will use this filename later to display these pictures.
22. Populate the description from the form data.
23. Invoke Picture::create() to save the Picture record to the database.
24. Redirect the client side to the Product index.

Viewing the Pictures

In such an application, we simply call up all the pictures in a product detail user story with a controller method as follows:

By setting the pictures to be part of the product, we are able to pass all the data as a single object to then output the picture URLs as in the following view code:

On line 3, we output an image element that will refer to the faved file in the images folder under the Web server document root. On line 4, we also include a delete link.

Deleting the Picture

Mistakes happen and things change, so it makes sense to be able to delete pictures from our Web application. The hyperlink form the above view calls the deletePicture method from our product controller, defined as follows:

The process is simply to find the Picture record using its primary key value, delete the associated file with the unlink instruction, and delete the picture record before redirecting back the appropriate page (here we go back to the product details.

The Picture Model and Database

To support all these operations, we need a Picture model similar to the following:

This model performs operations on a Picture table with the following fields:

• picture_id: the automatically incremented integer primary key
• product_id: the foreign key to the product table
• filename: a string type to hold the short filename
• description: a text type field to hold long descriptions.

Conclusion

These general guidelines are also applicable to records that are child records of a master-detail relationship. You could change the upload types by modifying the upload whitelist and replacing getimagesize with other validation mechanisms.

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.