zeeslag/dbase.inc

<?php

require_once "gamefunction.inc";
class dbase
{
    //properties
    protected $db;

    //methods
    public function __construct()
    {
        $this->db = new SQLite3("db.sqlite");
    }

    //check login
    public function loginQuery($login, $password)
    {

        $stmt = $this->db->prepare("SELECT * FROM users WHERE login = :usernm AND password = :pswrd");

        $stmt->bindValue(':usernm', $login, SQLITE3_TEXT);
        $stmt->bindValue(':pswrd', $password, SQLITE3_TEXT);

        $gameLogin = $stmt->execute();

        return $gameLogin->fetchArray(SQLITE3_ASSOC);
    }

    //check if name exists
    public function registerQuery($login)
    {
        $resultStmt = $this->db->prepare("SELECT login FROM users WHERE LOWER(login) = LOWER(:lgin)");
        $resultStmt->bindValue(':lgin', $login, SQLITE3_TEXT);
        $result = $resultStmt->execute();
        return $result->fetchArray(SQLITE3_ASSOC);
    }

    //insert new user into db
    public function registerInsert($login, $password)
    {
        $insertStmt = $this->db->prepare("INSERT INTO users(login, password) VALUES (:lgin, :pswd)");
        $insertStmt->bindValue(':lgin', $login, SQLITE3_TEXT);
        $insertStmt->bindValue(':pswd', $password, SQLITE3_TEXT);
        $insertStmt->execute();
    }


    public function highScoreQuery()
    {
        return  $this->db->query("SELECT login, count(*) AS HighScore 
                    FROM game JOIN users ON users.id = user_id 
                    WHERE won = 1 
                    GROUP BY login 
                    ORDER BY highScore DESC, login ASC");
    }

    //score current user
    public function userScoreQuery($spelerId)
    {
        $stmtUserScore = $this->db->prepare("SELECT date, won, difficulty FROM game WHERE user_id = :usrid ORDER BY date");
        $stmtUserScore->bindValue(':usrid', $spelerId, SQLITE3_INTEGER);
        return $stmtUserScore->execute();
    }

    //insert new result after end game
    public function insertResult()
    {
        $resultStmt = $this->db->prepare("INSERT INTO game (user_id, date, won, difficulty) VALUES (:splrId, :date, :splrscr, '{$_SESSION['game']->difficulty}')");
        $resultStmt->bindValue(':splrId', $_SESSION['spelerId'], SQLITE3_INTEGER);
        $resultStmt->bindValue(':date', date('Y-m-d H:i:s'), SQLITE3_TEXT);
        $resultStmt->bindValue(':splrscr', $_SESSION['game']->gameResult, SQLITE3_INTEGER);
        $resultStmt->execute();
    }
}

$dbase = new dbase;