pdo = new PDO('sqlite::memory'); // echo "Db connection okay"; } catch(PDOException $e) { throw new PDOException($e->getMessage(), (int)$e->getCode()); } } public function run($sql) { $this->pdo->query($sql); } public function runFetchAll($sql) { return $this->pdo->query($sql)->fetchall(); } } function setTable($data, ...$headers){ echo "

Data output:

"; echo ""; foreach($headers as $header) { echo ""; } echo ""; foreach($data as $row) { echo ""; foreach($headers as $h) { echo ""; } echo ""; } echo "

" . $header . "
" . $row[$h] . "

"; echo "

Number of records: ".count($data)."

"; } ///////// function runQuery($db, $sql, $title) { $result = $db->run($sql); echo "

".$title."

"; echo "

SQL string:

"; echo '

' . $sql . "

"; echo "

"; } ///////// function runQueryTable($db, $sql, $title, ...$headers) { $result = $db->runFetchAll($sql); echo "

".$title."

"; echo "

SQL string:

"; echo '

' . $sql . "

"; // var_dump($result); setTable($result, ...$headers); // echo "

"; return $result; } // function testLogin($db, $user, $psw) { echo '

Testing login with username="'.$user.'", password="'.$psw.'"

'; $title1 = "Get user from Users table"; $statement ='SELECT * FROM Users WHERE username='.'"'.$user.'" COLLATE NOCASE;'; $getUserResult = runQueryTable($db, $statement, $title1, 'ID', 'username', 'hash_password'); processResult($psw, $getUserResult); } function processResult($password, $result) { if(count($result)==0) { echo '

Output: User not found!

'; } else if(password_verify($password, $result[0]['hash_password'])) { echo '

Output: Login successfully with the correct password.

'; } else { echo '

Output: Wrong password!

'; } } $db = new DBSqlite(); echo "

SQL Query

"; // SQL statement for creating new tables $title = 'Create table'; $statement = 'CREATE TABLE Countries( id INT PRIMARY KEY, country VARCHAR(50) NOT NULL );'; $title = 'Create table'; runQuery($db, $statement, $title); // SQL statement for creating new tables $title = 'Create table'; $statement = 'CREATE TABLE Users ( ID INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(50) NOT NULL, hash_password VARCHAR(50) NOT NULL );'; runQuery($db, $statement, $title); // $password = 'abc12345'; $title = 'Insert a user with username="John" and password="'. $password.'"'; $hash_password = password_hash($password, PASSWORD_DEFAULT); $statement = "INSERT INTO Users (username, hash_password) VALUES ('John', '". $hash_password ."');"; runQuery($db, $statement, $title); $password = 'SecretCode!'; $title = 'Insert a user with username="Cindy" and password="'. $password.'"'; $hash_password = password_hash($password, PASSWORD_DEFAULT); $statement = "INSERT INTO Users (username, hash_password) VALUES ('Cindy', '". $hash_password ."');"; runQuery($db, $statement, $title); // $title = "Show all Users table"; $statement ="SELECT * FROM Users;"; runQueryTable($db, $statement, $title, 'ID', 'username', 'hash_password'); echo "

"; //////////// $username = "john"; $password="abcd"; testLogin($db, $username, $password); echo "

"; //////////// $username = "John"; $password="abc12345"; testLogin($db, $username, $password); echo "

"; //////////// $username = "cindy"; $password="secret"; testLogin($db, $username, $password); echo "

"; //////////// $username = "cindy"; $password="SecretCode!"; testLogin($db, $username, $password); echo "

"; //////////// $username = "mandy"; $password="SecretCode!"; testLogin($db, $username, $password); echo "

SQL query - User table with hash password