582 lines
17 KiB
PHP
582 lines
17 KiB
PHP
<?php
|
|
|
|
if (!isset($TABLE_INCLUDED) || $TABLE_INCLUDED != 1) {
|
|
|
|
$TABLE_INCLUDED = 1;
|
|
|
|
// Define the Table class
|
|
// - This assumes we have already connected to the database
|
|
|
|
class Table
|
|
{
|
|
|
|
public $table_name;
|
|
|
|
public $column_names = array();
|
|
public $column_types = array();
|
|
public $column_nulls = array();
|
|
public $column_defaults = array();
|
|
public $column_values = array();
|
|
public $column_values_array = array();
|
|
public $column_default_values = array();
|
|
public $key_columns = array();
|
|
public $num_columns;
|
|
|
|
public $columns = array();
|
|
public $school_num_flag = 'N';
|
|
|
|
public $userdata;
|
|
public $err;
|
|
public $err_no;
|
|
|
|
|
|
|
|
|
|
|
|
//
|
|
// Initialize the table data object with column and key information
|
|
//
|
|
public function __construct($table_name) {
|
|
|
|
// Setup main parameters
|
|
$this->table_name = $table_name;
|
|
|
|
|
|
// Get table information
|
|
$query = "show columns from $table_name";
|
|
$result = db_query($query);
|
|
|
|
if (! $result) {
|
|
$this->err = db_error();
|
|
$this->err_no = 103;
|
|
return;
|
|
}
|
|
|
|
|
|
// Get column information
|
|
$this->num_columns = db_num_rows($result);
|
|
$data = array();
|
|
while ($row = db_fetch_array($result)) {
|
|
$data[] = $row;
|
|
}
|
|
|
|
$this->columns = $data;
|
|
|
|
for ($i = 0; $i < count($data); $i++) {
|
|
$row = $data[$i];
|
|
|
|
$name = $row['Field'];
|
|
if ($row['Field'] == 'school_num') $this->school_num_flag = 'Y';
|
|
|
|
$type = preg_replace("#\(.*\)#", "", $row['Type']);
|
|
$type = str_replace(" unsigned", "", $type);
|
|
|
|
$values = "";
|
|
$values_array = array();
|
|
if ($type == 'enum') {
|
|
$values = preg_replace("#\)#", "", preg_replace("#.*\(#", "", $row['Type']));
|
|
if (strlen($values)) {
|
|
$values_array = explode(',', str_replace("'","",$values));
|
|
}
|
|
}
|
|
|
|
$null = $row['Null'];
|
|
|
|
$key = $row['Key'];
|
|
$default = $row['Default'];
|
|
|
|
$this->column_names[$i] = $name;
|
|
// echo("$i $name ".$this->column_names[$i]."\n");
|
|
$this->column_types[$i] = $type;
|
|
$this->column_nulls[$i] = $null;
|
|
$this->column_defaults[$i] = $default;
|
|
$this->column_values[$i] = $values;
|
|
$this->column_values_array[$i] = $values_array;
|
|
$this->key_columns[$i] = ($key == "PRI");
|
|
|
|
$this->column_types[$name] = $type;
|
|
$this->column_nulls[$name] = $null;
|
|
$this->column_defaults[$name] = $default;
|
|
$this->key_columns[$name] = ($key == "PRI");
|
|
|
|
switch ($name) {
|
|
case 'school_num':
|
|
if (array_key_exists('school_num',$GLOBALS)) $this->column_default_values[$name] = $GLOBALS['school_num'];
|
|
else $this->column_default_values[$name] = 0;
|
|
break;
|
|
|
|
default:
|
|
$this->column_default_values[$name] = $default;
|
|
break;
|
|
}
|
|
}
|
|
|
|
$this->err_no = 0;
|
|
|
|
} // End of Table() function
|
|
|
|
|
|
|
|
//
|
|
// Display the table structure in report format
|
|
//
|
|
function ShowTable() {
|
|
|
|
printf(" Table: $this->table_name\n\n");
|
|
|
|
printf(" %-30s %-15s %-15s% -15s\n",
|
|
"Column", "Type", "Null", "Pri");
|
|
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$value = $this->column_values[$i];
|
|
if (strlen($this->column_defaults[$i])) $value .= " (".$this->column_defaults[$i].")";
|
|
|
|
printf("%4d %-30s %-15s %-15s %-15s %s\n",
|
|
$i + 1,
|
|
$this->column_names[$i],
|
|
$this->column_types[$i],
|
|
$this->column_nulls[$i],
|
|
$this->key_columns[$i],
|
|
$value);
|
|
}
|
|
|
|
return;
|
|
|
|
} // End of ShowTable() function
|
|
|
|
|
|
|
|
//
|
|
// Display table structure in SQL format
|
|
//
|
|
function ShowStructure() {
|
|
|
|
print("create table $this->table_name (\n");
|
|
$primary = "";
|
|
$unique = "";
|
|
$index = "";
|
|
|
|
for ($i = 0; $i < count($this->columns); $i++) {
|
|
$row = $this->columns[$i];
|
|
|
|
$name = $row[0];
|
|
$type = $row[1];
|
|
$null = ($row[2] == "YES" ? "" : " not null");
|
|
|
|
switch ($row[3]) {
|
|
case "PRI":
|
|
if (strlen($primary)) $primary .= ", ";
|
|
$primary .= $name;
|
|
break;
|
|
|
|
case "UNI":
|
|
$unique .= " unique ($name),\n";
|
|
break;
|
|
|
|
case "MUL":
|
|
$index .= " index ($name),\n";
|
|
break;
|
|
|
|
default:
|
|
if ($row[3]) printf("%s - ", $row[3]);
|
|
}
|
|
|
|
printf(" %-30s%s%s,\n", $name, $type, $null);
|
|
}
|
|
|
|
printf("\n");
|
|
|
|
if (strlen($unique)) {
|
|
printf("%s\n", $unique);
|
|
}
|
|
|
|
if (strlen($index)) {
|
|
printf("%s\n", $index);
|
|
}
|
|
|
|
if (strlen($primary)) {
|
|
printf(" primary key (%s)\n", $primary);
|
|
}
|
|
|
|
print(");\n\n");
|
|
return;
|
|
|
|
} // End of ShowStructure() function
|
|
|
|
|
|
|
|
//
|
|
// Build an SQL query statement based on the current values
|
|
// of GLOBALS variables
|
|
//
|
|
// - This assumes that all values that are set in GLOBALS are to be used.
|
|
// It may be a problem if lingering data is in the GLOBALS array
|
|
//
|
|
function BuildQuery($orderby_fields="",$special_where="",$prefix="") {
|
|
// Initialize variables
|
|
$query = "";
|
|
|
|
$where = "$special_where";
|
|
$orderby = "";
|
|
|
|
// Process each field in the table
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$name = $this->column_names[$i];
|
|
$data = $GLOBALS[$name];
|
|
|
|
if (! empty($data) && $data != "NULL") {
|
|
// If there is a column already in the where clause add 'and'
|
|
if ($where) {
|
|
$where .= " and ";
|
|
}
|
|
|
|
// LIKE WILDCARD
|
|
if (preg_match("/[\*\?%]/", $data)) {
|
|
$data = preg_replace("/[\*]/", "%", $data);
|
|
$where .= "$prefix$name like " . $this->FieldData($name, $data);
|
|
}
|
|
// NOT EQUAL TO
|
|
elseif (preg_match("/^!= /",$data)) {
|
|
$data = preg_replace("/^!= /","",$data);
|
|
$where .= "$prefix$name != " . $this->FieldData($name, $data);
|
|
}
|
|
// LESS THAN OR EQUAL TO
|
|
elseif (preg_match("/^<= /",$data)) {
|
|
$data = preg_replace("/^<= /","",$data);
|
|
$where .= "$prefix$name <= " . $this->FieldData($name, $data);
|
|
}
|
|
// LESS THAN
|
|
elseif (preg_match("/^< /",$data)) {
|
|
$data = preg_replace("/^< /","",$data);
|
|
$where .= "$prefix$name < " . $this->FieldData($name, $data);
|
|
}
|
|
// GREATER THAN OR EQUAL TO
|
|
elseif (preg_match("/^>= /",$data)) {
|
|
$data = preg_replace("/^>= /","",$data);
|
|
$where .= "$prefix$name >= " . $this->FieldData($name, $data);
|
|
}
|
|
// GREATER THAN
|
|
elseif (preg_match("/^> /",$data)) {
|
|
$data = preg_replace("/^> /","",$data);
|
|
$where .= "$prefix$name > " . $this->FieldData($name, $data);
|
|
}
|
|
// EQUAL TO
|
|
else {
|
|
$where .= "$prefix$name = " . $this->FieldData($name, $data);
|
|
}
|
|
}
|
|
|
|
// Build order by part based on field sequence
|
|
if ($name != "record_num" && $this->key_columns[$i]) {
|
|
if ($ord) {
|
|
$orderby .= ",";
|
|
}
|
|
|
|
$orderby .= "$prefix$name";
|
|
$ord = 1;
|
|
}
|
|
}
|
|
|
|
// Build up the statement
|
|
$query = "SELECT * from $this->table_name";
|
|
|
|
// If where clause then append it
|
|
if ($where) {
|
|
$query .= " where $where";
|
|
}
|
|
|
|
// If there is an orderby clause append it
|
|
if ($orderby_fields) {
|
|
$query .= " order by " . $orderby_fields;
|
|
}
|
|
else {
|
|
if ($ord) {
|
|
$query .= " order by $orderby";
|
|
}
|
|
}
|
|
|
|
return $query;
|
|
|
|
} // End of BuildQuery() function
|
|
|
|
|
|
|
|
//
|
|
// Build the set of hidden fields for this table
|
|
// - This function has probably been replaced with the script:
|
|
// "hidden-form-fields.php"
|
|
//
|
|
function BuildHidden() {
|
|
// Initialize variables
|
|
$hidden = "";
|
|
|
|
// Process each field in the table
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$name = $this->column_names[$i];
|
|
$data = db_escape_string($GLOBALS[$name]);
|
|
|
|
$hidden .= "<INPUT TYPE=\"HIDDEN\" NAME=\"$name\" VALUE=\"$data\">\n";
|
|
}
|
|
|
|
return $hidden;
|
|
|
|
} // End of BuildHidden() function
|
|
|
|
|
|
|
|
//
|
|
// Build an SQL insert statement based on the values in the GLOBALS array
|
|
//
|
|
function BuildInsert($data_array=0,$include_record_num='No') {
|
|
|
|
// Set processing array
|
|
if (!is_array($data_array) || count($data_array) == 0) $data_array = $GLOBALS;
|
|
|
|
// Initialize variables
|
|
$query = "";
|
|
|
|
$q1 = "INSERT into $this->table_name (";
|
|
$q2 = "values (";
|
|
|
|
// Process each field in the table
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$name = $this->column_names[$i];
|
|
$ftype = $this->column_types[$i];
|
|
|
|
$data = '';
|
|
if (array_key_exists($name, $data_array)) $data = $data_array[$name];
|
|
else {
|
|
if ($ftype == 'enum') $data = $this->column_defaults[$i];
|
|
}
|
|
|
|
if ($name != "record_num" || $include_record_num == 'Yes') {
|
|
// If there is a column already in the string add a comma
|
|
if ($i) {
|
|
$q1 .= ", ";
|
|
$q2 .= ", ";
|
|
}
|
|
|
|
$q1 .= $name;
|
|
$q2 .= $this->FieldData($name, $data);
|
|
}
|
|
}
|
|
|
|
$query = $q1 . ") " . $q2 . ")";
|
|
return $query;
|
|
|
|
} // End of BuildInsert() function
|
|
|
|
|
|
|
|
//
|
|
// Build an SQL update statement based on the values in the GLOBALS array
|
|
//
|
|
function BuildUpdate($data_array=0,$mode="single") {
|
|
// Initialize variables
|
|
$query = "UPDATE $this->table_name set ";
|
|
$update = "";
|
|
|
|
// Process each field in the table
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$name = $this->column_names[$i];
|
|
|
|
if (($name != "record_num")) {
|
|
unset($data);
|
|
|
|
// See if a data value is available for use in query
|
|
if (is_array($data_array) && count($data_array) > 0) {
|
|
if (isset($data_array[$name])) $data = $data_array[$name];
|
|
}
|
|
else {
|
|
if (isset($GLOBALS[$name])) $data = $GLOBALS[$name];
|
|
}
|
|
|
|
if (isset($data)) {
|
|
// If there is a column already in the string add a comma
|
|
$value = $this->FieldData($name, $data);
|
|
|
|
// Single record update statement
|
|
if ($mode == "single") {
|
|
if ($update) $update .= ", ";
|
|
$update .= $name . " = " . $value;
|
|
}
|
|
|
|
// Multi record update statement
|
|
if ($mode == "multi") {
|
|
if ($value == "''") $value = NULL;
|
|
if ($value == "NULL") $value = NULL;
|
|
if ($value == "0") $value = NULL;
|
|
|
|
if ($value) {
|
|
if ($update) $update .= ", ";
|
|
$update .= $name . " = " . $value;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
// Define the where clause
|
|
$where_clause = "";
|
|
if ($mode == "single") {
|
|
$record_num = 0;
|
|
if (is_array($data_array) && count($data_array) > 0) {
|
|
if (array_key_exists('record_num',$data_array)) $record_num = intval($data_array["record_num"]);
|
|
}
|
|
else {
|
|
if (array_key_exists('record_num',$GLOBALS)) $record_num = intval($GLOBALS["record_num"]);
|
|
}
|
|
$where_clause = "where record_num = $record_num";
|
|
}
|
|
|
|
$query = "$query $update $where_clause";
|
|
|
|
return $query;
|
|
|
|
} // End of BuildUpdate() function
|
|
|
|
|
|
|
|
//
|
|
// Format the data correctly based on the field type in
|
|
// the table structure
|
|
//
|
|
function FieldData($field_name, $data = "") {
|
|
$value = "";
|
|
|
|
$type = $this->column_types[$field_name];
|
|
$null = $this->column_nulls[$field_name];
|
|
|
|
$data = trim($data);
|
|
|
|
// format data based on field type
|
|
switch ($type) {
|
|
case "varchar":
|
|
case "text":
|
|
case "longtext":
|
|
case "char":
|
|
case "enum":
|
|
$data = htmlspecialchars($data,ENT_QUOTES);
|
|
$data = str_replace("&quot;",'"',$data);
|
|
$data = str_replace("&","&",$data);
|
|
$data = str_replace("&","&",$data);
|
|
$data = str_replace(""",'"',$data);
|
|
$data = str_replace("'","'",$data);
|
|
$data = str_replace("\\","",$data);
|
|
$data = str_replace("\\","",$data);
|
|
$data = db_escape_string($data);
|
|
|
|
if (strlen($data) == 0) {
|
|
if ($null == "NO" || $type == "enum") {
|
|
$data = $this->column_defaults[$field_name];
|
|
}
|
|
}
|
|
|
|
$value = "'$data'";
|
|
break;
|
|
|
|
case "int":
|
|
case "bigint":
|
|
case "smallint":
|
|
case "decimal":
|
|
if (strlen($data) == 0) {
|
|
if ($null == "YES") {
|
|
$value = "NULL";
|
|
}
|
|
else {
|
|
$value = "0";
|
|
}
|
|
}
|
|
else {
|
|
if ($type == 'decimal') $value = floatval($data);
|
|
else $value = intval($data);
|
|
}
|
|
break;
|
|
|
|
case "date":
|
|
if (empty($data) || strlen($data) == 0) {
|
|
if ($null == "YES") {
|
|
$value = "NULL";
|
|
}
|
|
else {
|
|
$dt = date("Y-m-d", time());
|
|
$value = "'$dt'";
|
|
}
|
|
}
|
|
else {
|
|
$data = db_escape_string($data);
|
|
$value = "'$data'";
|
|
}
|
|
break;
|
|
|
|
case "time":
|
|
if (empty($data) || strlen($data) == 0) {
|
|
if ($null == "YES") {
|
|
$value = "NULL";
|
|
}
|
|
else {
|
|
$dt = date("H:i:s", time());
|
|
$value = "'$dt'";
|
|
}
|
|
}
|
|
else {
|
|
$data = db_escape_string($data);
|
|
$value = "'$data'";
|
|
}
|
|
break;
|
|
|
|
case "datetime":
|
|
if (! $data) {
|
|
$dt = date("Y-m-d H:i:s", time());
|
|
$value = "'$dt'";
|
|
}
|
|
else {
|
|
$data = db_escape_string($data);
|
|
$value = "'$data'";
|
|
}
|
|
break;
|
|
|
|
case "timestamp":
|
|
if (! $data) {
|
|
$dt = date("YmdHis", time());
|
|
$value = "'$dt'";
|
|
}
|
|
else {
|
|
$data = db_escape_string($data);
|
|
$value = "'$data'";
|
|
}
|
|
break;
|
|
|
|
default:
|
|
$data = db_escape_string($data);
|
|
$value = "'$data'";
|
|
}
|
|
|
|
// return the correctly formatted data
|
|
return $value;
|
|
|
|
} // End of FieldData() function
|
|
|
|
|
|
|
|
//
|
|
// Function to convert a data record into GLOBALS data
|
|
//
|
|
function RecordData($record) {
|
|
|
|
// Process each field in the table
|
|
for ($i = 0; $i < count($this->column_names); $i++) {
|
|
$name = $this->column_names[$i];
|
|
$GLOBALS[$name] = $record[$name];
|
|
}
|
|
|
|
} // End of RecordData() function
|
|
|
|
|
|
} // End of class definition
|
|
|
|
|
|
} // End of if TABLE_INCLUDED statement
|
|
|
|
?>
|