PHP DBManager::query方法代码示例

 /**
  * Get all beans from link
  * @see Link2::query()
  */
 public function query($params)
 {
     unset($params['return_as_array']);
     $query = $this->getQuery($params);
     $result = $this->db->query($query);
     $rows = array();
     while ($row = $this->db->fetchByAssoc($result, false)) {
         $rows[$row['id']] = $row;
     }
     return array("rows" => $rows);
 }

 /**
  * @param string $table_name
  * @param array $join_key_values
  *
  * @return bool
  */
 function relationship_exists($table_name, $join_key_values)
 {
     // find the key values for the table.
     $dup_keys = $this->_get_alternate_key_fields($table_name);
     if (empty($dup_keys)) {
         Log::debug("No alternate key define, skipping duplicate check..");
         return false;
     }
     $delimiter = '';
     $this->_duplicate_where = ' WHERE ';
     foreach ($dup_keys as $field) {
         //look for key in  $join_key_values, if found add to filter criteria else abort duplicate checking.
         if (isset($join_key_values[$field])) {
             $this->_duplicate_where .= $delimiter . ' ' . $field . "='" . $join_key_values[$field] . "'";
             $delimiter = 'AND';
         } else {
             Log::error('Duplicate checking aborted, Please supply a value for this column ' . $field);
             return false;
         }
     }
     //add deleted check.
     $this->_duplicate_where .= $delimiter . ' deleted=0';
     $query = 'SELECT id FROM ' . $table_name . $this->_duplicate_where;
     Log::debug("relationship_exists query(" . $query . ')');
     $result = $this->_db->query($query, true);
     $row = $this->_db->fetchByAssoc($result);
     if ($row == null) {
         return false;
     } else {
         $this->_duplicate_key = $row['id'];
         return true;
     }
 }

 /**
  * Fetch the next job in the queue and mark it running
  * @param string $clientID ID of the client requesting the job
  * @return SugarJob
  */
 public function nextJob($clientID)
 {
     $now = $this->db->now();
     $queued = SchedulersJob::JOB_STATUS_QUEUED;
     $try = $this->jobTries;
     while ($try--) {
         // TODO: tranaction start?
         $id = $this->db->getOne("SELECT id FROM {$this->job_queue_table} WHERE execute_time <= {$now} AND status = '{$queued}' ORDER BY date_entered ASC");
         if (empty($id)) {
             return null;
         }
         $job = new SchedulersJob();
         $job->retrieve($id);
         if (empty($job->id)) {
             return null;
         }
         $job->status = SchedulersJob::JOB_STATUS_RUNNING;
         $job->client = $clientID;
         $client = $this->db->quote($clientID);
         // using direct query here to be able to fetch affected count
         // if count is 0 this means somebody changed the job status and we have to try again
         $res = $this->db->query("UPDATE {$this->job_queue_table} SET status='{$job->status}', date_modified={$now}, client='{$client}' WHERE id='{$job->id}' AND status='{$queued}'");
         if ($this->db->getAffectedRowCount($res) == 0) {
             // somebody stole our job, try again
             continue;
         } else {
             // to update dates & possible hooks
             $job->save();
             break;
         }
         // TODO: commit/check?
     }
     return $job;
 }

 /**
  * Returns report schedule properties
  *
  * @param string $id Report schedule ID
  *
  * @return array
  */
 public function getInfo($id)
 {
     $query = "SELECT report_id, next_run, time_interval\n        FROM {$this->table_name}\n        WHERE id = " . $this->db->quoted($id);
     $result = $this->db->query($query);
     $row = $this->db->fetchByAssoc($result);
     $row = $this->fromConvertReportScheduleDBRow($row);
     return $row;
 }

 /**
  * Remove list of ids from fts_queue table
  * @param array $deleteIDs
  */
 private function delFtsIDs($deleteIDs)
 {
     $tableName = self::QUEUE_TABLE;
     $inClause = implode("','", $deleteIDs);
     $query = "DELETE FROM {$tableName} WHERE id in ('{$inClause}')";
     $GLOBALS['log']->debug("DELETE BEAN QUERY IS: {$query}");
     $this->db->query($query);
 }

 /**
  * Run the query and return the db result object
  * @return db result object
  */
 protected function runQuery()
 {
     $sql = $this->compileSql();
     if ($this->usePreparedStatements) {
         return $this->db->preparedQuery($sql, $this->data);
     } else {
         return $this->db->query($sql);
     }
 }

 /**
  * doUpdateUsDollarRate
  *
  * execute the standard sql query for updating rates.
  * to use a specific query, override doCustomUpdateUsDollarRate()
  * in your extended class and make your own.
  *
  * @access protected
  * @param  string    $tableName
  * @param  string    $usDollarColumn
  * @param  string    $amountColumn
  * @param  string    $currencyId
  * @return boolean true on success
  */
 protected function doUpdateUsDollarRate($tableName, $usDollarColumn, $amountColumn, $currencyId)
 {
     // setup SQL statement
     $query = sprintf("UPDATE %s SET %s = %s / base_rate where currency_id = '%s'", $tableName, $usDollarColumn, $amountColumn, $currencyId);
     // execute
     $result = $this->db->query($query, true, string_format($GLOBALS['app_strings']['ERR_DB_QUERY'], array('CurrencyRateUpdate', $query)));
     if (empty($result)) {
         return false;
     }
     return true;
 }

 /**
  * @ticket 34892
  */
 public function test_Bug34892_MssqlNotClearingErrorResults()
 {
     // execute a bad query
     $this->_db->query("select dsdsdsdsdsdsdsdsdsd", false, "test_Bug34892_MssqlNotClearingErrorResults", true);
     // assert it found an error
     $this->assertNotEmpty($this->_db->lastError(), "lastError should return true as a result of the previous illegal query");
     // now, execute a good query
     $this->_db->query("select * from config");
     // and make no error messages are asserted
     $this->assertEmpty($this->_db->lastError(), "lastError should have cleared the previous error and return false of the last legal query");
 }

 function retrieve_relationships($table, $values, $select_id)
 {
     $query = "SELECT {$select_id} FROM {$table} WHERE deleted = 0  ";
     foreach ($values as $name => $value) {
         $query .= " AND {$name} = '{$value}' ";
     }
     $query .= " ORDER BY {$select_id} ";
     $result = $this->db->query($query, false, "Retrieving Relationship:" . $query);
     $ids = array();
     while ($row = $this->db->fetchByAssoc($result)) {
         $ids[] = $row;
     }
     return $ids;
 }

 public static function storeCrashReport($email, $crash_report)
 {
     try {
         $crashTable = DBManager::$mysql_crash_table;
         $sql = "INSERT INTO {$crashTable} (`EMAIL`, `CRASH_REPORT`) VALUES ('{$email}','{$crash_report}') ON DUPLICATE KEY UPDATE EMAIL = VALUES(EMAIL) , CRASH_REPORT = VALUES (CRASH_REPORT)";
         $result = DBManager::query($sql);
         if (DBManager::$local_debug) {
             echo $result;
         }
     } catch (Exception $e) {
         if (DBManager::$local_debug) {
             echo "Got exception";
         }
     }
 }

 /**
  * @depends testAddPrimaryKey
  */
 public function testRemovePrimaryKey()
 {
     $tablename = 'testConstraints';
     $this->created[$tablename] = true;
     $sql = $this->_db->add_drop_constraint($tablename, array('name' => 'testConstraints_pk', 'type' => 'primary', 'fields' => array('id')), true);
     $result = $this->_db->query($sql);
     $indices = $this->_db->get_indices($tablename);
     // find if any are primary
     $found = false;
     foreach ($indices as $index) {
         if ($index['type'] == "primary") {
             $found = true;
             break;
         }
     }
     $this->assertFalse($found, 'Primary Key Found On Table');
 }

 /**
  * DEPRECATED.
  */
 public function retrieve()
 {
     if (!isset($this->bean)) {
         $GLOBALS['log']->fatal('DynamicField retrieve, bean not instantiated');
         return false;
     }
     if (!$this->bean->hasCustomFields()) {
         return false;
     }
     $query = 'SELECT * FROM ' . $this->bean->table_name . "_cstm WHERE id_c='" . $this->bean->id . "'";
     $result = $this->db->query($query);
     $row = $this->db->fetchByAssoc($result);
     if ($row) {
         foreach ($row as $name => $value) {
             // originally in pre-r30895 we checked if this field was in avail_fields i.e., in fields_meta_data and not deleted
             // with the removal of avail_fields post-r30895 we have simplified this - we now retrieve every custom field even if previously deleted
             // this is considered harmless as the value although set in the bean will not otherwise be used (nothing else works off the list of fields in the bean)
             $this->bean->{$name} = $value;
         }
     }
     return true;
 }

     $messages = array();
     
     while($row = $dbManager->fetch_assoc($result)) {
         array_push($messages, $row);
     }
     
     echo json_encode($messages);
     
 });
 
 $app->get('/classes/:className/where/jsonString', function ( $className ) {
    
    
     $dbManager = new DBManager();
     
     $result = $dbManager->query("SELECT * FROM ".$className);
     $messages = array();
     
     while($row = $dbManager->fetch_assoc($result)) {
         array_push($messages, $row);
     }
     
     echo json_encode($messages);
     
 });
 
 $app->run();
 
 
 
 

 /**
  * @param string    $lhs_module
  * @param string    $rhs_module
  * @param DBManager $db
  * @param string    $type
  *
  * @return array|null
  */
 function retrieve_by_modules($lhs_module, $rhs_module, &$db, $type = '')
 {
     //give it the relationship_name and base module
     //it will return the module name on the other side of the relationship
     $query = "\tSELECT * FROM relationships\n\t\t\t\t\tWHERE deleted=0\n\t\t\t\t\tAND (\n\t\t\t\t\t(lhs_module = '" . $lhs_module . "' AND rhs_module = '" . $rhs_module . "')\n\t\t\t\t\tOR\n\t\t\t\t\t(lhs_module = '" . $rhs_module . "' AND rhs_module = '" . $lhs_module . "')\n\t\t\t\t\t)\n\t\t\t\t\t";
     if (!empty($type)) {
         $query .= " AND relationship_type='{$type}'";
     }
     $result = $db->query($query, true, " Error searching relationships table..");
     $row = $db->fetchByAssoc($result);
     if ($row != null) {
         return $row['relationship_name'];
     }
     return null;
 }

 /**
  * @param $metadata
  * @param $module
  * @return bool|resource
  */
 public function updateDashboardsMetadata($metadata, $module)
 {
     return $this->dbManager->query(sprintf("\n                UPDATE dashboards SET\n                    metadata = '%s'\n                WHERE\n                    deleted = 0\n                    AND name = 'LBL_DEFAULT_DASHBOARD_TITLE'\n                    AND dashboard_module = '{$module}'\n                    AND view_name = 'record'\n                    AND dashboard_type = 'dashboard'\n                ", $this->dbManager->quote(json_encode($metadata))));
 }