queryFirstColumn() public static method

public static queryFirstColumn ( )
Esempio n. 1
1
 function test_3_more_inserts()
 {
     DB::insert('`accounts`', array('username' => 'Bart', 'password' => 'hello', 'age' => 15, 'height' => 10.371));
     $dbname = DB::$dbName;
     DB::insert("`{$dbname}`.`accounts`", array('username' => 'Charlie\'s Friend', 'password' => 'goodbye', 'age' => 30, 'height' => 155.23, 'favorite_word' => null));
     $this->assert(DB::insertId() === 3);
     $counter = DB::queryFirstField("SELECT COUNT(*) FROM accounts");
     $this->assert($counter === strval(3));
     DB::insert('`accounts`', array('username' => 'Deer', 'password' => '', 'age' => 15, 'height' => 10.371));
     $username = DB::queryFirstField("SELECT username FROM accounts WHERE password=%s0", null);
     $this->assert($username === 'Deer');
     $password = DB::queryFirstField("SELECT password FROM accounts WHERE favorite_word IS NULL");
     $this->assert($password === 'goodbye');
     DB::$usenull = false;
     DB::insertUpdate('accounts', array('id' => 3, 'favorite_word' => null));
     $password = DB::queryFirstField("SELECT password FROM accounts WHERE favorite_word=%s AND favorite_word=%s", null, '');
     $this->assert($password === 'goodbye');
     DB::$usenull = true;
     DB::insertUpdate('accounts', array('id' => 3, 'favorite_word' => null));
     DB::$param_char = '###';
     $bart = DB::queryFirstRow("SELECT * FROM accounts WHERE age IN ###li AND height IN ###ld AND username IN ###ls", array(15, 25), array(10.371, 150.123), array('Bart', 'Barts'));
     $this->assert($bart['username'] === 'Bart');
     DB::insert('accounts', array('username' => 'f_u'));
     DB::query("DELETE FROM accounts WHERE username=###s", 'f_u');
     DB::$param_char = '%';
     $charlie_password = DB::queryFirstField("SELECT password FROM accounts WHERE username IN %ls AND username = %s", array('Charlie', 'Charlie\'s Friend'), 'Charlie\'s Friend');
     $this->assert($charlie_password === 'goodbye');
     $charlie_password = DB::queryOneField('password', "SELECT * FROM accounts WHERE username IN %ls AND username = %s", array('Charlie', 'Charlie\'s Friend'), 'Charlie\'s Friend');
     $this->assert($charlie_password === 'goodbye');
     $passwords = DB::queryFirstColumn("SELECT password FROM accounts WHERE username=%s", 'Bart');
     $this->assert(count($passwords) === 1);
     $this->assert($passwords[0] === 'hello');
     $username = $password = $age = null;
     list($age, $username, $password) = DB::queryOneList("SELECT age,username,password FROM accounts WHERE username=%s", 'Bart');
     $this->assert($username === 'Bart');
     $this->assert($password === 'hello');
     $this->assert($age == 15);
     $mysqli_result = DB::queryRaw("SELECT * FROM accounts WHERE favorite_word IS NULL");
     $this->assert($mysqli_result instanceof MySQLi_Result);
     $row = $mysqli_result->fetch_assoc();
     $this->assert($row['password'] === 'goodbye');
     $this->assert($mysqli_result->fetch_assoc() === null);
 }
