-- Create tracker database CREATE DATABASE IF NOT EXISTS tracker; USE tracker; CREATE TABLE IF NOT EXISTS parties( partyID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, companyname CHAR(50) NOT NULL, PRIMARY KEY (partyID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS roles( roleID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, roleRank SMALLINT NOT NULL, PRIMARY KEY (roleID), UNIQUE UC_roleRank (roleRank)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS usecases( usecaseID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, roleID INT NOT NULL, FOREIGN KEY (roleID) REFERENCES roles (roleID), PRIMARY KEY (usecaseID), INDEX roleID (roleID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS users( userID INT NOT NULL AUTO_INCREMENT, partyID INT, username CHAR(10) NOT NULL, password CHAR(10) NOT NULL, status SMALLINT NOT NULL, date_created DATETIME NOT NULL, date_edited DATETIME, entered_by INT, FOREIGN KEY (partyID) REFERENCES parties (partyID), PRIMARY KEY (userID), INDEX userpartyID (partyID), INDEX userenterID (entered_by)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS userroles( userroleID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, roleID INT NOT NULL , userID INT NOT NULL , INDEX ur_roleID( roleID ) , INDEX ur_userID( userID ) , FOREIGN KEY ( roleID ) REFERENCES roles( roleID ) , FOREIGN KEY ( userID ) REFERENCES users( userID ) ) ENGINE = InnoDb; CREATE TABLE IF NOT EXISTS professions( professionID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, description TEXT, date_created DATETIME NOT NULL, last_edited DATETIME, created_by INT NOT NULL, edited_by INT NOT NULL, dependsonID INT NOT NULL, modifiable TINYINT, PRIMARY KEY (professionID), INDEX usercreateID (created_by), INDEX usereditID (edited_by), INDEX profdependsID (dependsonID), FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID) ) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS skills( skillID INT NOT NULL AUTO_INCREMENT, name CHAR(50) NOT NULL, description TEXT, date_created DATETIME NOT NULL, last_edited DATETIME, created_by INT NOT NULL, edited_by INT NOT NULL, professionID INT, modifiable TINYINT, FOREIGN KEY (professionID) REFERENCES professions (professionID), PRIMARY KEY (skillID), INDEX skillprofID (professionID) ) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS party_professions( partyprofID INT NOT NULL AUTO_INCREMENT, creation_date DATETIME, partyID INT, professionID INT, FOREIGN KEY (partyID) REFERENCES parties (partyID), FOREIGN KEY (professionID) REFERENCES professions (professionID), PRIMARY KEY (partyprofID), INDEX partyID (partyID), INDEX professionID (professionID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS party_skills( partyskillID INT NOT NULL AUTO_INCREMENT, date_created DATETIME, partyID INT, skillID INT, FOREIGN KEY (partyID) REFERENCES parties (partyID), FOREIGN KEY (skillID) REFERENCES skills (skillID), PRIMARY KEY (partyskillID), INDEX pspartyID (partyID), INDEX psskillID (skillID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS addresstypes( addresstypeID INT NOT NULL AUTO_INCREMENT, name CHAR(10), PRIMARY KEY (addresstypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS addresses( addressID INT NOT NULL AUTO_INCREMENT, partyID INT, street_address CHAR(50), address_detail CHAR(50), city CHAR(50), state_prov CHAR(10), postal_code CHAR(10), phone_voice CHAR(14), phone_fax CHAR(14), phone_cell CHAR(14), email CHAR(100), url CHAR(100), addresstypeID INT, FOREIGN KEY (addresstypeID) REFERENCES addresstypes (addresstypeID), FOREIGN KEY (partyID) REFERENCES parties (partyID), PRIMARY KEY (addressID), INDEX addrpartyID (partyID), INDEX addresstypeID (addresstypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS contractor_client( contcliID INT NOT NULL AUTO_INCREMENT, clientpartyID INT, contractorpartyID INT, linktype CHAR(10), Comment TEXT, FOREIGN KEY (clientpartyID) REFERENCES parties (partyID), FOREIGN KEY (contractorpartyID) REFERENCES parties (partyID), PRIMARY KEY (contcliID), INDEX contractorpartyID (contractorpartyID), INDEX clientpartyID (clientpartyID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS projects( projectID INT NOT NULL AUTO_INCREMENT, name CHAR(50), contcliID INT, date_created DATETIME, created_by INT, date_edited DATETIME, edited_by INT, FOREIGN KEY (contcliID) REFERENCES contractor_client (contcliID), FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID), PRIMARY KEY (projectID), INDEX projcontcliID (contcliID), INDEX projcreateID (created_by), INDEX projeditID (edited_by)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS budgitemtypes( budgtypeID INT NOT NULL AUTO_INCREMENT, code CHAR(10), description TEXT, PRIMARY KEY (budgtypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS tasktypes( tasktypeID INT NOT NULL AUTO_INCREMENT, name CHAR(50), PRIMARY KEY (tasktypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS project_tasks( taskID INT NOT NULL AUTO_INCREMENT, name CHAR(50), description TEXT, date_to_start DATETIME, date_to_finish DATETIME, start_date DATETIME, completion_date DATETIME, status SMALLINT, projectID INT, tasktypeID INT, created_by INT, date_created DATETIME, edited_by INT, date_edited DATETIME, FOREIGN KEY (projectID) REFERENCES projects (projectID), FOREIGN KEY (tasktypeID) REFERENCES tasktypes (tasktypeID), FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID), PRIMARY KEY (taskID), INDEX projtaskID (projectID), INDEX projtasktypeID (tasktypeID), INDEX taskcreateID (created_by), INDEX taskeditID (edited_by)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS budgetitems( budgitemID INT NOT NULL AUTO_INCREMENT, amount DECIMAL(10,2), external_code CHAR(25), description TEXT, date_created DATETIME, created_by INT, budgtypeID INT, date_edited DATETIME, edited_by INT, taskID INT, FOREIGN KEY (budgtypeID) REFERENCES budgitemtypes (budgtypeID), FOREIGN KEY (taskID) REFERENCES project_tasks (taskID), FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID), PRIMARY KEY (budgitemID), INDEX budgitemcreateID (created_by), INDEX budgitemeditID (edited_by), INDEX budgtypeID (budgtypeID), INDEX taskbudgID (taskID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS chargetypes( chargetypeID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (chargetypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS charges( chargeID INT NOT NULL AUTO_INCREMENT, external_code CHAR(25), transaction_date DATE, enter_date DATETIME, description TEXT, chargetypeID INT, budgitemID INT, correctionID INT, FOREIGN KEY (budgitemID) REFERENCES budgetitems (budgitemID), FOREIGN KEY (chargetypeID) REFERENCES chargetypes (chargetypeID), PRIMARY KEY (chargeID), INDEX chgtypeID (chargetypeID), INDEX budgitemID (budgitemID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS suspense( suspenseID INT NOT NULL AUTO_INCREMENT, external_code CHAR(25), transaction_date DATE, enter_date DATETIME, description TEXT, chargetypeID INT, budgitemID INT, created_by INT, edited_by INT, FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID), PRIMARY KEY (suspenseID), INDEX suspcreateID (created_by), INDEX suspeditID (edited_by)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS charge_corrections( correctionID INT NOT NULL AUTO_INCREMENT, external_code CHAR(25), transaction_date DATE, enter_date DATETIME, description TEXT, created_by INT, suspenseID INT, FOREIGN KEY (suspenseID) REFERENCES suspense (suspenseID), FOREIGN KEY (created_by) REFERENCES users (userID), PRIMARY KEY (correctionID), INDEX corrcreateID (created_by), INDEX suspID (suspenseID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS invoices( invoiceID INT NOT NULL AUTO_INCREMENT, invdate DATE, invamount DECIMAL(10,2), fedtax DECIMAL(10,2), statetax DECIMAL(10,2), interest DECIMAL(10,2), date_created DATETIME, external_ref CHAR(25), created_by INT, edited_by INT, FOREIGN KEY (created_by) REFERENCES users (userID), FOREIGN KEY (edited_by) REFERENCES users (userID), PRIMARY KEY (invoiceID), INDEX invcreateID (created_by), INDEX inveditID (edited_by)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS invoiceitems( invitemID INT NOT NULL AUTO_INCREMENT, amount DECIMAL(10,2), external_code CHAR(25), description TEXT, date_created DATETIME, created_by INT, date_edited DATETIME, edited_by INT, taskID INT, invoiceID INT, FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID), FOREIGN KEY (taskID) REFERENCES project_tasks (taskID), PRIMARY KEY (invitemID), INDEX invtaskID (taskID), INDEX invitemID (invoiceID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS pmt_types( pmttypeID INT NOT NULL AUTO_INCREMENT, name CHAR(10), number_len SMALLINT, PRIMARY KEY (pmttypeID)) ENGINE=InnoDb; CREATE TABLE IF NOT EXISTS payments( paymentID INT NOT NULL AUTO_INCREMENT, pmtdate DATE, pmtamount DECIMAL(10,2), date_created DATETIME, date_edited DATETIME, external_ref CHAR(25), pmt_code CHAR(25), expiry CHAR(5), pmt_aux_code CHAR(10), description TEXT, invoiceID INT, pmttypeID INT, FOREIGN KEY (invoiceID) REFERENCES invoices (invoiceID), FOREIGN KEY (pmttypeID) REFERENCES pmt_types (pmttypeID), PRIMARY KEY (paymentID), INDEX pmtinvID (invoiceID), INDEX pmttypID (pmttypeID)) ENGINE=InnoDb;
<!-- docs.php -->
<html>
<head>
<title>Docs</title>
<style>
p { color: #990000; }
</style>
</head>
<body>
<?php
require_once( "session_start.php" );
if( isset( $_GET['table'] )) {
$url = "doc.php?_sess=" . $_GET['_sess'] . "¬estopic=" . $_GET['table'];
echo "<script language='JavaScript'>parent.location='$url';</script>";
} else {
$url = "";
}
$conn = mysql_pconnect( "localhost", "pietari", "perkele" )
or die( "Failed to connect to MySQL server" );
mysql_select_db( "docs", $conn ) or die( "Could not select docs database" );
$sql = "SELECT table_name FROM information_schema.tables " .
"WHERE table_schema = 'docs' AND table_name <> 'docs'";
$res = mysql_query( $sql );
// TABLE-VIEW SELECTOR FORM
$tbl = (isset( $_SESSION['table'] )) ? $_SESSION['table'] : "";
echo "<br><form name='viewForm' id='prompt' action='docs.php' method='GET'>\n";
echo " <INPUT type='hidden' name='_sess' value='$session_name'>\n";
echo " <p><b>Select docs table:</b></p> \n";
echo " <SELECT name='table' onChange='submit()'>\n";
while( $row = mysql_fetch_row( $res )) {
$sel = ( $tbl === $row[0] ) ? "id='sel' selected" : "";
printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] );
}
echo " </select>\n";
echo " <input id='table' type='button' value='Select a table' onClick='submit()'>\n";
?>
</body></html>
<?php
// doc.php
require_once( "session_continue.php" );
if( isset( $_GET["notestopic"] )) {
$_SESSION['db'] = 'docs';
$_SESSION['hdr'] = $_GET["notestopic"];
$_SESSION['table'] = $_GET["notestopic"];
$_SESSION['keycol'] = "topic";
$_SESSION['txtcol'] = "text";
}
echo "<html><head><title>NoteMan</title></head><body>\n";
if( !isset( $_SESSION['db'] )) die( "NoteMan: incorrect configuration" );
require_once( "noteman.php" );
echo "</body></html>\n";
?>
<html>
<head>
<title><?php $_SESSION['hdr'] . "Notes" ?></title>
<style>
body, p, form, option, select, input, textarea { color: #3300ff; }
p { width: 50em; }
h3 { color: #990000; }
.code { width:200%; font-size:8pt; font-family:courier; }
</style>
<script>
function cancel( f, url ) {
f.action=url;
f.submit();
}
</script>
</head>
<body>
<?php
// noteman.php
// included in doc.php so session_contunue call needed
if( isset( $_GET['back'] )) header( "Location: docs.php" );
else if( isset( $_POST['back'] )) header( "Location: docs.php" );
$db = $_SESSION['db'];
$hdr = $_SESSION['hdr'];
$table = $_SESSION['table'];
$url = $_SERVER['PHP_SELF'];
$urlreturn = $url."?_sess=".$session_name;
// CONNECT
$conn = mysql_connect( "localhost", "webauth", "webauth" );
mysql_select_db( $db, $conn ) or exit( "Cannot connect to $db database" );
// ID
if( isset( $_POST['id'] )) $_SESSION['id'] = $_POST['id'];
else if( isset( $_GET['id'] )) $_SESSION['id'] = $_GET['id'];
else if( !isset( $_SESSION['id'] )) $_SESSION['id'] = 0;
$id = $_SESSION['id'];
echo "<font color='#990000'><b><i>Please select a $hdr topic...</i></b></font>\n";
// ACTION/TOPIC SELECTOR FORM. RESULT POSTS TO SELF.
// echo "<FORM action='$urlreturn' METHOD='POST'>\n";
echo "<FORM action='$url' METHOD='GET'>\n";
echo "<INPUT type='hidden' name='_sess' value='$session_name'>\n";
echo "<SELECT name='id' SIZE='8' onChange='submit()'>\n";
$query = "SELECT topic,id FROM $table ORDER BY topic";
$result = mysql_query( $query, $conn );
if ( $myrow = mysql_fetch_array( $result )) {
$spc = " ";
do {
$sel = ( $myrow[1] == $id ? "SELECTED='SELECTED'" : "" );
printf( "<option %s value='%s'>%s %s</option>\n", $sel, $myrow[1], $myrow[0], $spc );
} while ($myrow = mysql_fetch_array($result));
}
echo "</SELECT><p></p>\n";
echo "<INPUT type='Submit' name='view' value='View'>
<INPUT type='Submit' name='viewer' value='Read in Viewer'>
<INPUT type='Submit' name='edit' value='Edit'>
<INPUT type='Submit' name='add' value='Add'> \n";
echo "<INPUT type='Submit' name='back' value='Back to Docs'>\n";
echo "</FORM>\n"; // END FORM
// DEAL WITH USER CHOICE
if( isset( $_GET['viewer'] )) {
$viewer = $_GET['viewer'];
} else {
$viewer = 0;
}
if( isset( $_GET['view'] )) {
$view = $_GET['view'];
} else {
$view = 0;
}
if( isset( $_GET['cancel'] )) { // ALWAYS A GET
$edit = 0;
} else if( isset( $_GET['edit'] )) {
$edit = $_GET['edit'];
} else {
$edit = 0;
}
if( isset( $_GET['add'] )) {
$add = $_GET['add'];
} else {
$add = 0;
}
if (isset( $_GET['topic'] )) {
$topic = $_GET['topic'];
}
// VIEW IS THE DEFAULT
if( $edit || $add || $viewer ) {
$view = 0;
$_SESSION['view'] = 0;
} else {
$view = 'View';
$_SESSION['view'] = $view;
}
// ADD TOPIC
if ($add) {
echo "<FORM action='notesins.php' method='POST'>\n";
echo "<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n";
echo "Topic: <INPUT name='topic' size=72>\n";
echo "<br>Text:<br>\n";
echo "<TEXTAREA name='text' rows=20 cols=96>\n";
echo "";
echo "</TEXTAREA>\n";
echo "<p></p>\n";
echo "<INPUT type='hidden' name='table' value=$table>";
echo "<INPUT type='submit' name='update' value='Update'>";
echo " \n";
echo "<input type='button' value='Cancel' onClick='cancel(this.form,\"$urlreturn\")'>\n";
echo "</FORM>\n";
}
elseif( $id ) {
$query = "SELECT topic,text FROM $table WHERE id=$id";
$result = mysql_query( $query, $conn );
if( $result ) {
$myrow = mysql_fetch_array( $result );
$topic = $myrow[0];
$text = $myrow[1];
}
// VIEW TOPIC TEXT
if ($view) {
echo "<h3 font-color='#990000'>".$topic."</h3>\n";
if( $myrow ) {
// echo "<p style='width:50em;'>" . nl2br( $myrow[0] ) . "</p>\n";
// echo "<p style='width:50em;'>" . $myrow[0] . "</p>\n";
echo "<pre width='100'>" . $text . "</pre>\n";
}
// SEND TO THE VIEWER
} elseif( $viewer ) {
if( $myrow ) {
$_SESSION['txt'] = $text;
$_SESSION['title'] = $topic;
$url = "txtbrows/txtbrows.php?_sess=".$session_name;
echo "<script language='JavaScript'>parent.location='$url';</script>";
// echo $url,"<br/>\n";
// header( "Location: $url" );
}
// EDIT TOPIC TEXT
} elseif( $edit ) {
if ( $myrow ) {
echo "<FORM action='notesupd.php' method='POST'>\n";
echo "<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n";
echo "<font color='#990000'><b>Header: <INPUT name='topic' value='$topic' size=72></b></font><br/>\n";
echo "<TEXTAREA name='text' rows=20 cols=96>\n";
echo $text;
echo "</TEXTAREA>\n";
echo "<p></p>\n";
echo "<INPUT type='hidden' name='table' value=$table>\n";
echo "<INPUT type='hidden' name='id' value=$id>\n";
echo "<INPUT type='submit' name='update' value='Save'> \n";
echo "<INPUT type='button' value='Cancel' onClick='cancel(this.form,\"$urlreturn\")'>\n";
echo "</FORM>\n";
} // fetch loop
} // edit loop
} // topic loop
mysql_close();
?>
</body>
</html>
#EOF
<?
// notesins.php
require_once( "session_continue.php" );
$conn = mysql_pconnect( "localhost", "webauth", "webauth" );
mysql_select_db( $_SESSION['db'], $conn );
// Initialise vars from POST
$table = $_POST['table'];
$topic = $_POST['topic'];
$text = $_POST['text'];
$url = "doc.php?_sess=$session_name";
if( isset( $_POST['update'] )) {
// ESCAPE QUOTES, INSERT:
$text = addslashes( str_replace( "<", "<", $text ));
$topic = addslashes( $topic );
$sql = "INSERT INTO $table (topic,text) VALUES('$topic','$text')";
$result = mysql_query($sql);
if ($result) {
echo "Thank you! Information entered.<br>\n";
$_SESSION['topic'] = $topic;
$_SESSION['view'] = 'View';
gohome();
}
else {
echo mysql_error();
echo "Click <a href='$url'>here</a> to return to ", "$table.";
}
}
else {
gohome();
}
function gohome() {
GLOBAL $url;
echo "<html><head>";
echo "<script language='JavaScript'>parent.location='$url';</script>";
echo "</body></html>";
}
?>
#EOF
<?
// notesupd.php
require_once( "session_continue.php" );
$conn = mysql_pconnect( "localhost", "webauth", "webauth" )
or exit( "Cannot connect to localhost: " . mysql_error() );
mysql_select_db( $_SESSION['db'], $conn ) or exit( "Cannot select $db: " . mysql_error() );
// Initialise vars from POST
$table = $_POST['table'];
$id = $_POST['id'];
$topic = $_POST['topic'];
$text = $_POST['text'];
$update = $_POST['update'];
$url = "doc.php?_sess=$session_name";
if( $id ) {
// ESCAPE QUOTES, UPDATE:
$topic = addslashes( $topic);
$text = addslashes( str_replace( "<", "<", $text ));
$sql = "UPDATE $table SET topic='$topic',text='$text' WHERE id=$id";
// STUB
// printf( "<PRE>Query = " . "$sql" . "</PRE><br>\n" );
$result = mysql_query($sql);
if ($result) {
// THIS PAGE IS INVISIBLE IF UPDATE SUCCEEDS
$_SESSION['id'] = $id;
$_SESSION['view'] = 'View';
echo "Thank you! Information entered.<br>\n";
go_home();
}
else {
// ERROR REPORT
echo "<html><head>Update error page</head><body>";
echo mysql_error()."<BR>";
echo "Could not complete the update.<br>";
echo "Click the <i>Back</i> button to return to $table.";
echo "</body></html>";
}
}
else {
go_home();
}
function go_home() {
GLOBAL $url;
echo "<html><head>";
echo "<script language='JavaScript'>parent.location='$url';</script>";
echo "</head><body>Click <a href='$url'>here</a> to return to ", "$table.";
echo "</body></html>";
}
?>
The most recent source code needed to install and run theUsual for PHP is in theUsualmysql.rar and theUsualmysqli.rar in the Get It Done With MySQL 5 archive.
<?php
// the_usual_start.php
ini_set( "session.gc_maxlifetime", 1800 );
$session_name = 'theUsual' . str_replace( ".", "", microtime(true) );
session_name( $session_name );
session_start();
echo "<script language='JavaScript'>parent.location.replace('theusual.php?_sess=$session_name');</script>\n";
?>
<?php
// session_continue.php
if( isset( $_GET['_sess'] )) $session_name = $_GET['_sess'];
else if( isset( $_POST['_sess'] )) $session_name = $_POST['_sess'];
else die( "Session configuration error<br>\n" );
session_name( $session_name );
session_start();
?>
<?php
// theusual.php
require_once( "session_continue.php" );
$theusual_version = "1.05";
set_time_limit( 0 );
$_SESSION['debug'] = FALSE;
?>
<html>
<head>
<title>TheUsual for MySQL mysql lib</title>
<!-- by Artful Software Development -->
<LINK href="theusual.css" rel="stylesheet" type="text/css">
<script language="javascript1.2">
function db_submit( f ) {
clearview();
f.submit();
}
function table_submit( f ) {
clearview();
f.submit();
}
function clearview() {
if( document.DbForm.view == undefined ) {}
else if( document.DbForm.view.value === "" )
document.DbForm.removeChild( document.DbForm.view );
}
function query_submit( f ) {
f.submit();
}
function view_submit( f ) {
f.db.value = '';
f.table.value = '';
f.submit();
}
function qryForm_submit( arg ) {
if( document.qryForm.name.value === "" || document.qryForm.qry.value === "" ) {
alert( "Query name and text must not be blank" );
return false;
}
document.qryForm.method = 'POST';
document.qryForm.action = arg;
document.qryForm.submit();
}
function queryexec( arg ) {
document.qryForm.method = 'POST';
document.qryForm.action = arg;
document.qryForm.submit();
}
function fcancel( f, url ) {
f.action=url;
f.submit();
}
</script>
</head>
<body>
<?php
// PERMIT CALLS AND OTHER NON-SELECT STATEMENTS?
$permit_non_select = TRUE;
// DEFINITIONS
define( "CLIENT_MULTI_STATEMENTS", 65536 ); // support sproc calls
// FLAGS
define( "IS_NEW_QUERY", 0 );
define( "MAY_EDIT", 1 );
define( "IS_EDIT", 2 );
define( "IS_ADD", 3 );
define( "IS_COPY", 4 );
define( "IS_EDITQRY", 5 );
define( "IS_DETAIL_TABLE", 6 );
define( "HILITEFIRST", 7 );
// POSSIBLE $qry_type VALUES:
define( "IS_SELECT", 1 );
define( "IS_AUTOSEL", 2 );
define( "IS_CALL", 3 );
define( "IS_LIST", 4 );
define( "IS_CMD", 5 );
define( "IS_DISALLOWED", -1 );
// COMMANDS TO SCAN FOR
$cmds = array( 'ALTER','ANALY','BACKU','BEGIN','CACHE','CHANG','CHECK','COMMI',
'CREAT','DELET','DROP ','FLUSH','GRANT','INSER','LOAD ','OPTIM',
'PURGE','RESET','RESTO','ROLLB','START','STOP ','TRUNC','UPDAT' );
// USER CLICKED EXIT?
if( !empty( $_GET['exit'] )) {
$_SESSION[] = array();
session_destroy();
exit( "Thank you for using TheUsual-MySQL<br>\n" );
}
require_once( "theusual-funcs.php" );
theusual_init();
connset();
$detail_enabled = ( substr( scalar_qry_result( "SELECT VERSION()" ), 0, 1 ) >= "5" );
$qry_type = NULL;
$may_query = TRUE;
if( $detail_enabled ) {
// $_SESSION['detail'] SUBARRAY
define( "CHILDDB" , 0 );
define( "CHILDTABLE" , 1 );
define( "CHILDKEYCOL" , 2 );
define( "PARENTKEYCOL", 3 );
}
/*
* TABLE DATA ROW APPEARANCE WHEN MOUSE IS OVER THE ROW
*/
define( "ROWHILITE",
"onmouseover=\"javascript:this.style.background='#ddddff'\"
onmouseout=\"javascript:this.style.background=''\"" );
// TABLE DATA ROW APPEARANCE WHEN ITS KEY VALUE MATCHES USER FIND ARGUMENT
define( "FINDROWHILITE",
"onmouseover=\"javascript:this.style.background='#ccccff'\"
onmouseout=\"javascript:this.style.background='#fdb900'\"" );
/* ---------------------- SESSION VARIABLES -------------------------- */
if( isset( $_GET['debug'] )) {
$debug = $_GET['debug'];
$_SESSION['debug'] = $debug;
}
elseif( isset( $_SESSION['debug'] )) {
$debug = $_SESSION['debug'];
}
else {
$debug = 0;
}
if( !isset( $_SESSION['crlf'] )) $_SESSION['crlf'] = FALSE;
if( isset( $_GET['crlf'] )) $_SESSION['crlf'] = !$_SESSION['crlf'];
$thispage = $_SERVER['PHP_SELF'];
$thispagesess = "$thispage?_sess=$session_name";
$_SESSION['home'] = $thispage;
$pagelens = array( 5, 10, 20, 50, 100 );
$is_new_query = false;
// UNSET QUERY VARS IF TABLENAME PASSED IN
if( isset( $_GET['table'] )) {
$_SESSION['qry'] = NULL;
$_SESSION['querytype'] = -1;
$is_new_query = TRUE;
}
// QUERY SPECIFIED?
if( isset( $_GET['view'] )) {
$_SESSION['control'] = 'view';
$viewobj = view_init( $_GET['view'], $_GET['db'] );
}
else {
unset( $_SESSION['view'] );
}
if( isset( $viewobj ) && is_object( $viewobj )) {
query_init( $viewobj->Query );
}
else if( isset( $_POST['qry'] )) {
query_init( $_POST['qry'] );
}
elseif( !empty( $_SESSION['qry'] )) {
$qry = $_SESSION['qry'];
$qry_type = $_SESSION['querytype'];
}
else {
$qry = '';
$qry_type = NULL;
}
$_SESSION['querytype'] = $qry_type;
/*
* UPDATE TABLE, IF ANY, IS THE FIRST TABLE NAMED IN THE FROM CLAUSE
*/
if( $qry_type == IS_SELECT ) {
$_SESSION['table'] = query_token( $qry, "FROM" );
asc_desc_init( $qry );
}
/*
* DB SPECIFIED?
*/
if( isset( $_GET['db'] ) && !(isset( $viewobj ) && is_object( $viewobj ))) {
if( strcasecmp( $_GET['db'], $_SESSION['db'] ) != 0 ) {
$_SESSION['control'] = 'db';
$_GET['table'] = NULL;
$_GET['view'] = NULL;
$_SESSION['view'] = NULL;
$_SESSION['viewobj'] = NULL;
}
$_SESSION['qry'] = NULL;
$qry = NULL;
db_init_vars( $_GET['db'] );
}
elseif( isset( $_SESSION['db'] )) {
$db = $_SESSION['db'];
}
else {
db_init_vars( NULL );
}
/*
* TABLE SPECIFIED?
*/
if( !empty( $_GET['table'] ) && !(isset( $viewobj ) && is_object( $viewobj ))) {
$_SESSION['control'] = 'table';
$_SESSION['qry'] = NULL;
$qry = NULL;
table_init_vars( $_GET['table'] );
}
elseif( isset( $_SESSION['table'] )) {
$table = $_SESSION['table'];
}
else {
table_init_vars( NULL );
}
/*
* IF USER CLICKED DETAIL DROPDOWN, PROCESS CHOICE, ELSE USE $_SESSION VAR
*/
if( isset( $_GET['detail'] )) {
if( empty( $_GET['detail'] )) {
$detail = NULL;
$_SESSION['detail'] = NULL;
$_SESSION['parent_key'] = NULL;
} else {
$_SESSION['detail'] = explode( ".", $_GET['detail'] );
$detail = $_SESSION['detail'];
}
} elseif( isset( $_SESSION['detail'] )) {
$detail = $_SESSION['detail'];
} else {
$detail = NULL;
}
/*
* IF THERE IS A QUERY, INITIALISE ORDER_BY, ASC_DESC
*/
if( $qry ) {
$order_by=NULL; $ord_col=NULL;$asc_desc=NULL;
orderby_init( $qry, $order_by, $ord_col, $asc_desc, '' );
if( $detail ) {
$det_order_by=NULL; $det_ord_col=NULL;$det_asc_desc=NULL;
orderby_init( $det_qry, $det_order_by, $det_ord_col, $det_asc_desc, 'det_' );
}
}
/*
* DATA PAGE SPECIFIED?
*/
if( isset( $_GET['page'] )) {
$page = $_GET['page'];
$_SESSION['page'] = $page;
} elseif( isset( $_SESSION['page'] )) {
$page = $_SESSION['page'];
} else {
$page = 0;
$_SESSION['page'] = $page;
}
/*
* PAGELEN SPECIFIED?
*/
if( isset( $_GET['pagelen'] )) {
$pagelen = $_GET['pagelen'];
$_SESSION['pagelen'] = $pagelen;
} elseif( isset( $_SESSION['pagelen'] )) {
$pagelen = $_SESSION['pagelen'];
} else {
$pagelen = 10;
$_SESSION['pagelen'] = $pagelen;
}
/*
* INITIALISE IS_ADD, IS_COPY, IS_EDIT
*/
$updmsg = '';
$is_add = 0; $is_edit=0; $is_copy=0;$keyval=NULL;
edit_flags_init( $is_add, $is_copy, $is_edit, $keyval, $updmsg, "" );
if( $detail ) {
$det_is_add = 0; $det_is_edit=0; $det_is_copy=0;$det_keyval=NULL;
edit_flags_init( $det_is_add, $det_is_copy, $det_is_edit, $det_keyval, $updmsg, "det_" );
}
/*
* USER SPECIFIED KEY VALUE TO FIND?
*/
if( !empty( $_GET['findkey'] )) {
$findkey = $_GET['findkey'];
$page = findpage( $findkey );
$_SESSION['page'] = $page;
} else {
$findkey = '';
}
/*
* PARENT_KEY SPECIFIED FOR DETAIL TABLE?
*/
if( isset( $_GET['parent_key'] )) {
$parent_key = $_GET['parent_key'];
$_SESSION['parent_key'] = $parent_key;
} elseif( isset( $_SESSION['parent_key'] )) {
$parent_key = $_SESSION['parent_key'];
} else {
$parent_key = NULL;
$_SESSION['parent_key'] = NULL;
}
$_SESSION['count_time'] = 0;
$_SESSION['query_time'] = Null;
/* ------------------- END OF SESSION VARIABLES ------------------------------ */
// STUBS
if( $debug ) {
require_once( "theusual-stubs.php" );
}
/* ---------------- DATABASE-TABLE-QUERY-VIEW SELECTORS ---------------------- */
$is_editqry = (bool) !empty( $_GET['editqry'] );
$is_view = (bool) ( isset( $viewobj ) && is_object( $viewobj ));
// HEADER
$result = mysql_query( "SELECT VERSION()" ) or err_handler( "Cannot query MySQL" );
$mysql_version = mysql_result( $result, 0 );
echo "<p id='infohdrtop'>",
"THEUSUAL v" . $theusual_version,
". <i>Server</i>: MySQL v" . $mysql_version,
". <i>PHP</i>: v" . PHP_VERSION, " lib=mysql",
". <i>Configuration:</i> Accept ",
( $permit_non_select ? "queries and non-queries. " : "SELECT, SHOW, EXPLAIN and CALL only. " ),
"</p>";
// DATABASE LIST
$result = mysql_query( "SHOW DATABASES" ) or err_handler( "Cannot retrieve database list" );
// DB-TABLE-VIEW SELECTOR FORM
echo "\n<FORM name='DbForm' id='prompt' action='" . $thispage . "' method='GET'>\n",
"<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n",
" <b>Schema:</b> \n <select name='db' onChange='db_submit(this.form)'>\n";
while( $row = mysql_fetch_row( $result )) {
$sel = ( $db === $row[0] ) ? "id='sel' selected" : "";
printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] );
}
echo " </select>\n";
// IF NO DB SELECTED, STOP HERE
if ( empty( $db )) {
echo " <input id='edit' type='button' value='Select a database' $buttonstyle onClick='db_submit(this.form)'>\n",
"</FORM>";
exit();
}
mysql_select_db( $db, $conn ) or err_handler( "Could not select $db database" );
// TABLE SELECTOR
$tables = mysql_query( "SHOW tables" ) or err_handler( "Cannot retrieve table list" );
echo " <b>Table:</b> \n",
" <select name='table' onChange='table_submit(this.form)'>\n";
while( $row = mysql_fetch_row( $tables )) {
$sel = ( $table === $row[0] ) ? "id='sel' selected" : "";
printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] );
}
echo " </select>\n",
" <input id='edit' type='button' value='Select' $buttonstyle onClick='table_submit(this.form)'>\n";
// SAVED QUERY SELECTOR
if( $_SESSION['views'] && !$is_view ) {
if( scalar_qry_result( "SHOW COLUMNS FROM mysql.theusualviews" ) === FALSE ) {
$res = mysql_db_query( "mysql", viewstableddl() );
if( $res === FALSE ) $_SESSION['views'] = FALSE;
}
if( $_SESSION['views'] ) {
$res = mysql_query( "SELECT name FROM mysql.theusualviews WHERE db='$db'" );
$rows = mysql_num_rows( $res );
if( $rows > 0 ) {
echo " <b>Saved query:</b> \n",
" <select name='view' onChange='view_submit(this.form)'>\n";
$sel = ( empty( $_SESSION['view'] ) ? "id='sel' selected" : "" );
echo " <option $sel '[None]' value=''>[None]</option>\n";
while( $row = mysql_fetch_row( $res )) {
if( isset( $_SESSION['view'] ))
$sel = ( strcasecmp( $_SESSION['view'], $row[0] ) == 0 ) ? "id='sel' selected" : "";
else
$sel = "";
printf( " <option %s value='%s'>%s</option>\n", $sel, $row[0], $row[0] );
}
echo " </select>\n",
" <input id='edit' name='viewsel' type='button' value='Select'",
" $buttonstyle onClick='view_submit(this.form)'>";
}
}
}
echo "</FORM>\n";
// IF NO TABLE HAS BEEN SELECTED IMPLICITLY OR EXPLICITLY, STOP HERE
if( $qry_type != IS_LIST && empty( $table ) && !$is_editqry ) {
exit();
}
// WAS A QUERY PASSED IN?
if( ( $qry_type == IS_SELECT ) && !empty( $qry )) {
if( $is_new_query || !isset( $_SESSION['total_rows'] )) {
$countqry = orderby_upd( limit_upd( $qry, "" ), "" );
// HOW MANY ROWS WILL IT RETURN?
$result = count_query( "SELECT COUNT(*) FROM ($countqry) AS usualTemp" )
or err_handler( "The query $countqry failed" );
$total = mysql_result( $result, 0 );
$_SESSION['total_rows'] = $total;
} else {
}
$total = $_SESSION['total_rows'];
$maxpage = ceil( $total / $pagelen ) - 1;
$firstrow = $page * $pagelen;
if( $page > 0 && $page == $maxpage ) {
$diff = $firstrow + $pagelen - $total;
if( $diff > 0 ) {
$firstrow -= $diff;
}
}
$qry = limit_upd( $qry, "$firstrow,$pagelen " );
}
elseif( $qry_type == IS_LIST ) {
// NO PAGING
$result = timed_query( $qry ) or err_handler( "Error" );
$total = mysql_num_rows( $result );
$maxpage = 0;
$firstrow = 0;
$page = 0;
$pagelen = $total;
}
elseif( $qry_type == IS_CMD && $permit_non_select ) {
$result = timed_query( $qry ) or err_handler( "Command error" );
$total = 0;
$maxpage = 0;
$firstrow = 0;
$page = 0;
$pagelen = $total;
}
elseif( $qry_type != IS_CALL && !empty( $table )) {
// CONSTRUCT DEFAULT QUERY FOR SELECTED DB/TABLE
$qry_type = IS_AUTOSEL;
$result = count_query( "SELECT COUNT(*) FROM $table" )
or err_handler( "Error querying $table table" );
$total = mysql_result( $result, 0 );
$_SESSION['total_rows'] = $total;
$maxpage = ceil( $total / $pagelen ) - 1;
$firstrow = $page * $pagelen;
if( $page > 0 && $page == $maxpage ) {
$diff = $firstrow + $pagelen - $total;
if( $diff > 0 ) {
$firstrow -= $diff;
}
}
if( empty( $_SESSION['qry'] )) {
$_SESSION['qry'] = "SELECT * FROM $table\n" .
"ORDER BY 1 ASC " .
"LIMIT $firstrow,$pagelen ";
$qry = $_SESSION['qry'];
$qry_type = IS_SELECT;
$_SESSION['querytype'] = $qry_type;
}
}
elseif( $qry_type == IS_CALL ) {
// IF THIS IS A CALL TO A STORED PROCEDURE, WE CANNOT PAGE
$result = timed_query( $qry ) or err_handler( "Error calling stored procedure" );
$total = mysql_num_rows( $result );
$maxpage = 0;
$firstrow = 0;
$page = 0;
$pagelen = $total;
}
if( $debug ) echo "DB:$db TABLE:$table QUERY:$qry<br/>";
if( $is_editqry ) {
$height = ( $qry_type == IS_CALL ) ? 3 : 10;
$arg = "viewupd.php?_sess=$session_name";
$savearg = ( $_SESSION['views'] ) ? $arg : "$arg&isviews";
echo "<FORM id='prompt' NAME='qryForm' onSubmit=\"return qryForm_submit('$arg')\";>\n",
"<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n",
"<TABLE margin=0 cellpadding=0 border=0>\n";
if( $is_view ) {
echo "<INPUT type='hidden' name='viewID' value='", $viewobj->ViewID,"'>\n";
$name = $viewobj->Name;
}
else $name = "";
echo "<INPUT type='hidden' name='db' value='$db'>\n";
if( $_SESSION['views'] ) {
echo "<tr><td><b>Query name:</b></td>\n",
"<td><INPUT type='text' maxlength=32 name='name' value='$name' size=32></td></tr>\n";
$arg = $thispagesess . "&editqry=1";
if( $is_view ) $arg .= "$&view=" . $viewobj->Name . "&db=$db";
}
else $arg = $thispagesess;
echo "<tr><td valign='top'><b>Query text:</b></td>\n",
"<td><textarea id='edit' name='qry' rows=$height cols=120>$qry</textarea></td>\n",
"<td valign='bottom'> <input type='button' value='Execute' $buttonstyle ",
"onClick='queryexec(\"$arg\")'></td>\n";
if( $_SESSION['views'] )
echo "<td valign='bottom'> <input type='submit' value='Save' $buttonstyle></td>\n";
echo "</tr></TABLE></FORM>\n";
}
if( empty( $qry )) exit( $is_editqry ? "" : "No query to execute" );
/* ----------- END OF DATABASE-TABLE-QUERY-VIEW SELECTORS -------------- */
/*
* DETERMINE IF TABLE IS EDITABLE
*/
if( ( $qry_type == IS_SELECT ) || ( $qry_type == IS_AUTOSEL )) {
$result = timed_query( $qry ) or err_handler( "Query $qry has failed" );
}
if( !$is_editqry ) {
// DISPLAY QUERY AND ITS EXECUTION TIME
echo "<b>Query:</b> $qry; " .
(isset($_SESSION['query_time']) ? " <i>(" . $_SESSION['query_time'] . " secs)</i>" : "" ) .
"<br>\n";
}
$_SESSION['key'] = NULL;
$_SESSION['keycolnum'] = NULL;
$tables = array();
$colobjs = array();
$may_edit = PKinit( $qry_type, $result, $table, $tables, $colobjs );
/* ------------ NAVIGATION HEADER/PAGELEN SELECTOR/FORM ---------------- */
echo "<FORM name='theusualnav' action='" . $thispage . "' method='GET'>\n";
echo "<INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n";
echo " <p id='infohdr'>" . $updmsg . "Rows " . ($firstrow+1) . "-" .
((($page+1)*$pagelen < $total) ? (($page+1)*$pagelen) : ($total)) .
" of $total.";
if( $qry_type != IS_CALL ) {
echo " Per page:\n";
echo " <select name='pagelen' onChange='db_submit(this.form);'>\n";
// SHOW PAGELENS, HIGHLIGHT SELECTED ITEM IF ANY
for( $i = 0; $i < count($pagelens); $i++ ) {
$sel = ( $pagelen == $pagelens[$i] ) ? "id='sel' selected" : "";
printf( " <option %s value='%s'>%s\n", $sel, $pagelens[$i], $pagelens[$i] );
}
echo " </p>\n </select>\n";
}
// PAGINATION: FIRST/PREV
if( $page > 0 ) {
echo " <a id='infohdr' href='$thispagesess&page=0'>Top</a> \n",
" <a id='infohdr' href=\"$thispagesess&page=".($page-1)."\">Prev</a> \n";
}
// PAGINATION: NEXT/LAST
if( (($page+1)*$pagelen) < $total ) {
echo " <a id='infohdr' href=\"$thispagesess&page=".($page+1)."\">Next</a> \n",
" <a id='infohdr' href=\"$thispagesess&page=".($maxpage)."\">Bott</a> \n";
}
if( !($is_edit || $is_add || $is_copy )) {
echo " <a id='infohdr' href='$thispagesess&crlf'>Text</a> \n";
// FIND ON KEY COLUMN IF ANY
if( $maxpage && !empty( $_SESSION['key'] )) {
// $key = ucwords( $colobjs[$_SESSION['keycolnum']]->name );
$key = ucwords( firstPKcolname() );
echo " Find $key:<input type='text' size=8 name='findkey'",
"maxlength=20 value='' onBlur='db_submit(this.form)'> ";
}
// CREATE QUERY OR EDIT QUERY IF PERMITTED & IF NOT ALREADY DOING SO
if( !$is_editqry ) {
if( $is_view && $viewobj->Modifiable ) {
$url = "$thispagesess&editqry=1&view=" . $viewobj->Name . "&db=$db";
echo " <a id='infohdr' href='$url'>Edit query</a>\n";
}
else {
echo " <a id='infohdr' href='$thispagesess&editqry=1'>Custom query</a>\n";
}
}
// DETAIL TABLE SELECTOR: '[None]', or a child table:
if( $detail_enabled && $qry_type == IS_SELECT || $qry_type == IS_AUTOSEL ) {
$tables = mysql_query( childtablesqry( $db, $table )) or err_handler( "Cannot list detail tables" );
echo " Detail Table:<select name='detail' onChange='table_submit(this.form)'>\n";
$det = ( isset( $detail ) ? $detail[1] : '' );
$sel = ( $det === '' ) ? "id='sel' selected" : "";
echo " <option $sel '[None]' value=''>[None]</option>\n";
while( $row = mysql_fetch_row( $tables )) {
$sel = ( $det === $row[1] ) ? "id='sel' selected" : "";
printf( " <option %s value='%s'>%s</option>\n",
$sel,
$row[0].'.'.$row[1].'.'.$row[2].'.'.$row[3],
$row[1]
);
}
echo " </select>\n";
}
}
// RESET AND EXIT OPTIONS
echo " <a id='infohdr' href='". $_SERVER['HTTP_REFERER']."'>Reset</a>\n",
" <a id='infohdr' href='$thispagesess&exit=1'>Exit</a>\n",
"</form></p>\n";
/* --------- END OF NAVIGATION HEADER/PAGELEN SELECTOR/FORM ------------- */
if( $qry_type == IS_CMD ) {
$s = mysql_info( $conn );
if( !empty( $s )) {
echo "MySQL responded: $s";
} else {
echo "OK";
}
exit;
}
/* --------------------------- BROWSE THE DATA --------------------------- */
$flags = array( $is_new_query, $may_edit, $is_edit, $is_add, $is_copy, $is_editqry, false, false );
/*
* SET CURRENT ROW FOR DETAIL BROWSING, IF NECESSARY
*/
if( $detail ) {
if( $det_is_edit ) {
$findkey = $parent_key;
}
elseif(( !empty( $parent_key )) && isset($_GET['page'] ) ) {
$flags[HILITEFIRST] = true;
}
elseif( !empty( $parent_key ) && empty( $findkey )) {
$findkey = $parent_key;
}
elseif(( empty( $findkey )) || isset($_GET['page'] ) ) {
$flags[HILITEFIRST] = true;
}
elseif( !empty( $findkey ) && empty( $parent_key )) {
$parent_key = $findkey;
}
elseif( empty( $parent_key )) {
$parent_key = $_SESSION['toprowkeyval'];
$findkey = $parent_key;
}
elseif( $parent_key != $findkey )
$parent_key = $findkey;
}
// BROWSE MASTER TABLE
browser( $thispage, $table, $colobjs, $qry, $result, $total, $findkey, $flags );
// BROWSE DETAIL TABLE IF SPECIFIED
if( $detail ) {
$_SESSION['det_key'] = NULL;
// BUILD DETAIL QUERY
if( $parent_key != $findkey )
$parent_key = $findkey;
$det_tblref = $detail[CHILDDB] . "." . $detail[CHILDTABLE];
$det_table = $detail[CHILDTABLE];
$det_key = $detail[CHILDKEYCOL];
if( $colobjs[firstPKcolno()]->numeric )
$linkval = $parent_key;
else
$linkval = Chr(39) . $parent_key . Chr(39);
$order_by = ( empty( $_SESSION['det_order_by'] ) ? "1" : $_SESSION['det_order_by'] );
$det_qry = "SELECT * FROM $det_tblref WHERE $det_key = $linkval ORDER BY $order_by";
$det_res = mysql_query( $det_qry ) or err_handler( "Error in $det_qry" );
$det_total = mysql_num_rows( $det_res );
$det_tables = array();
$det_colobjs = array();
$det_may_edit = PKinit( IS_AUTOSEL, $det_res, $det_table, $det_tables, $det_colobjs, "det_" );
echo "<p><b>Detail Query</b>: $det_qry <i>(Rows=$det_total)</i></p>\n";
$det_flags = array( false, $det_may_edit, $det_is_edit, $det_is_add, $det_is_copy, false, true, false );
$det_findkey = NULL;
browser( $thispage, $det_tblref, $det_colobjs, $det_qry, $det_res, $det_total, $det_findkey, $det_flags );
}
// STUBS
Function DetStubs( $prefix="" ) {
GLOBAL $parent_key,$findkey,$detail,$flags;
echo $prefix,
" PARENTKEY:", (IS_NULL($parent_key) ? 'null' : (empty($parent_key) ? 'empty' : $parent_key )),
" FINDKEY:", (IS_NULL($findkey) ? "null" : (empty($findkey) ? 'empty' : $findkey )),
" DETAIL:", (IS_NULL($detail) ? "null" : implode(".",$detail )), "<br/>\n";
}
function showvars( $aname, $a ) {
echo $aname,":<br/>";
echo "<pre>";
print_r( $a );
echo "</pre>";
}
/* -------------------------- END OF BROWSE DATA ------------------------- */
?>
<!-- Creative Commons Licence -->
<br/><br/>
<a href="http://creativecommons.org/licenses/GPL/2.0/">
<img alt="CC-GNU GPL" border="0" src="http://creativecommons.org/images/public/cc-GPL-a.png" />
</a> This software is licenced under the
<a href="http://creativecommons.org/licenses/GPL/2.0/">CC-GNU GPL</a>.
<!--
<rdf:RDF xmlns="http://web.resource.org/cc/"
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
<Work rdf:about="">
<license rdf:resource="http://creativecommons.org/licenses/GPL/2.0/" />
<dc:type rdf:resource="http://purl.org/dc/dcmitype/Software" />
</Work>
<License rdf:about="http://creativecommons.org/licenses/GPL/2.0/">
<permits rdf:resource="http://web.resource.org/cc/Reproduction" />
<permits rdf:resource="http://web.resource.org/cc/Distribution" />
<requires rdf:resource="http://web.resource.org/cc/Notice" />
<permits rdf:resource="http://web.resource.org/cc/DerivativeWorks" />
<requires rdf:resource="http://web.resource.org/cc/ShareAlike" />
<requires rdf:resource="http://web.resource.org/cc/SourceCode" />
</License>
</rdf:RDF>
-->
</body></html>
body {
font-family: arial, tahoma, arial narrow;
font-size: 12px;
color: black;
}
td {
font-family: arial, tahoma, arial narrow;
font-size: 12px;
color: black;
}
#prompt {
font-family: arial, tahoma, verdana;
font-size: 14px;
color: black;
}
#tblhdr {
font-family: arial, tahoma, verdana;
font-size: 12px;
color: blue;
}
#infohdrtop {
font-family: arial, verdana, tahoma;
font-size: 12px;
line-height: 22px;
color: white;
background: blue;
font-weight: bold;
}
#infohdr {
font-family: arial, verdana, tahoma;
font-size: 12px;
color: white;
background: blue;
font-weight: bold;
}
#sel {
font-family: arial, tahoma
font-size: 12px;
color: white;
background: blue;
}
#hdrsel {
font-family: arial, tahoma
font-size: 12px;
color: red;
background: white;
}
#found {
font-family: arial, tahoma, verdana
font-size: 12px;
color: black;
background: #fdb900;
}
#edit {
font-family: arial, tahoma, verdana;
font-size: 12px;
color: black;
}
<?php
/***************************************************************
Functions for theUsual
***************************************************************/
$conn=0;
$host='';
$user='';
$pswd='';
require_once( "phpinc.php" );
/*
* LOOK IN $_POST[], THEN IN $_SESSION[], THEN IN THEUSUAL_LOGIN.PHP FOR LOGIN PARAMS
*/
function connset() {
GLOBAL $conn, $host, $user, $pswd;
if( isset( $_POST['host'] ) && isset( $_POST['user'] ) && isset( $_POST['pswd' ] )) {
$host = $_POST['host'];
$user = $_POST['user'];
$pswd = $_POST['pswd'];
$_SESSION['host'] = $host;
$_SESSION['user'] = $user;
$_SESSION['pswd'] = $pswd;
}
else if( !empty( $_SESSION['host'] ) && !empty( $_SESSION[ 'user'] ) && !empty( $_SESSION['pswd' ] )) {
$host = $_SESSION['host'];
$user = $_SESSION['user'];
$pswd = $_SESSION['pswd'];
}
else {
include "theusual-login.php";
$fatal = "";
if( empty( $_SESSION['host'] )) $fatal .= "Missing host name. ";
if( empty( $_SESSION['user'] )) $fatal .= "Missing username. ";
if( empty( $_SESSION['pswd'] )) $fatal .= "Missing password.";
if( !empty( $fatal )) die( "$fatal Cannot continue.<br/>" );
else {
$host = $_SESSION['host'];
$user = $_SESSION['user'];
$pswd = $_SESSION['pswd'];
}
}
$e = error_reporting( 0 );
$conn = mysql_connect( $host, $user, $pswd, 1, CLIENT_MULTI_STATEMENTS )
or die( "Connection failed for host $host, user $user." );
error_reporting( $e );
return $conn;
}
/*
* SCALAR_QRY_RESULT( $qry )
*/
function scalar_qry_result( $qry ) {
$res = mysql_query( $qry );
return ( $res === FALSE ) ? $res : mysql_result( $res, 0 );
}
/*
* COUNT_QUERY()
* Execute count query, fill in $_SESSION['count_time']
*/
function count_query( $qry ) {
$t1 = microtime();
$result = mysql_query( $qry ) or err_handler( "Error" );
$t2 = microtime();
$_SESSION['count_time'] = ( substr($t2,-10) + substr($t2,0,9)) - (substr($t1,-10) + substr($t1,0,9) );
return $result;
}
/*
* TIMED_QUERY()
* Execute query, set $_SESSION['query_time]} = exec time of COUNT query + exec time of actual query,
*/
function timed_query( $qry ) {
$t0 = $_SESSION['count_time'];
$t1 = microtime();
$result = mysql_query( $qry ) or err_handler( "Error" );
$t2 = microtime();
$t = ( $t0 + (substr($t2,-10) + substr($t2,0,9)) - (substr($t1,-10) + substr($t1,0,9) ) );
$_SESSION['query_time'] = number_format( $t, 4 );
return $result;
}
/*
* THEUSUAL_INIT()
* Initialise vars for startup
*/
function theusual_init( $force = false ) {
if( !isset( $_SESSION['init'] )) {
$_SESSION['init'] = 0;
}
if( $force ) {
$_SESSION['host'] = '';
$_SESSION['user'] = '';
$_SESSION['pswd'] = '';
}
if( $force || !$_SESSION['init'] ) {
$_SESSION['control'] = NULL;
$_SESSION['view'] = NULL;
db_init_vars( NULL );
$_SESSION['init'] = 1;
}
}
/*
* DB_INIT_VARS()
* Initialise db vars
*/
function db_init_vars( $dbname ) {
GLOBAL $db, $qry, $is_new_query;
$do_init = (bool) ( !isset( $_SESSION['db'] ));
if( !$do_init ) {
if( strcasecmp( $_SESSION['db'], $dbname ) != 0 ) {
$do_init = true;
}
}
if( $do_init ) {
table_init_vars( NULL );
}
$db = $dbname;
$_SESSION['db'] = $db;
$is_new_query = TRUE;
}
/*
* VIEW_INIT()
* Retrieve query corresponding to view name, set it as theUsual's query
*/
function view_init( $viewname, $db ) {
GLOBAL $host, $user, $pswd, $conn, $is_new_query;
if( $conn ) mysql_close( $conn );
$conn = mysql_connect( $host, $user, $pswd, 1, CLIENT_MULTI_STATEMENTS );
mysql_select_db( $db );
$newView = new View;
$res = mysql_query( "SELECT id,qry,User,modifiable FROM mysql.theusualviews WHERE name='$viewname' AND db='$db'" );
if( $res ) {
$row = mysql_fetch_row( $res );
$newView->init( $row[0], $viewname, $row[1], $row[2], $row[3] );
$_SESSION['view'] = $newView->Name;
}
$is_new_query = TRUE;
return $newView;
}
class View {
var $ViewID;
var $Name;
var $Query;
var $Owner;
var $Modifiable;
function View() {}
function init( $viewID, $viewname, $query, $owner, $modifiable ) {
$this->ViewID = $viewID;
$this->Name = $viewname;
$this->Query = $query;
$this->Owner = $owner;
$this->Modifiable = $modifiable;
}
function show( $sPrefix = '' ) {
stub( "$sPrefix View ID: $this->ViewID Name:" . $this->Name . " Owner:" . $this->Owner .
" Modifiable:" . $this->Modifiable . "\nQuery:" . $this->Query, true );
}
}
function query_init( $str ) {
GLOBAL $qry, $is_new_query, $qry_type, $may_edit, $db, $table, $cmds;
$qry = trim( $str );
$_SESSION['detail'] = NULL;
if( strcasecmp( orderby_upd( limit_upd( $qry )), orderby_upd( limit_upd( $_SESSION['qry'] )))) {
$is_new_query = true;
}
if( strcasecmp( substr( $qry, 0, 7 ), 'select ' ) == 0 ) {
$qry_type = IS_SELECT;
// SAVE DB & TABLE NAMES
$table = query_token( $qry, "FROM" );
if( empty( $table )) {
$may_edit = FALSE;
}
else {
// EXTRACT OR DEDUCE DB NAME
if( $pos = strpos( $table, "." )) {
db_init_vars( substr( $table, 0, $pos ));
table_init_vars( substr( $table, $pos+1 ));
}
else {
$db = $_SESSION['db'];
table_init_vars( $table );
}
}
$_SESSION['qry'] = $qry;
}
elseif( strcasecmp( substr( $qry, 0, 5 ), "call " ) == 0 ) {
$_SESSION['qry'] = $qry;
$qry_type = IS_CALL;
}
elseif( strcasecmp( substr( $qry, 0, 8 ), "explain " ) == 0 ||
strcasecmp( substr( $qry, 0, 5 ), "show " ) == 0 ||
strcasecmp( substr( $qry, 0, 4 ), "desc" ) == 0 ) {
$_SESSION['qry'] = $qry;
$qry_type = IS_LIST;
}
elseif( in_array( strtoupper( substr( $qry, 0, 5 )), $cmds )) {
$_SESSION['qry'] = $qry;
$qry_type = IS_CMD;
}
$_SESSION['querytype'] = $qry_type;
}
/*
* TABLE_INIT_VARS()
* Initialise table vars
*/
function table_init_vars( $tablename ) {
GLOBAL $table, $order_by, $page, $is_new_query;
$table = $tablename;
$order_by = 1;
$page = 0;
if( !isset( $_SESSION['control'] )) {
$_SESSION['control'] = '';
}
if( strcasecmp( $_SESSION['control'], 'view' ) != 0 ) {
$_SESSION['view'] = NULL;
$_SESSION['viewobj'] = NULL;
}
$_SESSION['table'] = $table;
$_SESSION['order_by'] = 1;
$_SESSION['asc_desc'] = "ASC";
$_SESSION['page'] = 0;
$_SESSION['key'] = NULL;
$_SESSION['keycolnum'] = 0;
$_SESSION['keyval'] = NULL;
$_SESSION['edit'] = FALSE;
$_SESSION['detail'] = NULL;
$_SESSION['parent_key'] = NULL;
$is_new_query = TRUE;
}
/*
* EDIT_FLAGS_INIT()
* Grab & initialise edit flags for master ($prefix='') or detail ($prefix='det_') browser
*/
function edit_flags_init( &$is_add, &$is_copy, &$is_edit, &$keyval, &$updmsg, $prefix ) {
if( isset( $_GET[$prefix.'is_add'] )) {
$is_add = $_GET[$prefix.'is_add'];
$is_edit = FALSE;
$is_copy = FALSE;
}
elseif( isset( $_GET[$prefix.'is_copy'] )) {
$is_add = FALSE;
$is_edit = FALSE;
$is_copy = $_GET[$prefix.'is_copy'];
$keyval = explode( "|", $_GET[$prefix.'keyval'] );
if( count( $keyval ) == 1 ) $keyval = $keyval[0];
$_SESSION[$prefix.'keyval'] = $keyval;
}
elseif( isset( $_GET[$prefix.'is_edit'] )) {
$is_edit = $_GET[$prefix.'is_edit'];
$is_add = FALSE;
$is_copy = FALSE;
switch( $is_edit ) {
case 2:
$updmsg = "Update succeeded. ";
$is_edit = FALSE;
break;
case 1:
$keyval = $_GET[$prefix.'keyval'];
$keyval = explode( "|", $_GET[$prefix.'keyval'] );
if( count( $keyval ) == 1 ) $keyval = $keyval[0];
$_SESSION[$prefix.'keyval'] = $keyval;
break;
default:
$keyval = NULL;
$_SESSION[$prefix.'keyval'] = NULL;
}
}
else {
$is_add = FALSE;
$is_copy= FALSE;
$is_edit = FALSE;
$keyval = NULL;
$_SESSION[$prefix.'keyval'] = NULL;
}
}
/*
* ORDERBY_INIT()
*/
function orderby_init( &$qry, &$order_by, &$ord_col, &$asc_desc, $prefix ) {
if( isset( $_GET[$prefix.'ord_col'] )) {
$ord_col = $_GET[$prefix.'ord_col'];
if( strpos( $ord_col, ' ' ) !== FALSE ) $ord_col = "`" . $ord_col . "`";
// IF USER CHANGED ORDER, FORCE ASC. ASC-DESC BLOCK WILL PICK IT UP
if( $ord_col <> $_SESSION[$prefix.'order_by'] ) $_GET[$prefix.'asc_desc'] = 'ASC';
$order_by = $ord_col . " " . $asc_desc;
$_SESSION[$prefix.'order_by'] = $order_by;
$qry = orderby_upd( $qry, $order_by );
}
elseif( isset( $_SESSION[$prefix.'order_by'] )) {
$order_by = $_SESSION[$prefix.'order_by'];
}
else {
$order_by = query_clause( $qry, "ORDER BY" );
$_SESSION[$prefix.'order_by'] = $order_by;
}
if( isset( $_GET[$prefix.'asc_desc'] )) {
$asc_desc = $_GET[$prefix.'asc_desc'];
$_SESSION[$prefix.'asc_desc'] = $asc_desc;
if( $asc_desc == "ASC" ) {
if( strpos( $order_by, " ASC" ) == FALSE ) {
$order_by = str_replace( " DESC", "", $order_by ) . " ASC ";
}
}
elseif( strpos( $order_by, "DESC" ) == 0 ) {
$order_by = str_replace( " ASC", "", $order_by ) . " DESC ";
}
$_SESSION[$prefix.'order_by'] = $order_by;
$qry = orderby_upd( $qry, $order_by );
}
elseif( isset( $_SESSION[$prefix.'asc_desc'] )) {
$asc_desc = $_SESSION[$prefix.'asc_desc'];
}
else {
$asc_desc = 'ASC';
$_SESSION[$prefix.'asc_desc'] = $asc_desc;
}
}
/*
* ASC_DESC_INIT()
* Set or default $asc_desc from query
*/
function asc_desc_init( $q ) {
global $asc_desc;
if( strpos( strtolower( $q ), " desc " )) {
$asc_desc = "DESC";
} else {
$asc_desc = "ASC";
}
$_SESSION['asc_desc'] = $asc_desc;
}
/*
* CELL_VAL()
* Return one cell value ready for display--numeric zeros as zero, other blanks as
*/
function cell_val( $v ) {
if( $v == null )
$v = "NULL";
else if( is_numeric( $v ))
$v += 0;
else if( empty( $v ))
$v = " ";
else if( $_SESSION['crlf'] )
$v = nl2br( $v );
return $v;
}
/*
* EDITABLE()
* Translate html entities
*/
function editable( $v ) {
return htmlentities( $v );
}
/*
* SHOW_DATA_ROW()
* Show one row of query data in HTML table
*/
function show_data_row( $row, $cols, $f ) {
echo " <td> </td>\n";
for( $i = 0; $i < $cols; $i++ ) {
echo " <td>" . cell_val( $row[$i] ) . "</td>\n";
}
}
/*
* COL_DEFAULTS()
* Add default value, or auto_increment, if defined, to $colobjs elements
*/
function col_defaults( &$db, &$table, &$colobjs ) {
GLOBAL $host, $user, $pswd, $conn;
if( $conn ) mysql_close( $conn );
// CLIENT_MULTI_STATEMENTS FLAG PERMITS CALLING SPROCS.
$conn = mysql_connect( $host, $user, $pswd, 1, CLIENT_MULTI_STATEMENTS )
or exit( "<i>Failed to connect to MySQL server:</i> " . mysql_error() );
$r = mysql_query( "SHOW CREATE TABLE $db.$table", $conn )
or exit( "<i>Cannot retrieve structure of $db.$table:</i> " . mysql_error() );
$rr = mysql_fetch_row( $r );
$sql = $rr[1];
$sql = str_replace( '`', '', $sql );
// FIRST LEFT PAREN
$start = strpos( $sql, "(" ) + 1;
// LAST RIGHT PAREN
$stop = strrpos( $sql, ")" ) - 1;
// EXTRACT COLUMN LIST
$len = strlen( $sql );
$collist = substr( $sql, $start, ($len - $start - $len + $stop));
$cols = explode( ",", $collist );
// ASSIGN DEFAULTS TO $COLOBJS
$tr = array( "\t" => " " , "\r" => " ");
$n = count( $colobjs );
for( $i = 0; $i < $n; $i++ ) {
foreach( $cols as $colspec ) {
$colspec = strtr( trim( $colspec ), $tr );
$pos = strpos( $colspec, " " );
if( $pos ) {
// IF NAME IS THAT OF A COLUMN, LOOK FOR ITS DEFAULT
$name = strtolower( substr( $colspec, 0, $pos ));
if( strcmp( $name, strtolower( $colobjs[$i]->name )) == 0 ) {
if( strstr( strtolower( $colspec ), "auto_increment" )) {
$colobjs[$i]->def = "auto_increment";
}
elseif( $pos = strpos( strtolower( $colspec ), "default" )) {
$colobjs[$i]->def = substr( $colspec, $pos + 7 );
}
}
}
}
}
if( !isset( $_SESSION['dbinfo'] ))
$_SESSION['dbinfo'] = array( array( $db, $table, $colobjs ));
else {
$n = dbindex( $db, $table );
if( $n == null )
$_SESSION['dbinfo'][] = array( $db, $table, $colobjs );
else
$_SESSION['dbinfo'][$n][2] = $colobjs;
}
}
/*
* FINDPAGE()
* Count the rows before $findval, return its page number
*/
function findpage( $findval ) {
global $host, $user, $pswd, $conn, $db, $table, $total, $pagelen;
$page = 0;
$k = firstPKcolname();
if( !is_numeric( $findval )) {
$findval = "'$findval'";
}
if( !empty( $k ) && !empty( $findval ) ) {
// CLIENT_MULTI_STATEMENTS FALG PERMITS CALLING SPROCS.
if( $conn ) mysql_close( $conn );
$conn = mysql_connect( $host, $user, $pswd, 1, CLIENT_MULTI_STATEMENTS )
or die( "Failed to connect to MySQL server: " . mysql_error() );
$s = "SELECT COUNT(*) FROM $db.$table WHERE $k < $findval" ;
$res = mysql_query( $s ) or exit( "<i>Error traversing $db.$table:</i> " . mysql_error() );
$n = mysql_result( $res, 0 );
if( ($n ) && ( $n <> $total )) {
$n++;
$page = ceil( $n / $pagelen ) - 1;
}
}
return $page;
}
/*
* QUERY_KEYWORDS()
* Return array of query keywords
*/
function query_keywords() {
$keywords = array( "SELECT", "FROM", "WHERE", "GROUP BY", "HAVING",
"LIMIT", "PROCEDURE", "UNION", "ORDER BY",
"FOR UPDATE", "LOCK IN SHARE MODE" );
return $keywords;
}
/*
* ORDERBY_UPD()
* Update the order by clause in a query
*/
function orderby_upd( $q, $new = "" ) {
$keyword = "ORDER BY";
$old = query_clause( $q, $keyword );
if( empty( $old )) {
if( empty( $new )) {
// NOTHING TO DO
$ret = $q;
} else {
// INSERT ORDER BY CLAUSE
$pos = word_pos( $q, "LIMIT" );
if( $pos == 0 ) {
$pos = word_pos( $q, "FOR UPDATE" );
}
if( $pos == 0 ) {
$pos = word_pos( $q, "LOCK IN SHARE MODE" );
}
if( $pos == 0 ) {
$ret = $q . " $keyword $new ";
} else {
$ret = substr( $q, 0, $pos ) . " $keyword $new " . substr( $q, $pos );
}
}
} else {
$pos = word_pos( $q, $keyword );
$len = 2 + strlen( $keyword ) + strlen( $old );
if( empty( $new )) {
$ret = substr( $q, 0, $pos ) . substr( $q, $pos + $len );
} else {
$ret = substr( $q, 0, $pos ) . " $keyword $new " . substr( $q, $pos + $len );
}
}
return $ret;
}
/*
* LIMIT_UPD()
* Update the limit clause in a query
*/
function limit_upd( $q, $new = "" ) {
$keyword = "LIMIT";
$old = query_clause( $q, $keyword );
if( empty( $old )) {
if( empty( $new )) {
// NOTHING TO DO
$ret = $q;
} else {
// INSERT LIMIT CLAUSE
$pos = word_pos( $q, "FOR UPDATE");
if( $pos < 0 ) {
$ret = $q . " $keyword $new";
} else {
$ret = substr( $q, 0, $pos ) . " $keyword $new " . substr( $q, $pos + $len );
}
}
} else {
$pos = word_pos( $q, $keyword );
$len = 2 + strlen( $keyword ) + strlen( $old );
if( empty( $new )) {
$ret = substr( $q, 0, $pos ) . substr( $q, $pos + $len );
} else {
$ret = substr( $q, 0, $pos ) . " LIMIT $new " . substr( $q, $pos + $len );
}
}
return $ret;
}
/*
* QUERY_TOKEN()
* Return one punctuation-delimited word after specified SQL keyword
*/
function query_token( $q, $keyword ) {
$punct = array( " ", "\r", "\n", "\t", "," );
$ret = "";
if (( $pos = word_pos( strtoupper( $q ), strtoupper( $keyword ), $punct )) > 0 ) {
$q = trim( substr( $q, 1 + $pos + strlen( $keyword )));
$first = strlen( $q );
for( $i = 0; $i < 4; $i++ ) {
$pos = strpos( $q, $punct[$i] );
if( $pos > 0 ) {
$first = min( $pos, $first );
}
}
$ret = substr( $q, 0, $first );
}
return $ret;
}
/*
* QUERY_CLAUSE()
* Return a specified clause from a query
*/
function query_clause( $q, $keyword ) {
$stop = 8192;
$keywords = query_keywords();
$keyword = strtoupper( $keyword );
$pos = word_pos( strtoupper( $q ), $keyword );
if( $pos < 0 ) {
$ret = "";
} else {
if( $keyword == "SELECT" ) {
$start = 6;
} else {
$start = $pos + strlen( $keyword );
}
// THIS CLAUSE ENDS AT NEXT KEYWORD OR AT END OF QUERY, WHICHEVER FIRST OCCURS
$i = 1 + array_search( $keyword, $keywords );
$n = count( $keywords );
while( $i < $n ) {
if( ( $pos = word_pos( $q, $keywords[$i] )) > $start ) {
$pos--;
$stop = min( $stop, $pos );
}
$i++;
}
if( $stop < 8192 ) {
$ret = substr( $q, $start, $stop - $start + 1 );
} else {
$ret = substr( $q, $start );
}
}
return $ret;
}
/* WORD_POS()
* Return position in string of a punctuation-delimited word, -1 if not found
*/
function word_pos( $s, $word, $punct='' ) {
if( !is_array( $punct )) {
$punct = array( " ", "\r", "\n", "\t" );
}
$s = strtoupper( $s );
$n = count( $punct );
$ret = -1;
for( $i = 0; $i < $n; $i++ ) {
for( $j = 0; $j < $n; $j++ ) {
$pos = strpos( $s, $punct[$i] . $word . $punct[$j] );
if( $pos ) {
$ret = $pos;
break;
}
}
}
return $ret;
}
/*
* TOKEN()
* Return one punctuation-delimited word after specified position
*/
function token( $q, $start ) {
$punct = array( " ", "\r", "\n", "\t", ",", "(", ")" );
$ret = "";
$first = 8192;
$start++;
for( $i = 0; $i < 7; $i++ ) {
$pos = strpos( $q, $punct[$i], $start );
if( $pos > 0 ) {
$first = min( $pos, $first );
}
}
if( $first < 8192 ) {
$ret = substr( $q, $start, $first-$start );
}
return $ret;
}
/*
* PKinit()
* Fill tables and colobjs arrays, set session array elements 'key' and 'keycolnum', return boolean may_edit
*/
function PKinit( $qry_type, &$result, &$table, &$tables, &$colobjs, $prefix='' ) {
// COULD USE SCHEMA ONLY FOR IS_AUTOSEL QUERIES:
// SELECT column_name FROM information_schema.key_column_usage .
// WHERE constraint_schema='db' AND table_name='$table' AND constraint_name='PRIMARY'
$keycolnums = array();
if( $qry_type == IS_AUTOSEL || $qry_type == IS_SELECT ) {
$cols = mysql_num_fields( $result );
$colobjs = array();
for( $i = 0; $i < $cols; $i++ ) {
if ( $colobj = mysql_fetch_field( $result )) {
$colobjs[$i] = $colobj;
if( !empty( $colobj->table )) {
if( !in_array( $colobj->table, $tables )) {
array_push( $tables, $colobj->table );
}
if( strcasecmp( $colobj->table, $table ) == 0 ) {
if ( $colobj->primary_key ) {
array_push( $keycolnums, $i );
}
}
}
}
}
}
$_SESSION[$prefix.'colobjs'] = $colobjs;
switch ( sizeof( $keycolnums )) {
case 0:
$_SESSION[$prefix.'keycolnum'] = NULL;
$_SESSION[$prefix.'key'] = NULL;
$may_edit = FALSE;
break;
case 1:
$_SESSION[$prefix.'keycolnum'] = $keycolnums[0];
$_SESSION[$prefix.'key'] = $colobjs[$keycolnums[0]]->name;
$may_edit = TRUE;
break;
default:
$_SESSION[$prefix.'keycolnum'] = $keycolnums;
$_SESSION[$prefix.'key'] = array();
foreach( $keycolnums as $k ) {
array_push( $_SESSION[$prefix.'key'], $colobjs[$k]->name );
}
$may_edit = true;
break;
}
return $may_edit;
}
/*
* FIRSTPKCOLNAME()
* Return name of first PK column
*/
function firstPKcolname( $prefix="" ) {
if( isset( $_SESSION[$prefix.'key'] )) {
$k = $_SESSION[$prefix.'key'];
$ret = ( is_array( $k ) ? $k[0] : $k );
}
else $ret = NULL;
return $ret;
}
/*
* FIRSTPKCOLNO()
* Return $colobjs index of first PK column
*/
function firstPKcolno( $prefix="" ) {
if( isset( $_SESSION[$prefix.'keycolnum'] )) {
$k = $_SESSION[$prefix.'keycolnum'];
$ret = ( is_array( $k ) ? $k[0] : $k );
}
else $ret = NULL;
return $ret;
}
/*
* FIRSTPKCOLVAL()
* Return value of first column of PK from a row
*/
function firstPKcolval( $row, $prefix="" ) {
if( isset( $_SESSION[$prefix.'keycolnum'] )) {
$k = $_SESSION[$prefix.'keycolnum'];
if( is_array( $k ))
$ret = $row[$k[0]];
else
$ret = $row[$k];
}
else $ret = NULL;
return $ret;
}
/*
* PKVALUE()
* Return PK value as a single column value or as an array of them
*/
function PKvalue( $row, $prefix="" ) {
if( isset( $_SESSION[$prefix.'keycolnum'] )) {
$k = $_SESSION[$prefix.'keycolnum'];
if( is_array( $k )) {
$ret = array();
foreach( $k as $i )
array_push( $ret, $row[$i] );
}
else {
$ret = $row[$k];
}
}
else $ret = NULL;
return $ret;
}
/*
* PKEXPR()
* Return PK column name or a SQL-compatible parenthesised list of comma-separated PK colnames for SQL
*/
function PKexpr( $prefix="" ) {
if( isset( $_SESSION[$prefix.'key'] )) {
$k = $_SESSION[$prefix.'key'];
$ret = ( is_array( $k ) ? ( "(".implode(",",$k).")") : $k );
}
else $ret = NULL;
return $ret;
}
/*
* PKVALEXPR()
* From a resultset row, return PK value as a scalar, or parenthesised & comma-separated, for SQL
*/
function PKvalexpr( $row, $colobjs, $prefix="" ) {
if( isset( $_SESSION[$prefix.'keycolnum'] )) {
$k = $_SESSION[$prefix.'keycolnum'];
if( is_array( $k )) {
$n = count($k) - 1;
$ret = "(";
foreach( $k as $i ) {
$v = $row[$i];
if( !$colobjs[$i]->numeric ) $v = Chr(39).$v.Chr(39);
$ret .= $v;
if( $i < $n ) $ret .= ",";
}
$ret .= ")";
}
else {
$ret = $row[$k];
// if( !$colobjs[$k]->numeric ) $ret = Chr(39).$ret.Chr(39);
}
}
else $ret = NULL;
return $ret;
}
/*
* PKNAMESTR()
* Return PK column name, or |-separated concatenation of them
*/
function PKnamestr( $prefix="" ) {
if( isset( $_SESSION[$prefix.'key'] )) {
$k = $_SESSION[$prefix.'key'];
$ret = ( is_array( $k ) ? implode( "|", $k ) : $k );
} else $ret = NULL;
return $ret;
}
/*
* PKVALSTR()
* Return PK value, or |-separated concatenation of them
*/
function PKvalstr( &$row, $prefix="" ) {
if( isset( $_SESSION[$prefix.'keycolnum'] )) {
$k = $_SESSION[$prefix.'keycolnum'];
if( is_array( $k )) {
$v = array();
foreach( $k as $i )
array_push( $v, $row[$i] );
$ret = implode( "|", $v );
} else $ret = $row[$k];
} else $ret = NULL;
return $ret;
}
/*
* rowPKequals()
* Return true if PK value in row matched passed value or array of them
*/
function rowPKequals( $row, $pkval, $prefix="" ) {
$spec = $_SESSION[$prefix.'keycolnum'];
$ret = TRUE;
if( is_array( $spec )) {
$i = 0;
foreach( $spec as $k ) {
if( $pkval[$i] != $row[$k] ) {
$ret = FALSE;
break;
}
$i++;
}
}
elseif( $pkval != $row[$spec] )
$ret = FALSE;
return $ret;
}
/*
* quote_smart( $str )
* Make a value safe by quoting it
*/
function quote_smart( $value ) {
// Stripslashes
if( get_magic_quotes_gpc() ) {
$value = stripslashes($value);
}
// Quote if not a numeric
if( !is_numeric( $value )) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
/*
* another query func
*/
function db_query($query) {
$args = func_get_args();
$query = array_shift($args);
$query = str_replace("?", "%s", $query);
$args = array_map('mysql_real_escape_string', $args);
array_unshift($args,$query);
$query = call_user_func_array('sprintf',$args);
$result = mysql_query( $query ) or die( 'Query failed: ' . mysql_error() );
return $result;
}
/*
* BROWSER()
* Browse master or detail data retrieved by query, optionally add, edit, copy, delete rows.
*/
function browser( &$thispage, &$table, &$colobjs, &$qry, &$qry_result, &$rowcount, &$findkey, &$flags ) {
GLOBAL $session_name, $detail, $qry_type;
$thispagesess = "$thispage?_sess=$session_name";
$buttonstyle = "style='color:white; background:#0088ee; font-size:10px;'";
$cols = mysql_num_fields( $qry_result );
if( $flags[IS_DETAIL_TABLE] ) {
$a = explode( ".", $table );
$db = $a[0];
$table = $a[1];
$prefix = "det_";
$has_detail = FALSE;
}
else {
$db = $_SESSION['db'];
$prefix = "";
$has_detail = isset( $_SESSION['detail'] );
}
// TABLE HEADER
echo "<table border='1' cellpadding='3'>\n <tr>\n";
if( $rowcount == 0 && $flags[MAY_EDIT] ) {
$flags[IS_ADD] = true;
}
if( $flags[IS_EDIT] || $flags[IS_ADD] || $flags[IS_COPY] ) {
// 1 BLANK HEADER CELL FOR UPDATE AND CANCEL LINKS
$cancelurl = "$thispagesess&is_edit=0";
col_defaults( $db, $table, $colobjs );
echo " <td> </td>\n";
}
elseif( $flags[MAY_EDIT] ) {
if( $rowcount == 0 ) {
// TABLE IS EMPTY & EDITABLE, SO PUT ONLY AN INSERT LINK IN HEADER
echo " <td><a href='$thispagesess&$prefix.is_add=1'>Ins</a></td>\n";
} else {
// BLANK HEADER CELL FOR links to 'Edit', 'Ins', 'Copy', 'Del'
echo " <td> </td>\n";
}
}
// INACTIVE HEADERS IF USER IS EDITING A ROW
if( $flags[IS_EDIT] || $flags[IS_ADD] || $flags[IS_COPY] ) {
for( $j = 0; $j < $cols; $j++ ) {
echo " <td><b>" . ucwords( str_replace( '_',' ', $colobjs[$j]->name )) . "</b></td>\n";
}
}
elseif( $qry_type == IS_CALL || $qry_type == IS_LIST ) {
for( $j = 0; $j < $cols; $j++ ) {
echo "<td><b>" . ucwords( str_replace( '_',' ', mysql_field_name($qry_result, $j) )) . "</b></td>\n";
}
}
else {
// ACTIVE HEADERS IF USER IS NOT EDITING
$ord_col = query_token( $qry, "ORDER BY" );
for( $j = 0; $j < $cols; $j++ ) {
$colname = $colobjs[$j]->name;
$s = ucwords( str_replace( '_',' ', $colname ));
// USER MAY CLICK ON HEADER TO SET 'ORDER BY' TO THE COLUMN, OR REVERSE ASC/DESC
$hdr = ( $ord_col == $j+1 || $ord_col === $colname ) ? "<i>$s</i>" : $s;
echo "<td><b><a href='$thispagesess&";
if( strcasecmp( $ord_col, $colname ) == 0 ) {
echo $prefix . "asc_desc=" . (($asc_desc=='ASC')?'DESC':'ASC');
} else {
echo $prefix . "ord_col=$colname";
}
echo "'>$hdr</a></b></td>\n";
}
}
echo " </tr>\n";
$editing = 0;
// IF USER IS INSERTING, PUT THE FORM IN THE TOP ROW OF THE HTML TABLE
if( $flags[IS_ADD] ) {
$editing = 1;
echo " <tr>\n",
" <form id='edit' action='mysqlins.php' method='POST'>\n",
" <INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n",
" <input type='hidden' name='table' value='$table'></td>\n",
" <td NOWRAP><input id='edit' type='submit' name='mysql_ins' value='Save' $buttonstyle> \n",
" <BUTTON class='editlink' type='button' name='cancel' $buttonstyle;\n",
" onclick=\"fcancel(this.form,'$cancelurl')\">Cancel</BUTTON></td>\n";
for( $i = 0; $i < $cols; $i++ ) {
$colobj = $colobjs[$i];
if( strcasecmp( $colobj->table, $table ) != 0 ) {
echo " <td> </td>\n";
} elseif( strcmp( $colobj->def, 'auto_increment' ) == 0 ) {
echo " <td>auto</td>\n";
} elseif( $flags[IS_DETAIL_TABLE] && $colobjs[$i]->name == $detail[CHILDKEYCOL] ) {
echo " <td><input id='edit' name='" . $colobjs[$i]->name .
"' value='".$_SESSION['parent_key']."'></td>\n";
} else {
$val = cell_val( $colobj->def );
if( strstr( "text|binary|blob", strtolower( $colobj->type ))) {
// TEXTAREA
echo " <td><textarea id='edit' rows=5 cols=100 " .
"name='" . $colobjs[$i]->name . "'>" . $val . "</textarea></td>\n";
} else {
// OTHER COL TYPES
echo " <td><input id='edit' name='" . $colobjs[$i]->name .
"' value='' width='" . $colobjs[$i]->max_length . "'></td>\n";
}
}
}
echo " </form>";
echo " </tr>\n";
}
$currow = NULL;
$keystr = PKnamestr( $prefix );
$selectable = ( $has_detail && ($flags[IS_DETAIL_TABLE] == false) );
$rownum = 0;
while( $row = mysql_fetch_row( $qry_result )) {
$keyval = PKvalue( $row, $prefix ); // $KEYVAL CAN BE AN ARRAY
if( $selectable ) {
if( $rownum == 0 ) {
if( $flags[HILITEFIRST] ) {
$findkey = $keyval; // $FINDKEY CAN BE AN ARRAY
$parent_key = $findkey; // ???
$_SESSION['parent_key'] = $findkey; // ???
} else {
$_SESSION['toprowkeyval'] = $keyval; // CAN BE AN ARRAY
}
}
}
if( $flags[IS_COPY] ) {
// EDIT SPECIFIED ROW FOR INSERTION AS A NEW ROW
echo " <tr>\n";
if( $editing == 0 && rowPKequals( $row, $_SESSION[$prefix.'keyval'], $prefix )) {
$editing = 1;
echo " <tr>\n", // COPY FORM BEGINS
" <FORM id='edit' action='mysqlins.php' method='POST'>\n",
" <INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n",
" <INPUT type='hidden' name='table' value='$table'></td>\n",
" <td NOWRAP><input id='edit' type='submit' name='mysql_ins' value='Save' $buttonstyle> \n",
" <BUTTON class='editlink' type='button' name='cancel' $buttonstyle;\n",
" onclick=\"fcancel(this.form,'$cancelurl')\">Cancel</BUTTON></td>\n";
for( $i = 0; $i < $cols; $i++ ) {
$val = cell_val( $row[$i] );
$colobj = $colobjs[$i];
if( strcasecmp( $colobj->table, $table ) != 0 ) {
echo " <td>$val</td>\n";
} elseif( strcmp( $colobj->def, 'auto_increment' ) == 0 ) {
echo " <td>auto</td>\n";
} else {
if( strstr( "text|binary|blob", strtolower( $colobj->type )) || strstr( $row[$i], "\r\n" )) {
// TEXTAREA
echo " <td><textarea id='edit' rows=5 cols='100' " .
"name='" . $colobjs[$i]->name . "'>" . $row[$i] . "</textarea></td>\n";
} else {
// OTHER COL TYPES
echo " <td><input id='edit' name='" . $colobjs[$i]->name .
"' value='$val' width='" . $colobjs[$i]->max_length . "'></td>\n";
}
}
}
echo " </form>"; // COPY FORM ENDS
}
else {
show_data_row( $row, $cols, $flags );
}
echo " </tr>\n";
}
elseif( $flags[IS_EDIT] ) {
// EDIT THIS ROW IN PLACE
echo " <tr>\n";
if( $editing == 0 && rowPKequals( $row, $_SESSION[$prefix.'keyval'], $prefix )) {
// ONE ROW ONLY MAY BE EDITED AT ONE TIME. _POST_ VALUES TO MYSQLUPD SCRIPT
$editing = 1; // EDIT FORM BEGINS
$key = PKexpr( $prefix );
$val = PKvalexpr( $row, $colobjs, $prefix );
echo " <FORM id='edit' action='mysqlupd.php' method='POST'>\n",
" <INPUT type='hidden' name='_sess' value=\"$session_name\"'>\n",
" <td NOWRAP><input id='edit' type='submit' name='mysql_upd' value='Save' $buttonstyle> \n",
" <BUTTON class='editlink' type='button' name='cancel' $buttonstyle;\n",
" onclick=\"fcancel(this.form,'$cancelurl')\">Cancel</BUTTON></td>\n",
" <input type='hidden' name='table' value='$table'>\n",
" <input type='hidden' name='key' value='$key'>\n",
" <input type='hidden' name='keyval' value='$val'>\n";
for( $i = 0; $i < $cols; $i++ ) {
$colobj = $colobjs[$i];
$val = ( $row[$i] == null ) ? "" : cell_val( $row[$i] );
// MAY EDIT JUST ONE TABLE AT A TIME
if( strcasecmp( $colobj->table, $table ) == 0 ) {
if( strstr( "text|binary|blob", strtolower( $colobj->type )) || strstr( $row[$i], "\r\n" )) {
// TEXTAREA
echo " <td><textarea id='edit' rows=5 cols='100' " .
"name='" . $colobjs[$i]->name . "'>" . $row[$i] . "</textarea></td>\n";
} else {
// OTHER COL TYPES
echo " <td><input id='edit' name='" . $colobjs[$i]->name .
"' value='$val' width='" . $colobjs[$i]->max_length . "'></td>\n";
}
} else {
echo " <td>". $val . "</td>\n";
}
}
echo " </form>"; // EDIT FORM ENDS
}
else {
show_data_row( $row, $cols, $flags );
}
echo " </tr>\n";
}
else {
// BUILD JAVASCRIPT STRINGS FOR SELECTING ROW AND FOR HIGHLIGHTS
$s = " <tr ";
if( $selectable ) {
$url = Chr(39) . $thispagesess . "&parent_key=" . $keyval . "&findkey=" . $keyval . Chr(39);
$s .= "onclick=" . Chr(34) . "parent.location=$url" . Chr(34) . " ";
}
if( (!$currow ) && ( $findkey ) && ( firstPKcolval( $row, $prefix ) == $findkey ) ) {
$s .= "id='found' " . FINDROWHILITE . ">\n";
$currow = $rownum;
} else {
$s .= ROWHILITE . ">\n";
}
echo $s;
if( $flags[IS_ADD] ) {
// IF A NEW ROW IS BEING EDITED, PAINT BLANK ROWS BENEATH IT
echo " <td> </td>\n";
}
elseif( $flags[MAY_EDIT] ) {
// PAINT ADD, EDIT, COPY, DELETE LINKS
$keyvalstr = ( is_array( $keyval ) ? implode( "|", $keyval ) : $keyval );
echo " <td class='editlink' NOWRAP>",
" <a href='$thispagesess&".$prefix."is_edit=1&".$prefix."keyval=$keyvalstr'>Edit</a>\n",
" <a href='$thispagesess&".$prefix."is_add=1'>Ins</a>\n",
" <a href='$thispagesess&".$prefix."is_copy=1&".$prefix."keyval=$keyvalstr'>Copy</a>\n",
" <a href='mysqldel.php?_sess=$session_name&table=$table&key=$keystr&keyval=$keyvalstr'>Del</a>",
" </td>\n";
}
// PAINT DATA
for( $i = 0; $i < $cols; $i++ ) {
echo " <td>" . cell_val( $row[$i] ). "</td>\n";
}
echo "</tr>\n";
}
$rownum++;
}
echo "</table>\n";
// STUBS
// if( $flags[IS_DETAIL_TABLE] ) {
// echo "<HR><pre>";
// print_r( get_defined_vars() );
// echo "</pre>";
} // END OF BROWSER
/*
* STUB()
*/
function stub( $s, $newline = true ) {
echo $s;
if( $newline ) {
echo "<br>\n";
}
}
/*
* ERR_HANDLER()
*/
function err_handler( $txt, $exit = true ) {
echo "<table border=2 cellspacing=2 cellpadding=2 width='80%' align='left'>",
"<tr><td><b><i>$txt</i> ", mysql_error(), "</b></td></tr>\n",
"</table>";
if( $exit ) {
exit();
}
}
?>
// phpinc.php
$buttonstyle = "style='color:white; background:#0088ee; font-size:12px; font-weight:bold'";
function dbindex( $db, $table ) {
$k = null;
for( $i = 0; $i < count( $_SESSION['dbinfo'] ); $i++ ) {
if( $_SESSION['dbinfo'][$i][0] == $db && $_SESSION['dbinfo'][$i][1] == $table ) {
$k = $i;
$break;
}
}
return $k;
}
function colobj( $colobjs, $colname ) {
$k = null;
$i = 0;
foreach( $colobjs as $obj ) {
if( $obj->name == $colname ) {
return $obj;
}
}
return null;
}
function viewstableddl() {
return "CREATE TABLE mysql.theusualviews(" .
"id INT AUTO_INCREMENT PRIMARY KEY, " .
"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 ''," .
"modifiable TINYINT NOT NULL DEFAULT 1, " .
"changed TIMESTAMP)";
}
function childtablesqry( $db, $table ) {
return "SELECT c.table_schema,u.table_name,u.column_name,u.referenced_column_name " .
"FROM information_schema.table_constraints AS c " .
"INNER JOIN information_schema.key_column_usage AS u " .
"USING( constraint_schema, constraint_name ) " .
"WHERE c.constraint_type = 'FOREIGN KEY' " .
"AND u.referenced_table_schema='$db' " .
"AND u.referenced_table_name = '$table' " .
"ORDER BY c.table_schema,u.table_name";
}
?>
<html><head><title>mysqlins.php</title></head><body>
<?php
require_once( "session_continue.php" );
require_once( "phpinc.php" );
if( isset( $_POST['table'] )) {
$host = $_SESSION['host'];
$user = $_SESSION['user'];
$pswd = $_SESSION['pswd'];
$url = $_SESSION['home'];
$db = $_SESSION['db'];
$table = $_POST['table'];
$n = dbindex( $db, $table );
$colobjs = $_SESSION['dbinfo'][$n][2];
// CONNECT
$conn = mysql_connect( $host, $user, $pswd ) or exit( "Failed to connect to MySQL server.");
mysql_select_db( $db, $conn ) or exit( "Cannot select $db database." );
// CONSTRUCT INSERT QUERY FROM SESSION VARS AND POSTED DATA
$qry = "INSERT INTO $table SET ";
while( list( $name, $value ) = each( $_POST )) {
if( $name == 'mysql_ins' || $value == 'Save' || $name == 'table' || $name == "_sess" ) continue;
$obj = colobj( $colobjs, $name );
if( is_object( $obj )) {
if( $value == "NULL" && $obj->not_null != 1 )
$qry .= "$name=NULL, ";
else
$qry .= "$name='" . mysql_real_escape_string( $value ) . "', ";
}
}
$qry = chop( $qry, ", " );
echo "<b>Update query</b>: $qry<br>\n";
if ( $result = mysql_query( $qry ) ) {
// AUTOMATIC RETURN IF UPDATE SUCCEEDS
$_SESSION['total_rows'] = null;
$url .= "?_sess=$session_name&is_edit=2";
echo "Thank you! Information entered.<br>\n";
echo "<script language='JavaScript'>parent.location='$url';</script>";
// echo "<pre>";
// print_r( $_POST );
// echo "</pre>";
}
else {
// ERROR REPORT
echo "<b>Error</b>: " . mysql_error() . "<br>";
echo "Could not insert the data\<br/>n";
echo "Click the <i>Back</i> button to return to $url.\n</body></html>";
}
}
?>
</body></html>
<html><body>
<?php
require_once( "session_continue.php" );
require_once( "phpinc.php" );
$host = $_SESSION['host'];
$user = $_SESSION['user'];
$pswd = $_SESSION['pswd'];
$url = $_SESSION['home'];
$db = $_SESSION['db'];
$table = $_POST['table'];
// CONNECT
$conn = mysql_connect( $host, $user, $pswd ) or exit( "Failed to connect to MySQL server.");
mysql_select_db( $db, $conn ) or exit( "Cannot select $db database." );
$n = dbindex( $db, $table );
$colobjs = $_SESSION['dbinfo'][$n][2];
// CONSTRUCT UPDATE QUERY FROM POSTED KEYS AND DATA
$qry = "UPDATE $table SET ";
while( list( $name, $value ) = each( $_POST )) {
if( $name == 'mysql_ins' || $value == 'Save' || $name == 'table' || $name == "_sess" ) continue;
$obj = colobj( $colobjs, $name );
if( is_object( $obj )) {
if( $value == "NULL" && $obj->not_null != 1 )
$qry .= "$name=NULL, ";
else
$qry .= "$name='" . mysql_real_escape_string( $value ) . "', ";
}
}
$qry = rtrim( $qry, ", " );
$qry .= " WHERE " . $_POST['key'] . "=" . $_POST['keyval'] . "\n";
echo "<b>Update query</b>: $qry<br>\n";
if ( $result = mysql_query( $qry ) ) {
// AUTOMATIC RETURN IF UPDATE SUCCEEDS
$url .= "?_sess=$session_name&is_edit=2";
echo "Thank you! Information entered.<br>\n";
echo "<script language='JavaScript'>parent.location='$url';</script>";
} else {
// ERROR REPORT
echo "<b>Error</b> execcuting $qry: ". mysql_error()."<br>";
echo "Could not complete the update.<br>";
echo "Click the <i>Back</i> button to return to $url.\n</body></html>";
}
?>
</body></html>
<html><head><title>mysqldel.php</title></head><body>
<?php
require_once( "session_continue.php" );
// ASSEMBLE DELETION QUERY FROM PARAMS PASSED AS GETS BY THEUSUAL
if( isset( $_GET['table'] ) && isset( $_GET['key'] ) && isset( $_GET['keyval'] ) ) {
$key = $_GET['key'];
$keyval = $_GET['keyval'];
if( strpos( $key, "|" ) != FALSE ) {
$key = "(" . str_replace( "|", ",", $key ) . ")";
$keyval = "('" . str_replace( "|", "','", $keyval ) . "')";
} else
$keyval = Chr(39) . $keyval . Chr(39);
$qry = "DELETE FROM ". $_GET['table'] . " WHERE $key=$keyval";
echo '<form action="' . $_SERVER["PHP_SELF"] . '"method="POST">';
echo "<b>About to execute</b>: $qry\n";
echo " <input type='hidden' name='delqry' value=\"$qry\">\n";
echo " <input type='submit' name='submit' value='DELETE'>\n";
echo " <input type='submit' name='cancel' value='Cancel'>\n";
echo "</form>";
}
elseif( isset( $_POST['cancel'] )) {
$url = $_SESSION['home'];
echo "<script language='JavaScript'>parent.location='$url';</script>";
}
// POST ASSEMBLED DELETION QUERY BACK TO THIS PAGE
elseif( isset( $_POST['delqry'] )) {
$host = $_SESSION['host'];
$user = $_SESSION['user'];
$pswd = $_SESSION['pswd'];
$url = $_SESSION['home'];
$db = $_SESSION['db'];
$qry = $_POST['delqry'];
echo "<b>Executing</b>: $qry\n";
$conn = mysql_connect( $host, $user, $pswd ) or die( "Failed to connect to MySQL server.");
mysql_select_db( $db, $conn ) or die( "Cannot select $db database." );
if ( $result = mysql_query( $qry )) {
// AUTOMATIC RETURN IF DELETION SUCCEEDS
$_SESSION['total_rows'] = null;
$url .= "?_sess=$session_name&is_edit=2";
echo "Deletion executed successfully.<br>\n";
echo "<script language='JavaScript'>parent.location='$url';</script>";
}
else {
echo "<b>Error</b> in command:<br/>" . $_POST['delqry'] . "<br/>" . mysql_error()."<br/>";
echo "Could not complete the deletion.<br>";
echo "Click the <i>Back</i> button to return to $url.\n</body></html>";
}
}
?>
</body></html>
#EOF
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%--
I offer the following pattern for JDBC applications.
By using try/finally you avoid the null state for conn, stmt, and rs.
They will always close except when the thread dies.
Limiting the declaration scope of stmt and rs has benefits.
--%>
<html>
<head><b><i>MySqlQuery.Jsp</b></i></head>
<body>
<br>
<%
// PAGE-WIDE EXCEPTION HANDLER
try {
// REGISTER THE DRIVER
Class driverClass = com.mysql.jdbc.Driver.class;
java.sql.Connection conn = java.sql.DriverManager.getConnection(
"jdbc:mysql://localhost/mysql?user=USR&password=PSWD" );
// HANDLE EXCEPTIONS FROM CREATING STATEMENT
try {
java.sql.Statement stmt = conn.createStatement();
// HANDLE EXCEPTIONS FROM EXECUTING QUERY
try {
String sql = "SELECT Host, User, Select_priv, Insert_priv, " +
"Update_priv, Delete_priv, Create_priv " +
"FROM mysql.user ORDER BY user";
java.sql.ResultSet rs = stmt.executeQuery( sql );
%>
<!-- HTML TABLE HEADERS -->
<h2>Database: mysql Table: user</h2>
<table width="80%" border="2">
<tr>
<th>Host</th>
<th>User</th>
<th>Select</th>
<th>Insert</th>
<th>Update</th>
<th>Delete</th>
<th>Create</th>
</tr>
<!-- END RAW HTML BLOCK -->
<%
// RESUME JAVA. POPULATE HTML TABLE WITH ALL COLS & ROWS
// HANDLE EXCEPTIONS PROCESSING RESULT
try {
// getColumnCount() protects against column count coding errors
int columnCount = rs.getMetaData().getColumnCount();
while( rs.next() ) {
out.println( "" );
// zero based
for( int i = 0; i < columnCount; i++ ) {
out.println( " " + rs.getString( i+1 ) + " " );
}
out.println( " " );
}
out.println( "" );
}
finally {
rs.close();
}
}
finally {
stmt.close();
}
}
finally {
conn.close();
}
// CATCH PAGE-WIDE EXCEPTIONS
} catch( Exception x ) {
// MAKE STACK TRACE READABLE
out.println("");
if ( x instanceof java.sql.SQLException ) {
out.println( "SQLException: " + ((java.sql.SQLException)x).getMessage());
out.println( "SQLState: " + ((java.sql.SQLException)x).getSQLState());
}
x.printStackTrace( new java.io.PrintWriter( out ));
out.println( "" );
// TO DEBUG, UNCOMMENT:
// throw (x);
}
%>
</body>
</html>
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace master_detail_conn {
public partial class GridForm : Form {
MySqlDataAdapter daMaster, daDetail;
MySqlCommandBuilder cmdMas, cmdDet;
private BindingSource bindSrcMaster = new BindingSource();
private BindingSource bindSrcDetail = new BindingSource();
DataSet ds;
DataRelation relCustOrd;
string sConn, sMasterTbl, sDetailTbl;
public GridForm() {
InitializeComponent();
// CUSTOM SPECS BEGIN: EDIT USR & PWD TO SUIT
sConn = "server=127.0.0.1;uid=USR;pwd=PWD;database=nwib;";
sMasterTbl = "Customers";
sDetailTbl = "Orders";
string sMasterKey = "customerID";
string sDetailKey = sMasterKey;
string sRelationName = sMasterTbl + sDetailTbl;
string sqlMaster = "SELECT * FROM " + sMasterTbl;
string sqlDetail = "SELECT * FROM " + sDetailTbl;
// CUSTOM SPECS END
try {
MySqlConnection oConn;
oConn = new MySqlConnection(sConn);
oConn.Open();
statusMessages.Text = "Connected";
dataGridView1.DataSource = bindSrcMaster;
dataGridView2.DataSource = bindSrcDetail;
ds = new DataSet();
daMaster = new MySqlDataAdapter(sqlMaster, oConn);
cmdMas = new MySqlCommandBuilder(daMaster);
daMaster.Fill(ds, sMasterTbl);
daDetail = new MySqlDataAdapter(sqlDetail, oConn);
cmdDet = new MySqlCommandBuilder(daDetail);
daDetail.Fill(ds, sDetailTbl);
ds.Relations.Clear();
relCustOrd = new DataRelation(
sRelationName,
ds.Tables[sMasterTbl].Columns[sMasterKey],
ds.Tables[sDetailTbl].Columns[sDetailKey]);
ds.Relations.Add(relCustOrd);
bindSrcMaster.DataSource = ds;
bindSrcMaster.DataMember = sMasterTbl;
bindSrcDetail.DataSource = bindSrcMaster;
bindSrcDetail.DataMember = sRelationName;
bindingNavigator1.BindingSource = bindSrcMaster;
this.Load += new System.EventHandler(GridForm_Load);
this.btnUpdate.Click += new System.EventHandler(btnUpdate_Click);
}
catch (Exception e) {
MessageBox.Show(e.Message);
}
}
private void btnUpdate_Click(object sender, System.EventArgs e) {
try {
if (ds.HasChanges()) {
daMaster.Update(ds, sMasterTbl);
daDetail.Update(ds, sDetailTbl);
statusMessages.Text = "Updated";
}
else {
statusMessages.Text = "No changes to write";
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
private void GridForm_Load(object sender, System.EventArgs e) {
dataGridView1.AutoResizeColumns();
dataGridView2.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
}
}
}
// EOF
<%@ Page Language="C#" Debug="true" %>
<%@ Register TagPrefix="Custom" Namespace="CustomControls" Assembly="CustomControls" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Drawing" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<script runat="server">
MySqlConnection mySqlConnection =
new MySqlConnection( "Host=localhost;Database=nwib;User Id=USR;Password=PWD" );
MySqlCommand mySqlCommand;
MySqlDataAdapter mySqlDataAdapter;
DataSet dataSet;
int firstRow;
int rowCount;
string sql;
string sOrderBy = "";
override protected void OnInit(EventArgs e) {
base.OnInit(e);
sql = "SELECT customerID,CompanyName,ContactName,ContactTitle,City,Country FROM customers ";
bFirst.Visible = false;
bPrevious.Visible = false;
bNext.Visible = false;
bLast.Visible = false;
}
void Page_Load(object sender, EventArgs e) {
tbSQL.Text = sql;
GridFill();
}
void GridFill() {
try{
mySqlConnection.Open();
rowCount = querySize();
// GET/SET ORDERBY AND FIRSTROW VIEWSTATES, BUILD SQL SUFFIX
if( ViewState["sOrderBy"] != null ) {
sOrderBy = ViewState["sOrderBy"].ToString();
} else {
ViewState["sOrderBy"] = sOrderBy;
}
if( ViewState["firstRow"] != null ) {
firstRow = int.Parse( ViewState["firstRow"].ToString() );
} else {
firstRow = 0;
ViewState["firstRow"] = firstRow;
}
string sqlSuffix = OrderByClause() + LimitClause();
tbSQL.Text = sql + System.Environment.NewLine + sqlSuffix;
mySqlCommand = new MySqlCommand( tbSQL.Text );
mySqlCommand.Connection = mySqlConnection;
dataSet = new DataSet();
mySqlDataAdapter = new MySqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
mySqlDataAdapter.Fill(dataSet, "Table");
dataGrid.DataSource = dataSet.Tables["Table"];
DataBind();
if( rowCount > dataGrid.PageSize ) {
bFirst.Visible = true;
bPrevious.Visible = true;
bNext.Visible = true;
bLast.Visible = true;
}
} catch (Exception exception) {
lbError.Text = exception.Message;
}
}
protected string OrderByClause() {
string s = ( sOrderBy == "" ? "" : "ORDER BY " + sOrderBy + " " );
return s;
}
void GridSort( object sender, DataGridSortCommandEventArgs e ) {
string s = e.SortExpression;
if( ViewState["sOrderBy"] == null ) {
ViewState["sOrderBy"] = s;
} else if( s == ViewState["sOrderBy"].ToString() ) {
ViewState["sOrderBy"] = s + " DESC";
} else {
ViewState["sOrderBy"] = s;
}
GridFill();
}
protected string LimitClause() {
string s = " LIMIT " + firstRow.ToString() + "," +
dataGrid.PageSize.ToString();
return s;
}
int querySize() {
int n;
string s;
string sqlCount = "SELECT COUNT(*) FROM (" + tbSQL.Text + ") AS qry";
MySqlCommand cmd = new MySqlCommand( sqlCount );
cmd.Connection = mySqlConnection;
s = cmd.ExecuteScalar().ToString();
n = int.Parse( s ) ;
return n;
}
void bFirst_Click( object sender, EventArgs e ) {
firstRow = 0;
ViewState["firstRow"] = firstRow;
GridFill();
}
void bPrevious_Click( object sender, EventArgs e ) {
firstRow = System.Math.Max( firstRow - dataGrid.PageSize, 0 );
ViewState["firstRow"] = firstRow;
GridFill();
}
void bNext_Click( object sender, EventArgs e ) {
firstRow = System.Math.Min( firstRow + dataGrid.PageSize,
rowCount - dataGrid.PageSize );
ViewState["firstRow"] = firstRow;
GridFill();
}
void bLast_Click( object sender, EventArgs e ) {
firstRow = System.Math.Max( rowCount - dataGrid.PageSize, 0 );
ViewState["firstRow"] = firstRow;
GridFill();
}
</script>
<html>
<head>
</head>
<body>
<form id="Form1" method="post" runat="server">
<table id="Table1" style="Z-INDEX: 104; LEFT: 10px; WIDTH: 700px; POSITION: absolute; TOP: 17px" cellspacing="5" cellpadding="0" bgcolor="#ccccff">
<tbody>
<tr>
<td>
<asp:Label id="lbTitle" runat="server" font-names="Verdana" font-size="12pt" enableviewstate="False" font-bold="True" forecolor="Navy"> Paging
DataGrid </asp:Label></td>
</tr>
</tbody>
</table>
<asp:Label id="Label2" style="Z-INDEX: 106; LEFT: 9px; POSITION: absolute; TOP: 64px" runat="server" font-names="Verdana" font-size="10pt" enableviewstate="False" font-bold="True" forecolor="Navy">SQL</asp:Label>
<asp:textbox id="tbSQL" style="Z-INDEX: 101; LEFT: 9px; POSITION: absolute; TOP: 81px; Height:89; Width:554" runat="server" Font-Names="Courier New" wrap="False" textmode="MultiLine"></asp:textbox>
<asp:Label id="lbError" style="Z-INDEX: 104; LEFT: 13px; POSITION: absolute; TOP: 178px" runat="server" font-names="Verdana" font-size="10pt" enableviewstate="False" font-bold="True" forecolor="Red"></asp:Label>
<p>
<br />
<br />
<asp:datagrid id="dataGrid" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 214px" runat="server" Font-Names="Verdana" Width="700px" OnSortCommand="GridSort" PageSize="5" Font-Size="8pt" CellPadding="3" BorderColor="Black" BackColor="#CCCCFF" AllowSorting="True">
<HeaderStyle backcolor="#AAAADD"></HeaderStyle>
</asp:datagrid>
</p>
<asp:LinkButton id="bNext" style="Z-INDEX: 100; LEFT: 200px; POSITION: absolute; TOP: 352px" onclick="bNext_Click" runat="server">Next</asp:LinkButton>
<asp:LinkButton id="bPrevious" style="Z-INDEX: 101; LEFT: 120px; POSITION: absolute; TOP: 352px" onclick="bPrevious_Click" runat="server">Previous</asp:LinkButton>
<asp:LinkButton id="bFirst" style="Z-INDEX: 102; LEFT: 42px; POSITION: absolute; TOP: 352px" onclick="bFirst_Click" runat="server">First</asp:LinkButton>
<asp:LinkButton id="bLast" style="Z-INDEX: 103; LEFT: 280px; POSITION: absolute; TOP: 352px" onclick="bLast_Click" runat="server">Last</asp:LinkButton>
<br />
<br />
</form>
<br />
<br />
</body>
</html>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Drawing" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<script runat="server">
MySqlConnection mySqlConnection = new MySqlConnection( "Host=localhost;Database=nwib;User Id=USR;Password=PWD" );
MySqlCommand mySqlCommand;
MySqlDataAdapter mySqlDataAdapter;
DataSet dataSet;
int firstRow;
int rowCount;
string sql = "";
string sOrderBy = "";
override protected void OnInit(EventArgs e) {
base.OnInit(e);
sql = tbSQL.Text + OrderByClause() + LimitClause();
bFirst.Visible = false;
bPrevious.Visible = false;
bNext.Visible = false;
bLast.Visible = false;
}
protected void tbSQL_TextChanged(object sender, EventArgs e) {
sql = tbSQL.Text + OrderByClause() + LimitClause();
}
private void Page_Load(object sender, EventArgs e) {
GridFill();
}
void GridFill() {
try {
mySqlConnection.Open();
sql = tbSQL.Text;
rowCount = querySize();
// GET/SET ORDERBY AND FIRSTROW VIEWSTATES, BUILD SQL SUFFIX
if (ViewState["sOrderBy"] != null) {
sOrderBy = ViewState["sOrderBy"].ToString();
} else {
ViewState["sOrderBy"] = sOrderBy;
}
if (ViewState["firstRow"] != null) {
firstRow = int.Parse(ViewState["firstRow"].ToString());
} else {
firstRow = 0;
ViewState["firstRow"] = firstRow;
}
string sqlSuffix = OrderByClause() + LimitClause();
sql += System.Environment.NewLine + sqlSuffix;
mySqlCommand = new MySqlCommand(sql);
mySqlCommand.Connection = mySqlConnection;
dataSet = new DataSet();
mySqlDataAdapter = new MySqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
mySqlDataAdapter.Fill(dataSet, "Table");
dataGrid.DataSource = dataSet.Tables["Table"];
DataBind();
if (rowCount > dataGrid.PageSize) {
bFirst.Visible = true;
bPrevious.Visible = true;
bNext.Visible = true;
bLast.Visible = true;
}
} catch (Exception exception) {
lbError.Text = exception.Message;
} finally {
mySqlConnection.Close();
}
}
protected string OrderByClause() {
string s = ( sOrderBy == "" ? "" : "ORDER BY " + sOrderBy + " " );
return s;
}
void GridSort( object sender, DataGridSortCommandEventArgs e ) {
string s = e.SortExpression;
if( ViewState["sOrderBy"] == null ) {
ViewState["sOrderBy"] = s;
} else if( s == ViewState["sOrderBy"].ToString() ) {
ViewState["sOrderBy"] = s + " DESC";
} else {
ViewState["sOrderBy"] = s;
}
GridFill();
}
protected string LimitClause() {
string s = " LIMIT " + firstRow.ToString() + "," + dataGrid.PageSize.ToString();
return s;
}
int querySize() {
int n;
string s;
string sqlCount = "SELECT COUNT(*) FROM (" + tbSQL.Text + ") AS qry";
MySqlCommand cmd = new MySqlCommand( sqlCount );
cmd.Connection = mySqlConnection;
s = cmd.ExecuteScalar().ToString();
n = int.Parse( s ) ;
return n;
}
void bFirst_Click( object sender, EventArgs e ) {
firstRow = 0;
ViewState["firstRow"] = firstRow;
GridFill();
}
void bPrevious_Click( object sender, EventArgs e ) {
firstRow = System.Math.Max( firstRow - dataGrid.PageSize, 0 );
ViewState["firstRow"] = firstRow;
GridFill();
}
void bNext_Click( object sender, EventArgs e ) {
firstRow = System.Math.Min( firstRow + dataGrid.PageSize, rowCount - dataGrid.PageSize );
ViewState["firstRow"] = firstRow;
GridFill();
}
void bLast_Click( object sender, EventArgs e ) {
firstRow = System.Math.Max( rowCount - dataGrid.PageSize, 0 );
ViewState["firstRow"] = firstRow;
GridFill();
}
void bRefresh_Click(object sender, EventArgs e) {
int i;
sql = tbSQL.Text;
i = sql.IndexOf( "LIMIT " );
if( i > 0 ) sql = sql.Substring( 0, i-1 );
tbSQL.Text = sql;
sql += OrderByClause() + LimitClause();
firstRow = 0;
GridFill();
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Paging DataGrid with Connector/NET</title>
</head>
<body runat="server">
<form id="Form2" method="post" runat="server">
<table id="Table1" style="Z-INDEX: 104; LEFT: 10px; WIDTH: 700px; POSITION: absolute; TOP: 17px" cellspacing="5" cellpadding="0" bgcolor="#ccccff">
<tbody>
<tr>
<td>
<asp:Label id="lbTitle" runat="server" forecolor="Navy" font-bold="True" enableviewstate="False" font-size="12pt" font-names="Verdana"> Paging
DataGrid </asp:Label></td>
</tr>
</tbody>
</table>
<asp:Label id="Label2" style="Z-INDEX: 106; LEFT: 9px; POSITION: absolute; TOP: 64px" runat="server" forecolor="Navy" font-bold="True" enableviewstate="False" font-size="10pt" font-names="Verdana">SQL (edit, then click the <i>Refresh</i> linkbutton)</asp:Label>
<asp:textbox id="tbSQL" style="Z-INDEX: 101; LEFT: 12px; POSITION: absolute; TOP: 82px" runat="server" textmode="MultiLine" wrap="False" Font-Names="Courier New" Width="554" Height="89" OnTextChanged="tbSQL_TextChanged">SELECT * FROM nwib.orders</asp:textbox>
<asp:Label id="lbError" style="Z-INDEX: 104; LEFT: 13px; POSITION: absolute; TOP: 178px" runat="server" forecolor="Red" font-bold="True" enableviewstate="False" font-size="10pt" font-names="Verdana"></asp:Label>
<p>
<br />
<br />
<asp:datagrid id="dataGrid" style="Z-INDEX: 101; LEFT: 8px; POSITION: absolute; TOP: 214px" runat="server" Font-Names="Verdana" Width="700px" AllowSorting="True" BackColor="#CCCCFF" BorderColor="Black" CellPadding="3" Font-Size="8pt" PageSize="5" OnSortCommand="GridSort">
<HeaderStyle backcolor="#AAAADD"></HeaderStyle>
</asp:datagrid>
</p>
<asp:LinkButton id="bNext" style="Z-INDEX: 100; LEFT: 200px; POSITION: absolute; TOP: 550px" onclick="bNext_Click" runat="server">Next</asp:LinkButton>
<asp:LinkButton id="bPrevious" style="Z-INDEX: 101; LEFT: 120px; POSITION: absolute; TOP: 550px" onclick="bPrevious_Click" runat="server">Previous</asp:LinkButton>
<asp:LinkButton id="bFirst" style="Z-INDEX: 102; LEFT: 42px; POSITION: absolute; TOP: 550px" onclick="bFirst_Click" runat="server">First</asp:LinkButton>
<asp:LinkButton id="bLast" style="Z-INDEX: 103; LEFT: 280px; POSITION: absolute; TOP: 550px" onclick="bLast_Click" runat="server">Last</asp:LinkButton>
<asp:LinkButton ID="bRefresh" runat="server" Height="19px" style="Z-INDEX: 104; LEFT: 360px; POSITION: absolute; TOP: 550px" onclick="bRefresh_Click">Refresh</asp:LinkButton>
<br />
<br />
</form>
<br />
<br />
</body>
</html>
USE tracker;
DROP PROCEDURE IF EXISTS security_create_admin_owner;
DELIMITER |
CREATE PROCEDURE security_create_admin_owner(
tracker_usr CHAR( 10), usr CHAR(16), pswd CHAR(41), hst CHAR(60))
BEGIN
DECLARE s CHAR( 10 ) DEFAULT NULL;
DECLARE s2 CHAR( 10 ) DEFAULT NULL;
DECLARE s3 CHAR(77);
SELECT username
INTO s
FROM users
WHERE username = tracker_usr;
IF ISNULL( s ) = 0 THEN
SELECT user
INTO s2
FROM mysql.user
WHERE user = s;
IF ISNULL( s ) = 0 THEN
SET s = CONCAT_WS( ’@’, usr, hst );
GRANT ALL ON tracker.* TO s IDENTIFIED BY ’pswd’
WITH GRANT OPTION;
END IF;
END IF;
END;
|
DELIMITER ;
// EOF
DELIMITER go
CREATE PROCEDURE ListDescendants( ancestor CHAR(20) )
BEGIN
DECLARE rows, iLevel, iMode INT DEFAULT 0;
-- create temp tables
DROP TEMPORARY TABLE IF EXISTS descendants,nextparents,prevparents;
CREATE TEMPORARY TABLE descendants( childID INT, parentID INT, level INT );
CREATE TEMPORARY TABLE nextparents ( parentID INT );
CREATE TEMPORARY TABLE prevparents LIKE nextparents;
-- seed nextparents
IF ancestor RLIKE '[:alpha:]+' THEN -- ancestor passed as a string
INSERT INTO nextparents SELECT id FROM family WHERE name=ancestor;
ELSE
SET iMode = 1; -- ancestor passed as a numeric
INSERT INTO nextparents VALUES( CAST( ancestor AS UNSIGNED ));
END IF;
SET rows = ROW_COUNT();
WHILE rows > 0 DO
-- add children of nextparents
SET iLevel = iLevel + 1;
INSERT INTO descendants
SELECT t.childID, t.parentID, iLevel
FROM familytree AS t
INNER JOIN nextparents USING(parentID);
SET rows = ROW_COUNT();
-- save nextparents to prevparents
TRUNCATE prevparents;
INSERT INTO prevparents
SELECT * FROM nextparents;
-- next parents are children of these parents:
TRUNCATE nextparents;
INSERT INTO nextparents
SELECT childID FROM familytree JOIN prevparents USING (parentID);
SET rows = rows + ROW_COUNT();
END WHILE;
-- result
IF iMode = 1 THEN
SELECT CONCAT(REPEAT( ' ', level), parentID ) As Parent, GROUP_CONCAT(childID) AS Child
FROM descendants GROUP BY parentID ORDER BY level;
ELSE
SELECT CONCAT(REPEAT( ' ', level), PersonName(parentID) ) As Parent, PersonName(childID) AS Child
FROM descendants;
END IF;
END
go
DELIMITER ;