MySQL report


Author: Philip Clarke
License: FPDF


This script is a variation on Table with multi-page columns. The difference is that when a query is executed initially the php loops through, gathers the string widths and then resizes the columns to try and fit everything onto the page. If a column never uses the maximum width alloted to it, the column is sized down and the saved space is re-assigned to the other columns. The aim of this was to be able to get out a good looking mysql report using as few a lines as possible, the disadvantage is that because the query has to loop through the results to work out the column sizes, it takes a bit of time. To counteract this, there is a dump option, that dumps out the php required to set up column widths, so one could check with a small report that the columns looked okay, then apply the dumped column widths to a larger report.

New version (29/01/2015)

Now issued under the CC share-a-like attribution licence 4.0 (use it freely for any purpose just mark up your changes which is basically common sense as it makes bug fixing easier).

Can now use multiple SQL statements to get report tables on one page, e.g. older version would do "November Sales Report", then another PDF would need to be generated for the next Sales report (typically by a posted SQL query or cron job saving to different file), whereas now you can put all your sales reports into one page using multiple SQL queries (still recommend running the report on a cron job and outputting it, but altering the PHP so that one does not even need to post data to create the reports).

After filling out your FPDF library and DB details, then adding a SQL statement, this script outputs a PDF. It has been running reliably since 2003 with minor changes, and this version is the first major alteration where it now uses the mysqli functions. The file ex.php contains lots of comments to help with creating your own reports. To create your own reports I recommend just taking ex.php renaming it, filing in the details, removing all except one the examples, which you use as a template and running it on your server.

Also included now at the top of ex.php are ini_set statements to turn on and off error_reporting and display_errors in the file itself to aid you in your debugging (nothing worse that a blank page with no info because of your server settings ;-). Over the years the most common bugs have been

The most common question has been "how do I change the column titles", that's simple, it's done in SQL and is very easy with an example now in ex.php.

Large sums of money deposited in Swiss bank accounts/ blank signed cheques made out to cash; always appreciated...

-- thank you, Philip Clarke



# Copyright (c) Bouncing Ltd 2003-2016
# Author Philip Clarke
# Released under the CC Attribution 4.0 licence
# You may do with it as you please just keep the credits. If you change something note it down for your own good
# This Version released 12/11/2016 (keep in as helps with bug fixes)

# mysql_report is now mysqli_report PHP 5+ compatible
# General Principle for setting up.
# Get the mysql_report and fpdf libraries loaded
# Set the page side (although pdf's tend to scale well)
# add database connection details
# add report title
# Add SQL statement (it is sanitized in mysql_report but take precautions with any user input)
# Output PDF (lots of people forget this and then wonder why the page is blank).

// you may need to change mysql_report.php to find the fpdf libraries

// the PDF is defined as normal, in this case a Landscape, measurements in points, A3 page.
$pdf = new PDF('L','pt','A3');

// change the below to establish the database connection.
$host = 'localhost';
$username = 'root';
$password = 'default';
$database = 'mysql';

// should not need changing, change above instead.
$pdf->connect($host, $username, $password, $database);

// attributes for the page titles
$attr = array('titleFontSize'=>18, 'titleText'=>'First Example Title.');

# Example SQL Statements
# Normally one would have 1 SQL statement and generate the report, e.g. a weekly sales breakdown
# mysql_report can now produce more than one SQL statement in the report, so one could do a 
# more complex set of tables like monthly reports using differing SQL
# Examples are from the mysql table. The tables are generated and then outputted.

/* Multiple SQL tables will merge into 1 numbered PDF */

/* Example 1: multiple page table full width table */
// SQL statement
$sql_statement = "SHOW VARIABLES" ;

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/* Example 2: single page small non-full width table, mysql_report chooses not to spread table out */
// SQL statement
$sql_statement = 'SHOW TABLES';

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/* Example 3: Changing Title mid-report. Single page table more columns, at A3 page size still does not spread out */
/* if titles are same font size you can change them per table */
// SQL statement
$attr = array('titleFontSize'=>18, 'titleText'=>'Second Example Title.');
$sql_statement = 'DESCRIBE user';

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/* Example 4: Using SQL to change column headings */
#!!! Careful what you publish ;-0 !!!#
// SQL statement
$sql_statement = "SELECT Host as `Hostname of Mysql Server`, User as `Username extended to widen table by using SQL statment`, Password as `Some Hashed passwords` FROM user ORDER BY user";

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/* Example 5: Showing what happens when no rows are returned, column headers are still printed */
// SQL statement
$sql_statement = "SELECT * FROM user JOIN tables_priv ON user.User=tables_priv.User ORDER BY user.User" ;

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/* Example 6: Same report as above but set up to output rows using a LEFT JOIN and SQL to improve the layout */
// SQL statement
$sql_statement = "SELECT user.Host, user.User, user.Password, Select_priv as `Select priv`, Insert_priv as `Insert Priv`, Update_priv as `Update priv`, Delete_priv as `Delete priv`, Create_priv as `Create priv`, Reload_priv as `Reload priv`, Shutdown_priv as `Shutdown priv`, Process_priv as `Process priv`, File_priv as `File priv`, Grant_priv as `Grant priv`, References_priv as `References priv`, Index_priv as `Index priv` FROM user LEFT JOIN tables_priv ON user.User=tables_priv.User ORDER BY user.User" ;

// Generate report
$pdf->mysql_report($sql_statement, false, $attr );

/*!!! Very Important: after having done all the work of 
  setting up the SQL don't forget to output the PDF else
  you just get a blank page !!!*/


/* ADVICE do not use a PHP closing tag like  ?> */
View the result here.