PHP ApiBase::doQuery方法代码示例

 function run($constraints)
 {
     #set limit
     list($limit, $w) = ApiBase::setLimit($_GET['limit']);
     $warning = isset($w) ? $w : null;
     #set offset
     list($offset, $w) = ApiBase::setOffset($_GET['offset']);
     $warning = isset($w) ? $warning . $w : $warning;
     #set view
     list($target_table, $w) = self::setView($_GET['view']);
     $warning = isset($w) ? $warning . $w : $warning;
     #load list of parameters to select
     list($select, $w) = self::setSelect($_GET['show']);
     $warning = isset($w) ? $warning . $w : $warning;
     #load constraints
     #isset($_GET['muni']) ? $constraints['muni'] = $_GET['muni'] : null;
     #isset($_GET['county']) ? $constraints['county'] = $_GET['county'] : null;
     #construct query
     $query = '
             SELECT SQL_CALC_FOUND_ROWS ' . $select . '
             FROM `' . mysql_real_escape_string($target_table) . '`
             ';
     $query = isset($constraints) ? ApiBase::addConstraints($query . 'WHERE ', $constraints) : $query;
     $query .= 'LIMIT ' . mysql_real_escape_string($offset) . ', ' . mysql_real_escape_string($limit) . '
             ';
     #run query
     try {
         $response = ApiBase::doQuery($query);
         $hits = ApiBase::doQuery('SELECT FOUND_ROWS()');
     } catch (Exception $e) {
         return ApiBase::makeErrorResult('610', 'Select Failed. ' . 'Probably wrong name supplied.[' . $e->getMessage() . ']', $warning);
     }
     $body = array();
     foreach ($response as $r) {
         array_push($body, array('hit' => ApiBase::sanitizeBit1($r)));
     }
     # so that xml plays nice
     #Did we get all?
     $hits = $hits[0]['FOUND_ROWS()'];
     $head = array('hits' => $offset . '–' . ($offset + count($body)) . ' of ' . $hits, 'limit' => $limit);
     if ($hits > $offset + $limit) {
         $head['continue'] = $offset + $limit;
     }
     if (!empty($warning)) {
         $head['warning'] = $warning;
     }
     //~ $head = Array(
     //~ 'hits' => $hits,
     //~ 'limit' => $limit,
     //~ 'warning' => $warning
     //~ );
     $results = ApiBase::makeSuccessResultHead($head, $body);
     return $results;
 }

 private function splitBy($sp_table, $table, $column, $constraints)
 {
     $num_label_sql = isset($column) ? mysql_real_escape_string($column) : $table;
     $sp_table_sql = mysql_real_escape_string($sp_table) . '_table';
     $label_sql = mysql_real_escape_string($sp_table) . '_name';
     $real_table_sql = mysql_real_escape_string($table) . '_table';
     $query = '
             SELECT `' . $sp_table_sql . '`.`name` AS `' . $label_sql . '`, COUNT(*) AS `' . $num_label_sql . '`
             FROM `' . $real_table_sql . '`, `' . $sp_table_sql . '`
             WHERE `' . $real_table_sql . '`.`' . mysql_real_escape_string($sp_table) . '` = `' . $sp_table_sql . '`.`id`
             ';
     $query = isset($column) ? ApiBase::notEmpty($query . 'AND ', $column) : $query;
     $query = isset($constraints) ? ApiBase::addConstraints($query . 'AND ', $constraints) : $query;
     $query .= 'GROUP BY `' . $label_sql . '`
             ORDER BY `' . $label_sql . '` ASC
             ';
     try {
         $response = ApiBase::doQuery($query);
     } catch (Exception $e) {
         throw $e;
     }
     foreach ($response as $r) {
         $mod_resp[] = array($sp_table => $r);
     }
     return $mod_resp;
 }

 private function getYearlessArtist($limit, $offset)
 {
     $query = '
             SELECT SQL_CALC_FOUND_ROWS id, CONCAT_WS(" ", first_name, last_name) AS name, `birth_year`, `death_year`
             FROM `artist_table`
             WHERE `death_year` IS NULL
             AND (
                     `birth_year` IS NULL
                  OR (
                         `birth_year` IS NOT NULL
                     AND birth_year+ 100 < YEAR(CURRENT_TIMESTAMP)
                     )
                 )
             ';
     #run query
     try {
         $response = ApiBase::doQuery($query);
         $hits = ApiBase::doQuery('SELECT FOUND_ROWS()');
     } catch (Exception $e) {
         throw $e;
     }
     foreach ($response as $r) {
         $body[] = array('hit' => $r);
     }
     return array($body, $hits);
 }

 private function namedParam($table, $value)
 {
     $vArray = explode('|', $value);
     #remove any empty parameters
     $i = 0;
     foreach ($vArray as $v) {
         if (empty($v)) {
             unset($vArray[$i]);
         }
         $i++;
     }
     if (!empty($vArray)) {
         #since all elements may have been removed
         $query = 'SELECT `id`, `name`
         FROM `' . mysql_real_escape_string($table) . '_table`
         WHERE `name` IN ("' . implode('" , "', array_map('mysql_real_escape_string', $vArray)) . '")
         ';
         try {
             $response = ApiBase::doQuery($query);
         } catch (Exception $e) {
             throw $e;
         }
         foreach ($response as $r) {
             $ids[] = $r['id'];
         }
         return implode('|', $ids);
     }
 }

 function run($constraints)
 {
     #either look up on artwork_id or one of the others
     $prefix = null;
     $w = null;
     $otherSelectors = array('wiki', 'id', 'first_name', 'last_name', 'name', 'birth_year', 'death_year', 'is_dead', 'lifespan');
     if (isset($_GET['artwork'])) {
         $prefix = 'at';
         if (count(array_intersect($otherSelectors, array_keys($_GET))) > 0) {
             # if any of $otherSelectors were provided
             $w = 'The artwork parameter cannot be combined with any other selectors, these are therefore disregarded. ';
         }
     }
     $warning = isset($w) ? $w : null;
     #set limit
     list($limit, $w) = ApiBase::setLimit($_GET['limit']);
     $warning = isset($w) ? $warning . $w : $warning;
     #set offset
     list($offset, $w) = ApiBase::setOffset($_GET['offset']);
     $warning = isset($w) ? $warning . $w : $warning;
     #load list of parameters to select
     list($select, $getWorks, $w) = self::setSelect($_GET['show'], $prefix);
     $warning = isset($w) ? $warning . $w : $warning;
     #Needs support for
     # name, first_name, last_name as well (with name=Concatenate(first, ' ', last))
     # as a constraint
     # Look up on artwork_id or other
     $query = null;
     if (isset($_GET['artwork'])) {
         $query = '
             SELECT SQL_CALC_FOUND_ROWS ' . $select . '
             FROM `artist_table` at
             INNER JOIN `artist_links` al ON al.`artist` = at.`id`
             WHERE al.`object` in (
             "' . implode('", "', array_map('mysql_real_escape_string', explode('|', $_GET['artwork']))) . '"
             )
             ';
     } else {
         #add available constraints
         $query = '
             SELECT SQL_CALC_FOUND_ROWS ' . $select . '
             FROM `artist_table`
             ';
         $query = isset($constraints) ? ApiBase::addConstraints($query . 'WHERE ', $constraints) : $query;
     }
     # add limit
     $query .= 'LIMIT ' . mysql_real_escape_string($offset) . ', ' . mysql_real_escape_string($limit) . '
             ';
     #run query
     try {
         $response = ApiBase::doQuery($query);
         $hits = ApiBase::doQuery('SELECT FOUND_ROWS()');
     } catch (Exception $e) {
         return ApiBase::makeErrorResult('610', 'Select Failed. ' . 'Probably wrong name supplied.[' . $e->getMessage() . ']', $warning);
     }
     # look up works for each artist
     $works = null;
     if ($getWorks) {
         $artists = array();
         foreach ($response as $r) {
             array_push($artists, $r['id']);
         }
         list($works, $w) = self::getWorks($artists, $warning);
         $warning = isset($w) ? $warning . $w : $warning;
     }
     #collect results
     $body = array();
     foreach ($response as $r) {
         if ($getWorks) {
             $r['works'] = array();
             if (isset($works[$r['id']])) {
                 foreach ($works[$r['id']] as $work) {
                     array_push($r['works'], array('work' => $work));
                     # so that xml plays nice
                 }
             }
         }
         array_push($body, array('hit' => ApiBase::sanitizeBit1($r)));
         # so that xml plays nice
     }
     #Did we get all?
     $hits = $hits[0]['FOUND_ROWS()'];
     $head = array('hits' => $offset . '–' . ($offset + count($body)) . ' of ' . $hits, 'limit' => $limit);
     if ($hits > $offset + $limit) {
         $head['continue'] = $offset + $limit;
     }
     if (!empty($warning)) {
         $head['warning'] = $warning;
     }
     $results = ApiBase::makeSuccessResultHead($head, $body);
     return $results;
 }