Esempio n. 2
0
function printLogEntry($name, $gc, $type, $created, $log, $logId, $username, $logType, $difficulty, $terrain, $favorites, $finds, $country, $url, $sessionResults, $address, $district, $lat, $lon)
{
    $type = determineTypeIcon($type);
    $difficulty = ratingToStars("D:", $difficulty);
    $terrain = ratingToStars("T:", $terrain);
    $district = districtToImage($country, $district);
    $country = countryToImage($country);
    $logType = determineLogTypeIcon($logType);
    $ratio = findsToRatio($finds, $favorites);
    $favorites = favoritesToHearts($favorites);
    $images = DB::queryFirstColumn("SELECT\n                                           url\n                                         FROM\n                                           image\n                                         WHERE\n                                           image.log = %i", $logId);
    if (isset($lat) && isset($lon)) {
        $address = "<a href='http://maps.google.com/maps?q={$lat},{$lon}'>{$address}</a>";
    }
    if (substr($created, 0, 10) == date('d.m.Y')) {
        echo "<div class='panel panel-primary'>";
    } else {
        echo "<div class='panel panel-info'>";
    }
    $favoritesRatio = "";
    if ($favorites != "" && $ratio != "") {
        $favoritesRatio = "({$favorites} {$ratio})";
    }
    if ($type != "") {
        echo "<div class='panel-heading'><a href='{$url}'><b>{$name}</b></a> <img src='res/icons/{$type}' width='20px' /> {$difficulty} {$terrain} {$favoritesRatio} {$country} {$district} {$address}</div>";
    } else {
        echo "<div class='panel-heading'><b>{$name}</b></div>";
    }
    echo "<div class='panel-body'>{$log}";
    if (!empty($images)) {
        foreach ($images as $image) {
            echo '<img class="img-rounded img-padding" src="' . $image . '" />';
        }
    }
    echo "</div>";
    $urlencodedUsername = urlencode($username);
    if (in_array($gc, $sessionResults) && $username != getSessionUser()) {
        echo "<div class='panel-footer'><a class='u' href='index.php?username={$urlencodedUsername}'>{$username}</a> {$logType} {$created} (You <i class='fa fa-thumbs-up'></i> this one too.)</div>";
    } else {
        echo "<div class='panel-footer'><a class='u' href='index.php?username={$urlencodedUsername}'>{$username}</a> {$logType} {$created}</div>";
    }
    echo "</div>";
}
Esempio n. 3
0
    $difficulty = (int) $cache->difficulty;
    $terrain = (int) $cache->terrain;
    $country = (string) $cache->country;
    $url = (string) $wpt->url;
    foreach ($cache->logs->log as $log) {
        $gc = (string) $wpt->name;
        $name = (string) $cache->name;
        $username = (string) $log->finder;
        $logtext = (string) $log->text;
        $logType = (string) $log->type;
        $logDate = substr((string) $log->date, 0, 10);
        if ($inserted == 0) {
            DB::insertIgnore('user', array('username' => $username));
            $inserted = 1;
            $userId = DB::queryFirstField("SELECT id FROM user WHERE username=%s LIMIT 1", $username);
            $logIds = DB::queryFirstColumn("SELECT log.id FROM image, log WHERE image.log = log.id AND log.user = %i", $userId);
            foreach ($logIds as $logId) {
                DB::delete('image', "log=%i", $logId);
            }
            DB::delete('log', "user=%i", $userId);
        }
        DB::insertUpdate('geocache', array('gc' => $gc, 'name' => $name, 'type' => $typeId, 'lat' => $lat, 'lon' => $lon, 'difficulty' => $difficulty, 'terrain' => $terrain, 'country' => $country, 'url' => $url));
        $geocacheId = DB::queryFirstField("SELECT id FROM geocache WHERE gc = %s LIMIT 1", $gc);
        $logTypeId = DB::queryFirstField("SELECT id FROM logtype WHERE type = %s LIMIT 1", $logType);
        DB::insert('log', array('user' => $userId, 'geocache' => $geocacheId, 'created' => $logDate, 'type' => $logTypeId, 'log' => $logtext));
    }
    $finds++;
}
DB::update('user', array('finds' => $finds), "username=%s", $username);
//  echo "</div>";
unlink(realpath(dirname(__FILE__)) . "/pocketquery.gpx");
Esempio n. 4
0
          <div class="col-sm-6 col-sm-pull-6">
            <div class="panel panel-default">
              <div class="panel-heading">
                <?php 
$timestamp = DB::queryFirstField(" SELECT\n                                                   DATE_FORMAT(timestamp, '%d.%m.%y %H:%i:%s') as 'timestamp_display'\n                                                 FROM\n                                                   crawl\n                                                 WHERE\n                                                   result = 'success'\n                                                 ORDER BY\n                                                   timestamp DESC");
?>
                <h3 class="panel-title"><i class="fa fa-rss"></i> <a href="index.php">News</a> (<?php 
echo $timestamp;
?>
 <i class="fa fa-clock-o" aria-hidden="true"></i>)</h3>
              </div>
              <div class="panel-body">
                <?php 
