Statistics Reporter PHP-MySQL Project

Statistics reporter is a database management software developed using PHP as front-end programming tool and MySQL as the back-end database. This application makes it easier to manage and analyze total records/data saved in the system database, total number of tables present in the database, and total space required to store the records.

This PHP mini project consists of four different source codes or PHP files. The purpose of these four files along with the source code is briefly discussed below. You can also download the source code from the link below.

Download Statistics Reporter PHP-MYSQL Project with Source Code

[sociallocker]

Download Statistics Reporter PHP-MYSQL Project with Source Code

[/sociallocker]

About Statistics Reporter Software:

Database is used by many system software and application, primarily to store data. And, there are different types of databases based on project requirement. Two mostly used databases are MySQL and Oracle.

These databases are used for different purposes such as: creating tables, updating records to tables, modifying them, searching records within the system, and deleting existing records. The proposed statistics reporter mini project uses MySQL as the database, and is implemented with all these features.

The main objectives of statistics reporter software are:

1. analyze all the records saved in the software database
2. analyze and manage total number of tables updated and modified in the database
3. manage total space of the database to store the data

The source code in this project consists of four PHP files. These codes are briefly introduced below:

1. Database Connection: The code here consists of info such as username and password, server, database, and database link, that are used to connect to database.

<?php
/**** MySQL Stat Reporter ****/
/** DB connect **/
/**** Tyler Normile ****/  
$server = "SERVER";
$username = "USERNAME";
$password = 'PASSWORD';
$database = "DB_NAME";
$dblink = mysqli_connect($server,$username,$password,$database);

/* check connection */
if (mysqli_connect_errno())     {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
}
?>

2. Database Statistics: This source code categorizes all the tables present in the database and sorts them by using columns.

#!/usr/local/bin/php
<?php
/**** MySQL Stat Reporter ****/
/** DB stats for total records categorized and sorted by specific column **/
/**** Tyler Normile ****/ 

/* GLOBAL Params */
set_time_limit(0);
ini_set('memory_limit', '-1');
ini_set('display_errors',true);
date_default_timezone_set('America/New_York');
include '/var/www/path/to/file/conn.php';

$query = "SELECT * FROM `TABLE_NAME`";

// categorized by `source` column as axample
if ($result = mysqli_query($dblink, $query)) {
        while ($row = mysqli_fetch_assoc($result)) {
        $source = $row['Source'];
        $srcArray[] = $source;
        }
        $count = count($srcArray);
        echo date("m/d/Y h:i:s a", time()). "\n";
        echo 'Total Records: '. $count. "\n";
        asort($srcArray);
        print_r(array_count_values($srcArray));
}
mysqli_free_result($result);
mysqli_close($dblink);
?>

3. Image Check: It checks empty rows in the database.

#!/usr/local/bin/php
<?php 
/**** MySQL Stat Reporter ****/
/** Checks for empty rows in DB for specific column **/
/**** Tyler Normile ****/ 

/* File handlers */
fclose(STDIN);
fclose(STDOUT);
fclose(STDERR);
$STDIN = fopen('/dev/null', 'r');
$STDOUT = fopen('/var/www/path/to/file/imageCheck.log', 'wb');
$STDERR = fopen('/var/www/path/to/file/error.log', 'wb');

/* GLOBAL Params */
set_time_limit(0);
ini_set('memory_limit', '-1');
ini_set('display_errors',true);
date_default_timezone_set('America/New_York');
include '/var/www/path/to/file/conn.php';

// `Image` column used as an example
$query = "SELECT * FROM `TABLE_NAME` WHERE `Image` =''";

if ($result = mysqli_query($dblink, $query)) {
	while ($row = mysqli_fetch_assoc($result)) {
	$source = $row['Source'];
	$srcArray[] = $source;
	}	
	$count = count($srcArray);
	echo date("d")."\n";
	echo date("m/d/Y h:i:s a", time()). "\n";	
	echo 'Empty Images: '. $count. "\n";
	print_r(array_count_values($srcArray));
}
mysqli_free_result($result);
mysqli_close($dblink);
?>

4. Today Stats: This file analyzes total database used by the system and provides a report.

#!/usr/local/bin/php
<?php
/**** MySQL Stat Reporter ****/
/** Checks todays DB stats for reporting **/
/**** Tyler Normile ****/ 

/* File handlers */
fclose(STDIN);
fclose(STDOUT);
fclose(STDERR);
$STDIN = fopen('/dev/null', 'r');
$STDOUT = fopen('/var/www/path/to/file/dailyTotal.log', 'wb');
$STDERR = fopen('/var/www/path/to/file/error.log', 'wb');

/* GLOBAL Params */
set_time_limit(0);
ini_set('memory_limit', '-1');
ini_set('display_errors',true);
date_default_timezone_set('America/New_York');
include '/var/www/path/to/file/conn.php';

// using `CreateDate` column as a point of reference example
$query = "SELECT * FROM `TABLE_NAME` WHERE `CreateDate` >= now() - INTERVAL 1 DAY;";

// using `Source` column as an example to sort data
if ($result = mysqli_query($dblink, $query)) {
        while ($row = mysqli_fetch_assoc($result)) {
        $source = $row['Source'];
        $srcArray[] = $source;
        }
        $count = count($srcArray);
        echo date("m/d/Y h:i:s a", time()). "\n";
        echo 'Today\'s Records: '. $count. "\n";
	asort($srcArray);
	print_r(array_count_values($srcArray));
}
mysqli_free_result($result);
mysqli_close($dblink);
?>

All these source code files are available in the download link. You can find more PHP projects here.