DBTable object private $tableList = -1; // default value: unassigned private $upToDate = false; /* Constructor */ public function __construct($dbhost, $dbuser, $dbname) { $this->DB_HOST = $dbhost; $this->DB_USER = $dbuser; $this->DB_NAME = $dbname; } /* Connect to database server and select DB $DB_NAME */ public function connectToDB() { $this->DB = mysql_connect($this->DB_HOST, $this->DB_USER, $this->PASSWD) or die("DB: connect failed"); mysql_select_db($this->DB_NAME) or die("DB: select failed"); mysql_query("SET time_zone = '-4:00'"); // adjust for Eastern time } /******** execute an sql command. Returns boolean or an array with result resource extracted, or error message ***/ public function query($sql) { $result = mysql_query($sql); if (mysql_error() != "") { return "Error in query: ".mysql_error(); } else { return $this->extractRecords($result); } } /************************ GETTER METHODS ********************************/ /* Get all the database table names. Returns an array of table names */ public function getTableList() { if ($this->tableList == -1) { // retrieve table list for first time $DB_TABLES = mysql_query("show tables", $this->DB); $i = 0; while ($currtable = mysql_fetch_assoc($DB_TABLES)) { $db_tables[$i] = $currtable; $i = $i + 1; } $tb_list = array(); $i = 0; foreach ($db_tables as $key => $tb_title_arr) { $tb_list[$i] = $tb_title_arr["Tables_in_".$this->DB_NAME]; $i = $i + 1; } $this->tableList = $tb_list; } return $this->tableList; } /* Gets names of the table fields */ public function getTableFields($tb_name) { $sql = "show columns from ".$tb_name; $RESULT = mysql_query($sql, $this->DB); $entries = $this->extractRecords($RESULT); // field names contained in $entries[int]['Field'] reset($entries); $allfields = array(); $i = 0; while ($field = current($entries)) { $allfields[$i] = $field["Field"]; next($entries); $i = $i + 1; } return $allfields; } /* Get number of affected rows of last mysql operation */ public function getNumOfAffectedRows() { return mysql_affected_rows(); } /* Insertion operation. Returns true if successful, otherwise an array containing reason for failure */ public function insertRow($tb_name, $row) { // validate table name and fields being inserted $fields = array_keys($row); $valid = $this->checkTableAndFields($tb_name, $fields); if ($valid !== true) { return $valid; } // build sql string and send query $sql = "INSERT INTO ".$tb_name." ("; reset($fields); while ($value = current($fields)) { if (!next($fields)) { $sql = $sql."`".$value."`) VALUES ("; } else { $sql = $sql."`".$value."`,"; } } reset($row); while ($value = current($row)) { if (!next($row)) { $sql = $sql."\"".$value."\")"; } else { $sql = $sql."\"".$value."\","; } } $RESULT = mysql_query($sql, $this->DB); // check status of the insert if ($RESULT) { $this->upToDate = false; return true; } else { return "Error in insertRow: ".mysql_error()."
".$sql; } } /************************ private helper functions *********************************************/ /* Extract information from a result resource into associative array */ private function extractRecords($result) { $rows = array(); $i = 0; while ($row = mysql_fetch_assoc($result)) { $rows[$i] = $row; $i = $i + 1; } return $rows; } /* Checks for valid table name and fields. Returns true if valid, otherwise an error code. */ private function checkTableAndFields($tb_name, $fields) { // check if tableList is retrieved if ($this->tableList == -1) { $this->getTableList(); } // check for valid table name in tableList if (!in_array($tb_name, $this->tableList)) { return "Table *".$tb_name."* does not exist in database ".$this->DB_NAME."."; } // check validity of fields in associative array $allfields = $this->getTableFields($tb_name); while ($notfound == false && $field = current($fields)) { if (!in_array($field, $allfields)) { return "Field *".$field."* does not exist in table ".$tb_name."."; } next($fields); } return true; } } ?>