$results = DB::query("SELECT\n                                          geocache.name,\n                                          geocache.gc,\n                                          geocache.difficulty,\n                                          geocache.terrain,\n                                          geocache.favorites,\n                                          geocache.finds,\n                                          geocache.country,\n                                          geocache.url,\n                                          geocache.address,\n                                          geocache.lat,\n                                          geocache.lon,\n                                          geocache.district,\n                                          type.type AS type_type,\n                                          log.created,\n                                          DATE_FORMAT(log.created, '%d.%m.%Y') AS created_display,\n                                          logtype.type,\n                                          log.log,\n                                          log.id AS id,\n                                          user.username,\n                                          user.id AS user_id\n                                        FROM\n                                          geocache, log, logtype, user, type\n                                        WHERE\n                                          geocache.id = log.geocache AND\n                                          log.user = user.id AND\n                                          type.id = geocache.type AND\n                                          log.type = logtype.id AND\n                                          created > date_sub(now(), INTERVAL 1 WEEK)\n                                        ORDER BY\n                                          created DESC,\n                                          id DESC");
$feedUserIds = DB::queryFirstColumn("SELECT\n                                                         feed.feeduser\n                                                       FROM\n                                                         user, feed\n                                                       WHERE\n                                                         user.id = feed.user AND\n                                                         user.username = %s", getSessionUser());
$sessionResults = DB::queryFirstColumn("SELECT\n                                                            geocache.gc\n                                                          FROM\n                                                            geocache, log, logtype, user, type\n                                                          WHERE\n                                                            geocache.id = log.geocache AND\n                                                            log.user = user.id AND\n                                                            type.id = geocache.type AND\n                                                            log.type = logtype.id AND\n                                                            (logtype.type = 'Found it' OR logtype.type = 'Attended' OR logtype.type = 'Webcam Photo Taken') AND\n                                                            user.username = %s\n                                                          ORDER BY\n                                                            log.created DESC,\n                                                            log.id DESC", getSessionUser());
$found = false;
foreach ($results as $row) {
    if (in_array($row['user_id'], $feedUserIds)) {
        $found = true;
        $name = $row['name'];
        $gc = $row['gc'];
        $created = $row['created_display'];
        $log = str_replace("/images/icons/", "https://www.geocaching.com/images/icons/", $row['log']);
        $logId = $row['id'];
        $username = $row['username'];
        $type = $row['type_type'];
        $difficulty = $row['difficulty'];
        $terrain = $row['terrain'];
        $country = $row['country'];
        $favorites = $row['favorites'];
Esempio n. 5
0
            <div class="form-group">
              <input class="form-control" name="username" placeholder="Username">
            </div>
          </form>
        </div>
      </div>
<?php 
}
if (isset($_GET['username']) && $_GET['username'] != '' || getSessionUser() != "") {
    if (isset($_GET['username']) && $_GET['username'] != '') {
        $username = urldecode($_GET['username']);
    } else {
        $username = getSessionUser();
    }
    if (getSessionUser() != "") {
        $sessionResults = DB::queryFirstColumn("SELECT\n                              geocache.gc\n                            FROM\n                              geocache, log, logtype, user, type\n                            WHERE\n                              geocache.id = log.geocache AND\n                              log.user = user.id AND\n                              type.id = geocache.type AND\n                              log.type = logtype.id AND\n                              logtype.type = 'Found it' AND\n                              user.username = %s AND\n                              month(curdate()) = month(created) AND\n                              day(curdate()) = day(created) AND\n                              year(curdate()) != year(created)\n                            ORDER BY\n                              log.created DESC,\n                              log.id DESC", getSessionUser());
    } else {
        $sessionResults = array();
    }
    $finds = DB::queryFirstField("SELECT finds FROM user WHERE username = %s", $username);
    echo "<div class='panel panel-info'>";
    $month = date('m');
    $day = date('d');
    echo "<div class='panel-heading'>On this day of <b>{$username}</b> ({$finds})</div>";
    echo "</div>";
} else {
    echo "<div class='panel panel-info'>";
    $month = date('m');
    $day = date('d');
    echo "<div class='panel-heading'>On this day of all indexed users</div>";
    echo "</div>";
Esempio n. 6
0
 public function process_block($block_hash)
 {
     // Get block
     try {
         $vars = $this->client->getblock($block_hash);
     } catch (Exception $e) {
         return false;
     }
     // Add confirmation
     DB::query("UPDATE coin_inputs SET confirmations = confirmations + 1 WHERE confirmations > 0");
     //DB::query("UPDATE coin_transactions SET confirmations = confirmations + 1 WHERE currency = %s", $this->currency);
     // Set variables
     $blocknum = $vars['height'];
     $tx = isset($vars['tx']) && is_array($vars['tx']) ? $vars['tx'] : array();
     $mempool = DB::queryFirstColumn("SELECT txid FROM coin_mempool");
     // Go through txs
     foreach ($tx as $txid) {
         if (!in_array($txid, $mempool)) {
             $this->check_transaction($txid);
         }
         DB::query("UPDATE coin_inputs SET confirmations = 1, blocknum = %d WHERE txid = %s", $blocknum, $txid);
         DB::query("DELETE FROM coin_mempool WHERE txid = %s", $txid);
     }
     // Check unconfirmed
     $this->check_unconfirmed();
 }
Esempio n. 7
0
          </form>
        </div>
      </div>
      <div class='panel panel-info'>
        <div class='panel-heading'>Remove user from feed</div>
        <div class='panel-body'>
          <form class="form-inline">
            <div class="form-group">
              <div class="dropdown">
                <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true">
                  Remove user
                  <span class="caret"></span>
                </button>
                <ul class="dropdown-menu">
<?php 
$users = DB::queryFirstColumn("SELECT\n                                   u2.username\n                                 FROM\n                                   user u1, feed, user u2\n                                 WHERE\n                                   feed.user = u1.id AND\n                                   u1.username = %s AND\n                                   u2.id = feed.feeduser", getSessionUser());
foreach ($users as $user) {
    echo '<li><a href="?removeUser='******'">' . $user . '</a></li>';
}
?>
                </ul>
              </div>
            </div>
          </form>
        </div>
      </div>
    </div>
    <script type="text/javascript">geolookup();</script>
  </body>
</html>
Esempio n. 8
0
<?php

require_once 'connection.php';
$username = $_GET['username'];
if ($_GET['data'] == 'allYearLabels') {
    echo json_encode(DB::queryFirstColumn("SELECT DISTINCT\n                                              year(created)\n                                            FROM\n                                              log, user\n                                            WHERE\n                                              log.user = user.id AND\n                                              user.username = %s\n                                              ORDER BY year(created) ASC", $username));
} else {
    if ($_GET['data'] == 'allYearData') {
        echo json_encode(DB::queryFirstColumn("SELECT\n                                             COUNT(*)\n                                           FROM\n                                             log,\n                                             user,\n                                             logtype\n                                           WHERE\n                                             log.user = user.id AND\n                                             user.username = %s AND\n                                             log.type = logtype.id AND\n                                             logtype.type = 'Found it'\n                                           GROUP BY year(created)\n                                           ORDER BY year(created) ASC", $username));
    } else {
        if ($_GET['data'] == 'allTypesData') {
            $results = DB::query("SELECT\n                           type.type,\n                           COUNT(*) AS cnt\n                         FROM\n                           log,\n                           geocache,\n                           user,\n                           type\n                         WHERE\n                           log.user = user.id AND\n                           user.username = %s AND\n                           geocache.type = type.id AND\n                           log.geocache = geocache.id\n                         GROUP BY type.type\n                         ORDER BY type.id ASC", $username);
            $colors = array("#347880", "#4496a1", "97c7a6", "#eaf8ab", "#dbe694", "#ccd47e", "#b5c653", "#9fb828", "#cde0db", "#255a60");
            $out = "[";
            $i = 0;
            foreach ($results as $row) {
                $out = $out . "{";
                $out = $out . "\"value\": " . $row['cnt'] . ", \"color\": \"" . $colors[$i] . "\", \"label\": \"" . $row['type'] . "\"";
                $out = $out . "}, ";
                $i++;
            }
            $out = rtrim($out, ", ");
            $out = $out . "]";
            echo $out;
        } else {
            if ($_GET['data'] == 'lastYearData') {
                $results = DB::query("SELECT\n                                   month(created) as month,\n                                   count(month(created)) as cnt\n                                 FROM\n                                   log,\n                                   user,\n                                   logtype\n                                 WHERE\n                                   log.user = user.id AND\n                                   user.username = %s AND\n                                   log.type = logtype.id AND\n                                   logtype.type = 'Found it' AND\n                                   year(log.created) = year(now())-1\n                                 GROUP BY month(created)\n                                 ORDER BY created", $username);
                $out = "[";
                for ($i = 0; $i <= 11; $i++) {
                    $found = false;
                    foreach ($results as $row) {
Esempio n. 9
0
function add_alert($action, $reference_id, $amount = 0)
{
    // Go through admins
    $admins = DB::queryFirstColumn("SELECT id FROM users WHERE group_id = 1 AND status = 'active'");
    foreach ($admins as $userid) {
        if (!$userid > 0) {
            continue;
        }
        DB::insert('alerts', array('userid' => $userid, 'type' => $action, 'reference_id' => $reference_id, 'amount' => $amount));
    }
}
<?php

//LESLIE
//DB login
require_once '../lib/php/meekrodb.class.php';
require_once "../inc/db_credentials.php";
//Arrays
$errors = array();
$data = array();
//Get data from DB
$result = DB::queryFirstColumn("SELECT count(id) FROM user");
//Set return statement
if (!empty($errors)) {
    $data['success'] = false;
    $data['errors'] = $errors;
} else {
    $data['success'] = true;
    $data['message'] = 'Skills retrieved!';
    $data['result'] = $result;
}
//Return data
echo json_encode($data);