PHP db_getOne函数代码示例

function image_zap($image_id)
{
    $filename = db_getOne("SELECT filename FROM image WHERE id=?", $image_id);
    db_do("DELETE FROM image WHERE id=?", $image_id);
    db_commit();
    unlink(image_path($filename));
}

 function execute($value)
 {
     $journo_id = db_getOne("SELECT id FROM journo WHERE ref=?", $value);
     if (is_null($journo_id)) {
         $params = array();
         throw new ValidationError(vsprintf($this->msg, $params), $this->code, $params);
     }
 }

function FetchArticle($article_id)
{
    $q = db_query('SELECT * FROM article WHERE id=?', $article_id);
    $art = db_fetch_array($q);
    $art['images'] = db_getAll("SELECT * FROM article_image WHERE article_id=?", $article_id);
    $art['content'] = db_getOne("SELECT content FROM article_content WHERE article_id=?", $article_id);
    return $art;
}

function page_header($title, $params = array())
{
    header('Content-Type: text/html; charset=utf-8');
    if (arr_get('pingbacks', $params, FALSE)) {
        $pingback_url = OPTION_BASE_URL . "/pingback";
        header("X-Pingback: {$pingback_url}");
    }
    if ($title) {
        $title .= ' - ' . OPTION_WEB_DOMAIN;
    } else {
        $title = OPTION_WEB_DOMAIN;
    }
    $P = person_if_signed_on(true);
    /* Don't renew any login cookie. */
    $datestring = date('l d.m.Y');
    $mnpage = array_key_exists('menupage', $params) ? $params['menupage'] : '';
    $rss_feeds = array();
    if (array_key_exists('rss', $params)) {
        $rss_feeds = $params['rss'];
    }
    $canonical_url = null;
    if (array_key_exists('canonical_url', $params)) {
        $canonical_url = $params['canonical_url'];
    }
    $js_files = array("/jl.js");
    if (array_key_exists('js_extra', $params)) {
        $js_files = array_merge($js_files, $params['js_extra']);
    }
    $head_extra = '';
    if (array_key_exists('head_extra', $params)) {
        $head_extra .= $params['head_extra'];
    }
    if (array_key_exists('head_extra_fn', $params)) {
        ob_start();
        call_user_func($params['head_extra_fn']);
        $head_extra .= ob_get_contents();
        ob_end_clean();
    }
    $logged_in_user = null;
    $can_edit_profile = FALSE;
    if ($P) {
        if ($P->name_or_blank()) {
            $logged_in_user = $P->name;
        } else {
            $logged_in_user = $P->email;
        }
        if (db_getOne("SELECT * FROM person_permission WHERE person_id=? AND permission='edit'", $P->id())) {
            $can_edit_profile = TRUE;
        }
    }
    $search = array('q' => '', 'type' => 'journo');
    if (array_key_exists('search_params', $params)) {
        $search = $params['search_params'];
    }
    include "../templates/header.tpl.php";
}

function admCheckAccess()
{
    $P = person_if_signed_on();
    if (!is_null($P)) {
        // check for admin permission
        $perm = db_getOne("SELECT id FROM person_permission WHERE permission='admin' AND person_id=?", $P->id());
        if (!is_null($perm)) {
            return TRUE;
        }
    }
    return FALSE;
}

function view()
{
    $P = person_if_signed_on();
    if (is_null($P)) {
        // only for logged-in users
        header("Location: /");
        return;
    }
    /* they might have multiple profiles, thus option to specify one here */
    $ref = strtolower(get_http_var('ref'));
    $journo = NULL;
    if ($ref) {
        $journo = db_getRow("SELECT * FROM journo WHERE ref=?", $ref);
        if (!$journo) {
            header("HTTP/1.0 404 Not Found");
            return;
        }
    }
    if (is_null($journo)) {
        // no journo given - if person is logged on, see if they are associated with a journo (or journos)
        $editables = db_getAll("SELECT j.* FROM ( journo j INNER JOIN person_permission p ON p.journo_id=j.id) WHERE p.person_id=? AND p.permission='edit'", $P->id());
        if (sizeof($editables) == 0) {
            header("Location: /");
            return;
        } elseif (sizeof($editables) > 1) {
            /* let user pick which one... */
            tmpl_pickjourno($editables);
            return;
        } else {
            // sizeof($editables) == 1
            $journo = $editables[0];
            // just one journo.
        }
    }
    // is this person allowed to edit this journo?
    if (!db_getOne("SELECT id FROM person_permission WHERE person_id=? AND journo_id=? AND permission='edit'", $P->id(), $journo['id'])) {
        // nope
        $journo = null;
    }
    if (!is_null($journo)) {
        header("Location: /{$journo['ref']}");
    } else {
        header("Location: /fuck");
    }
}

