PHP Db::queryOne方法代碼示例

 /**
  * @param string $query
  * @param array  $parameters
  *
  * @return string
  */
 public static function querySingleOne($query, $parameters = [])
 {
     try {
         $result = Db::queryOne($query, $parameters);
         return $result[0];
     } catch (PDOException $e) {
         self::reportProblem($e);
         return false;
     }
 }

    public function trySendLink($email, $year, $language)
    {
        //inkredintions are correctly set
        if (!isset($email, $year)) {
            return ['s' => 'error', 'cs' => 'Nepovedlo se získat data. Zkus to znovu prosím', 'en' => 'We didn\'t catch data correctly - please try it again'];
        }
        //correct year in antispam
        if ($year != date("Y") - 1) {
            return ['s' => 'error', 'cs' => 'Bohužel, antispam byl tentokrát mocnější než ty', 'en' => 'Nothing happend, antispam was stronger than you'];
        }
        $result = Db::queryOne('SELECT `email` FROM `users`
                                WHERE `email` = ?', [$_POST['email']]);
        //skip all when email ins't the same as typed
        if ($email == $result[0]) {
            $randomHash = hash('sha512', uniqid(mt_rand(1, mt_getrandmax()), true));
            if (!Db::queryModify('INSERT INTO `restart_password` (`validation_string`, `email`, `active`, `timestamp`)
                                  VALUES (?, ?, 1, NOW())', [$randomHash, $result[0]])) {
                $this->newTicket('problem', $_SESSION['id_user'], 'nepovedlo se zapsat do restart_password ve funkci register');
                return ['s' => 'chyba', 'cs' => 'Pokus se nepovedl uložit; zkus to prosím znovu za pár minut', 'en' => 'We failed on saving data. Try it again please after couple of minutes'];
            }
            $subject = ['cs' => NAME . ' Paralelní Polis - žádost o restart hesla', 'en' => NAME . ' Paralell Polis - change password request'];
            $activeLink = ROOT . '/' . $language . '/RestartPasswordByLink/' . $randomHash;
            $message = ['cs' => 'Zdravím!<br/>
<br/>
Na stránce <a href="' . ROOT . '/' . $language . '">' . ROOT . '</a> jsme registrovali žádost o restart hesla.<br/>
<br/>
Heslo si můžeš změnit klikem na odkaz <a href="' . $activeLink . '">' . $activeLink . '</a>. Platnost odkazu je <b>' . round(CHANGE_PASS_TIME_VALIDITY / 60) . '</b> minut.<br/>
<br/>
Pokud tento mail neočekáváš, stačí ho ignorovat. Pokud by ti přesto přišel podezřelý nebo vícekrát za sebou,
prosím konkatuj správce stránek na <a href="' . ROOT . '/' . $language . '/contact">' . ROOT . '/' . $language . '/contact</a><br/>', 'en' => 'Hello!<br/>
<br/>
We are register request about password change on the page <a href="' . ROOT . '/' . $language . '">' . ROOT . '</a>.<br/>
<br/>
You can change your password by clicking on this link: <a href="' . $activeLink . '">' . $activeLink . '</a>. Time validity of this link is <b>' . round(CHANGE_PASS_TIME_VALIDITY / 60) . '</b> minutes.<br/>
<br/>
If you don\'t awaiting for this mail, just ignore it. But if you want to know what to do next,
please contact our webmaster on this page: <a href="' . ROOT . '/' . $language . '/contact">' . ROOT . '/' . $language . '/contact</a><br/>'];
            if (!$this->sendEmail(EMAIL, $email, $subject[$language], $message[$language])) {
                $this->newTicket('problem', $_SESSION['id_user'], 'nepovedlo se odeslat email');
                return ['s' => 'error', 'cs' => 'Nepovedlo se odeslat email s aktivačním linkem; zkus to prosím za pár minut znovu', 'en' => 'We failed in sending email with activation link; try it again please after couple of minutes'];
            }
            $this->newTicket('restartHesla', $email, 'poslan mail s linkem');
        } else {
            //check if we can grab who is logged - serve as primitive honeypot
            if (isset($_SESSION['username'])) {
                $loggedUser = $_SESSION['username'];
            } else {
                $loggedUser = "we dont know :(";
            }
            $this->newTicket("restartHesla", $loggedUser, 'neplatny pokus restartu hesla pro uzivatele: ' . $_POST['email']);
        }
        return ['s' => 'success', 'cs' => 'Ozvali jsme se na zadaný email', 'en' => 'We send as email on desired address'];
    }

 public function validateForceActivationData($tariffId, $startDate)
 {
     if ($startDate != date('Y-m-d', strtotime($startDate))) {
         $this->newTicket('error', 'function validateForceActivationData in Activation', '\\$_POST[startDate] is in bad format');
         return ['s' => 'error', 'cs' => 'Špatný formát zadání prvního dne', 'en' => 'Bad format of starting date'];
     }
     $result = Db::queryOne('SELECT `id_tariff` FROM `tariffs` WHERE id_tariff = ?', [$tariffId]);
     if ($result) {
         return ['s' => 'success'];
     } else {
         return ['s' => 'error', 'cs' => 'Špatně jsme zachytili vybraný tarif', 'en' => 'We didn\'t recognize your choosed tariff'];
     }
 }

 public function checkKeyReturnEmail($key)
 {
     if ($key == null) {
         return ['s' => 'error', 'cs' => 'Brutforce klíč je prázdný', 'en' => 'Brutforce key is blank'];
     }
     $result = Db::queryOne('SELECT `validation_string`,`email` FROM `restart_brutforce`
         WHERE `active` = 1 && `validation_string` = ?', [$key]);
     if ($result[0] == null) {
         return ['s' => 'error', 'cs' => 'Bohužel nesouhlasí aktivační klíč', 'en' => 'Activation key is not valid'];
     } else {
         return $result['email'];
     }
 }

 public function validateData($data)
 {
     if (!is_numeric($data['ic'])) {
         return ['s' => 'error', 'cs' => 'IČ musí být číslo', 'en' => 'VAT number must be a number'];
     }
     $attempt = Db::queryOne('SELECT `password`,`salt` FROM `users`
                              WHERE `email` = ?', [$_SESSION['username']]);
     $userPassword = hash('sha512', $data['p'] . $attempt['salt']);
     if ($userPassword != $attempt['password']) {
         return ['s' => 'error', 'cs' => 'Současné heslo bylo zadáno nesprávně', 'en' => 'Incorrect password'];
     }
     return ['s' => 'success'];
 }

 public function validateData($data)
 {
     if (empty($data['email'])) {
         return ['s' => 'error', 'cs' => 'Prosím vyplň svůj přihlašovací email', 'en' => 'Please enter your login email'];
     }
     if (empty($data['tariff'])) {
         $this->newTicket('error', 'function validateData in Registration', '\\$_POST[place] is empty');
         return ['s' => 'error', 'cs' => 'Nepodařilo se zachytit vybraný tarif. Zkus to prosím znovu', 'en' => 'Unable to capture the selected tariff. Please try again.'];
     }
     if (empty($data['firstname'])) {
         return ['s' => 'error', 'cs' => 'Prosím vyplň křestní jméno', 'en' => 'Please fill in your first name'];
     }
     if (empty($data['surname'])) {
         return ['s' => 'error', 'cs' => 'Prosím vyplň příjmení', 'en' => 'Please enter your surname'];
     }
     if (!empty($data['telephone']) && !preg_match('/^\\+?[\\d ]+$/', $data['telephone'])) {
         return ['s' => 'error', 'cs' => 'Telefoní číslo musí být číslo (volitelně i s národní předvolbou)', 'en' => 'Telephone number must be a number (optionally with country prefix)'];
     }
     if (!empty($data['ic'])) {
         if (!is_numeric($data['ic'])) {
             return ['s' => 'error', 'cs' => 'IČ musí být číslo', 'en' => 'VAT must be a number'];
         }
     }
     if (strlen($data['address']) > 120) {
         return ['s' => 'error', 'cs' => 'Adresa by měla být dlouhá max. 120 znaků', 'en' => 'Address should be long maximum of 120 characters'];
     }
     if ($data['startDate'] != date('Y-m-d', strtotime($data['startDate']))) {
         $this->newTicket('error', 'function validateData in Registration', '\\$_POST[startDate] is in bad format');
         return ['s' => 'error', 'cs' => 'Špatný formát zadání prvního dne', 'en' => 'Bad format of starting date'];
     }
     if ($data['tariff'] == 'X') {
         return ['s' => 'error', 'cs' => 'Prosím vyber svůj tarif', 'en' => 'Please select your tariff'];
     }
     if (strlen($data['p']) != 128) {
         $this->newTicket('error', 'function validateData in Registration', 'Something wrong with \'p\' in registration; p=' . $_POST['p'] . ',strlen($p)=' . strlen($data['p']));
         return ['s' => 'error', 'cs' => 'Nepovedlo se správně zachytit heslo - zkus to prosím znovu', 'en' => 'Failed to properly capture the password - please try again'];
     }
     $attempt = Db::queryOne('SELECT `id_user`,`email`,`password`,`salt` FROM `users`
                              WHERE `email` = ?', [$data['email']]);
     //if in DB is found that email
     if ($attempt[0] != null) {
         return ['s' => 'error', 'cs' => 'Tento email už registrovaný je. <a href="' . ROOT . '/cs/login">Přihlásit se?</a>', 'en' => 'This email has already been registred. <a href="' . ROOT . '/en/login">Log in?</a>'];
     }
     //success
     return ['s' => 'success'];
 }

 public function getUserData($userId)
 {
     $user = Db::queryOne('SELECT `id_user`,`first_name`,`last_name`,`telephone`,`address`,`ic`,`active`,`email`,`name`,`tariffCZE`,`tariffENG`,`invoicing_start_date`
                           FROM `users`
                           JOIN `tariffs` ON `id_tariff` = `user_tariff`
                           JOIN `places` ON `place_id` = `places`.`id`
                           WHERE `id_user` = ?', [$userId]);
     $tariff = Db::queryOne('SELECT `id_tariff`, `priceCZK`,`tariffCZE`,`tariffENG`
                             FROM `users`
                             JOIN `tariffs` ON `users`.`user_tariff` = `tariffs`.`id_tariff`
                             JOIN `places` ON `place_id` = `places`.`id`
                             WHERE  `id_user` = ?', [$userId]);
     $payments = Db::queryAll('SELECT `id_payment`,`bitcoinpay_payment_id`,`id_payer`,`payed_price_BTC`,`payment_first_date`,`status`,`tariff_id`,`price_CZK`,`invoice_fakturoid_id`
                               FROM `payments` WHERE `id_payer` = ?
                               ORDER BY `payment_first_date` DESC', [$userId]);
     return ['user' => $user, 'tariff' => $tariff, 'payments' => $payments];
 }

 public function validateData($data)
 {
     if (!empty($data['telephone']) && !preg_match('/^\\+?[\\d ]+$/', $data['telephone'])) {
         return ['s' => 'error', 'cs' => 'Telefoní číslo musí být číslo (volitelně i s národní předvolbou)', 'en' => 'Telephone number must be a number (optionally with country prefix)'];
     }
     if (!empty($data['ic'])) {
         if (!is_numeric($data['ic'])) {
             return ['s' => 'error', 'cs' => 'IČ musí být číslo', 'en' => 'VAT number must be a number'];
         }
     }
     $attempt = Db::queryOne('SELECT `password`,`salt` FROM `users`
                              WHERE `email` = ?', [$_SESSION['username']]);
     $userPassword = hash('sha512', $data['p'] . $attempt['salt']);
     if ($userPassword != $attempt['password']) {
         return ['s' => 'error', 'cs' => 'Současné heslo bylo zadáno nesprávně', 'en' => 'Incorrect password'];
     }
     return ['s' => 'success'];
 }

 public function checkForm($link, $p)
 {
     $result = Db::queryOne('SELECT `validation_string`,`users`.`email` FROM `restart_password`
         JOIN `users` WHERE `users`.`email` = `restart_password`.`email` && `validation_string` = ?', [$link]);
     //password must be 128 chars long after user-side hashing
     if (strlen($p) != 128) {
         $this->newTicket('problem', $link, 'hash ve funkci zkontrolovatFormular nemá delku 128 znaků - link: ' . $link . ' a možná přihlášený uživatel: ' . $_SESSION['username']);
         return ['s' => 'error', 'cs' => 'Stalo se něco divného v hashování hesla. Prosím zkuste to znovu', 'en' => 'Something wierd happend in password hashing. Please try it again'];
     }
     $randomSalt = $this->getRandomHash();
     $saltedPassword = hash('sha512', $p . $randomSalt);
     if (!Db::queryModify('UPDATE `users` SET `password` = ? , `salt` = ?
                           WHERE email = ?', [$saltedPassword, $randomSalt, $result['email']])) {
         return ['s' => 'error', 'cs' => 'Nepovedlo se uložení do databáze. Zkuste to prosím znovu', 'en' => 'We failed at database save. Try it again please'];
     }
     //success
     $this->invalidateAttemptsForMail($result['email']);
     return ['s' => 'success', 'cs' => 'Heslo bylo úspěšně změněno', 'en' => 'Password was changed successfully'];
 }

            <li class="active">Agencies</li>
        <?php 
} elseif ($getPage == 'results') {
    ?>
            <li class="active">Properties</li>
        <?php 
} elseif ($getPage == 'item-detail') {
    $item = Db::queryOne('SELECT title FROM items_table WHERE item_id=?', $_GET['item']);
    ?>
            <li class="active"><?php 
    echo $item['title'];
    ?>
</li>
        <?php 
} elseif ($getPage == 'company-detail') {
    $company = Db::queryOne('SELECT company_title FROM companies_table WHERE company_id=?', $_GET['company']);
    ?>
            <li class="active"><?php 
    echo $company['company_title'];
    ?>
</li>
        <?php 
} else {
    ?>
            <li class="active"><?php 
    echo $page;
    ?>
</li>
        <?php 
}
?>

    private function getPaymentData($paymentId)
    {
        $payment = Db::queryOne('SELECT `id_payer`,`email`,`priceCZK`,`invoice_fakturoid_number` FROM `payments`
			JOIN `users` ON `users`.`id_user` = `payments`.`id_payer`
			JOIN `tariffs` ON `users`.`user_tariff` = `tariffs`.`id_tariff`
			WHERE `id_payment` = ?', [$paymentId]);
        //add sum of all extras
        $extras = Db::queryAll('SELECT `priceCZK` FROM `extras` WHERE `payment_id` = ?', [$paymentId]);
        $extrasSum = 0;
        foreach ($extras as $e) {
            $extrasSum += $e['priceCZK'];
        }
        $payment['priceCZK'] += $extrasSum;
        return $payment;
    }

<?php

$includePath = '';
include_once 'Db.php';
include_once 'connect_db.php';
$count = Db::queryOne('SELECT counter FROM faq_table WHERE faq_id=?', $_POST['id']);
if ($_POST['action'] == 'no') {
    echo 'This answer was helpful for ' . '<strong>' . $count['counter'] . '</strong>' . ' people';
} elseif ($_POST['action'] == 'yes') {
    Db::query('UPDATE faq_table SET counter=? WHERE faq_id=?', $count['counter'] + 1, $_POST['id']);
    echo 'This answer was helpful for ' . '<strong>' . ($count['counter'] + 1) . '</strong>' . ' people';
}

    public function tryLogin($data, $language)
    {
        $attempt = Db::queryOne('SELECT `id_user`,`email`,`password`,`salt` FROM `users`
                                 WHERE `email` = ?', [$data['login']]);
        $userPassword = hash('sha512', $data['p'] . $attempt['salt']);
        //if user doesn't exists
        if ($attempt == null) {
            return ['s' => 'error', 'cs' => 'Bohužel, uživatel není v databázi. <br/><a href="' . ROOT . '/cs/registration">Nechceš se registrovat?</a>', 'en' => 'Sorry, this user is not in our database. <br/><a href="' . ROOT . '/en/registration">Maybe you want to register instead?</a>'];
        }
        //account is not locked
        if ($this->checkBrute($data['login']) == false) {
            //password is different!
            if ($userPassword != $attempt['password']) {
                //write it into brutcheck
                Db::queryModify('INSERT INTO `login_attempts` (`login`,`success`,`timestamp`)
                                 VALUES (?, 0, NOW())', [$data['login']]);
                return ['s' => 'error', 'cs' => 'Bohužel, heslo není správně. <br/><a href="' . ROOT . '/cz/GetLinkForNewPassword">Nepotřebuješ si nechat zaslat nové?</a>', 'en' => 'Sorry, password is not correct. <br/><a href="' . ROOT . '/en/GetLinkForNewPassword">Don\'t you need a new one?</a>'];
                //corrent both login and password - success!
            } else {
                //store information about newly logged user
                $_SESSION['id_user'] = $attempt['id_user'];
                $_SESSION['username'] = $data['login'];
                $_SESSION['login_string'] = hash('sha512', $userPassword . $_SERVER['HTTP_USER_AGENT']);
                Db::queryModify('INSERT INTO `login_attempts` (`login`,`success`,`timestamp`)
                                 VALUES (?, 1, NOW())', [$data['login']]);
                return ['s' => 'success', 'cs' => 'Přihlášeno, vítejte zpět!', 'en' => 'Logged in, welcome back!'];
            }
            //account is locked by bruteforce
        } else {
            //check if need to send unlock mail
            $timeOfAttempt = date("Y-m-d H:i:s", time() - BRUTEFORCE_LOCKED_TIME);
            $unlockMailCheck = Db::queryOne('SELECT `timestamp` FROM `restart_brutforce`
                                             WHERE `timestamp` > ? && `email` = ?
                                             ORDER BY `timestamp` DESC', [$timeOfAttempt, $data['login']]);
            //when email has been already sent
            if ($unlockMailCheck[0] != null) {
                return ['s' => 'error', 'cs' => 'Už byl poslán mail s odblokováním - jestli nedorazil, konktatuj prosím správce.', 'en' => 'Mail with unblock was already sent - if you did\'t recieve anything, please contact our webmaster'];
            }
            //wirte into DB about unblocking key...
            $randomHash = $this->getRandomHash();
            Db::queryModify('INSERT INTO `restart_brutforce` (`validation_string`, `email`, `active`, `timestamp`)
                                 VALUES (?, ?, TRUE, NOW())', [$randomHash, $data['login']]);
            //...and send email
            $activeLink = ROOT . '/' . $language . '/unlockBrutforce/' . $randomHash;
            $subject = ['cs' => NAME . ' Paralelní polis - příliš neúspěšných přihlášení', 'en' => NAME . ' Paralell polis - too many login attemps'];
            $message = ['cs' => 'Ahoj! <br/>
<br/>
Někdo se pokusil na tento email přihlásit pod tímto emailem více než ' . BRUTEFORCE_NUMBER_OF_ATTEMPTS . ' krát do ' . NAME . ' Paralelního Polisu.<br/>
<br/>
<a href="' . ROOT . '/cs/contact">Pokud jsi to nebyl ty, měl by ses ozvat správci.</a><br/>
<br/>
Kliknutí na tento link ti odemkne dalších pět pokusů: <a href="' . $activeLink . '">' . $activeLink . '</a><br/>', 'en' => 'Hi! <br/>
<br/>
Someone has tried to log in from this email more than ' . BRUTEFORCE_NUMBER_OF_ATTEMPTS . ' times into' . NAME . ' from Paralell polis.<br/>
<br/>
<a href="' . ROOT . '/en/contact">If it wasn\'t you, you should contact the webmaster.</a><br/>
<br/>
Clicking on this link will unlock ' . BRUTEFORCE_NUMBER_OF_ATTEMPTS . ' more tries: <a href="' . $activeLink . '">' . $activeLink . '</a><br/>'];
            $this->sendEmail(EMAIL, $data['login'], $subject[$language], $message[$language]);
            $dataForTicket = ['sentUnlockBruteforce', $data['login'], 'mail with unlocking link is sent'];
            Db::queryModify('INSERT INTO `tickets` (`type`, `title`, `message`, `timestamp`)
                             VALUES (?,?,?, NOW())', $dataForTicket);
            return ['s' => 'error', 'cs' => 'Zkusil jsi se přihlásit ' . BRUTEFORCE_NUMBER_OF_ATTEMPTS . 'krát za sebou.<br/>
                    Počkej ' . round(BRUTEFORCE_LOCKED_TIME / 60) . ' minut nebo klikni v emailu na odemykací link, který jsme ti teď poslali', 'en' => 'You\'ve tried to login ' . BRUTEFORCE_NUMBER_OF_ATTEMPTS . ' times.<br/>
                    Wait ' . round(BRUTEFORCE_LOCKED_TIME / 60) . ' minutes or click on the link to unlock, which we have sent you on mail just now'];
        }
    }

 public static function getOne($id)
 {
     $db = new Db();
     $query = "SELECT * FROM " . static::$table . " WHERE id = " . $id;
     return $db->queryOne($query, static::$class);
 }

<?php

include_once $includePath . 'Db.php';
Db::connect('localhost', 'adonata', 'root', '501062');
$config = Db::queryOne('SELECT * FROM configuration_table');
if (isset($_SESSION['user_id'])) {
    $user = Db::queryOne('SELECT account_type, is_admin, agency_admin, person_name FROM persons_table WHERE person_id=?', $_SESSION['user_id']);
}