In the first two parts of this tutorial (Part 1, Part 2), you learned how to create a web camera stream on your PC, accept it on the remote location and display it on a web page. The third part of the tutorial will show you how to secure, store and display the data.
Secure Web Login
An application with sensitive data, such as a video surveillance application, needs secure website login. The simplest and fastest way to implement secure user authentication is to use Laravel PHP framework, which has authentication functions built-in and implements all important security techniques. Their documentation is pretty straightforward, and you can read it here. If you would like to read more details about what those techniques are, read Tips for Ensuring Secure Web Login.
Storing the Data
To store and display the video surveillance data, we need to create a MySQL database first. Database architecture would look like this:
CREATE TABLE IF NOT EXISTS 'video' ( 'video_id' INT(10) NOT NULL AUTO_INCREMENT, 'video_path' VARCHAR(255) NOT NULL, 'start_time' INT(10) NOT NULL, 'end_time' INT(10) NOT NULL, PRIMARY KEY ('video_id')ENGINE = MyISAMCREATE TABLE IF NOT EXISTS 'image' ( 'image_id' INT(10) NOT NULL AUTO_INCREMENT, 'image_path' VARCHAR(255) NOT NULL, 'image_time' INT(10) NOT NULL PRIMARY KEY ('image_id')ENGINE = MyISAMWe would use two database tables—one for storing videos and the second one for storing images from camera (useful for motion detection setup).
Display All Videos and Images
After the database is created, the data is shown by executing SQL queries from PHP. It is recommended to use the MySQLi database driver for all database operations. If you’re using Laravel framework, MySQLi is already implemented—just use the framework’s database functions.
Here is a simple query that would select all videos from the database:
connect_error) { trigger_error('Database connection failed: ' . $db->connect_error, E_USER_ERROR); } // SQL query $sql = "SELECT * FROM videos"; // Fetch all videos as an array $query = $db->query($sql); $result = $query->fetch_all(MYSQLI_ASSOC);?> Since video surveillance would generate a lot of data, it would be a good solution to use pagination. So, the code with pagination would be:
connect_error) { trigger_error('Database connection failed: ' . $db->connect_error, E_USER_ERROR); } $per_page = 20; // Change to any number you like $page = $_GET['page']; // Validate page number If(empty($page) || !ctype_digit($page)) { die("Page number is not valid.");} $offset = ($page - 1) * $per_page; // SQL query $sql = "SELECT * FROM videos LIMIT {$offset},{$per_page}"; // Fetch all videos as an array $query = $db->query($sql); $result = $query->fetch_all(MYSQLI_ASSOC);?> The page number is passed as $_GET superglobal variable, meaning that it would be fetched from the URL. For example, the URL http://www.example.com/videos.php?page=2 would open the second page with videos. Since it is possible to manually change the page number in the URL, we have to validate it before using it in the query. So, if page number is empty or if it is not a number, the error is shown and none of the code below is executed. In production environment, use exceptions instead of the die function for error handling in PHP.
The code from above would be the same for images—we would just change the name of the database table to “images”.
Find a Video or an Image by Time
All dates in the database are stored as UNIX timestamps—number of seconds since January 1970. This is convenient for operations with the dates, since they are represented as 10-digit integers. Let’s see it in practice—the following code would select all videos from yesterday:
connect_error) { trigger_error('Database connection failed: ' . $db->connect_error, E_USER_ERROR); } $now = time(); $yesterday = time() - 86400; // There are 86400 seconds in a day // Convert UNIX timestamp to readable date format $yesterday_date = date("Y-j-n", $yesterday); // This array would hold 3 elements - year, month and day $e = explode("-",$yesterday_date); $start_time = mktime(0,0,0,$e[2],$e[1],$e[0]); // First second of the yesterday's day $end_time = mktime(23,59,59,$e[2],$e[1],$e[0]); // Last second of the yesterday's day // SQL query $sql = "SELECT * FROM videos WHERE start_time BETWEEN '{$start_time}' AND '{$end_time}' OR end_time BETWEEN '{$start_time}' AND '{$end_time}'"; // Fetch all images as an array $query = $db->query($sql); $result = $query->fetch_all(MYSQLI_ASSOC);?> This code would select all videos that either started or ended yesterday. The PHP code for selecting all images is similar, but slightly different:
connect_error) { trigger_error('Database connection failed: ' . $db->connect_error, E_USER_ERROR); } $now = time(); $yesterday = time() - 86400; // There are 86400 seconds in a day // Convert UNIX timestamp to readable date format $yesterday_date = date("Y-j-n", $yesterday); // This array would hold 3 elements - year, month and day $e = explode("-",$yesterday_date); $start_time = mktime(0,0,0,$e[2],$e[1],$e[0]); // First second of the yesterday's day $end_time = mktime(23,59,59,$e[2],$e[1],$e[0]); // Last second of the yesterday's day // SQL query $sql = "SELECT * FROM images WHERE image_time BETWEEN '{$start_time}' AND '{$end_time}'"; // Fetch all images as an array $query = $db->query($sql); $result = $query->fetch_all(MYSQLI_ASSOC);?> You should now be able to create a web camera stream on your PC, accept it on the remote location and display it on a web page and be able to secure, store and display the required data.
