Published under the Creative Commons Licence
by Artful Software Development
artfulsoftware.com
Version 2.5.8, 15 July 2024
TheUsual/PHP exists to provide a fast, free, user-friendly web browser viewing and maintenance interface for MySQL databases. It runs on your own computer under a web server like Apache or IIS, on a dedicated intranet or internet server, or on a remote hosting server (dedicated or shared) running MySQL 5 or up and PHP 5.5 or up.
There is one current version of TheUsual/PHP, for the mysqli PHP API. (The mysql PHP API is disappearing from PHP; the last version of TheUsual for the mysql PHP API was 1.90.)
TheUsual implements the Artful concept of TheUsual—a clean, efficient, intuitive web interface for doing the jobs you usually have to do to create, update, populate, maintain and query databases, their objects and tables and data.
Chapter 12 of Get it Done with MySQL 5&Up, excerpted here, outlines the basic design and approach.
To download TheUsual/PHP, visit https://artfulsoftware.com/theusual.html and scroll to the bottom of the page.
TheUsual/PHP requires:
Pre-5 PHP used MySQL the version 3.x client module. This is no longer supported.
The mysql PHP API is obsolete. The last upgrade of TheUsual/PHP for that API, theUsualPHPmysql.rar, was version 1.90. Use the mysqli API instead, and the version of theUsual for that API, theUsualPHPmysqli.rar.
PHP and MySQL authentication: Since v5.5, MySQL has supported
authentication plugins, and version 8.0 has made caching sha2_password
pluggable authentication the default, but ...
caching_sha2_password
or the MySQL 8.0
default plugin,default_authentication_plugin
to
mysql_native_password
, so these PHP versions can't use
caching_sha2_password
,caching_sha2_password
and for the MySQL 8.0
default authentication plugin auth_plugin_caching_sha2_password
remains incomplete,mysql.user.plugin
to
mysql_native_password
. In any PHP version,
phpinfo.php (or the function phpinfo()
) lists
supported authentication plugins under "Loaded plugins" in entries
whose names begin with auth_plugin
.
Users of TheUsual are responsible for acquiring and remembering their MySQL login credentials. It's not in TheUsual's brief to intrude on MySQL user access management; TheUsual's job is to remember securely hashed passwords for one or more host/user pairs and make those hashes available for validation against a submitted login password. As of v2.5.0 it uses the PHP functions password_hash(), password_verify().
MySQL-PHP configuration has many moving parts. If you're not using one of the many package webdev installations (LAMP, WAMP, XAMPP &c;), these are the best guides we know for getting them to work together:
Linux:
https://www.linuxhelp.net/guides/lamp/
https://hostlibrary.com/installing_apache_mysql_php_on_linux
Windows:
https://artfulsoftware.com/php_mysql_win.html
Windows with Apache web server:
https://forge.mysql.com/wiki/PhpFAQ
Windows with IIS
https://www.devarticles.com/c/a/Apache/Installing_PHP_under_Windows
https://www.devarticles.com/c/a/PHP/Installing-PHP-under-Windows-Further-Configuration-of-WAMP
Mac:
https://www.apachefriends.org/en/index.html
As of v2.5.0, installation has been streamlined.
Step 1: Unpack the archive. Create a folder named theusual off the Document Root of your web server. Document Root is usually something like /var/www, /apache24/htdocs, home/your_site_name/public_html, or under IIS on Windows c:\inetpub\wwwroot. Into this new theusual folder, unpack theUsualPHPmysqli.rar preserving path info. That will yield...
ajax-loader.gif
artfulA.jpg
artfulA.png
calendar.css
calendar.js
download.php
phpinc.php
mysqlddl.php
mysqldel.php
mysqlins.php
mysqlupd.php
refresh.php
session_continue.php
theusual_start.php
theusual.php
theusual-funcs.php
upload.php
viewupd.php
theusualauth.php
theusualcall.php
theusual_start.php
includes/theusualcall.inc
includes/theusualinc.php
includes/theusual-login.php
In recent versions the archive included theusual.css, but Google and Microsoft browsers can misread CSS files in the presence of Javascript code, so with version 2.5.2, stylesheet code has been moved back into theusual.php.
Of files from the archive the following manage authentication ...
user@host => hashed_password
values generated by the
PHP function password_hash()
to be inspected
by function theusualaccess()
in theusualauth.phphost, user, pswd
values so they can be read directly by
TheUsual in contexts where more security is not neededStep 2: Move the include files. If this is a new installation of TheUsual, or if your previous TheUsual version was pre-2.5.0, move the files in the unpacked includes installation folder into a folder that is in PHP's include_path, or to an includes folder outside the Document Root, e.g., to /var/includes if Document Root is /var/www/, to /home/your_site_name/includes/ if Document Root is /home/your_site_name/public_html, to /apache24/includes/ if Document Root is /apache24/htdocs/, and so on.
If this is an upgrade of TheUsual from version 2.5.0 or later, you can delete the unpacked includes files
Step 3: Simplest authentication. Most simply but least securely—e.g., if you are installing TheUsual on a private web server on your own well-secured development machine—you may want to forego a login dialog and write login specs directly into includes/theusual-login.php, e.g., ...
<?php $_SESSION['host'] = getenv( "php_host" ) ? getenv( "php_host" ) : "localhost" ; $_SESSION['user'] = getenv( "php_user" ) ? getenv( "php_user" ) : "myusername" ; $_SESSION['pswd'] = getenv( "php_pswd" ) ? getenv( "php_pswd" ) : "mypassword" ; ?>In your browser run localhost/theusual/theusual_start.php, bookmark the page, then login. You're up and running.
Step 4: More secure authentication. If you are installing TheUsual on a public web server, you do not want to be writing plaintext passwords anywhere on the server—you need better security than Step 3 provides. Visit https://php-password-hash-online-tool.herokuapp.com, click on PASSWORD HASH, enter your MySQL password, copy the result, and follow instructions at the top of includes/theusualinc.php to paste in the hash result along with your MySQL 'host@username' value. For multiple MySQL users, repeat for each user.
In your browser run YOUR_HOST_URL/theusual/theusualauth.php, bookmark the page, then login. You're up and running.
Alternatives: We're in the open source ecosystem so naturally you're free to replace step 3 or 4 with any other authentication scheme you please. If you're doing this, please remember ...
sha256_password
authentication plugin nor the MySQL 8.0
caching_sha2_password
authentication plugin,$_SESSION['host'],
$_SESSION['user']
, and $_SESSION['pswd']
settings that TheUsual can use to connect to MySQL.If you are running TheUsual on IIS:
1. Be sure to enable read-write sharing for IIS_IUSR for TheUsual's folders and subfolders.
2. Since Windows 7, IIS authentication is byzantine. If PHP scripts like TheUsual fail with an error like "You do not have permission to view this directory or page because of the access control list (ACL) configuration ...", from Start run inetmgr, click on the top level in the left navigation panel, select Authentication in the IIS middle panel, right-click on Anonymous Authentication and select Edit, and in the resulting Edit Anonymous Authentication Credentials dialog, click the Application pool identity radio button. Incredible, but there it is.
3. To allow imports bigger than 30MB, try this: select the top item in the left inetmgr navigation pane, click on Request Filtering in the middle pane under IIS, select Hiodden Segments, right click on Web.Config, from the context select Edit Feature Settings, and change Maximum Allowed Content Length to a value that suits your needs. Then restart IIS. If IIS doesn't allow big uploads, you will have to FTP or otherwise copy them directly into TheUsual's uploads folder.
The script theusualcall.php puts up dropdown menus for multiple installed instances of TheUsual and of available MySQL servers.
It uses the text configuration file includes/theusualcall.ini, which may have two kinds of configuration lines, one kind for instances of TheUsual and one for available instances of MySQL server ...
1. For each available installation of TheUsual:
one line of comma-separated key=value
pairs
specifying name
, group
, host
and
path
for one installation of TheUsual ...
theusual
= your choice of name for this instance of TheUsual,
perhaps for example its version number and/or the name of the machine it's running on;
this will be the name by which this instance of TheUsual is selected in the menu.
group
can access only MySQL
database servers with the same group
name (e.g., you couldn't
run a local instance of TheUsual against a web MySQL instance on the
same parameters you use for local instances)host
= LAN machine name or URL
for accessng this instance of TheUsualpath
= path at host
to the PHP
script that invokes this installation of TheUsual, typically either .../theusual/theusualauth.php
to bring up a login dialog, or/theusual/theusual_start.php
Example: Suppose you have two local installations of TheUsual, and one on a remote web site ...
theusual=thelaptop, group=lan, host=thelaptop, path=/theusual/theusualauth.php.. unless you write a custom login script for any of those installations.
theusual=thedesktop, group=lan, host=thedesktop, path=/theusual/theusualauth.php
theusual=thewebsite, group=wan, host=https://thewebsite.com, path=/theusual/theusualauth.php
Attacks on web servers are getting smarter, so is browser security.
Best practice now is to enable SSL on all web servers including
one's own private and presumably secure development server; for all such servers,
LAN and WAN, server host
will begin with
https://.
2. For each distinct MySQL user login, one line of comma-separated
key=value
pairs specifying the MySQL server name and its server group.
Optionally, if the MySQL server is entirely private and secure, this line can also have
login username and password, though that's not recommended ...
mysql
= the name of this MySQL server; if it is on your
LAN, this is that machine's name; if on the web, it is the MySQL
server host name specified by the hosting provider; this is the naame that
theusualcall.php will show for this instance of MySQL,/li>
group
= the name of the group to
which this MySQL database server belongs; installations of TheUsual
with a given group
name can access only MySQL database
servers with the same group
namemysql=mylaptop, group=lan
mysql=mydesktop, group=lan
mysql=webprovider_name, group=thewebsite
Theusualcall.php ignores lines in theusualcall.ini that don't begin with 'theusual' or 'mysql'. See the sample includes/theusualcall.ini.
TheUsual maintains its metadata---settings, saved queries, input form plugins,
non-transactional master-detail table relationships---in MySQL tables. TheUsual's
startup script theusual_start.php stores the name of the database for these tables,
default theusualsys
, in a PHP session array element $_SESSION['sysdb']
.
If the MySQL login you are using for TheUsual includes the Create Database
privilege, you need do nothing to make this work---TheUsual will create the
theusualsys
database when it finds the metadata database missing.
(Until MySQL version 5.7, we could use 'sys' as the default name of the metadata database.
MySQL 5.7 implemented its own sys database, so we changed the default name to 'system'.
MySQL 8.0.3 made 'system' a reserved word, so since theUsual v2.18, the name
is what it should have been all along, theusualsys
.)
If the MySQL login you are using for TheUsual doesn't include the Create Database
privilege---as on a shared hosting account where databases must be created in other software
like cPanel---you'll need to create a database for TheUsual to use for these tables,
then edit theusual_start.php to set $_SESSION['sysdb']
to the name of your
metadata database.
If you have access to just one database and no privileges to create other databases,
just set $_SESSION['sysdb']
in theusual_start.php to the name of your
one database; TheUsual will create and maintain your metadata tables in your one database.
Any number of the same or different instances of TheUsual can run simultaneously in browser tabs, because TheUsual implements the session setup described in Chapter 12 of Get It Done With MySQL 5&6 and under MySQL and PHP | Basics | Session setup at https://artfulsoftware.com/infotree/mysqltips.php
On load, TheUsual offers a list of databases for which you have privileges. Once you select a database, TheUsual puts up a dropdown table selection list, and a saved queries dropdown for the current database if the theusualsys database has been initialised and if there are saved queries.
Once you click the Select button for a choice from the Table dropdown, TheUsual brings up a paged datagrid display of that table with order-selectable headers, and a navigation bar permitting selection of rows-per-page and the Top/Previous/Next/Bottom page of data. When the table is editable (that is, it has a primary or unique key), each row has prompts for adding, editing, adding using a copy of ther current row, or deleting.
If the selected table uses an ACID-compliant storage engine like InnoDB, and if other tables in the current database have foreign keys referencing that table, the navigation bar offers a dropdown detail table selector. If you select a detail table from that dropdown, TheUsual displays a second grid with detail rows matching the current row of the master table. If that child table is in turn the pmaster or arent of another table, Go commands in that grid select that detail table as the master table in a refreshed page.
Clicking on the Metadata link brings up a table browser for the current schema with Analyze, Check, Export, GoTo, Optimize, Repair and Drop options. GoTo has the same effect as selecting that table or View from the Table/View dropdown. In v2.31 the SQL formatter function (originally from GitHub) was revamped to make it more useful for reading the vast amounts of interesting Stored Routine and View code that MySQL provides since v5.7 in its sys database:
Per page: A rows/page dropdown selector appears on the dashboard when the query is pageable. Possible values are 1,2,5,10,20,50,100,500,1000,2000,5000. To change these, edit $pagelens in theusual.php.
Prev, Top, Next, Last: Display the previous, top, next or last <per page>
rows returned by the current query. These prompts default to arrow icons but can be set to
Top Prv Nxt Bot
in Settings.
Detail table: This is a dropdown selection list of ACID-compliant tables in the currently selected database with a foreign key referencing the currently selected table. Up to version 1.89, TheUsual could display one master-detail table relationship. Since version 2.00 (mysqli version only), there is no limit to the number of cascading master-detail relationships.
Master-detail relationships without declared foreign keys: Some MySQL and MariaDB storage engines do not support foreign keys, yet management of data in such tables often needs master-detail logic. TheUsual helps with this by letting you add, to the above child tables list, master-detail table relationships without declared foreign keys. It does this by storing such table relationship specifications in a theusualfks table in the metadata database.
If TheUsual does not find such a table, it creates it. You can use TheUsual (or any other MySQL client) to add rows to this table specifying master-detail (parent-child) table relationships.
TheUsual documents master-detail relatonships in the same way MySQL
information_schema
documents foreign keys. To create a non-InnoDB master-detail
relation for a pair of tables, insert rows in the theusualfks table where the
Referenced
schema, table and column document the parent table, and the
remaining three columns document the child table, as in this example for a MyISAM version
of the MySQL world
database ...
When you browse world.country in TheUsual, you can choose city or countrylanguage as a child table, and TheUsual will auto-generate dropdown picklists for the relevant foreign keys...
Beware that TheUsual does not (yet) validate parent-child entries in theusualfks table; it just tries to execute them as if they were real foreign keys, and reports an error if the specification is incorrect.
If the detail table itself has a detail table, the leftmost column of each detail row shows, in addition to Edit, Ins, Copy and Del options, a Go option that makes the current detail table the master table in the next browse, which will be of the page containing the selected row. Use Go to daisy-chain down any number of cascading master-detail table pairs.
The navigation bar GoTo control goes to the first page on which it finds a value
matching a column search value the user has entered. It's enabled for a base table or a View
selected from the Table dropdown. Up to v2.01, it supports primary key searches. Since v2.02, select
a column from the dropdown that appears, then enter a value to search for: if TheUsual finds a
first instance of that column value on the current ordering, it will display that page. Since
v2.03, if the column value you enter contains the '%' wildcard, TheUsual will look for
a column value LIKE your search value. (To find multiple instances of a value,
use Query, with parameters if desired (see below), to add a WHERE
condition
to the current query.)
When the table has a primary or unique key, each row offers options to Edit, Ins(ert), Copy or Del(ete) that row. Clicking on Edit puts the current row in edit mode, clicking on Ins brings up a new row for editing, and clicking on Copy brings up a new row which is a copy of the current row for editing. Finish an Edit, Ins or Copy cycle by clicking Save or Cancel.
As of v2.36, the Settings page has an item specifyng the number of columns a table must have to switch the format of the Edit/Ins/Copy form from in-table rowwise horizontal to vertical showing just one row. The default is 5. If you set it to 0, the Edit/Ins/Copy format will always show just one row or record vertically. If you set it to a number greater than the greatest number of columns you have in a table, the Edit/Ins/Copy form format will always be in-table rowwise horizontal.
Automatic foreign key lookup dropdowns: Since v1.57, during row edits, for columns that are foreign keys in ACID-compliant tables, and also for TheUsual's user-defined foreign keys, TheUsual automatically puts up a dropdown for selection of a key value from the referenced table. In that dropdown, beside the key column, it also displays a (VAR)CHAR description column, if it can find one. You can tell TheUsual what description column to use if you enabled TheUsual's user-defined foreign keys: edit the theusualfks table in the database specified by $_SESSION['sysdb'] = 'admin', and in the row for that foreign key, set the value of the column named referenced_desc_column to the desired SELECT expression (beware: TheUsual doesn't verify your syntax!).
Version 1.85 introduced input form plugins. This feature needs the
theusualsys theusualplugins
table.
Install an input form plugin by adding a row to that table specifying coltype, onfocus, jsfile and cssfile, and optionally specify script and onblur.
Coltype may be DECIMAL, TINYINT, SMALLINT, INTEGER, FLOAT, DOUBLE, BIGINT, MEDIUMINT, TIMESTAMP, DATE, TIME, DATETIME, YEAR, DATE, BIT, DECIMAL, ENUM, SET, TINYBLOB, MEDIUMBLOB, LONGBLOB, BLOB, VARCHAR, CHAR, or GEOMETRY; onfocus specifies onfocus code; cssfile and jsfile specify CSS and Javascript files which TheUsual must load for the plugin; script specifies a javascript file in addition to jsfile, if any, to load in the input form; and onblur speciies required onblur code if any.
As a demonstration example and as a convenience, if the theusualplugins
table is empty and if TheUsual can find the files calendar.js and
calendar.css in the current folder, it installs a calendar popup plugin for
date inputs by creating and populating a theusualplugins
row for it. To import some other plugin, bring up the theusualplugins
table in TheUsual, add and populate a row.
MySQL since 5.0 implements Views, so theUsual/PHP treats Views as tables. To create a View, run a CREATE VIEW statement as a custom query. To execute a View, select it from the Tables dropdown.
A single-table View is editable on the same criteria that MySQL uses to determine editability. Columns of the main table of a multi-table View are editable on the same criteria.But MySQL Views have their limitations. They tend to be slow; indeed executing queries retrieved from a saved queries table is up to twice as fast as executing a View that invokes the query. And before MySQL version 5.7.7, Views may not have FROM clause subqueries, which multi-table queries and reports often need.
So a Query option in the navigation bar brings up an edit window in which you can create a custom query. Its execution module can handle multiquery scripts, so it can be used not just to run queries in the conventional sense, but also to run SQL script jobs (less than about 250KB of query text; see Big query scripts below for how to run larger import scripts).
Such queries can be saved so long as theUsual has been able to create a saved queries table ...
CREATE TABLE theusualviews ( name VARCHAR(64) PRIMARY KEY DEFAULT '', db VARCHAR(64) NOT NULL DEFAULT '', User VARCHAR(16) NOT NULL DEFAULT '', Host VARCHAR(64) NOT NULL DEFAULT '%', qry VARCHAR(8192) NOT NULL DEFAULT '', changed TIMESTAMP );Once this saved queries table is present, a query Name field appears above the query code input control, and a Save button appears at the bottom right edge of the query text window beside the Execute button. Once at least one saved query exists for a given database, a Query: prompt and dropdown appear at the top of the page, to the right of the Table: prompt.
theusualviews
table in its theusualsys
database or in the current database if you prefer, and can retrieve such queries for
editing and execution.
Of course your query scripts can include CREATE PROCEDURE | FUNCTION | TRIGGER
SQL, for ehich, if the routine consists of multiple statements, you'll need to specify an
alternative DELIMITER, e.g.,...
DELIMITER go CREATE PROCEDURE... BEGIN ... code ... END; go DELIMITER ;
Since v2.5.3, TheUsual suppresses display of query SQL when it's executed
if query code includes the comment /* theusual_suppress_query_text */
.
Multi-query results: The mysqliAPI supports multiple resultsets, so TheUsual shows them all even when they're from a stored procedure call in a multi-query script.
To manage your saved queries, select the database where it is, then select theusualviews from the Tables dropdown, browse and edit the saved queries table as you would for any other table. For editing of non-trivial queries it's usually much more efficient to copy to your preferred text editor, edit the query script there, then paste back into theUsual's interface for the saved queries table.
Runtime query parameters: As of v2.36, the Custom
Query module supports query parameters which the user fills in at runtime before the query
executes. Mark such parameters with the string [theusual_query_param]
including the surrounding square brackets. For example, if you often need to search
a documents table items( id int, author varchar(128), txt text )
for entries
with text containing a certain word or character sequence, you could save a custom query ...
select * from items where txt like '%[theusual_query_param]%';When asked to execute this query (either saved, or entered raw), theUsual will put up a miniform prompting for a paramater value ...
theusual_query_param
value to search for, it
will substitute that value into the query, then execute it. You can specify any number of
such parameters.
Load Data Infile: Since version 2.47 TheUsual has a user-friendly
wrapper for Load Data Infile
queries. It's invoked both for standalone Load Data
queries and for Load Data queries occurring in multi-query scripts.
The wrapper supports runtime query parameters so, for example, if a query begins with...
LOAD DATA INFILE '[theusual_infile_param]' INTO ...... and if the system variable
secure_file_priv
names an available input file
folder, then before executing that query, TheUsual will show the query to the user
with a dropdown file selector replacing [theusual_infile_param]
, and will
substitute the user's selection into the query unless she cancels.
When a Load Data
query runs against an InnoDB
table, TheUsual
runs the query asynchronously so during execution it can report the InnoDB
insert
count by polling Show Engine InnoDB Status
at intervals on another connection;
by default it spits out a reporting line for each chunk of 10M bytes. With transactional and
non-transactional tables alike, it reports warnings and errors.
Do not try to send scripts with more than about 0.25MB of SQL text through Query. The custom query module is meant for scripts that are small enough to be meaningfully interactive and which may display SELECT results; it bogs down with more than 250 KB of input, and depending on the browser, can grind to a halt near 1MB.
For query scripts > 200KB in length use the Import command in the navigation bar. The import module can process 30MB of SQL commands on ordinary hardware in a few seconds. The Import option brings up a form where the user may ...
The Import module creates its uploads folder if it does not exist, logs file uploads to theusual_uploads.txt, and logs upload errors to theusual_uploads.err. If the import file is too big to be uploaded through the browser (e.g., the IIS 7.5 limit is 30MB unzipped, and difficult to change), you'll need to FTP or otherwise copy the SQL command file directly into the uploads folder.
The module can handle files that contain SQL text; it can also handle zip, gzip, bzip2
and rar SQL archives. Most PHP installations support zip and gzip. Bzip2 needs PHP configuration
using --with-bz2
. (The PECL rar module was unstable in PHP 5.2 through 5.4---if
you find a rar plugin that works with those PHP versions, please let us know). The import form
also sets sql mode, character set, file deletion policy, and whether to try to determine that the
file actually contains SQL before executing any commands from it.
Beware that several php.ini settings govern import/download file size and script execution
time: edit php.ini, or invoke ini_set()
, to set memory_limit
,
max_file_uploads
, max_execution_time
and max_input_time
to limits that accommodate your largest and longest imports, and post_max_size
and upload_max_filesize
to zero if you can live with no limits, or to values
you can live with.
The internet wasn't designed to display results of long batch jobs in real time. The MySQL client program isn't so good at the job either: results fly by faster than they can be read. The Import module is meant to handle potentially huge Inserts, so it shows just the first 80 characters of each SQL command it executes, along with the result. Even with that, screen output may be massive. How will a web browser respond?
Browsers do their own output buffering. We've no control at that level.
Under Apache, output_buffering in php.ini is determinative; it defaults to 4KB, so theUsual uses flush commands keep the user informed about how execution is proceeding.
But under IIS, screen output is usually held back until script execution completes. A Microsoft engineer offered this lame explanation: "For performance reasons and interoperability with some ASP.NET features ... we entirely buffer responses < 1MB and then send them at once." In IIS 7.5 it's actually 4MB. A 30 MB SQL script may take a minute or more to execute. Leaving the user uninformed for that long is rude, but IIS Manager offers no access to the controlling variable. A recommended workaround for this bit of IIS stupidity is first, in the left panel of IIS Manager, create an IIS "web site" forTheUsual, called, say, theUsual_mysqli; second, in the central IIS Manager panel find the name of the PHP engine--say it's PHP52_via_FastCGI; then finally, run this command ...
%windir%\system32\inetsrv\appcmd set config "theUsual_mysqli" /section:handlers /commitPath:apphost -[name='PHP52_via_FastCGI'].ResponseBufferLimit:0
... substituting your values for theUsual_mysqli and PHP52_via_FastCGI. Restart IIS.A Chart prompt appears on the dashboard if TheUsual can match the data in the current datagrid to a type of chart it knows how to draw. TheUsual can now display, on the fly, nine kinds of Google Visualisation API charts—Area, Histogram, Line, Smoothed Line, Motion, Org (Tree), Pie, Pie with Percentages, and Scatter. These charts can use [VAR]CHAR, numeric and DATE values; BLOB, TEXT, ENUM and SET values are ignored. When customising queries for these charts, remember that MySQL functions like FORMAT() return strings; to make strings usable as numeric values for a chart, use CAST( FORMAT(...) AS DECIMAL[...]).
YYYYQn
, or a string specifying year
weeks in YYYYWnn
format; and at least one numeric result
column. See the example below.To draw a chart, TheUsual has to write out the data to be charted in a format that the Google Visualization engine can understand, so charts work best with aggregating queries that keep down the rowcount. Area charts, Histograms and Scatter charts can handle up to 10k rows; Org charts can handle 5k rows but are best with a few hundred at most; Line charts can handle 2k rows. Pie charts become unreadable with much more than a dozen rows, though the module will accept 50.
Here is a simple GNP-By-Continent query on the world database:
And here is an image of a minimum motion chart in its final state. All we had to do was ...
country.continent
column,Motion
from the Chart
dropdown in TheUsual, then click the Trails
checkboxes and the Play button: select floor(population/100000)*100000 as 'Population Band', count(*) as Size from world.city where countrycode='GBR' group by 1;Version 2.5.2 added bubble charts—X-Y scatter plots against a key column with data points shown as bubbles coloured according to a group they belong to and sized proportionately to the value of another numeric column. From the query that's just been executed in theUsual, a bubble chart reads five data columns left to right...
Here's the data for the example ...
['AUT', 0.297, 0.66, 'Western Europe', 8091800], ['BEL', 0.251, 1.95, 'Western Europe', 10239000], ['CAN', 0.303, 1.76, 'North America', 33739900], ['CHE', 0.331, 0.54, 'Western Europe',7 160400], ['DEU', 0.319, 0.95, 'Western Europe', 82164700], ['DNK', 0.253, 1.01, 'Nordic Countries', 5330000], ['ESP', 0.343, 0.62, 'Southern Europe', 39441700], ['FRA', 0.299, 1.20, 'Western Europe', 59225700], ['GBR', 0.350, 1.20, 'British Islands', 59623400], ['ISR', 0.389, 1.36, 'Middle East', 6217000], ['ITA', 0.359, 0.67, 'Southern Europe', 57680000], ['NLD', 0.268, 0.55, 'Western Europe', 15864000], ['NOR', 0.249, 0.47, 'Nordic Countries', 4478500], ['RUS', 0.375, 8.21, 'Eastern Europe', 146934000], ['USA', 0.480, 4.96, 'North America', 327000000]... and here's the auto-generated chart ...
Print: Version 1.44 introduced a Print option to send browsed data to the printer of your choice.
Refresh: If your most recent custom query added or dropped a database or table, use this option to refresh the Database and Table dropdown menus.
Exit: Exit.
The Metadata prompt in the navigation bar browses all objects in the currently selected database. Each base table and View gets a row with a GoTo option to browse it; base table rows also have Analyze, Check, Optimize, Repair, Drop options. Below base tables and Views, all stored routines including Triggers are listed with their source code. The Metadata page's navigation bar has Backup, Import, MySQL Error Log (since v2.49), Settings prompts.
MySQL Error Log: Since v2.49, this option displays the MySQL error log, most recent entries first. if the log is available to be read. Note that in some shared hosting sites, and in some organisations, the MySQL error log is not available to some users.
Settings Panel: Since v2.37, this panel supports custom settings for each theusual user. Access the form by clicking Metadata in the navigation bar for any database , then click on Settingsin the Metadata page's navigation bar.
theusual_settings
table
in the theusualsys database. When you upgrade from an
earlier version of theUsual, any settings found in theusual_start.php are
written to the theusual_settings
table.
Database Backup is available in the Metadata database administration browser. It assembles, then offers for download, a file of SQL commands to recreate the tables, views, data, stored routines and Triggers of the current database.
The Backup parameters panel offers specification of whether the backup script is to create and/or issue a Use command for the database, generate dump commands within a single transaction, generate commands to drop and recreate tables, strip definer clauses from DDL for stored routines and Views, display error and warning messages, and offer to compress and download the result:
If the database being backed up keeps its saved queries in theusualsys.theusualviews rather than in itself, the Backup Parameters panel also offers an option to generate code to update the target server's theusualsys.theusualviews table.
The backup module writes its file to the current web folder, offers to download the file to the client, logs the file operation, and when the downlaod completes, deletes the dump file if that option has been chosen. If option is declined or if download is declined or if it fails, the file stays on the server.
Under Apache this is routine. Under Windows and IIS, it can require two additional steps: make the server folder's security tab visible, then use the Security tab to enable writes to wwwroot. Open My Computer, click on Tools | Folder Options and then on the View tab. At the bottom of the list uncheck 'Use simple file sharing (Recommended)'. Now Windows Explorer shows the Security tab in the Properties context menu for every folder. In Windows Explorer, right-click on wwwroot, select the Security tab, select the Internet Guest Account ([username]/IUSR_[username]), check Write and Modify, and press OK.
2.5.8 | Improved handling of quotes in query row counter function totalrows(). |
2.5.7 | Corrected chart type assignment for Org charts. |
2.5.6 | Corrected handling of Generated columns in Inserts, Updates, Export. |
2.5.5 |
Corrected bug that could mistakenly auto-update a subquery limit clause. |
2.5.4 |
In the Import form, sort upload file candidates by newest first. In DB backup, Flush Privileges if schema is mysql. In DB backup setup, added Single-transaction and Compression options. |
2.5.3 |
Corrected regression in handling of input default=current_timestamp. Corrected default charset for backup. Added backup option to strip definer clause from View, Routine and Trigger DDL. Added backup option to update theusualsys.theusualviews when theusualviews does not exist in the backup database. Suppress display of long query text in custom multiqueries with the comment /* theusual_suppress_query_text */. Changed target argument from '\_blank' to 'blank' in active urls. |
2.5.2 |
Bubble Chart added to Chart option dropdown. Switched css to inline to work around Chrome & Edge nisreading of linked .css. Corrected foreign key parsing error for referenced tables in other than the current schema. |
2.5.1 |
Added New/Overwrite flag to Save Query form. Corrected Order By parsing in custom queries. Corrected query parameter processing in unsaved custom queries. |
2.5.0 |
Two-dot version numbering. Updated and streamlined authentication. Return to previous page after cancelled Import. Corrected regression in translating CURRENT_TIMESTAMP to appropriate values. |
2.49 | Added read-error-log option to Metadata navbar. Corrected rowcount for tables whose stats aren't updated. Fixed bug that hid zero-row tables in metadata browser when use_infoschema is set off. |
2.48a | Removed debugging stub from backup download script writer. |
2.48 | Support MySQL 8.0.23 invisible columns. Improved error handling. Enhanced handling of database backup errors & results. |
2.47a |
Corrected row selection highlighting in Metadata browse.
Corrected identification of Values Row(...),... statements.
Corrected setting of default values for Views.
|
2.47 | Added user dialog & query wrapper for parameterised Load Data Infile. Corrections to initialisation of custom queries. Set information_schema_stats_expiry=0 in MySQL 8. Grow & improve query parsing regex. |
2.46 | Corrected html glitch in multi-query-parameter form. |
2.45a | Corrected MetaData browser to include stored routines that take no params. Corrected regression that skipped automatic input form for empty physical tables. Corrected server argument parsing in theusualcall.php |
2.45 | More refinement of big rowcount estimates. Corrected display of current saved query selection. |
2.44 | Improved prompts, tooltips and file-too-big error message in Import form. |
2.43 | Corrected regression in logic controlling table deletion in Metadata browser, loop exit code in urlactivate(). Drop Table execution in Metadata browse. |
2.42 | Use information_schema.parameters in Metadata browser. Corrected column headers for user query in browser 2.42a: corrected stray typo in browser() function. |
2.41 | Touched up main page and mysqldump headers, custom query form. Corrected handling of queries without From clauses. Use row count estimate for tables with > 2M rows. |
2.40 | Corrected handling of Limit clauses with Views. Corrected navigation bar prompt spacing. |
2.38 | Corrected page calculation when changing page length while at EOF. Show theusualviews `qry` column in SQL format. Corrected Unicode for nav arrows to survive Windows system font change. |
2.37 | Settings Panel now sets per-user preferences, no more need to set such $_SESSION values manually. Improved editing of Views referencing multiple tables. Always open live data urls in new tab. Tweaked SQL formatting of data columns expected to contain SQL. Corrected handling of non-numeric foreign keys. |
2.36 |
Implemented runtime parameters in saved queries.
Added a setting (default=5) for the minimum no. of table columns that forces use of a vertical add/edit form layout rather than the default horizontal within-table layout. |
2.35 |
Added vertical edit form for Ins/Edit/Copy when page length is set to 1 and no detail table is being browsed. Corrected SQL for the "Browse database" command when use_information_schema is enabled in Settings .
|
2.34 | Added setting for icons or Top/Prv/Nxt/Bot prompts for page navigation. Corrected view editability check. |
2.33 | Add Check Constraint code to failed constraint Insert/Update error messages from MySQL. |
2.32 | Corrected calls to SQL formatter. |
2.31 | Quote new reserved word 'Rows' in information_schema db stats query. Improved SQL formatter. |
2.30 | Corrected transactqry() retry handling, support for specifying retry count, charset matching in insert and update scripts. |
2.29 | Corrected saving of query updates, setting of defaults in input forms. |
2.28 | Faster rowcounts. Transaction support for InnoDB, XtraDB, TokuDB, PBXT inserts, updates, deletes. |
2.27 | Restored functionality growing last data page size up to page limit. Increased availability of Edit Query option. Disabled resetting of limit clause to default when executing a saved query with a limit clause. Work around 8 vs pre-8 no_auto_create_user incompatibility processing mysqldump scripts. Lengthened max_execution_time. Corrected master-detail display glitch. |
2.26 | Renamed row-find UI command name to 'Goto'. Disabled html autocomplete for form fields using plugins. Reworked child row code for cascading parent-child browse instances. |
2.25 | Added support for Common Table Expressions. Improved rowcount performance. |
2.24 | Changed dump delimiter for stored routines. |
2.23 | Backtick newly-reserved-word column names in dumptables(). |
2.22 | Allow for MariaDB in version check. Allow limits up to 5K rows. |
2.21 | Some fixes to master-detail cascades. Experimental: for editability accept unique keys in place of primary keys. |
2.20 | Scale display of width-unspecified pics for mobiles & non-mobiles. Changed default separator char for passing multi-column keys in utf environments to "ʃ". |
2.19 | More corrections to restoration of form data after save error. Handle quotes in keys. |
2.18 | Var name changes for PHP 7.1. 'System' is a reserved word in 8.0.3, default system DB name now 'theusualsys'. |
2.17 | More improvement to live URL display. |
2.16 | Corrected handling of primary key strings containing URLs, improved display of live URLs. |
2.15 | Corrected handling of user queries with nested subqueries. More custom queries now chartable. |
2.14 | Backticked new 8.0 reserved words. Corrected connect error handling. |
2.13 | Corrected child-table-fetch error. |
2.12 | Corrected pseudo-foreign-key retrieval from theusualFKs table. |
2.11 | Corrected restoration of form data after save error, handling of semicolons terminating custom queries. |
2.10 | Added Histogram charts, updated Google Visualization API call. Fixed bug in saving query edits. |
2.09 | Corrected BigInt display. Laid in support for column-specific JavaScript plugins. |
2.08 | Corrected display of Show Create Table in schema browser. |
2.07 | PHP before 5.4 chokes on some !empty() calls, changed to !strlen(). |
2.06 | Corrected edit link after non-PK Find. |
2.05 | Corrected master-detail row synchronisation. Don't override existing Limit clause in a saved query with the current setting. |
2.04 | Corrected row pointer after Insert, form value restoration after Insert or Update failure. |
2.03 | Added Find support for '%' wildcard. Corrected url display. |
2.02a | Corrected misplacement of textarea edit width spec. |
2.02 | Find option enhanced for search on any column in base tables and updatable views selected from the Tables dropdown. |
2.01 | Corrected bug introduced in 2.00 preventing table DDL display in database metadata browser. |
2.00 | Master-detail table browsing support for unlimited no. of cascading master-detail relationships. |
1.90 | Corrected handling of multiqueries that begin with a delimiter directive. Note: this is the last version of theUsual for the mysql API; from here on in, only the mysqli version of theUsual will be upgraded. |
1.89 | Changed the default name of the schema for server-wide settings from "sys" to "system" to accommodate MySQL 5.7 use of "sys" as the name of an endogenous schema. |
1.88 | Fixed parsing of illegal schema names. New setting liveurls activates urls in displayed data. |
1.87 | Option to filter HTML in all form strings. Fixed bug that could switch the current table on cancelling a theusualviews edit. |
1.86 | Recompute query rowcount on every refresh |
1.85 | Added support for input plugins |
1.84a | Corrected typo in mysqldump() Create Table If Not Exists |
1.84 | Corrected multiquery delimiter block parser error |
1.83 | Corrected Javascript childRemove() call that failed in IE |
1.82 | Do not switch default DB when the parser detects that a custom query's main table is in another DB |
1.81 | Removed glitch that displayed zero values as blanks |
1.80 | Corrected Backup download switch. |
1.79 | Corrected EOL handling in imports. |
1.78 | Added an Import module, and settings forms for import and backup. Fixed some display glitches. Optimised multiquery parsing. Added a GoTo command for each table & View in the database browser. |
1.77 | Corrected handling of multiple multiquery delimiters. |
1.76 | Modularised mysqldump routine, added table-export option to database browser. |
1.75a | Improved error handling. Corrected delimiter block handling in multiquery module. |
1.74 | Corrected next-result error in mysqli multiquery module. |
1.73 | Added lightweight query formatter to make CREATE VIEW results human-readable. |
1.72 | Corrected implementation of user's text display choice. Sort information_schema to bottom of DB list. |
1.71 | Corrected display of multiple resultsets from stored procedures. |
1.69 | Corrected handling of blank numeric values in forms. |
1.68 | Improved handling of missing theusualviews table. |
1.67 | Corrected parser code triggering warning in some foreign key checks. |
1.66 | Corrected handling of UNION queries. |
1.65 | Corrected minor error in reading user-defined foreign keys (theusualfks ) table. |
1.64 | Corrected parsing of complex multiqueries. |
1.63a | Corrected typo in parentkeys(). |
1.63 | Removed irrelevant links in database browser. |
1.62 | Corrected Drop Table bug in mysql version of Browse Database. |
1.61 | Added automatic dropdowns for parent keys specified in theusualfks . |
1.60 | Automatic dropdowns and initialisation of child foreign key columns to parent key values when adding a row in detail datagrid. |
1.59 | Improved handling of limits in saved queries, and of missing 'sysdb' database when it has been specified. |
1.58 | Corrected recently introduced problem in definition of some custom query columns. |
1.57 | Added automatic foreign key dropdown lookups. Corrected timezone detection. |
1.56 | Added smoothed line charts, and pie charts with percentage display. Corrected View ordering. |
1.55 | Corrected handling of database-specific routine privileges for backup under shared hosting. |
1.54 | Corrected handling of INSERT s and UPDATE s of blank BLOB and TEXT values. |
1.53 | Corrected bug that prevented browse of performance_schema . |
1.52 | Corrected initialisation of Saved Queries dropdown. |
1.51 | Corrected handling of user dropping the current database. |
1.50 | Corrected handling of queries, Views and saved queries for charts. |
1.49b | Do not automatically add an ORDER BY clause to a MySQL View. |
1.49a | Corrected JavaScript error in Saved Query handling under IE7-8. |
1.49 | Does on-the-fly Google Area, Motion, Pie, Org and Scatter charts. Corrected query saving. |
1.48 | More key-handling workarounds for servers that still set magic_quotes_gpc. |
1.47 | Corrected error handlers |
1.46 | Cleaned up display of decimal data. Corrected saving of Saved Queries |
1.45 | Backup option in Browse database mode |
1.44 | Print option |
1.43 | Corrected table selection button misdisplay after browser tab restoration. 143a: removed debugging stub |
1.42 | Correct for character set when using column length to determine single- vs multi-line edit (mysqli) |
1.41 | DB Browser: Disable join to mysql.procs for routine params when user does not have that privilege |
1.40 | Grey out rather than omit Top Prev Next Bott buttons when they can have no effect |
1.39 | Added dynamic character set setting, coordinated with Set Names. Corrected column headers. |
1.38 | Corrected handling of escaped quotes in multiqueries |
1.37b | Corrected display glitches for errors, non-editable tables |
1.37 | Added Go option in detail browse to cascade master-detail chains. Updated default settings. Smoothed error handling. |
1.36 | More text formatting options. No default ORDER BY clause in a query on a View. Corrected multi-key deletion & subquery parsing. |
1.35 | Added front end for multiple installations of TheUsual and of MySQL servers |
1.34 | Corrected master-detail synchronisation when controlling order is not the primary key |
1.33 | Data-driven master-detail browsing; corrections to child table argument parsing & key argument handling |
1.32 | Added Triggers list to database browser |
1.31 | Corrected handling of multiqueries and of queries with FROM clause derived tables |
1.30 | Improved handling of ENUM s, SET s and BOOL s in forms |
1.29 | Size limit setting for text and blob display/edit. Timestamp default handling improved. |
1.28 | Call mysql[i]_real_escape_string() for deletion key arguments only if !get_magic_quotes_gpc() |
1.27 | Corrected ordering of Views, handling of get_magic_quotes_gpc() |
1.26 | Accommodate MySQL 5-6 differences in information_schema.routines table structure |
1.25 | Corrected BLOB handling |
1.24 | Corrected handling of multi-column char keys in some update commands |
1.23 | Corrected handling of database and table names containing prohibited characters |
1.22 | Corrected handling of delimiters embedded in quoted strings inside multi-query strings |
1.21 | Corrected problem in Save Query |
1.20 | Settings form |
1.19 | List stored routines in database browser |
1.18 | Enable Drop View in database browser |
1.17 | Strip slashes if get_magic_quotes_gpc() is set on inserts and updates |
1.16 | Improved handling of MySQL commands |
1.15 | Database browser supports Analyze, Check, Optimize, Repair, Drop Table commands |
1.14 | Generalised DELIMITER support in custom queries |
1.13 | Support for multi-queries |
1.12 | Database browser |
1.11 | Remove any trailing semicolon from a query |
1.10 | Fixed row ordering glitches |
1.09 | Fixed master-detail glitches |
1.08 | Saved queries go to mysql.theusualviews if privs allow, otherwise to current db |
1.07 | Much faster: by default bypasses information_schema to get child table info |
1.06 | Instructions for installation on a shared hosting provider |
1.05 | Ins/Edit/Copy/Del/Save/Cancel buttons compressed. Accept NULLs to nullable columns. |
1.04 | Optional detail window if there is a child table |
1.03 | Query execution times |
1.02 | Support for many non-SELECT cmds |
1.01 | Support for calling sprocs |
1.00 | Original |