function auth_token_retrieve($scope, $token)
{
    $data = db_getOne('
                    select data
                    from token
                    where scope = ? and token = ?', array($scope, $token));
    /* Madness. We have to unescape this, because the PEAR DB library isn't
     * smart enough to spot BYTEA columns and do it for us. */
    $data = pg_unescape_bytea($data);
    $pos = 0;
    $res = rabx_wire_rd(&$data, &$pos);
    if (rabx_is_error($res)) {
        $res = unserialize($data);
        if (is_null($res)) {
            err("Data for scope '{$scope}', token '{$token}' are not valid");
        }
    }
    return $res;
}

 function validate($params)
 {
     $err = array();
     if ($params['prettyname'] == '') {
         $err['prettyname'] = 'blank Pretty Name';
     }
     if (!preg_match('/^[a-z]+(-[a-z0-9]+){1,}$/', $params['ref'])) {
         $err['ref'] = 'bad ref (needs to be lowercase and contain at least one hyphen)';
     } else {
         if (db_getOne("SELECT id FROM journo WHERE ref=?", $params['ref'])) {
             $err['ref'] = "Already a journo with that ref! <a href=\"/adm/{$params['ref']}\">{$params['ref']}</a>";
         }
     }
     if (!preg_match('/^[a-z]+$/', $params['firstname'])) {
         $err['firstname'] = 'bad first name';
     }
     if (!preg_match('/^[a-z]+$/', $params['lastname'])) {
         $err['lastname'] = 'bad last name';
     }
     return $err;
 }

function publication_collect($pub_id)
{
    $p = db_getRow("SELECT * FROM organisation WHERE id=?", $pub_id);
    if (0) {
        /* recent articles */
        $arts = db_getAll("SELECT id,title,pubdate,permalink FROM article WHERE srcorg=? ORDER BY pubdate DESC LIMIT 10", $pub_id);
        foreach ($arts as &$a) {
            article_augment($a);
        }
        unset($a);
        $p['recent_articles'] = $arts;
    }
    /* principles */
    if ($p['sop_url']) {
        $p['principles'] = array('name' => $p['sop_name'], 'url' => $p['sop_url']);
    } else {
        $p['principles'] = null;
    }
    unset($p['sop_url']);
    unset($p['sop_name']);
    /* recent journos */
    $sql = <<<EOT
SELECT DISTINCT j.ref, j.prettyname, j.lastname FROM
    ( ( journo j INNER JOIN journo_attr attr ON j.id=attr.journo_id )
        INNER JOIN article a ON a.id=attr.article_id)
    WHERE a.srcorg=?
        AND a.status='a'
        AND a.pubdate > NOW() - INTERVAL '1 week'
    ORDER BY j.lastname;
EOT;
    $journos = db_getAll($sql, $pub_id);
    $p['recent_journos'] = $journos;
    /* address (vcard adr fields) */
    $foo = db_getOne("SELECT adr FROM pub_adr WHERE pub_id=?", $pub_id);
    $p['adr'] = $foo ? vcard_parse_adr($foo) : NULL;
    /* telephone (assume type='voice' for now) */
    $p['tel'] = db_getOne("SELECT phone FROM pub_phone WHERE pub_id=?", $pub_id);
    return $p;
}

function random_bytes($num)
{
    if (!file_exists('/dev/random')) {
        // probably Windows. Use database.
        $res = '';
        while (strlen($res) < $num) {
            $res .= db_getOne('select chr((256*random())::int4)');
        }
        return $res;
    }
    global $random_bytes_filehandle;
    if ($num < 0) {
        err("NUM must be nonnegative in random_bytes");
    }
    if (!isset($random_bytes_filehandle) && !($random_bytes_filehandle = fopen("/dev/random", "r"))) {
        err("Unable to open /dev/random");
    }
    $res = '';
    while (strlen($res) < $num) {
        $res .= fread($random_bytes_filehandle, $num - strlen($res));
    }
    return $res;
}

function view()
{
    if (!admCheckAccess()) {
        exit;
    }
    // should return error code?
    $j = get_http_var('j');
    $j = strtolower($j);
    $journo = db_getRow("SELECT id,ref,prettyname,oneliner,status FROM journo WHERE ref=?", $j);
    if (is_null($journo)) {
        // TODO: 404
        return;
    }
    $sql = <<<EOT
    SELECT p.id,p.email,p.name,perm.permission
        FROM person p INNER JOIN person_permission perm ON perm.person_id=p.id
        WHERE perm.permission='edit' AND perm.journo_id=?
EOT;
    $users = db_getAll($sql, $journo['id']);
    $journo['arts'] = journo_collectArticles($journo, 5);
    $journo['num_arts'] = db_getOne("SELECT COUNT(*) FROM journo_attr WHERE journo_id=?", $journo['id']);
    $journo['linked_users'] = $users;
    template($journo);
}

function LookupToken($email)
{
    $sql = <<<EOT
SELECT token,created,data
    FROM token
    WHERE scope='login' AND encode( data, 'escape' ) ilike ?
    ORDER BY created DESC
EOT;
    $q = db_query($sql, '%' . $email . '%');
    $cnt = db_num_rows($q);
    if ($cnt == 0) {
        print "<p>No tokens found for <code>{$email}</code> (maybe they used a different email address?)</p>\n";
    } else {
        print "<p>Found {$cnt} tokens for <code>{$email}</code> (most recent first)</p>\n";
        print "<table border=1>\n";
        print "<tr><th>when issued</th><th>email</th><th>confirmation link</th><th>stashed url</th></tr>\n";
        while ($r = db_fetch_array($q)) {
            $t = strtotime($r['created']);
            $issued = strftime('%R %a %e %B %Y', $t);
            $token = $r['token'];
            $confirmation_url = OPTION_BASE_URL . "/login?t={$token}";
            $stashed_url = '????';
            $email = '????';
            $pos = 0;
            $res = rabx_wire_rd(&$r['data'], &$pos);
            if (!rabx_is_error($res)) {
                $email = $res['email'];
                $stashed_url = db_getOne("SELECT url FROM requeststash WHERE key=?", $res['stash']);
                if (!$stashed_url) {
                    $stashed_url = '-none- (which probably means they clicked the link)';
                }
            }
            ?>
<tr>
  <td><?php 
            echo $issued;
            ?>
</td>
  <td><code><?php 
            echo $email;
            ?>
</code></td>
  <td><code><?php 
            echo $confirmation_url;
            ?>
</code></td>
  <td><code><?php 
            echo $stashed_url;
            ?>
</code></td>
</tr>
<?php 
        }
        print "</table>\n";
    }
}

 function handleSubmit()
 {
     // rewrite the whole lot... (but only the types the user can edit!)
     db_do("DELETE FROM journo_weblink WHERE kind NOT IN ('pingback','twitter') AND journo_id=?", $this->journo['id']);
     $rankstep = 10;
     $rank = 100 + $rankstep * sizeof($this->submitted);
     foreach ($this->submitted as &$w) {
         db_do("INSERT INTO journo_weblink (journo_id,kind,url,description,approved,rank) VALUES (?,?,?,?,true,?)", $this->journo['id'], $w['kind'], $w['url'], $w['description'], $rank);
         $w['id'] = db_getOne("SELECT lastval()");
         $rank = $rank - $rankstep;
     }
     db_commit();
     eventlog_Add('modify-weblinks', $this->journo['id']);
 }

function stash_get_extra($key)
{
    return db_getOne('select extra from requeststash where key = ?', $key);
}

function DoAddAlert($P, $journo_ref)
{
    $journo = db_getRow("SELECT id,prettyname FROM journo WHERE ref=? AND status='a'", $journo_ref);
    if (!$journo) {
        err("bad journalist ref");
    }
    $url = "/{$journo_ref}";
    $journo_id = $journo['id'];
    if (!db_getOne("SELECT id FROM alert WHERE journo_id=? AND person_id=?", $journo_id, $P->id)) {
        db_query("INSERT INTO alert (person_id,journo_id) VALUES (?,?)", $P->id, $journo_id);
        db_commit();
        print "<p class=\"infomessage\"><a href=\"{$url}\">{$journo['prettyname']}</a> was added to your list.</p>\n";
    } else {
        print "<p class=\"infomessage\"><a href=\"{$url}\">{$journo['prettyname']}</a> is already on your list.</p>\n";
    }
}