<?php
////////////////////////////////////////////////////////////
// TableToHash 0.5
// turn a MySQL database table into a hash, and optionally
// a state-maintaining popup menu (<SELECT> box)
//
// Paul Schreiber
// paul@magic.ca
// http://paulschreiber.com/
//
//
// there are two complementary functions here:
// tableToHash, which returns a hash
// tableToPopup, which prints out a popup menu based on the hash
//
// IMPORTANT: you must have already opened a connection to
// a MySQL database before using this, otherwise
// mysql_query() will fail
//
//
// parameters
// ----------
// table: the database table name
//
// id: the unique key for the table
//
// values: the value (or values) that correspond to each key
// you can supply either a scalar (column name) or an
// array of column name
//
// where: an optional WHERE clause, restricting which items to select
//
// order: an optional ORDER BV clauses, sorting the results from the db
//
// concatChar: the character which joins multiple columns in the values
// of the hash
//
// sample code
// -----------
// $members = tableToHash ( "people", "people_id",
// array( "fname", "lname"),
// "", // leave where blank
// "lname" );
//
//
// tableToPopup ( "books", "book_id", "book_title", "book_author" );
//
//
//
//
if ( !defined('TABLE_POPUP_DEFINED') ) {
define('TABLE_POPUP_DEFINED', TRUE);
function tableToHash ($table, $id, $values, $where = "", $order = "", $concatChar = " ") {
// check the required parameters
if (! ($table && $id && $values) ) {
echo "[parameter error]";
return ( array() );
}
// if we are selecting multiple columns (i.e. have been passed an array)
// then join the array items together in a list
if ( is_array($values) ) {
$valueList = join (", ", $values);
} else {
$valueList = $values;
}
// the minimal select statement
$sql = "SELECT $id, $valueList FROM $table";
// add a where statement, if the user supplied one
if ($where)
$sql .= " WHERE $where";
// add a order by statement, if the user supplied one
if ($order)
$sql .= " ORDER BY $order";
// execute the query
// quiet mode, keep the output nice
$result = @mysql_query($sql);
if ($result) {
$rows = mysql_num_rows($result);
// the database returned at least one row,
// so loop through the result
if ($rows) {
// we are selecting multiple columns, so we will concatenate
// them together for the values of the hash
if ( is_array($values) ) {
while ( $myrow = mysql_fetch_array($result) ) {
$temp = "";
while ( list($key, $val) = each($values) ) {
$temp .= $myrow[$val] . $concatChar;
}
// get rid of the extra space at the end
$temp = chop ($temp);
$hash[ $myrow[$id] ] = $temp;
reset ($values);
}
// we are selecting only one column
} else {
while ( $myrow = mysql_fetch_array($result) ) {
$hash[ $myrow[$id] ] = $myrow[$values];
}
}
// success!
return $hash;
} else {
echo "[no $table available]";
return ( array() );
}
} else {
echo "[database error]";
return ( array() );
}
}
function tableToPopup ($table, $id, $values, $where = "", $order = "") {
require ("popup_menu.php");
popup_menu ($id, tableToHash ($table, $id, $values, $where, $order) );
}
}
?>
|
Download
