Published under the Creative Commons Licence
by Artful Software Development
http://www.artfulsoftware.com
Version 1.31, 20 May 2009
TheUsual/PHP exists to provide a fast, user-friendly web browser maintenance interface for MySQL databases. It installs easily and runs either on your own dedicated server, or on a remote hosting server (dedicated or shared) where MySQL is available.
It implements, in PHP 4-5 for MySQL 4-5-6, the Artful concept of TheUsual—the usual jobs you have to do to create, update & maintain database data. Like earlier versions of TheUsual for XBase and Sybase backends, and like its cousin for Visual Studio 2005 and MySQL, it implements a general-purpose maintenance interface for any available database and its tables:
The basic design and look-and-feel of TheUsual/PHP are described in Chapter 12 of Get it Done with MySQL 5, excerpted here.
To download TheUsual/PHP, visit http://www.artfulsoftware.com/theusual.html and scroll to the bottom of the page.
TheUsual/PHP requires:
There are two main PHP APIs for MySQL, mysql and mysqli, where 'i' means 'improved'. The corresponding versions of TheUsual/PHP are published in theUsualPHPmysql.rar and theUsualPHPmysqli.rar. If you write or invoke stored routines, the mysqli version is recommended; for an example of why, see "How to call a stored procedure with an OUT parameter" at http://www.paragon-cs.com/mag/issue3.pdf.
MySQL-PHP configuration has many moving parts. These are the best guides we know for getting them to work together:
Linux:
http://www.linuxhelp.net/guides/lamp/
http://hostlibrary.com/installing_apache_mysql_php_on_linux
Windows:
http://www.artfulsoftware.com/php_mysql_win.html
Windows with Apache web server:
http://forge.mysql.com/wiki/PhpFAQ
Windows with IIS
http://www.devarticles.com/c/a/Apache/Installing_PHP_under_Windows
http://www.devarticles.com/c/a/PHP/Installing-PHP-under-Windows-Further-Configuration-of-WAMP
Mac:
http://www.apachefriends.org/en/index.html
S1. Unpack
artful.bmp
art_ico.gif
phpinc.php
mysqlddl.php
mysqldel.php
mysqlins.php
mysqlupd.php
session_continue.php
theusual_start.php
theusual.php
theusual-funcs.php
viewupd.php
into a conveniently named folder (eg, theusual) in the document tree of your web server (for example with Apache c:/apache/htdocs/theusual/, or on Windows with IIS, c:/inetpub/wwwroot/theusual/).
S2. Unpack
theusual-login.php
into c:/php/includes if available, or failing that, in a directory that is not web-visible and that is visible to PHP.
S3. In theusual-login.php, edit the values of $host, $user and $pswd for your MySQL setup.
S4. In your browser run http://localhost/theusual/theusual_start.php, and bookmark the page.
W1. In your site's main web document folder (eg /home/your_site_name/public_html), create a folder named theusual. This folder will hold your web instance of TheUsual.
W2. Create a folder outside your web document tree, ie a folder that is not available via public_html, for example home/your_site_name/theusual. If your hosting provider does not provide access outside your document tree (unbelievably, some don't), get a better hosting provider right away!
W3. Visit http://www.tools.dynamicdrive.com/password/, and there enter the desired username and password for your instance of TheUsual. Usually, this will be the username and password provided by your hosting provider for access to your MySQL database. For 'Path to .htpasswd file', enter the full path to the folder you created in step W2. Click Submit.
W4. Save the indicated contents of .htaccess, which will look something like this...
AuthName "TheUsual"
AuthType Basic
AuthUserFile /home/your_site_name/theusual/.htpasswd
AuthGroupFile /dev/null
require valid-user
to a plain text file named .htaccess in the folder you made in step W1.
W5. Save the indicated contents of .htpasswd, which will look something like this...
your_username:your_encrypted_password
to a plain text file named .htpasswd in the folder you created in step W2,
W6. In the folder created in step W2, create a text file named
theusualinc.php, with this content:
<?php
$_SESSION['host'] = "HOSTNAME";
$_SESSION['user'] = "USERNAME";
$_SESSION['pswd'] = "PASSWORD";
?>
include( "/home/your_site_name/theusual/theusualinc.php" );
right below the call to session_start(), making sure that /home/your_site_name/theusual exactly matches the path to the folder you created in step W2.W8. Copy all files listed in step S1, including your newly modified copy of the_usual_start.php, into the folder you created in step W1.
W9. In your browser run http://www/your_site_name/theusual/theusual_start.php, enter your username and password, and bookmark the page.
TheUsual/PHP needs three authentication settings---$host, $user, $pswd---which can be ...
If you use theusual-login.php, remember that PHP include files can be in the
php-includes path, for example in the c:/php/includes folder, or in the current
folder, but keeping such include files in the current folder is a security disaster on an
internet-visible server.
Here is a sample theusual-login.php. Fill in your own HOST,
USER and PSWD values:
<?php
$_SESSION['host'] = getenv( "u_host" ) ? getenv( "u_host" ) :
"HOST" ;
$_SESSION['user'] = getenv( "u_user" ) ? getenv( "u_user" ) :
"USER" ;
$_SESSION['pswd'] = getenv( "u_pswd" ) ? getenv( "u_pswd" ) : "PSWD" ;
?>
A simple way to run TheUsual against multiple MySQL servers is to write a login script which accepts the user's server choice and passes host, user and password values as $_GET[] or $_POST[] elements to theusual_start.php for each desired MySQL server. Of course you can also write a login page for each desired instance if desired.
Versions 4.x of PHP use 3.x versions of MySQL client modules, but passwords
created with MySQL since version 4.1 use an authentication protocol which
earlier MySQL clients
cannot decode. If you are using PHP 4.x, the password of any user of theUsual.php
must have been entered with, or edited to OLD_PASSWORD( 'password' ).
For straightforward, robust support of independent multiple PHP sessions on a server see Session setup under MySQL and PHP | Basics at http://artfulsoftware.com/infotree/mysqltips.php.
On loading, TheUsual offers a list of databases for which you have privileges. Once you select a database, TheUsual puts up a table selection list and a dashboard band offering custom query, database browse and other command links. Clicking on the database browse link brings up a list of tables in the current database with Analyze, Check, Optimize, Repair and Drop options.
If you make a choice from the table dropdown, you see a table of data from that table with order-selectable headers, and a statusbar permitting selection of First/Previous/Next/Last row groups and rows-per-page selection. If the table has a unique or primary key, there is a statusbar control for entering a key value to search for; in addition, every data row then has options to Edit, Ins(ert), Copy or Del(ete). If the current database has ACID-compliant tables tables referring to the currently selected table, the statusbar also has a control for selecting one of them to browse as a synchronised child table.
MySQL 5 implements Views. When run against MySQL 5, theUsual/PHP lists Views as tables, accepts CREATE VIEW statements as custom queries, and of course runs Views and queries.
TheUsual also has a mechanism, which works with MySQL 4 and 5 alike, for saving queries to a custom queries table (in the mysql db if privileges allow, otherwise in the current db), and for retrieving them for editing and execution. Enable this option by setting
$_SESSION['views']=TRUE in theusual_start.php. Then
TheUsual creates its saved queries table if it does not exist by
executing:
CREATE TABLE theusualviews(
name VARCHAR(64) NOT NULL 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,
PRIMARY KEY(db,name)
);
and a Save button appears on the query-edit form.
Per page: Rows/page dropdown appears on the dashboard when the query is pageable. Possible values are 5,10,20,50,100,500. To change these, edit $pagelens in theusual.php.
Prev, Top, Next, Last: Previous, top, next, last rows-per-page rows
Detail table: Dropdown of ACID-compliant tables in the current database with a foreign key referencing the current table
Custom query: Custom query form. As of v1.14, supports CREATE PROCEDURE | FUNCTION | TRIGGER. Specify an alternative delimiter before, eg DELIMITER //, and after, eg END; //.
Browse database: Browse tables, their properties and their columns in the current database, with options to Analyze, Check, Optimize, Repair or Drop tables. To use Browse Database as a table/column reference with Custom Query, click first on Browse Database, then on Custom Query; Your query text is preserved as you click on table names. Starting with 1.19, Browse database also lists the selected database's stored routines.
Up to 1.19, Text and Use infoschema are clickable settings on TheUsual's statusbar. Starting with 1.20, they are replaced by a Settings prompt which brings up a Settings form allowing you to toggle text expansion, stripping of HTML tags from input queries, and use of information_schema for metadata. This setting is provided because the more databases and tables there are on the server, the slower information_schema is. The first information_schema access can take minutes on servers with dozens of databases and thousands of tables.
Exit: Exit.
1.31 Corrected handling of multiqueries and of queries with FROM clause derived tabloes
1.30 Improved handling of ENUMs, SETs and BOOLs 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