PHP Lessons - Lesson 3.5 - Working with MySQL DB. JOIN operator. Uploading files to the server.

Before I started writing this lesson, I thought for a long time about the best way to present queries using the JOIN operator. The point is that the JOIN operator is used to retrieve data from multiple tables at once. And since we need another table, let's create one. I suggest creating a table for files, which we will upload through a form in this lesson. This way, the lesson will combine two directions: working with databases and working with forms.

Let’s start by adding a field to upload a file. For the form to support file uploads, you need to set the form’s type in its parameters:

$content .= '
';

With the enctype parameter, we tell the browser that files will be uploaded through this form. Now that the form is set up, let’s add the file upload input:

public function display_admin() { // method for entering a message
  $content = '';

  $content .= '
';
  $content .= 'Name:
';
  $content .= '';
  $content .= '
';
  $content .= 'Message:
';
  $content .= '';
  $content .= ''; // file upload input
  $content .= '
';
  $content .= '';
  $content .= '
';
  $content .= '
Return to main page
';

  return $content;
}

With the file-type input, we’ll be uploading our file. After saving and refreshing the message addition page, a file upload field should appear.

Now let’s see how the file is passed through the POST request:

public function write($p) {
  print_r($p); // print the form array
  print_r($_FILES); // print the files array
  $sql = 'INSERT INTO Messages (title, bodytext, created) VALUES ("' . $p["title"] . '", "' . $p["bodytext"] . '", ' . time() . ')';
  return mysql_query($sql);
}

Now try uploading a file and submitting the message. All uploaded files are available in the superglobal variable $_FILES. Here’s what I got:

Array (
  [title] => asfasdf
  [bodytext] => asfasdf
)
Array (
  [filename] => Array (
    [name] => ip.txt
    [type] => text/plain
    [tmp_name] => Y:\tmp\phpAA.tmp
    [error] => 0
    [size] => 13
  )
)

Now that we have both the form and file data, let’s create a Files table to store uploaded files. First, create a files folder in the root of your site. If you're using a Linux OS or hosting, assign 777 permissions so the script can write to this folder.

Now modify the buildDB() method to create the updated tables:

$sql = "CREATE TABLE Messages (
  mid int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(mid),
  title varchar(15),
  bodytext text,
  created int(11),
  file int(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE Files (
  fid int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(fid),
  filename varchar(255),
  filepath varchar(255),
  filemime varchar(255),
  filesize int(10),
  timestamp int(10)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;";

We’ve added a fid field to the Messages table, which will be a foreign key to the Files table. Fields in Files:

• fid – primary key.
• filename – the name of the file.
• filepath – the file path from the site root.
• filemime – MIME type of the file.
• filesize – size in bytes.
• timestamp – time of upload.

Now let's write the file saving logic and insert into the Files table:

if ($_FILES["filename"]["size"] > 1024*3*1024) {
  echo ("File size exceeds 3MB");
  exit;
}
if (is_uploaded_file($_FILES["filename"]["tmp_name"])) {
  move_uploaded_file($_FILES["filename"]["tmp_name"], "files/" . $_FILES["filename"]["name"]);
} else {
  echo("File upload error");
}

And now insert the file into the database:

$sql = 'INSERT INTO Files (filename, filepath, filemime, filesize, timestamp)
VALUES ("' . $_FILES['filename']['name'] . '",
"files/' . $_FILES['filename']['name'] . '",
"' . $_FILES['filename']['type'] . '",
' . $_FILES['filename']['size'] . ',
' . time() . ')';

mysql_query($sql);

Then insert the message and reference the file using LAST_INSERT_ID():

$sql = 'INSERT INTO Messages (title, bodytext, created, fid) VALUES ("' . $p["title"] . '", "' . $p["bodytext"] . '", ' . time() . ', LAST_INSERT_ID())';

LAST_INSERT_ID() returns the ID of the last inserted row, in our case – the file.

Now let’s update the message display to use JOIN and show the file name:

public function display_public() {
  $content = '';
  $sql = 'SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid ORDER BY mid DESC';
  $result = mysql_query($sql) or die(mysql_error());

The LEFT JOIN returns all messages, even if they don’t have a file. Print the result to verify:

while ($row = mysql_fetch_array($result)) {
  print_r($row);

You’ll see data from both Messages and Files. If there's no file, file fields will be empty. So check them like this:

if (!empty($row['filename'])) {
  $content .= '
Attachment: ' . $row['filename'] . '
';
}

This will show the attachment as a download link. In the next lesson, we’ll take a deeper look at the powerful JOIN operator for querying multiple tables at once. I’ll also start attaching an up-to-date database dump exported via phpMyAdmin.