/** * * execute a SQL query in the form SELECT $formula as Result * * @param model class $model * @param string $formula A string that is a formula like 2+3 * @return mixed. The array of queries */ public function calculateViaSQL(Model $model, $formula) { $formula = "Select " . $formula . " as Result;"; $result = $this->dbo->fetchRow($formula); if (empty($result)) { return false; } else { return (string) $result[0]['Result']; } }
/** * Creates XML from the ADODB record set * * @param object $rs - record set object * @param bool $moveFirst - determines whether recordset is returned to first record * @return string $xml - resulting xml * @version V1.0 10 June 2006 (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved. * * Released under both BSD license and Lesser GPL library license. You can choose which license * you prefer. */ function rs2xml($rs, $moveFirst = false) { if (!$rs) { printf(ADODB_BAD_RS, 'rs2xml'); return false; } $xml = ''; $totalRows = 0; $totalRows = $rs->numrows(); $domxml = new DOMDocument('1.0', 'utf-8'); $root = $domxml->appendChild($domxml->createElement('rows')); $root->setAttribute('total-rows', $totalRows); $row_count = 1; while ($line = $rs->fetchRow()) { $row = $root->appendChild($domxml->createElement('row')); foreach ($line as $col_key => $col_val) { $col = $row->appendChild($domxml->createElement('column')); $col->setAttribute('name', strtolower($col_key)); $col->appendChild($domxml->createTextNode($col_val)); } $row_count++; } $domxml->formatOutput = true; $xml = $domxml->saveXML(); $domxml = null; if ($moveFirst) { $rs->MoveFirst(); } return $xml; }
/** * Run a query and return a single row * * @param string $sql * @return array */ function selectRow($sql) { $this->debug['queries']++; $this->debug['actualQueries'][] = $sql; $c = $this->db->fetchRow($sql); return $c; }
/** * get the mysql variable * * @access public * @param string name variable name * @return string variable */ function getVariable($name) { $sql = 'SHOW VARIABLES LIKE \'' . $name . '\''; $res = $this->_db->queryF($sql); while (list($key, $value) = $this->_db->fetchRow($res)) { $variable[$key] = $value; } return $variable[$name]; }
/** * Read a session from the database * * @param string &sess_id ID of the session * * @return array Session data */ function read($sess_id) { $sql = sprintf('SELECT sess_data FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id)); if (false != ($result = $this->db->query($sql))) { if (list($sess_data) = $this->db->fetchRow($result)) { return $sess_data; } } return ''; }
/** * Load this object * @since Version 3.10.0 * @return void; */ private function getFootage() { $data = $this->db->fetchRow("SELECT * FROM railcam_footage WHERE id = ?", $this->id); $data['fileinfo'] = json_decode($data['fileinfo'], true); $this->timeStamp = new DateTime($data['datestored']); $this->setCamera(new Camera($data['railcam_id'])); $this->type = $data['type']; $storageObject = new Storage($data['storage_id']); $this->footageData = ["id" => $data['id'], "camera" => $this->cameraObject->getArray(), "type" => $this->type, "url" => ["original" => $storageObject->getWebUrl($data['fileinfo'])]]; return; }
/** * 获取所有的fields * * @access public * @param string $table_name 数据表名 * @return array */ function get_fields($table_name) { $fields = array(); $result = $this->db->query("SHOW FIELDS FROM {$table_name}", 'SILENT'); if ($result) { while ($row = $this->db->fetchRow($result)) { $fields[] = $row['Field']; } } return $fields; }
/** * Constructor * * @param array Options containing 'table', 'name' */ public function __construct(array $options = array()) { // Initialize the options $options = $this->_initialize($options); // Mixin the KMixinClass $this->mixin(new KMixinClass($this, 'Rowset')); // Assign the classname with values from the config $this->setClassName($options['name']); // Set table object and class name $this->_tableClass = 'com.' . $this->getClassName('prefix') . '.table.' . $this->getClassName('suffix'); $this->_table = isset($options['table']) ? $options['table'] : KFactory::get($this->_tableClass); // Set the data if (isset($options['data'])) { $this->_data = $options['data']; } // Count the data $this->resetCount(); // Instantiate an empty row to use for cloning later $this->_emptyRow = $this->_table->fetchRow(); }
/** * count objects matching a criteria * * @param object $criteria {@link CriteriaElement} to match * @return int count of objects * @access public */ function getCount($criteria = null) { $sql = 'SELECT COUNT(*) FROM ' . $this->_db->prefix($this->_dbtable); if (isset($criteria) && is_subclass_of($criteria, 'criteriaelement')) { $sql .= ' ' . $criteria->renderWhere(); } if (!($result =& $this->_db->query($sql))) { return 0; } list($count) = $this->_db->fetchRow($result); return $count; }
/** * Count the number of online users * * @param object $criteria {@link CriteriaElement} */ function getCount($criteria = null) { $sql = 'SELECT COUNT(*) FROM ' . $this->db->prefix('online'); if (is_object($criteria) && is_subclass_of($criteria, 'criteriaelement')) { $sql .= ' ' . $criteria->renderWhere(); } if (!($result = $this->db->query($sql))) { return false; } list($ret) = $this->db->fetchRow($result); return $ret; }
/** * 获得用于验证身份的信息 * * @access private * @return string or boolean 成功返回用于登录短信服务的帐号信息,失败返回false。 */ function get_login_info() { $sql = 'SELECT `code`, `value` FROM ' . $this->ecs->table('shop_config') . " WHERE `code` = 'sms_user_name' OR `code` = 'sms_password'"; $result = $this->db->query($sql); $retval = array(); if (!empty($result)) { while ($temp_arr = $this->db->fetchRow($result)) { $retval[$temp_arr['code']] = $temp_arr['value']; } return base64_encode($retval['sms_user_name'] . "\t" . $retval['sms_password']); } return false; }
/** * calculateTotal() * Calculate totlas * @access public * @return type */ private function calculateTotal() { if (!$this->totalPages) { $this->query = new \NG\Query(); $this->query->select("COUNT(*) as total")->from($this->table); if (isset($this->where)) { $this->query->where($this->where); } $result = $this->db->fetchRow($this->query->__toString()); $this->totalCount = $result['total']; $this->totalPages = ceil($this->totalCount / $this->itemsPerPage); } return $this->totalPages; }
/** * Write a session to the database * * @param string $sess_id * @param string $sess_data * * @return bool **/ function write($sessionId, $data) { $sessionId = $this->db->quote($sessionId); $data = $this->db->quote($data); $time = time(); list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM `{$this->tableName}` WHERE sess_id={$sessionId}")); if ($count > 0) { $sql = "UPDATE `{$this->tableName}` SET sess_updated={$time},sess_data={$data} WHERE sess_id={$sessionId}"; } else { $ip = $this->db->quote($_SERVER['REMOTE_ADDR']); $sql = "INSERT INTO `{$this->tableName}` (sess_id,sess_updated,sess_ip,sess_data) VALUES ({$sessionId},{$time},{$ip},{$data})"; } return $this->db->queryF($sql); }
/** * Write a session to the database * * @param string $sess_id * @param string $sess_data * * @return bool **/ function write($sess_id, $sess_data) { global $HTTP_SERVER_VARS; list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM " . $this->db->prefix('session') . " WHERE sess_id='" . $sess_id . "'")); if ($count > 0) { $sql = sprintf("UPDATE %s SET sess_updated = %u, sess_data = '%s' WHERE sess_id = '%s'", $this->db->prefix('session'), time(), $sess_data, $sess_id); } else { $sql = sprintf("INSERT INTO %s (sess_id, sess_updated, sess_ip, sess_data) VALUES ('%s', %u, '%s', '%s')", $this->db->prefix('session'), $sess_id, time(), $HTTP_SERVER_VARS['REMOTE_ADDR'], $sess_data); } if (!$this->db->queryF($sql)) { return false; } return true; }
/** * Write a session to the database * * @param string $sess_id * @param string $sess_data * * @return bool **/ function write($sess_id, $sess_data) { $sess_id = $this->db->quoteString($sess_id); list($count) = $this->db->fetchRow($this->db->query("SELECT COUNT(*) FROM " . $this->db->prefix('session') . " WHERE sess_id=" . $sess_id)); if ($count > 0) { $sql = sprintf('UPDATE %s SET sess_updated = %u, sess_data = %s WHERE sess_id = %s', $this->db->prefix('session'), time(), $this->db->quoteString($sess_data), $sess_id); } else { $sql = sprintf('INSERT INTO %s (sess_id, sess_updated, sess_ip, sess_data) VALUES (%s, %u, %s, %s)', $this->db->prefix('session'), $sess_id, time(), $this->db->quoteString($_SERVER['REMOTE_ADDR']), $this->db->quoteString($sess_data)); } if (!$this->db->queryF($sql)) { return false; } return true; }
/** * Read a session from the database * * @param string &sess_id ID of the session * * @return array Session data */ function read($sess_id) { $sql = sprintf('SELECT sess_data, sess_ip FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id)); if (false != ($result = $this->db->query($sql))) { if (list($sess_data, $sess_ip) = $this->db->fetchRow($result)) { if ($this->securityLevel > 1) { $pos = strpos($sess_ip, ".", $this->securityLevel - 1); if (strncmp($sess_ip, $_SERVER['REMOTE_ADDR'], $pos)) { $sess_data = ''; } } return $sess_data; } } return ''; }
/** * Read a session from the database * * @param string $sess_id ID of the session * * @return array Session data */ public function read($sess_id) { $ip = \Xmf\IPAddress::fromRequest(); $sql = sprintf('SELECT sess_data, sess_ip FROM %s WHERE sess_id = %s', $this->db->prefix('session'), $this->db->quoteString($sess_id)); // if (false != $result = $this->db->query($sql)) { $result = $this->db->query($sql); if (!empty($result)) { if (list($sess_data, $sess_ip) = $this->db->fetchRow($result)) { if ($this->securityLevel > 1) { if (false === $ip->sameSubnet($sess_ip, $this->bitMasks[$this->securityLevel]['v4'], $this->bitMasks[$this->securityLevel]['v6'])) { $sess_data = ''; } } return $sess_data; } } return ''; }
/** * Inserts or updates data row(s) * * Checking is performed what columns should be updated. * If field is NULLABLE, contains some value and should be set to NULL, * then the String 'null' must be sent in order to set NULL in database * * Method skips the excessive columns in the $data array * * @param object $resource Resource object that corresponds to the table to be inserted into or updated * @param array $data Data array containing new values * @param string|array $where Where statement(s) to indicate what row(s) will be effected (optional) * @param boolean $enforceInsert During update, if row is not found an insert will be made * @param boolean $returnCleanDataOnly Doesn't save the data, but cleans and returns it * @return boolean True on successful operation and false on failure */ public static function save($resource, $data, $where = null, $enforceInsert = false, $returnCleanDataOnly = false) { $info = $resource->info(); $row = $where != null ? $resource->fetchRow($where) : null; $dataClean = array(); foreach ($info['cols'] as $col) { if ($info['metadata'][$col]['NULLABLE'] == 1 && $data[$col] == 'null') { $dataClean[$col] = new Zend_Db_Expr('NULL'); } else { if (isset($data[$col])) { $dataClean[$col] = $data[$col]; // For changed fields that cannot be NULL } else { if ($row != null) { $dataClean[$col] = $row[$col]; // For unchanged fields (avoiding nulling the "good" data) } } } } // Good for debugging /* if ($resourceName == 'SomeResourceName'){ Zend_Debug::dump($info); echo "<br />"; print_r($row->toArray()); echo "<br />"; print_r($dataClean); die; } */ if ($returnCleanDataOnly) { return $dataClean; } // do not insert, if $where was specified but $row was not found if (!($where != null && $row == null) || $enforceInsert) { return $resource->saveRow($dataClean, $row); } }
/** * Creates JSON ( http://www.json.org/ ) from the ADODB record set * * @param object $rs - record set object * @param bool $moveFirst - determines whether recordset is returned to first record * @return string $output - resulting json string * @version V1.0 10 June 2006 (c) 2006 Rich Zygler ( http://www.boringguys.com/ ). All rights reserved. * * Released under both BSD license and Lesser GPL library license. You can choose which license * you prefer. Example output from query "SELECT Name, Continent From Country LIMIT 10;" {"rows":[ {"row":{"Name":"Afghanistan","Continent":"Asia"}}, {"row":{"Name":"Netherlands","Continent":"Europe"}}, {"row":{"Name":"Netherlands Antilles","Continent":"North America"}}, {"row":{"Name":"Albania","Continent":"Europe"}}, {"row":{"Name":"Algeria","Continent":"Africa"}}, {"row":{"Name":"American Samoa","Continent":"Oceania"}}, {"row":{"Name":"Andorra","Continent":"Europe"}}, {"row":{"Name":"Angola","Continent":"Africa"}}, {"row":{"Name":"Anguilla","Continent":"North America"}}, {"row":{"Name":"Antigua and Barbuda","Continent":"North America"}} ]} */ function rs2json($rs, $moveFirst = false) { if (!$rs) { printf(ADODB_BAD_RS, 'rs2json'); return false; } $output = ''; $rowOutput = ''; $output .= '{"rows":'; $totalRows = $rs->numrows(); if ($totalRows > 0) { $output .= '['; $rowCounter = 1; while ($row = $rs->fetchRow()) { $rowOutput .= '{"row":{'; $cols = count($row); $colCounter = 1; foreach ($row as $key => $val) { $rowOutput .= '"' . $key . '":'; $rowOutput .= '"' . $val . '"'; if ($colCounter != $cols) { $rowOutput .= ','; } $colCounter++; } $rowOutput .= '}}'; if ($rowCounter != $totalRows) { $rowOutput .= ','; } $rowCounter++; } $output .= $rowOutput . ']'; } else { $output .= '"row"'; } $output .= '}'; if ($moveFirst) { $rs->MoveFirst(); } return $output; }
/** * Iterator next method * @return boolean TRUE if there are more results to fetch * @access public */ function next() { if (!isset($this->QueryId)) { return FALSE; } $this->properties = $this->QueryId->fetchRow(MDB2_FETCHMODE_ASSOC); if (is_array($this->properties)) { $this->prepare(); return TRUE; } else { $this->freeQuery(); return FALSE; } }
/** * Get information about a piece of railcam footage from the database * @since Version 3.10.0 * @param int $id * @return \Railpage\Railcams\Footage */ public function getFootage($id = null) { $query = "SELECT * FROM railcam_footage WHERE id = ?"; return $this->db->fetchRow($query, $id); }
/** * Run some coreDatabaseTable tests (ORM) * * @param object $db * @param object $test */ function ormTests($db, $test) { // Testing method access to the table peer attributes TestOrmPeer::exampleMethod($test); // Testing table data access through the peer class: echo "<h2>Select</h2>"; //$db->setFetchMode(coreDatabase::FETCH_NUM); $rows = $db->fetchAll(TestOrmPeer::select()); $db->dumpResultSet($rows); echo "<h2>Count</h2>"; $test->out(TestOrmPeer::count('id > ?', 24), "count('id > 24')"); echo "<h2>Insert</h2>"; TestOrmPeer::insert(array('firstname' => 'Superman', 'age' => 28)); // show new row $id = $db->lastInsertId(); echo '<p> Inserted new row with id: <b>' . $id . '</b>:'; $rows = $db->fetchRow(TestOrmPeer::select()->where('id = ?', $id)); $db->dumpResultSet($rows); echo "<h2>Update</h2>"; // delay 1 second to see the UPDATED_ON changes sleep(1); TestOrmPeer::update(array('firstname' => 'She-Ra', 'age' => 31), 'id = ?', $id); // show update results echo '<p> Updated row with id: <b>' . $id . '</b> (1 second delayed):'; $rows = $db->fetchRow(TestOrmPeer::select()->where('id = ?', $id)); $db->dumpResultSet($rows); echo "<h2>Delete</h2>"; echo '<p> Deleted row with id: <b>' . $id . '</b>.'; TestOrmPeer::delete('id >= ?', 27); }
/** * checkUserInDB() * Builds select query to check user in DB and returns result as an array * * @return array|false */ private function checkUserInDB() { $query = new Query(); $query->select()->from($this->table)->where($this->identityColumn . " = ?", $this->identity)->andWhere($this->credentialColumn . " = ?", $this->credential)->limit(1); return $this->dbAdapter->fetchRow($query); }
/** * Function to get a query from DB * * @param object $db Reference to the database object * @param string $table The table to get the value from * @param string $field The table to get the value from * @param string $condition The where condition (where clause) to use * @return mixed * @todo Move to a static class method - database */ function getDbValue(&$db, $table, $field, $condition = '') { $table = $db->prefix($table); $sql = "SELECT `{$field}` FROM `{$table}`"; if ($condition) { $sql .= " WHERE {$condition}"; } $result = $db->query($sql); if ($result) { $row = $db->fetchRow($result); if ($row) { return $row[0]; } } return false; }
/** * 获取查询信息中的一行数据 * * 注:本函数(类方法)需与query()组合使用。 * * @access public * * @param string $model 返回数据的索引类型:字段型/数据型 等。默认:字段型 * * @return array */ public function fetchRow($model = 'PDO::FETCH_ASSOC') { return $this->_dbLink->fetchRow($model); }
/** * Get count of clips for the admin index page * * @param string $languagesel specific language * * @return int count of clips */ function &getClipsCountFromAdmin($languagesel) { global $xoopsConfig, $xoopsDB; $smartConfig =& smartmedia_getModuleConfig(); if ($languagesel == 'all') { $where = ""; } else { $where = "WHERE clips_text.languageid = '" . $languagesel . "'"; } $sql = "SELECT COUNT(DISTINCT clips.clipid)\r\n\t\t\t\t\tFROM (\r\n\t\t\t\t\t " . $xoopsDB->prefix('smartmedia_clips') . " AS clips\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_clips_text') . " AS clips_text ON clips.clipid = clips_text.clipid\r\n\t\t\t\t\t\t)\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders') . " AS folders ON clips.folderid=folders.folderid\r\n \t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders_text') . " AS folders_text ON folders.folderid = folders_text.folderid\r\n\t\t\t\t\tINNER JOIN " . $this->_db->prefix('smartmedia_folders_categories') . " AS categories\r\n\t\t\t\t\tON folders.folderid = categories.folderid\r\n\t\t\t\t \r\n " . $where; //echo "<br />$sql<br />"; if (!($result =& $this->_db->query($sql))) { return 0; } list($count) = $this->_db->fetchRow($result); return $count; }
/** * verify that chosen transactional table hanlder is available in the database * * @param object $db database object that is extended by this class * @param string $table_type name of the table handler * @return mixed MDB_OK on success, a MDB error on failure * @access private */ function _verifyTransactionalTableType(&$db, $table_type) { switch (strtoupper($table_type)) { case 'BERKELEYDB': case 'BDB': $check = array('have_bdb'); break; case 'INNODB': $check = array('have_innobase', 'have_innodb'); break; case 'GEMINI': $check = array('have_gemini'); break; case 'HEAP': case 'ISAM': case 'MERGE': case 'MRG_MYISAM': case 'MYISAM': case '': return MDB_OK; default: return $db->raiseError(MDB_ERROR_UNSUPPORTED, NULL, NULL, 'Verify transactional table', $table_type . ' is not a supported table type'); } if (isset($this->verified_table_types[$table_type]) && $this->verified_table_types[$table_type] == $db->connection) { return MDB_OK; } $not_supported = false; for ($i = 0, $j = count($check); $i < $j; ++$i) { $result = $db->query('SHOW VARIABLES LIKE ' . $db->getValue('text', $check[$i]), null, false); if (MDB::isError($result)) { return $db->mysqlRaiseError(); } $has = $db->fetchRow($result, MDB_FETCHMODE_ORDERED); if (MDB::isError($has)) { return $has; } if (is_array($has)) { $not_supported = true; if ($has[1] == 'YES') { $this->verified_table_types[$table_type] = $db->connection; return MDB_OK; } } } if ($not_supported) { return $db->raiseError(MDB_ERROR_UNSUPPORTED, null, null, $table_type . ' is not a supported table type by this MySQL database server'); } return $db->raiseError(MDB_ERROR_UNSUPPORTED, null, null, 'could not tell if ' . $table_type . ' is a supported table type'); }
/** * checkUserInDB() * Builds select query to check user in DB and returns result as an array * @access private * @return array|false */ private function checkUserInDB() { return $this->dbAdapter->fetchRow("SELECT * FROM `" . $this->table . "`\n WHERE `" . $this->identityColumn . "` = '" . $this->identity . "'\n AND `" . $this->credentialColumn . "` = '" . $this->credential . "'\n LIMIT 1"); }
/** * 获取查询结果行数 * * @param object $criteria 查询类 * @access public * @return int 结果行数 */ function getCount($criteria = NULL) { if (is_object($criteria)) { if ($criteria->getGroupby() == '') { $sql = 'SELECT COUNT(*) FROM ' . $criteria->getTables() . ' ' . $criteria->renderWhere(); $nobuffer = true; } else { $sql = 'SELECT COUNT(' . $criteria->getGroupby() . ') FROM ' . $criteria->getTables() . ' ' . $criteria->renderWhere() . ' GROUP BY ' . $criteria->getGroupby(); $nobuffer = false; } $result = $this->db->query($sql, 0, 0, $nobuffer); if (!$result) { return 0; } if ($criteria->getGroupby() == '') { list($count) = $this->db->fetchRow($result); } else { $count = $this->db->getRowsNum($result); } return $count; } return 0; }
/** * モジュールIDをキーとしたモジュールデータ配列を作成する。 * * @param object $recordSet モジュールデータADORecordSetオブジェクト * @return string 指定文字区切りの文字列 * @access private */ function &_fetchModule(&$recordSet) { $modules = array(); while ($module = $recordSet->fetchRow()) { $moduleId = $module['module_id']; $modules[$moduleId] = $module; } return $modules; }