diff options
Diffstat (limited to 'db_table.class')
| -rw-r--r-- | db_table.class | 425 |
1 files changed, 425 insertions, 0 deletions
diff --git a/db_table.class b/db_table.class new file mode 100644 index 0000000..f99a733 --- /dev/null +++ b/db_table.class @@ -0,0 +1,425 @@ +<?php +/* +** $Id$ +** +** ITools - the Internet Tools Library +** +** Copyright (C) 1995-2003 by the ITools Authors. +** This program is free software; you can redistribute it and/or +** modify it under the terms of either the GNU General Public License +** or the GNU Lesser General Public License, as published by the Free +** Software Foundation. See http://www.gnu.org/licenses/ for details. +** +** it_db_table.class - A database table based on it_db.class +*/ + +/** + * Functions to handle SQL database tables + * @see it_db, it_db_record + */ +class it_db_table +{ + var $db; /* Underlying Database */ + var $name; /* Tabellenname */ + + var $numfields = 0; /* Number of fields in table or 0 if not known */ + var $fieldnames = array(); /* Array(0..$numfields) of field names */ + var $fieldtypes = array(); /* Array(0..$numfields) or (fieldnames) of field types */ + var $fieldlengths = array(); /* Array(0..$numfields) or (fieldnames) of field lengths */ + var $fieldflags = array(); /* Array(0..$numfields) or (fieldnames) of field flags */ + +/** + * Initialize object data + * @param $db it_db object of the database to be used + * @param $name name of the table to be used + */ +function it_db_table(&$db, $name) +{ + $this->db = &$db; + $this->name = $name; +} + + +/** + * Perform a safe SQL SELECT query on this table + * @see it_db::safe_sql_query + * @param $query additional query string, appended at the end of the generated query + * @param $fields comma seperated list of the columns to be returned + */ +function safe_sql_select($query, $fields="*") +{ + return $this->db->safe_sql_query("SELECT $fields FROM " . $this->name . " $query"); +} + + +/** + * Create an SQL query (the stuff after 'WHERE') according to + * an array of selection criteria.<br> + * Example:<br> + * $sql = $table->construct_sql_clause(array('Type' => 'bar', + * 'Date >=' => '1999-01-01', '-Date <' => 'NOW()', + * 'User NI' => 'chris'), 'ORDER BY Date'); + * + * @param $params optional array of fieldname => value tupels. + * These are ANDed to form a WHERE clause. + * fieldname can contain an operator (separated by space), the + * default operator is '='. The special operator 'NI' specifies + * that the argument must be contained in a comma-separated list. + * @param $sql Optional SQL addendum (added after $params), for ORDER BY etc. + * @param $omit_where (optional) Do not add 'WHERE ' at beginning of result (default: false) + * @return The generated SQL clause + * @see it_db_record::select, it_db_record::fetch_next + */ +function construct_sql_clause($params='', $sql='', $omit_where=false) +{ + if (is_array($params) && (count($params) > 0)) + { + $query = ''; + $sep = ''; + + foreach($params as $field => $value) + { + if (is_int($field)) /* no key specified; just append */ + { + if ($field === $value) /* ignore array(1 => 1) et al */ + continue; + + $query .= " $value"; + } + else + { + $needs_where = true; + + if (!isset($value)) + { + $op = 'IS'; + $qval = 'NULL'; + } + else + { + if (preg_match('/^(\S+)\s+(\S.*)$/', $field, $regs)) + { + $field = $regs[1]; + $op = strtoupper($regs[2]); + } + else + $op = '='; + + /* If the field name starts with '-', the value is taken as raw, + no escaping is done and no quotes are put around it. */ + if (substr($field, 0, 1) == '-') + { + $field = substr($field, 1); /* Strip that '-' sign */ + $qval = $value; + } + else if (!is_array($value)) + $qval = "'".mysql_real_escape_string((string)$value)."'"; + } + + switch ($op) + { + case 'NI': + $query .= $sep."CONCAT(',',$field,',') LIKE '%,$value,%'"; + break; + + case 'IN': + case 'NOT IN': + if (is_array($value)) + { + if ($value) + $query .= "$sep$field $op ('" . join("','", array_map('mysql_real_escape_string', $value)) . "')"; # null is mapped to '' + else + $query .= $sep . "0"; + + break; + } + /* FALLTHROUGH */ + + default: + if (isset($qval)) + $query .= "$sep$field $op $qval"; + else + it::fatal('Undefined $qval when constructing query due to invalid $value (array)'); + break; + } + $sep = ' AND '; + } + } + + if ($needs_where && !$omit_where) + $query = 'WHERE '.$query; + + if ($sql) + $query .= ' '; + } + $query .= $sql; + + return $query; +} + + +/** + * Return the count of rows on this table with optional WHERE clause + * @param $where Optional WHERE clause to narrow the set of rows to count + * @return Number of rows matching the WHERE clause + */ +function count($where='') +{ + $result = $this->safe_sql_select($where, 'COUNT(*)'); + $row = $this->db->fetch_assoc($result); + return $row['COUNT(*)']; +} + + +/* Internal: get information about fields */ +function _get_field_info() +{ + if ($this->numfields > 0) /* Already done! */ + return; + + if(($fields = mysql_list_fields($this->db->name, $this->name, $this->db->link)) >= 0) + { + $this->numfields = mysql_num_fields($fields); + for ($i=0; $i < $this->numfields; ++$i) + { + $name = mysql_field_name($fields, $i); + $this->fieldnames[$i] = $name; + $this->fieldtypes[$i] = $this->fieldtypes[$name] = mysql_field_type($fields, $i); + $this->fieldlengths[$i] = $this->fieldlengths[$name] = mysql_field_len($fields, $i); + $this->fieldflags[$i] = $this->fieldflags[$name] = mysql_field_flags($fields, $i); + /* debug("name='" . $this->fieldnames[$i] . "', len=" . $this->fieldlengths[$i] . ", type='" . $this->fieldtypes[$i] . "', flags='" . $this->fieldflags[$i] . "'\n", 6); */ + } + } + else internal_error("mysql_list_fields($this->db->name, $this->name, $this->db->link) failed."); +} + + +/** + * Return a comma separated list of all field names of this table + */ +function field_names() +{ + $result = ''; + $this->_get_field_info(); + for ($i=0; $i < $this->numfields; ++$i) + { + if ($i > 0) $result .= ","; + $result .= $this->fieldnames[$i]; + } + return $result; +} + + +/** + * Return the length of a field + * @param $fieldname Name of the field + */ +function get_field_length($name) +{ + $this->_get_field_info(); + return $this->fieldlengths[$name]; +} + + +/** + * Return number of fields of a record of this table + */ +function num_fields() +{ + $this->_get_field_info(); + return $this->numfields; +} + + +/** + * Variable name used to propagate sort criteria + */ +function get_sort_variable_name() +{ + return "Sort" . md5($this->db->name . $this->name); +} + + +/** + * Print an SQL table as an HTML table. Supports user-sorting by column, + * limited WHERE clause, lists of fields to display and header texts, and + * a list of links for each field. + * @param $tableargs format string for the table or "" for default + * @param $sqlwhere SQL WHERE clause to restrict elements + * @param $c_fields comma seperated list of the fields to print + * @param $c_descriptions comma seperated list of the header labels to print + * @param $c_links dito with links to the respective elements + * @param $default_order default sort order: "fieldname" or "fieldname DESC" + * @param $rows_limit limit and position: "10" or "20,5" + */ +function dump_html($tableargs="", $sqlwhere="", $c_fields="", $c_descriptions="", $c_links="", $default_order="", $rows_limit="", $thispage="", $strip_tags=0) +{ + /* Unique identifier of this table */ + $table_sort = $this->get_sort_variable_name(); + + /* Default HTML table */ + if ($tableargs=="") + $tableargs="border=1 cellspacing=0 cellpadding=5"; + + /* Default field list: show them all */ + if ($c_fields == "") + $c_fields = $this->field_names(); + + /* Default list title: Field names */ + if ($c_descriptions == "") + $c_descriptions = $c_fields; + + $fields = split(",", $c_fields); + $descriptions = split(",", $c_descriptions); + $links = split(",", $c_links); + $numfields = count($fields); + + /* Default sort criterium: Ascending sort by first column */ + if ($default_order == "") + $default_order = $fields[0]; + + if ($GLOBALS[$table_sort] == "") + $GLOBALS[$table_sort] = $default_order; + + $sql = "SELECT $c_fields FROM $this->name"; + + if ($sqlwhere) + $sql .= " WHERE $sqlwhere"; + + $sql .= " ORDER BY $GLOBALS[$table_sort]"; + + if ($rows_limit != "") + $sql .= " LIMIT $rows_limit"; + + $result = $this->db->safe_sql_query($sql); + + if ($this->db->num_rows($result) == 0) + { + if (is_object($GLOBALS['it_text'])) + echo T("db_NoObjectsFound"). "<br>\n"; + return; + } + + echo "<table $tableargs>\n"; + + if (empty($thispage)) $thispage = $GLOBALS['PHP_SELF']; + if (strstr($thispage, "?")) $thispage .= "&"; else $thispage .= "?"; + + /* Wenn man keine Titelzeile will, für c_descriptions einfach "," angeben */ + if ($descriptions[0]) + { + echo '<tr>'; + for ($i=0; $i < $numfields; ++$i) + { + if ($GLOBALS[$table_sort] == $fields[$i]) + { + $newsort = "$GLOBALS[$table_sort]+DESC"; + $sortimg = '<img src="/icons/down.gif" border="0" width="10" height="11" alt="">'; + } + else if ($GLOBALS[$table_sort] == "$fields[$i] DESC") + { + $newsort = $fields[$i]; + $sortimg = '<img src="/icons/up.gif" border="0" width="10" height="11" alt="">'; + } + else + { + $newsort = $fields[$i]; + $sortimg = ''; + } + + echo "<th align=\"left\">\n"; + echo "<table border=\"0\">\n<tr valign=\"middle\">\n"; + echo '<td><a href="'. $thispage . $table_sort .'='. $newsort .'">'. $descriptions[$i] .'</a></td>'; + echo '<td><a href="'. $thispage . $table_sort .'='. $newsort .'">'. $sortimg .'</a></td>'; + echo "</tr>\n</table>\n"; + echo "</th>\n"; + } + echo "</tr>\n"; + } + + while ($f = $this->db->fetch_array($result)) + { + echo "<tr>"; + for ($i=0; $i<$numfields; ++$i) + { + $fieldspec = $f[$fields[$i]]; + if (!$fieldspec) $fieldspec = " "; + if ($fieldspec == "0.00") $fieldspec = "-"; + /*if (strlen($fieldspec) > 80) $fieldspec = substr($fieldspec, 0, 80) . "..."; */ + + if (isset($links[$i])) + { + if (strstr($links[$i], "?")) $ch = "&"; else $ch = "?"; + $anchor="<a href=\"" . $links[$i] . $ch . $fields[$i] . "=" . urlencode($f[$fields[$i]]) . "\">"; + $anchor2="</a>"; + } + else + $anchor = $anchor2 = ""; + + if ($strip_tags) + $fieldspec = strip_tags($fieldspec); + echo "<td>$anchor" . $fieldspec . "$anchor2</td>"; + } + echo "</tr>\n"; + } + echo "</table>\n"; +} + + +/** + * Make a 'select form field' ("Select-Form-Feld") from a table and pre-select an entry + * @param $name Name of FORM object to generate + * @param $selected Selected record + * @param $globaloption Option to add as first value, syntax: key,text + * @param $optionfield Table field to use for option keys + * @param $descriptionfield Table field to display + * @param $query SQL Query after "FROM ...", defaults to "ORDER BY $descriptionfield" + */ +function make_select($name, $selected, $globaloption, $optionfield, $descriptionfield, $query="") +{ + $globalopt = split(",", $globaloption); + + if (empty($query)) + $query = "ORDER BY $descriptionfield"; + $query = "SELECT * FROM $this->name $query"; + + echo "<select name=\"$name\">"; + + if ($globaloption != "") + echo "<option value=\"", $globalopt[0], "\">", $globalopt[1], "</option>"; + + $result = $this->db->safe_sql_query($query); + + while ($row = $this->db->fetch_array($result)) + { + $tag = ($row[$optionfield] == $selected) ? " selected" : ""; + echo "<option value=\"", $row[$optionfield], "\"$tag>", $row[$descriptionfield], "</option>"; + } + echo "</select>\n"; +} + + +/** + * Drops the current table from the database + * @return MySQL result (0 means failure) + */ +function drop() +{ + if ($this->name == '') + internal_error("it_db_table::drop(): no table\n"); + + if ($result = $this->db->sql_query('DROP TABLE IF EXISTS '.$this->name)) + { + $this->name = ''; + $numfields = 0; + $fieldnames = array(); + $fieldtypes = array(); + $fieldlengths = array(); + $fieldflags = array(); + } + + return $result; +} + +} /* End class it_db_table */ +?> |