TheUsual for MySQL 4-5-6 and PHP 4-5 

Published under the Creative Commons Licence
by Artful Software Development http://www.artfulsoftware.com
Version 1.42, 17 May 2010

Topics

• Download
• Requirements
• Configuring MySQL and PHP
• Install TheUsual on your own server
• Install and password-protect TheUsual on a non-dedicated web server
• User authentication
• Front end to manage connections to multiple servers
• User interface
• Change history

TheUsual/PHP exists to provide a free, fast, user-friendly web browser 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 4 or preferably 5. 

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:

• automatic queries 
• ad hoc queries 
• saved queries
• automatic master-detail browsing (MySQL 5 only) 
• find key values 
• result pagination and sorting 
• add, edit, copy and delete data
• stored routines (MySQL 5 only)
• Views (MySQL 5 only)
• database browsing with database maintenance commands

The basic design and look-and-feel of TheUsual/PHP are described in Chapter 12 of Get it Done with MySQL 5, excerpted here. 

Download

To download TheUsual/PHP, visit http://www.artfulsoftware.com/theusual.html and scroll to the bottom of the page.

Requirements

TheUsual/PHP requires: 

• MySQL 4 or later for one-table browsing, MySQL 5.0.7 or later for master-detail browsing. The latest production or release-candidate version of MySQL is usually best.
• PHP 4.3 or later running in a web server, usually either Apache or Internet Information Services (IIS). 
• Configuration of PHP to communicate with MySQL.

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. 

Configuring PHP to work with and MySQL

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

How to install TheUsual on your own server

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
theusualcall.php
theusualcall.ini

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.

How to install and password-protect TheUsual on a non-dedicated web server

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" );

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, bookmark the page, and enter your username and password.

Other user authentication alternatives

TheUsual/PHP needs three MySQL authentication settings---$host, $user, $pswd---for authentication against the mysql.user table. These settings can be ...

1. passed to theusual_start.php as $_POST[] or $_GET[] values from theusualcall.php or from your custom login page, OR
2. set as $_SESSION[] variables after the call to session_start() in theusual_start.php and before it calls theusual.php, OR
3. specified as $host, $user and $pswd in theusual-login.php as described above, OR
4. set as environment variables named HOST, USER and PSWD respectively.

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" ;
?>

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' ).

Front end for multiple instances of TheUsual and MySQL

Beginning with version 1.35, theusualcall.php provides a simple frontend for selecting and running any instance of TheUsual you have access to, against any MySQL database server you have access to.

It requires a configuration file named theusualcall.ini containing two kinds of configuration lines ...

1. For each available installation of TheUsual, one line of comma-separated key=value pairs specifying the instance's name, group, host and path:

theusual = your name for this instance of TheUsual; this will be its selectable name in theusualcall.php

group = your name for the group to which this instance of TheUsual belongs; installations of TheUsual with a given group name can access only MySQL database servers with the same group name

host = the LAN machine name or URL where this instance of TheUsual lives (host pingability is not verified)

path = the path at host to the file which invokes this installation of TheUsual (for example theusual_start.php)

For example, suppose you have a LAN where you can invoke TheUsual from these two intranet URLs ...

http://mylaptop/theusual/theusual_start.php
http://mydesktop/php/theusual/theusual_start.php

and suppose you also have access to a web installation of TheUsual at ...

http://www.myclientswebsite.com/theusual/mylogin.php

then theusualcall.ini needs a line for each of these three instances ...

theusual=thelaptop, group=lan, host=mylaptop, path=/theusual/theusual_start.php
theusual=thedesktop, group=lan, host=mydesktop, path=/php/theusual/theusual_start.php
theusual=myclient's site, group=myclient, host=www.myclientswebsite.com, path=/theusual/mylogin.php

2. For each available MySQL server login, one line of comma-separated key=value pairs specifying the mysql server name, its group as defined above, and optionally the username (user) and password (pswd) for that login:

mysql = the name of this MySQL server; if it is on your LAN, this is the LAN machine's name; if it is on the web, it is the MySQL server host name specified by the hosting provider

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 name

