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.
* Example:
* $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 (!in_array(it::replace(array(' DESC$' => ""), $_REQUEST[$table_sort]), $fields)) $_REQUEST[$table_sort] = $default_order; $sql = "SELECT $c_fields FROM $this->name"; if ($sqlwhere) $sql .= " WHERE $sqlwhere"; $sql .= " ORDER BY $_REQUEST[$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"). "
\n"; return; } echo "\n"; if (empty($thispage)) $thispage = $_SERVER['PHP_SELF']; if (strstr($thispage, "?")) $thispage .= "&"; else $thispage .= "?"; /* Wenn man keine Titelzeile will, für c_descriptions einfach "," angeben */ if ($descriptions[0]) { echo ''; for ($i=0; $i < $numfields; ++$i) { if ($_REQUEST[$table_sort] == $fields[$i]) { $newsort = "$_REQUEST[$table_sort]+DESC"; $sortimg = ''; } else if ($_REQUEST[$table_sort] == "$fields[$i] DESC") { $newsort = $fields[$i]; $sortimg = ''; } else { $newsort = $fields[$i]; $sortimg = ''; } echo "\n"; } echo "\n"; } while ($f = $this->db->fetch_array($result)) { echo ""; 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=""; $anchor2=""; } else $anchor = $anchor2 = ""; if ($strip_tags) $fieldspec = strip_tags($fieldspec); echo ""; } echo "\n"; } echo "

\n"; echo "\n\n"; echo ''; echo ''; echo "\n '. $descriptions[$i] .' '. $sortimg .' \n"; echo "
$anchor" . $fieldspec . "$anchor2

\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 ""; if ($globaloption != "") echo "", $globalopt[1], ""; $result = $this->db->safe_sql_query($query); while ($row = $this->db->fetch_array($result)) { $tag = ($row[$optionfield] == $selected) ? " selected" : ""; echo "", $row[$descriptionfield], ""; } echo "\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 */ ?>