/sql/mysqlabstract
[return to app]1
<?php
2 /**
3 * To use the older [deprecated] PHP extension for MySQL, remove the letter i from the end of "mysqli" on the next
line
4 */
5 abstract class mysqlabstract extends mysqli {
6 /**
7 * The database method used to sanitize user input strings
8 */
9 const ESCAPE_STRING = 'real_escape_string';
10
11 /**
12 * Returns the SQL to get the column type definition
13 *
14 * This method is MySQL-specific and may also work in newer versions of PostgreSQL.
15 *
16 * @param string $table
17 * @param string $column
18 * @return string
19 */
20 public function getColumnTypeSql($table, $column) {
21 return "select column_type from information_schema.columns where table_schema='" . get::$config->DB_NAME
22 . "' and table_name=" . $this->cleanString($table) . " and column_name=" .
$this->cleanString($column);
23 }
24
25 /**
26 * Returns the valid options of a column of MySQL type "enum" or "set"
27 *
28 * @param string $table
29 * @param string $column
30 * @param string $columnType Optional, must be either enum (default) or set
31 * @return array
32 */
33 public function getEnumOptions($table, $column, $columnType = 'enum') {
34 $res = $this->query($this->getColumnTypeSql($table, $column));
35 $row = current($res->fetch_row());
36 return explode("','", str_replace(array($columnType . "('", "')"), '', $row));
37 }
38
39 /**
40 * Inserts a new row or updates the content of an existing one
41 *
42 * Takes the same parameters as the insertSql() method except that to operate optimally the column names
43 * must be defined either in cols or as the keys in the vals array (will still work without it though.)
44 *
45 * @param array $args
46 * @return string
47 */
48 public function insertOrUpdateSql(array $args) {
49 $sql = $this->insertSql($args);
50 if (!$this->_cols) { //cannot avoid locking MyISAM tables without column names defined
51 return 'replace' . substr($this->insertSql($args), 6);
52 }
53 foreach ($this->_cols as $col) {
54 $colSql[] = $col . '=values(' . $col . ')';
55 }
56 $sql .= ' on duplicate key update ' . implode(', ', $colSql);
57 return $sql;
58 }
59 }