path: root/db_table.class

<?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 (!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"). "<br>\n";
		return;
	}

	echo "<table $tableargs>\n";

	/* Wenn man keine Titelzeile will, für c_descriptions einfach "," angeben */
	if ($descriptions[0])
	{
		echo '<tr>';
		for ($i=0; $i < $numfields; ++$i)
		{
			if ($_REQUEST[$table_sort] == $fields[$i])
			{
				$newsort = "$_REQUEST[$table_sort] DESC";
				$sortimg = '<img src="/icons/down.gif" border="0" width="10" height="11" alt="">';
			}
			else if ($_REQUEST[$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="'. it_html::U($thispage, array($table_sort => $newsort) + $_GET) .'">'. $descriptions[$i] .'</a></td>';
			echo '<td><a href="'. it_html::U($thispage, array($table_sort => $newsort) + $_GET) .'">'. $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 = "&nbsp; ";
			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 */
?>