user = the mysql.user.User value for this login; if you do not pass it, the called instance of TheUsual must use another method of user/pswd authentication;

pswd = the mysql.user.Password value for this login; if you do not pass it, the called instance of TheUsual must use another method of user/pswd authentication;

For the above example, theusualcall.ini would have:

mysql=mylaptop, group=lan, user=thisusername, pswd=thispassword
mysql=mydesktop, group=lan, user=anotherusername, pswd=anotherpassword
mysql=mydesktop, group=lan, user=yetanotherusername, pswd=yetanotherpassword
mysql=webprovider_name, group=myclient

where the first three lines specify logins available to either of the group=lan instances of TheUsual defined above, and the last line tells theusualcall.php to pass only the mysql name to the instance of TheUsual at the myclient site, and not to send user and pswd with that call (on the assumption that mylogin.php will collect and authenticate those values).

theusualcall.php ignores lines in theusualcall.ini that don't begin with 'theusual' or 'mysql'. A sample theusualcall.ini is provided.

Sessions

Any number of independent 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 http://artfulsoftware.com/infotree/mysqltips.php.

User interface

On load, 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.

Views and saved custom queries

MySQL 5 implements Views. When run against MySQL 5 or later, theUsual/PHP lists Views as tables. To create a View, run a CREATE VIEW statement as a custom query. To run a View, select it from the Tables dropdown.

TheUsual can also save custom query SQL to a saved queries table named theusualviews (in the mysql db if privileges allow, otherwise in the current db), and retrieves such queries for editing and execution. Why save queries if you can just CREATE OR REPLACE VIEW? MySQL Views have serious limitations—they even disallow subqueries in the FROM clause!

Enable saved custom queries 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 custom query form. To manage your saved queries, select theusualviews from the Tables dropdown for the appropriate database, and browse it as you would any other table.

User controls

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. Beginning with v1.33, TheUsual also lets you add to this list master-detail table relationships without declared foreign keys. To enable this, in theusual_start.php specify the name of the database which is to hold the table specifying all such non-ACID master-detail relationships, for example if the database for this table is named admin, write:

$_SESSION['sysdb'] = 'admin';

The next time TheUsual runs, it will create a table named theusualfks in the database named by $_SESSION['sysdb']. Beware that TheUsual does not yet validate such entries; it just tries to execute them as if they were real foreign keys, and reports an error if the specification is incorrect. Here is an example. If you downloaded the database script world.sql for the sample MySQL world database from http://downloads.mysql.com/docs/world.sql.gz or http://downloads.mysql.com/docs/world.sql.zip, you probably noticed that the tables are MyISAM, not InnoDB. To make the master-detail links from the country table to the city and countrylanguage tables visible to TheUsual, use TheUsual to browse theusualfks in the database named by $_SESSION['sysdb'] and insert these two rows:

Now when you browse world.country in TheUsual, you can choose city or countrylanguage as a child table:

Beginning with v1.37, 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 which 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.

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 v1.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/blob formatting, stripping of HTML tags from input queries, and use of information_schema for metadata (this latter 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).

Default settings and the Settings panel:

As of v1.39, you can change the default MySQL character set in the Settings panel; the default, $_SESSION['charset'], is utf8. When you change the setting, TheUsual issues matching HTML charset and MySQL SET NAMES commands on the next page repaint.

As of v.1.37, the default setting of $_SESSION['use_info_schema'], which determines whether to use information_schema for metadata, is FALSE because MySQL queries on information_schema are so painfully slow when there are many databases and tables.

The default setting of $_SESSION['views'], which allows TheUsual to save queries in a database table and retrieve them for execution, is TRUE; and the default setting of $_SESSION['sysdb'], the name of the database where TheUsual will look for its table theusualfks storing data-driven non-InnoDB foreign key specifications, is sys.

To change these while TheUsual is running, click on Settings. To change the default startup settings, edit theusual_start.php. Do not specify a character with an ASCII value > 127 as a separator character.

Exit: Exit.

Change history