PHP ParlDB::query方法代码示例

function _api_getMembers_output($sql, $params)
{
    global $parties;
    $db = new ParlDB();
    $q = $db->query($sql, $params);
    $output = array();
    $last_mod = 0;
    for ($i = 0; $i < $q->rows(); $i++) {
        $pid = $q->field($i, 'person_id');
        $row = array('member_id' => $q->field($i, 'member_id'), 'person_id' => $pid, 'name' => html_entity_decode(member_full_name($q->field($i, 'house'), $q->field($i, 'title'), $q->field($i, 'given_name'), $q->field($i, 'family_name'), $q->field($i, 'lordofname'))), 'party' => isset($parties[$q->field($i, 'party')]) ? $parties[$q->field($i, 'party')] : $q->field($i, 'party'));
        if ($q->field($i, 'house') != 2) {
            $row['constituency'] = $q->field($i, 'constituency');
        }
        $output[$pid] = $row;
        $time = strtotime($q->field($i, 'lastupdate'));
        if ($time > $last_mod) {
            $last_mod = $time;
        }
    }
    $pids = array_keys($output);
    if (count($pids)) {
        $q = $db->query('SELECT person, dept, position, from_date, to_date FROM moffice
            WHERE to_date="9999-12-31" AND person IN (' . join(',', $pids) . ')');
        for ($i = 0; $i < $q->rows(); $i++) {
            $row = $q->row($i);
            $pid = $row['person'];
            unset($row['person']);
            $output[$pid]['office'][] = $row;
        }
    }
    $output = array_values($output);
    api_output($output, $last_mod);
}

function api_getMPsInfo_id($ids)
{
    $fields = preg_split('#\\s*,\\s*#', get_http_var('fields'), -1, PREG_SPLIT_NO_EMPTY);
    $ids = preg_split('#\\s*,\\s*#', $ids, -1, PREG_SPLIT_NO_EMPTY);
    $safe_ids = array(0);
    foreach ($ids as $id) {
        if (ctype_digit($id)) {
            $safe_ids[] = $id;
        }
    }
    $ids = join(',', $safe_ids);
    $db = new ParlDB();
    $last_mod = 0;
    $q = $db->query("select person_id, data_key, data_value, lastupdate from personinfo\n\t\twhere person_id in (" . $ids . ")");
    if ($q->rows()) {
        $output = array();
        for ($i = 0; $i < $q->rows(); $i++) {
            $data_key = $q->field($i, 'data_key');
            if (count($fields) && !in_array($data_key, $fields)) {
                continue;
            }
            $pid = $q->field($i, 'person_id');
            $output[$pid][$data_key] = $q->field($i, 'data_value');
            $time = strtotime($q->field($i, 'lastupdate'));
            if ($time > $last_mod) {
                $last_mod = $time;
            }
        }
        $q = $db->query("select memberinfo.*, person_id from memberinfo, member\n\t\t\twhere memberinfo.member_id=member.member_id and person_id in (" . $ids . ")\n\t\t\torder by person_id,member_id");
        if ($q->rows()) {
            $oldmid = 0;
            $count = -1;
            for ($i = 0; $i < $q->rows(); $i++) {
                $data_key = $q->field($i, 'data_key');
                if (count($fields) && !in_array($data_key, $fields)) {
                    continue;
                }
                $mid = $q->field($i, 'member_id');
                $pid = $q->field($i, 'person_id');
                if (!isset($output[$pid]['by_member_id'])) {
                    $output[$pid]['by_member_id'] = array();
                }
                if ($oldmid != $mid) {
                    $count++;
                    $oldmid = $mid;
                    $output[$pid]['by_member_id'][$count]['member_id'] = $mid;
                }
                $output[$pid]['by_member_id'][$count][$data_key] = $q->field($i, 'data_value');
                $time = strtotime($q->field($i, 'lastupdate'));
                if ($time > $last_mod) {
                    $last_mod = $time;
                }
            }
        }
        ksort($output);
        api_output($output, $last_mod);
    } else {
        api_error('Unknown person ID');
    }
}

function api_getMPinfo_id($id)
{
    $fields = preg_split('#\\s*,\\s*#', get_http_var('fields'), -1, PREG_SPLIT_NO_EMPTY);
    $db = new ParlDB();
    $last_mod = 0;
    $q = $db->query("select data_key, data_value, lastupdate from personinfo\n\t\twhere person_id = '" . mysql_escape_string($id) . "'");
    if ($q->rows()) {
        $output = array();
        for ($i = 0; $i < $q->rows(); $i++) {
            $data_key = $q->field($i, 'data_key');
            if (count($fields) && !in_array($data_key, $fields)) {
                continue;
            }
            $output[$data_key] = $q->field($i, 'data_value');
            $time = strtotime($q->field($i, 'lastupdate'));
            if ($time > $last_mod) {
                $last_mod = $time;
            }
        }
        $q = $db->query("select * from memberinfo\n\t\t\twhere member_id in (select member_id from member where person_id = '" . mysql_escape_string($id) . "')\n\t\t\torder by member_id");
        if ($q->rows()) {
            $oldmid = 0;
            $count = -1;
            for ($i = 0; $i < $q->rows(); $i++) {
                $data_key = $q->field($i, 'data_key');
                if (count($fields) && !in_array($data_key, $fields)) {
                    continue;
                }
                $mid = $q->field($i, 'member_id');
                if (!isset($output['by_member_id'])) {
                    $output['by_member_id'] = array();
                }
                if ($oldmid != $mid) {
                    $count++;
                    $oldmid = $mid;
                    $output['by_member_id'][$count]['member_id'] = $mid;
                }
                $output['by_member_id'][$count][$data_key] = $q->field($i, 'data_value');
                $time = strtotime($q->field($i, 'lastupdate'));
                if ($time > $last_mod) {
                    $last_mod = $time;
                }
            }
        }
        ksort($output);
        api_output($output, $last_mod);
    } else {
        api_error('Unknown person ID');
    }
}

function _api_getMPsInfo_id($ids) {
	$fields = preg_split('#\s*,\s*#', get_http_var('fields'), -1, PREG_SPLIT_NO_EMPTY);
	$ids = preg_split('#\s*,\s*#', $ids, -1, PREG_SPLIT_NO_EMPTY);
	$safe_ids = array(0);
	foreach ($ids as $id) {
		if (ctype_digit($id)) $safe_ids[] = $id;
	}
	$ids = join(',', $safe_ids);

	$db = new ParlDB;
	$last_mod = 0;
	$q = $db->query("select person_id, data_key, data_value, lastupdate from personinfo
		where person_id in (" . $ids . ")");
	if ($q->rows()) {
		$output = array();
		for ($i=0; $i<$q->rows(); $i++) {
			$data_key = $q->field($i, 'data_key');
			if (count($fields) && !in_array($data_key, $fields))
				continue;
			$pid = $q->field($i, 'person_id');
			$output[$pid][$data_key] = $q->field($i, 'data_value');
			$time = strtotime($q->field($i, 'lastupdate'));
			if ($time > $last_mod)
				$last_mod = $time;
		}
		$q = $db->query("select memberinfo.*, person_id from memberinfo, member
			where memberinfo.member_id=member.member_id and person_id in (" . $ids . ")
			order by person_id,member_id");
		if ($q->rows()) {
			for ($i=0; $i<$q->rows(); $i++) {
				$data_key = $q->field($i, 'data_key');
				if (count($fields) && !in_array($data_key, $fields))
					continue;
				$mid = $q->field($i, 'member_id');
				$pid = $q->field($i, 'person_id');
				if (!isset($output[$pid]['by_member_id'])) $output[$pid]['by_member_id'] = array();
				if (!isset($output[$pid]['by_member_id'][$mid])) $output[$pid]['by_member_id'][$mid] = array();
				$output[$pid]['by_member_id'][$mid][$data_key] = $q->field($i, 'data_value');
				$time = strtotime($q->field($i, 'lastupdate'));
				if ($time > $last_mod)
					$last_mod = $time;
			}
		}
		ksort($output);
		return array($output, $last_mod);
	} else {
		return null;
	}
}

function api_getCommittee_name($name) {
	$db = new ParlDB;

	$name = preg_replace('#\s+Committee#', '', $name);

	$date = parse_date(get_http_var('date'));
	if ($date) $date = '"' . $date['iso'] . '"';
	else $date = 'date(now())';
	$q = $db->query("select distinct(dept) from moffice
		where dept like '%" . mysql_real_escape_string($name) . "%Committee'
		and from_date <= " . $date . ' and '
		. $date . ' <= to_date');
	if ($q->rows() > 1) {
		# More than one committee matches
		for ($i=0; $i<$q->rows(); $i++) {
			$output['committees'][] = array(
				'name' => $q->field($i, 'dept')
			);
		}
		api_output($output);
	} elseif ($q->rows()) {
		# One committee
		$q = $db->query("select * from moffice,member
			where moffice.person = member.person_id
			and dept like '%" . mysql_real_escape_string($name) . "%Committee'
			and from_date <= " . $date . ' and ' . $date . " <= to_date
			and entered_house <= " . $date . ' and ' . $date . ' <= left_house');
		if ($q->rows()) {
			$output = array();
			$output['committee'] = $q->field(0, 'dept');
			for ($i=0; $i<$q->rows(); $i++) {
				$member = array(
'person_id' => $q->field($i, 'person'),
'name' => $q->field($i, 'first_name') . ' ' . $q->field($i, 'last_name'),
				);
				if ($q->field($i, 'position') == 'Chairman') {
					$member['position'] = $q->field($i, 'position');
				}
				$output['members'][] = $member;
			}
			api_output($output);
		} else {
			api_error('That committee has no members...?');
		}
	} else {
		api_error('That name was not recognised');
	}
}

 private function video_sidebar($row, $heading_hpos, $count)
 {
     $db = new \ParlDB();
     $gid_type = $this->video_gid_type();
     $vq = $db->query("select id,adate,atime from video_timestamps where gid='uk.org.publicwhip/{$gid_type}/{$row['gid']}' and (user_id!=-1 or user_id is null) and deleted=0 order by (user_id is null) limit 1");
     $adate = $vq->field(0, 'adate');
     $time = $vq->field(0, 'atime');
     $videodb = \MySociety\TheyWorkForYou\Utility\Video::dbConnect();
     if (!$videodb) {
         return '';
     }
     $video = \MySociety\TheyWorkForYou\Utility\Video::fromTimestamp($videodb, $adate, $time);
     $start = $video['offset'];
     $out = '';
     if ($count > 1) {
         $out .= '<div class="debate__video" id="video_wrap"><div>';
         if ($row['gid'] != $this->first_gid) {
             $out .= '<p class="video-instructions">This video starts around ' . ($row['hpos'] - $heading_hpos) . ' speeches in (<a href="#g' . gid_to_anchor($row['gid']) . '">move there in text</a>)</p>';
         }
     }
     $out .= \MySociety\TheyWorkForYou\Utility\Video::object($video['id'], $start, "{$gid_type}/{$row['gid']}");
     $flashvars = 'gid=' . "{$gid_type}/{$row['gid']}" . '&amp;file=' . $video['id'] . '&amp;start=' . $start;
     $out .= "<strong>Embed this video</strong><p class='video-instructions'>Copy and paste this code on your website</p><input readonly onclick='this.focus();this.select();' type='text' name='embed' size='40' value=\"<embed src='http://www.theyworkforyou.com/video/parlvid.swf' width='320' height='230' allowfullscreen='true' allowscriptaccess='always' flashvars='{$flashvars}'></embed>\">";
     if ($count > 1) {
         $out .= '<p class="hide-video"><a href="" onclick="return showVideo();">Hide</a></p>';
         $out .= '</div></div>';
         $out .= '<div id="video_show" class="show-video" style="display:none;">
 <p style="margin:0"><a href="" onclick="return hideVideo();">Show video</a></p></div>';
     }
     return $out;
 }

function wikipedize($source)
{
    $was_array = false;
    if (is_array($source)) {
        $source = join('|||', $source);
        $was_array = true;
    }
    # Set up various variables
    $capsword = "[A-Z][a-zA-Z'0-9]*";
    # not starting with number, as catches too much
    $fillerwords = "of|and|in|on|under|the";
    $middlewordre = "(?:{$capsword}|{$fillerwords})\\s*";
    $endwordre = "(?:{$capsword})\\s*";
    # and, of etc. can't appear at ends
    # Match either "Two Endwords" or "Endword and Some Middle Words"
    $greedyproperre = "/\\b{$endwordre}(?:{$middlewordre})*{$endwordre}\\b/ms";
    # Match without filler words (so if you have a phrase like
    # "Amnesty International and Human Rights Watch" you also get both parts
    # separately "Amnesty International" and "Human Rights Watch")
    $frugalproperre = "/\\b(?:{$endwordre}){2,}\\b/ms";
    # And do a greedy without the first word of a sentence
    $greedynotfirst = "/\\.\\s+\\S+\\s+({$endwordre}(?:{$middlewordre})*{$endwordre})\\b/ms";
    preg_match_all($greedyproperre, $source, $propernounphrases1);
    preg_match_all($frugalproperre, $source, $propernounphrases2);
    preg_match_all($greedynotfirst, $source, $propernounphrases3);
    # Three Letter Acronyms
    preg_match_all("/\\b[A-Z]{2,}/ms", $source, $acronyms);
    # We don't want no steenking duplicates
    $phrases = array_unique(array_merge($propernounphrases1[0], $propernounphrases2[0], $propernounphrases3[1], $acronyms[0]));
    # Sort into order, largest first
    usort($phrases, "lensort");
    foreach ($phrases as $i => $phrase) {
        $phrases[$i] = mysql_real_escape_string(str_replace(' ', '_', trim($phrase)));
    }
    # Open up a db connection, and whittle our list down even further, against
    # the real titles.
    $matched = array();
    $db = new ParlDB();
    $source = explode('|||', $source);
    $q = $db->query("SELECT title FROM titles WHERE title IN ('" . join("','", $phrases) . "')");
    for ($i = 0; $i < $q->rows(); $i++) {
        $wikistring = $q->field($i, 'title');
        $phrase = str_replace('_', ' ', $wikistring);
        # See if already matched a string this one is contained within
        foreach ($matched as $got) {
            if (strstr($got, $phrase)) {
                continue 2;
            }
        }
        # Go ahead
        twfy_debug("WIKIPEDIA", "Matched '{$phrase}'");
        # 1 means only replace one match for phrase per paragraph
        $source = preg_replace("/{$phrase}/", "<a href=\"http://en.wikipedia.org/wiki/{$wikistring}\">{$phrase}</a>", $source, 1);
        array_push($matched, $phrase);
    }
    if (!$was_array) {
        $source = join('|||', $source);
    }
    return $source;
}

function suggest_alerts($email,$criteria,$maxresults)
{
    $db = new ParlDB;
    if(stripos($criteria,"speaker:")==0)  //speaker only
    {
        // find emails who follow this speaker
        // find the speakers followed by those email
        // return their most frequently followed speakers, not followed by the searcher
        $sql ="SELECT count(*) AS c, criteria FROM alerts ";  // select and count criteria
        $sql.="WHERE email = any (SELECT email FROM alerts WHERE criteria like '%$criteria%') ";  // from emails which have the provided criteria/pid
        $sql.="AND LENGTH(criteria)=13.AND LEFT(criteria,8)='speaker:' ";  // filter in simple speaker alerts 'speaker:nnnnn'
        $sql.="AND NOT(criteria=ANY(SELECT criteria FROM alerts WHERE email='$email')) "; // disregard any alert of this emailer (already following)
        //$sql.="AND email like '%foo.test%' "; // filter in my test alerts  // REMOVE ME
        $sql.="GROUP BY criteria ORDER BY c DESC";  // most commo first
        $q=$db->query($sql);
        $resultcount=$q->rows();
        if($resultcount>0)    // if something was returned
	print "<p>You may also be interested in being alerted when these people speak too.</p>";
        {
            if($resultcount>$maxresults) $resultcount=$maxresults;  // cap results

            for($i=0; $i<$resultcount; $i++)  // iterate through results
            {
                if($q->field($i,'c')>1) // ignore suggestion where only one other has an alert for
                {
                    $pid=substr($q->field($i,'criteria'),-5); // extract members PID
                    $member=new MEMBER(array('person_id'=>$pid)); 
                    print '<p><a href="' . WEBPATH . 'alert/?r=1&only=1&amp;pid='.$member->person_id().'"><strong>Email me whenever '. $member->full_name() . ' speaks</strong></a></p>';
                }
            }
        }
    }
}

function create_key($commercial, $reason)
{
    global $THEUSER;
    $key = auth_ab64_encode(random_bytes(16));
    $db = new ParlDB();
    $db->query('INSERT INTO api_key (user_id, api_key, commercial, created, reason) VALUES
		(' . $THEUSER->user_id() . ', "' . $key . '", ' . mysql_escape_string($commercial) . ', NOW(), "' . mysql_escape_string($reason) . '")');
}

 /**
  * Normalise Constituency Name
  *
  * Turn variations on a constituency name into the canonical version.
  *
  * @param $names string A constituency name to normalise.
  *
  * @return string|bool The normalised constituency name, or false if no match.
  */
 public static function normaliseConstituencyName($name)
 {
     $db = new \ParlDB();
     // In case we still have an & lying around
     $name = str_replace("&", "&", $name);
     $query = "select cons_id from constituency where name like :name and from_date <= date(now()) and date(now()) <= to_date";
     $q1 = $db->query($query, array(':name' => $name));
     if ($q1->rows <= 0) {
         return false;
     }
     $query = "select name from constituency where main_name and cons_id = '" . $q1->field(0, 'cons_id') . "'";
     $q2 = $db->query($query);
     if ($q2->rows <= 0) {
         return false;
     }
     return $q2->field(0, "name");
 }

function api_check_key($key)
{
    $db = new ParlDB();
    $q = $db->query('SELECT user_id FROM api_key WHERE api_key="' . mysql_real_escape_string($key) . '"');
    if (!$q->rows()) {
        return false;
    }
    return true;
}

function api_getPerson_id($id)
{
    $db = new ParlDB();
    $q = $db->query("select * from member\n        where person_id = '" . mysql_real_escape_string($id) . "'\n        order by left_house desc");
    if ($q->rows()) {
        _api_getPerson_output($q);
    } else {
        api_error('Unknown person ID');
    }
}

function api_converturl_url($url) {
	$db = new ParlDB;
	$url_nohash = preg_replace('/#.*/', '', $url);
	$q = $db->query('select gid,major,htype,subsection_id from hansard where source_url = "' . mysql_real_escape_string($url) . '" order by gid limit 1');
	if ($q->rows())
		return api_converturl_url_output($q);

	$q = $db->query('select gid,major,htype,subsection_id from hansard where source_url like "' . mysql_real_escape_string($url_nohash) . '%" order by gid limit 1');
	if ($q->rows())
		return api_converturl_url_output($q);

	$url_bound = str_replace('cmhansrd/cm', 'cmhansrd/vo', $url_nohash);
	if ($url_bound != $url_nohash) {
		$q = $db->query('select gid,major,htype,subsection_id from hansard where source_url like "' . mysql_real_escape_string($url_bound) . '%" order by gid limit 1');
		if ($q->rows())
			return api_converturl_url_output($q);
	}
	api_error('Sorry, URL could not be converted');
}

function recordEvent($bucket, $event, $data = null, $timer = null)
{
    // Hook up to the DB
    $db = new \ParlDB();
    $db->query('
        INSERT INTO `research_qual2_log`
        (`time`, `page`, `bucket`, `event`, `data`, `timer`)
        VALUES
        (:timestamp, :page, :bucket, :event, :data, :timer)
    ', array('timestamp' => time(), 'page' => $_POST['page'], 'bucket' => (int) $bucket, 'event' => $event, 'data' => $data, 'timer' => (int) $timer));
}

function create_key($commercial, $reason, $estimated_usage)
{
    global $THEUSER;
    $key = auth_ab64_encode(urandom_bytes(16));
    $db = new ParlDB();
    if ($commercial == '') {
        $commercial = 0;
    }
    $db->query('INSERT INTO api_key (user_id, api_key, commercial, created, reason, estimated_usage) VALUES
        (:user_id, :key, :commercial, NOW(), :reason, :estimated_usage)', array(':user_id' => $THEUSER->user_id(), ':key' => $key, ':commercial' => $commercial, ':reason' => $reason, ':estimated_usage' => $estimated_usage));
}