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 getChanges($limit, $offset, $constraints) { #list of columns available in the audit_table $cols = array('id', 'title', 'artist', 'descr', 'year', 'year_cmt', 'type', 'material', 'inside', 'address', 'county', 'muni', 'district', 'lat', 'lon', 'source', 'official_url', 'owner', 'cmt', 'created'); #prepare sql statement $mainCols = '`main_table`.`' . implode(', `main_table`.`', array_map('self::colAsmPrefix', $cols)); $auditCols = '`audit_table`.`' . implode(', `audit_table`.`', array_map('self::colAsaPrefix', $cols)); $query = ' SELECT SQL_CALC_FOUND_ROWS ' . $mainCols . ', ' . $auditCols . ' FROM `main_table`, `audit_table` WHERE `main_table`.`ugc` = 1 AND `main_table`.`id` = `audit_table`.`id` '; $query = isset($constraints) ? ApiBase::addConstraints($query . 'AND `main_table`.', $constraints, '`main_table`.') : $query; $query .= 'LIMIT ' . mysql_real_escape_string($offset) . ', ' . mysql_real_escape_string($limit) . ' '; #run query $error = false; try { $response = ApiBase::doQuery($query); $hits = ApiBase::doQuery('SELECT FOUND_ROWS()'); } catch (Exception $e) { throw $e; $error = true; } if (!$error) { #go through each row of response and compare audit to main $body = array(); foreach ($response as $r) { $r = ApiBase::sanitizeBit1diff($r); $diff = array(); $diff['id'] = $r['m_id']; foreach ($cols as $c) { if ($r['m_' . $c] != $r['a_' . $c]) { $diff[$c] = array('new' => $r['m_' . $c], 'old' => $r['a_' . $c]); } } if (count($diff) > 1) { #so as to remove any entries with only an id (i.e. only image added) $body[] = array('diff' => $diff); } } return array($body, $hits); } }
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; }
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; }