<?php
/**
* Christophe Avonture
* php version 8.1
*
* @package Avonture/joomla_Show_Table
*
* @author Christophe Avonture <christophe@avonture.be>
* @copyright 2015-2020 (c) Christophe Avonture
* @license MIT
*
* Written date : 2016-10-16
* Last modified : 2024-04-24
*
* ! DEFAULT PASSWORD IS "Joomla". See constant PASSWORD below !
*
* Changes
* -------
* 2017-10-04 - Add export buttons (use https://datatables.net/ and no more table sorter)
*
* 2020-05-28 - Update dependencies versions (by Dechèvre Marc - https://www.woluweb.be)
* - datatables (js and css): 1.10.21
* - datatables buttons (js and css): 1.6.2
* - pdfmake: 0.1.62
* - jquery: 3.5.1
*
* 2020-11-21 - Refactoring
*
* 2024-04-24 - PHP 8.2.18 / Joomla 5
* - datatables (js and css): 2.0.5
* - datatables buttons (js and css): 3.0.2
* - pdfmake: 0.0.10
* - jquery: 5.3.2
*
* 2024-06-02 - Remove warning using null and md5
* - Remove thead & tfoot when format is raw
*
* Description
* -----------
* This small script will execute a SQL statement against the database
* of your Joomla website and will show the result in a nice HTML
* table (bootstrap).
* When the output is HTML, the DataTables plugin will be used to
* provide extra functionalities like sorting and filtering.
*
* Parameters :
*
* * password : the password define in the PASSWORD constant.
*
* * format : can be 'HTML' (default) or 'RAW'
* RAW will only output a table tag without html headers
* or javascript. RAW will be useful when f.i. the table
* will be used in a spreadsheet application or as input for
* an another program.
* For instance : in Excel, you can create a Data Query.
* Use the &format=RAW parameter to get a perfect table for Excel.
*
* Add yours : Add your own parameters !
* For instance a filter (period=xxxx), a selection (tablename=a_table),
* a limit (limit=10), ...
*
* Example : https://youriste/show_table.php?password=Joomla&format=RAW
*/
namespace Avonture;
// phpcs:disable PSR1.Files.SideEffects
// This is an example : this SQL will retrieve all users defined in your
// database and will return ID, name, pseudo, email, register date, last visit
// date and the group of the user (registered, super-users, ...)
\define(
'SQL',
'SELECT U.id UserID, U.name Name, U.username UserName, ' .
'U.email eMail, U.registerDate RegisterDate, ' .
'U.lastvisitDate LastVisitDate, G.title GroupTitle ' .
'FROM `#__users` U ' .
'LEFT JOIN (`#__user_usergroup_map` as UG) ON UG.user_id=U.id ' .
'LEFT JOIN (`#__usergroups` as G) on UG.group_id=G.id ' .
'ORDER BY registerDate DESC, name, GroupTitle ASC'
);
// SQL statement for retrieving information from, f.i., the content table
/*
define(
'SQL',
'SELECT C.id As Article_ID, C.title As Article_Title, '.
'G.title As Category_Title, '.
'U.name As Author_Name, C.Hits As Hits, C.language As Language, '.
'C.created As Writen_Date '.
'FROM `#__content` C LEFT JOIN `#__categories` G ON C.catid = G.id '.
'LEFT JOIN `#__users` U on C.created_by=U.id '.
'WHERE (state=1) '.
'ORDER BY C.created DESC'
);
*/
/**
* Run a SQL query against the Joomla database and display the result
* as a HTML table with or without layout and extra features like
* filtering, sorting, ...
*/
class ShowTable
{
/**
* Title of the page (heading 1)
*
* @var string
*/
public const TITLE = 'Example of Show_Table';
/**
* Enable/disable debug mode
*
* @var boolean
*/
public const DEBUG = true;
/**
* OS Directory separator
*
* @var string
*/
public const DS = DIRECTORY_SEPARATOR;
/**
* Password to use. The default one is "Joomla"
*
* If you want to change, use an online tool like f.i. http://www.md5.cz/
*
* @var string
*/
public const PASSWORD = '57ac91865e5064f231cf620988223590';
/**
* Root folder of Joomla. If you've save this script in the root
* folder of Joomla, just leave __DIR__ otherwise you'll need
* to update this constant and specify your own root
*
* You can manually force a folder like f.i. "C:\Christophe\Sites\beta"
*
* @var string
*/
public const ROOT = __DIR__;
/**
* Desired output format
*
* @var string f.i. "html" or "raw"
*/
private $format = '';
/**
* Constructor.
*/
public function __construct()
{
// Enable/disable debug mode
$this->debugMode();
// Die if the password isn't supplied
$this->checkPassword();
// Die if no configuration.php file found
$this->checkConfiguration();
// Load Joomla framework
$this->loadConfiguration();
// Get the requested format : HTML or RAW.
// If nothing is specified, HTML will be the default one
$this->setFormat((string) \filter_input(INPUT_GET, 'format', FILTER_UNSAFE_RAW));
}
/**
* Enable/disable debug mode
*
* @return void
*/
private function debugMode(): void
{
if (self::DEBUG) {
\ini_set('display_errors', '1');
\ini_set('display_startup_errors', '1');
\ini_set('html_errors', '1');
\ini_set('docref_root', 'http://www.php.net/');
\ini_set(
'error_prepend_string',
'<div style=\'color:red; \'font-family:verdana; border:1px solid red; padding:5px;\'>'
);
\ini_set('error_append_string', '</div>');
\error_reporting(E_ALL);
} else {
\ini_set('error_reporting', strval(E_ALL & ~E_NOTICE));
}
}
/**
* Add CSS to the page.
*
* @return string
*/
public function addCSS(): string
{
$script = '';
if ('HTML' === $this->getFormat()) {
$arr = [
'https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css',
'https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css',
'https://cdn.datatables.net/2.0.5/css/dataTables.bootstrap5.min.css',
'https://cdn.datatables.net/buttons/3.0.2/css/buttons.bootstrap5.min.css'
];
foreach ($arr as $style) {
$script .= "<link rel='stylesheet' href='" . $style . "' " .
"rel='stylesheet' media='screen' />\n";
}
}
return $script . "\n";
}
/**
* Add JS to the page.
*
* @return string
*/
public function addJS(): string
{
$script = '';
if ('HTML' === $this->getFormat()) {
$arr = [
'//cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js',
'//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/js/bootstrap.min.js',
'//cdn.datatables.net/2.0.5/js/dataTables.min.js',
'//cdn.datatables.net/2.0.5/js/dataTables.bootstrap5.min.js',
'//cdn.datatables.net/buttons/3.0.2/js/dataTables.buttons.min.js',
'//cdn.datatables.net/buttons/3.0.2/js/buttons.flash.min.js',
'//cdn.datatables.net/buttons/3.0.2/js/buttons.print.min.js',
'//cdn.datatables.net/buttons/3.0.2/js/buttons.html5.min.js',
'//cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js',
'//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.10/pdfmake.min.js',
'//cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.10/vfs_fonts.js',
];
foreach ($arr as $js) {
$script .= "<script type='text/javascript' src='" . $js . "'></script>\n";
}
// Initialize scripts
$script .= "<script type='text/javascript'>\n" .
"$(document).ready(function () {\n" .
" // Setup - add a text input to each footer cell\n" .
" $('#tbl tfoot th').each( function () {\n" .
" $(this).html('<input type=\"text\" placeholder=\"Search\" />');\n" .
" });\n" .
" \n" .
" $('#tbl').DataTable({\n" .
" 'scrollY': '60vh',\n" .
" 'scrollX': '100%',\n" .
" 'scrollCollapse': true,\n" .
" 'info': true,\n" .
" 'fixedHeader': true,\n" .
" 'dom' : 'Bfrtip',\n" .
" 'buttons' : ['copy', 'csv', 'excel', 'print'], \n" .
" 'lengthMenu': [ \n" .
" [25, 50, 100, 500, -1], \n" .
" [25, 50, 100, 500, 'All'] \n" .
" ] \n" .
" });\n" .
" \n" .
" // Apply the search\n" .
" var tbl = $('#tbl').DataTable();\n" .
" tbl.columns().every(function(){\n" .
" var that = this;\n" .
" $('input', this.footer()).on('keyup change', function(){\n" .
" if (that.search() !== this.value) {\n" .
" that.search(this.value).draw();\n" .
" }\n" .
" });\n" .
" });\n" .
"});\n" .
'</script>';
}
return $script;
}
/**
* Generate the output table
*
* @return string The HTML table
*/
public function outputTable(): string
{
$rows = self::getRows();
$return = '';
$table = '';
if (\count($rows) > 0) {
// Output the table
$table = '<table id="tbl" class="display compact nowrap order-column">';
// Output the list of fields name
$line = '';
foreach ($rows[0] as $field => $value) {
$line .= '<th>' . $field . '</th>';
}
if ('HTML' === $this->getFormat()) {
$table .=
'<thead><tr>' . $line . '</tr></thead>' .
'<tfoot><tr>' . $line . '</tr></tfoot>';
}
$table .= '<tbody>';
foreach ($rows as $row) {
$line = '';
foreach ($row as $value) {
$line .= '<td>' . $value . '</td>';
}
$table .= '<tr>' . $line . '</tr>';
}
$table .= '</tbody></table>';
}
$return = $table;
if ('HTML' === $this->getFormat()) {
// Get a few information
$infos = '<p><strong>Number of records : ' .
\number_format(\count($rows)) . '</strong></p>';
$sTitle = \trim(self::TITLE);
if ('' !== $sTitle) {
$sTitle = '<h1>' . $sTitle . '</h1>';
}
$return = '<div style="margin:10px;">' . $sTitle . $table . $infos . '</div>';
}
return $return;
}
/**
* Run the query and return the record set.
*
* @return array
*/
public static function getRows(): array
{
$rows = [];
try {
$db = \Joomla\CMS\Factory::getDBO();
$db->setQuery(SQL);
$rows = $db->loadObjectList();
} catch (\Exception $exception) {
echo $exception->getMessage();
}
return $rows;
}
/**
* Check if the password is valid; if not, stop immediately.
*
* @SuppressWarnings(PHPMD.Superglobals)
*
* @return void
*/
private function checkPassword(): void
{
// Get the password from the query string
$password = \filter_input(INPUT_GET, 'password', FILTER_UNSAFE_RAW) ?? '';
if (self::PASSWORD !== \md5($password)) {
header('HTTP/1.0 403 Forbidden');
echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="GET">' .
'Password: <input type="text" name="password" />' .
'<input class="Submit" type="submit" name="submit" /></form>';
die();
}
}
/**
* Die if no configuration.php file found.
*
* @return void
*/
private function checkConfiguration(): void
{
if (!\file_exists($config = \rtrim(self::ROOT, self::DS) . self::DS . 'configuration.php')) {
die(
'<strong>The file ' . $config . ' can\'t be found, please review ' .
'the ROOT constant to match your website root folder</strong>'
);
}
}
/**
* Load Joomla framework.
*
* @SuppressWarnings(PHPMD.NPathComplexity)
*
* @return void
*/
private function loadConfiguration(): void
{
/* Initialize Joomla framework */
define('_JEXEC', 1);
// Load system defines
if (file_exists(self::ROOT . '/defines.php')) {
include_once self::ROOT . '/defines.php';
}
if (!defined('_JDEFINES')) {
define('JPATH_BASE', self::ROOT);
include_once JPATH_BASE . '/includes/defines.php';
}
include_once JPATH_BASE . '/includes/framework.php';
/* Create the Application */
$container = \Joomla\CMS\Factory::getContainer();
$container->alias('session.web', 'session.web.site')
->alias('session', 'session.web.site')
->alias('JSession', 'session.web.site')
->alias(\Joomla\CMS\Session\Session::class, 'session.web.site')
->alias(\Joomla\Session\Session::class, 'session.web.site')
->alias(\Joomla\Session\SessionInterface::class, 'session.web.site');
// Instantiate the application.
$app = $container->get(\Joomla\CMS\Application\SiteApplication::class);
// Load the extension Namespaces
\JLoader::register('JNamespacePsr4Map', JPATH_LIBRARIES . '/namespacemap.php');
$extensionPsr4Loader = new \JNamespacePsr4Map();
$extensionPsr4Loader->load();
// Set the application as global app
\Joomla\CMS\Factory::$application = $app;
}
/**
* Desired output format
*
* @param string $format f.i. "HTML" or "RAW"
*
* @return void
*/
public function setFormat(string $format = 'HTML'): void
{
if ('' === $format) {
$format = 'HTML';
}
$this->format = \strtoupper($format);
if (!\in_array($this->format, ['HTML', 'RAW'])) {
$this->format = 'HTML';
}
}
/**
* Get the desired output format
*
* @return string f.i. "html" or "raw"
*/
public function getFormat(): string
{
return $this->format;
}
}
$showTable = new ShowTable();
?>
<!DOCTYPE html><html lang="en">
<head>
<meta charset="utf-8"/>
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=9; IE=8;" />
<?php echo $showTable->addCSS(); ?>
</head>
<style>
#tbl {margin-left : 0px ;}
</style>
<body>
<?php echo $showTable->outputTable(); ?>
<?php echo $showTable->addJS(); ?>
</body>
</html>