How to populate a database as soon as user uploads a file

Question

So Ive a code where in user uploads a video file from a certain directory.

-Now as soon as the user uploads the file, I want a table to be created with the name of the file as the name of table and exactly three columns named

ID(primary),type(varchar20), timestamp(float)

-Secondly, the page should show the table name created and there should be an option to delete the table if the user wants to.

My video upload and play code goes as below

<h1>HTML5 local video file player example</h1>
<div id="message_"></div>
<input type="file" accept="video/*"/>
<video controls autoplay></video>


<script>
(function localFileVideoPlayer() {
        'use strict'
  var URL = window.URL || window.webkitURL
  var displayMessage = function (message, isError) {
    var element = document.querySelector('#message_')
    element.innerHTML = message
    element.className = isError ? 'error' : 'info'
  }
  var playSelectedFile = function (event) {
    var file = this.files[0]
    var type = file.type
    var videoNode = document.querySelector('video')
    var canPlay = videoNode.canPlayType(type)
    if (canPlay === '') canPlay = 'no'
    var message = 'Can play type "' + type + '": ' + canPlay
    var isError = canPlay === 'no'
    displayMessage(message, isError)

    if (isError) {
      return
    }

    var fileURL = URL.createObjectURL(file)
    videoNode.src = fileURL
  }
  var inputNode = document.querySelector('input')
  inputNode.addEventListener('change', playSelectedFile, false)
})()
</script>

Answer 1

(Mercilessly stolen from php.net https://php.net/manual/en/features.file-upload.post-method.php)

<?php
$uploaddir = '/var/www/uploads/';
$uploadfile = $uploaddir . basename($_FILES['userfile']['name']);

if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile) && $_FILES['userfile']['type'] === 'image/jpeg') {
    echo "File is valid, and was successfully uploaded.\n";
} else {
    echo "Possible file upload attack!\n";
}

/*for creating table, which I don't recommend*/

$sql_query = "CREATE TABLE ".$_FILES['userfile']['name']." INT(6) AUTO_INCREMENT PRIMARY KEY,
type VARCHAR(20) NOT NULL,
timestamp FLOAT
)";
$mysqli->query($sql_query);

?>

/First half of the code, but something came up, I will be back in an hour.../

<?php
print '<form action="delete.php" method="post">
'.$_FILES['userfile']['name'].' <input type="submit" value="'.$_FILES['userfile']['name'].'" name="delete" />
</form>';
?>

<?php
$sql_query = "DROP TABLE ".$_POST['delete'];
$mysqli->query($sql_query);
?>

Instead of creating new tables for each file, I would recommend creating the table once, and insert the files like this:

<?php
$stmt = $mysqli->prepare("INSERT INTO table (name, type, timestamp) VALUES (?,?,?)");
$stmt->bind_param("ssf", $_FILES['userfile']['name'], $_FILES['userfile']['name'], strtotime(date("Y-m-d H:i:s")));
$stmt->execute();
?>

Similarly, if you want to delete:

<?php
$stmt = $mysqli->prepare("DELETE FROM table WHERE name = ?");
$stmt->bind_param("s",$_POST['delete']);
$stmt->execute();
?>

Or if you know the id, you can delete it by it instead (is safer, because multiple files can own the same name, but id must be unique).

Answer 2

Here is the big-picture overview of what you might wish to do.

1. Identifying who uploaded what

You may want each uploader to be able to request a list of his uploaded files and, optionally, to remove one or more. Or, you might want to be able to list all uploaded videos, grouping them by the uploader.

For this, you want a login system. Here is a post that links to a couple of videos that take you through that process.

2. Providing an upload system (client side)

You can write this from scratch, as you are doing, or you can use an already-invented wheel (rather than re-inventing it yourself). This jQuery plugin is awesome - looks world-class and works perfectly. I have used it on dozens of projects. Plus, it comes with example code for both server-side and client-side. Take a look.

3. Managing the uploaded files (server side)

On the server side, you might want to organize the uploaded files to some degree. There is no need to create much of a folder structure on the server since you have a database table to keep track of things, but at the least you might want to put them into a folder called "uploads" or "videos" or etc - just so that they are not stored in with the HTML files.

4. Adding each uploaded video to a MySQL (now called MariaDB) table

If you use the jQuery File Upload plugin, you will already have a file that handles that back-end receipt of the uploaded file. It is in this file that you write the code to also post the data into the database.

Look at this spot the docs for the jQuery file upload plugin. The plugin allows you to go fetch some additional data from the page (for example, you might have fields that ask the user for a tag, or a drop-down that lets users select a category) and you want to also insert those selections into the database when you upload the file. This is how you do that.

5. Getting the list of videos from the MySQL table and listing them on a webpage

This is a simple matter of querying the database, creating some HTML code in a PHP variable, and then later outputting the PHP variable containing that HTML at the appropriate place in the web page. Personally, I prefer to keep as much of my PHP as possible at the top of the page - create the strings containing any PHP output - and then echo the HTML code at the appropriate place. Keeps the file tidy and easy to read.

This youtube video will help you to grasp how to do that. Also see this post and this one.

Some notes to keep in mind:

a. You must name all of your web page files to end in .php instead of .html. There is almost no difference in how the pages will work - the sole difference is that any pages ending in .php can now process PHP code. Otherwise, they are identical. In fact, you can rename them now - try it - and they will work the same.

b. PHP code can be inserted anywhere in your web page -- you just place the PHP code between PHP_Start and PHP_END tags - it looks like this:

<?php
    $myvar = 'Bob';
?>

c. PHP is very unforgiving about missing end-of-statement semi-colons. If you forget one, the PHP script will abort at that spot with no notification This makes it a bit tricky to work with PHP. See this answer for a couple of suggestions.

d. All PHP variables begin with a dollar sign. Weird, but true. Use the echo command to output PHP strings to the screen (for example, a PHP string containing HTML will display that HTML on the screen).

e. The PHP $_SESSION is super-useful. It is not difficult - it is just a way to store variables on the server. Very useful for things like remembering if someone has logged in, remembering data about that user that is stored in a MySQL table, etc.

Good luck with your project.