This may be a bit overwhelming, but me and a friend of mine have written a Database class with methods for our queries. All of these queries should be generic so we can use them in multiple ways. We make use of PDO.
These are the thing bothering me;
- We need a session class to handle all the sessions within our classes (tips, anybody)?
- The user class is not fully written in OOP
- Don't mind the use of public, protected, private, everything is public, this is something we will fix when we have a full working system
Can anybody please review our code and give us some best practices, tips, thing we did wrong, etc?
Comments are in Dutch. If you have a question feel free to ask.
THE DATABASE CLASS
<?php
class Database{
//constantes zijn geen properties want die kan je niet veranderen
const DB_HOSTNAME = "localhost";
const DB_USERNAME = "root";
const DB_PASSWORD = "";
const DB_NAME = "quiz";
private $typedb = "mysql";
public $db;
public $sql;
public function __construct(){
//heeft geen conditie nodig. moet uitgevoerd worden dus try
try
{
//object aanroepen van de class PDO (ingebouwd in PHP)
$this->db = new PDO($this->typedb.':host='.self::DB_HOSTNAME.';dbname='.self::DB_NAME, self::DB_USERNAME, self::DB_PASSWORD);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
//anders wat? heeft wel een conditie nodig, bij een exception van PDO dus, die store je in de $e
catch(PDOException $e)
{
return $e->getMessage();
}
}
//$values verwacht $_POST waardes dus is al array
public function insertQuery($table, array $cols, array $values){
$query = "INSERT INTO $table SET ";
$column_count = count($cols);
for($i = 0; $i < $column_count; $i++){
//voeg toe aan de string
$query .= $cols[$i]."= :param".$i." ,";
}
//return een deel van de string, dus de laatste komma moet weg
//start bij 0, en return alleen het laatste (-1)
$query = substr($query, 0, -1);
$this->sql = $this->db->prepare($query);
$value_count = count($values);
//bijvoorbeeld :param1 = values['1']
for($j = 0; $j < $value_count; $j++){
$this->sql->bindParam(":param".$j, $values[$j]);
}
$this->sql->execute();
}
public function selectQuery(array $cols, $table, array $wherecolumns, array $values){
$column_count = count($cols);
$wherecolumn_count = count($wherecolumns);
$query = "SELECT ";
// SELECT column1, columns2 FROM table WHERE value1 = 4 ORDER BY
//bij meerdere kolommen selecteren
if($column_count > 1)
{
for($i = 0; $i < $column_count; $i++){
//voeg toe aan de string
$query .= $cols[$i].", ";
}
//substr komma weghalen
$query = substr($query, 0, -2);
}
//bij 1 of alle (*) kolommen selecteren
elseif($column_count == 1)
{
$singleColumn = implode(" ", $cols);
$query .= $singleColumn;
}
$query .= " FROM $table";
//als het geen globale query is, dus heeft wheres en values
if(!empty($wherecolumns) && !empty($values)){
$query .= " WHERE ";
for($j = 0; $j < $wherecolumn_count; $j++)
{
//de where columns en values uittellen en toevoegen aan de query
$query .= $wherecolumns[$j]." = :value".$j." && ";
}
$query = substr($query, 0, -3);
}
$this->sql = $this->db->prepare($query);
//de parameters binden
for($k = 0; $k < $wherecolumn_count; $k++){
$this->sql->bindParam(":value".$k, $values[$k]);
}
$this->sql->execute();
}
public function updateQuery(){
}
public function deleteQuery(){
}
}
?>
THE USER CLASS (note; this may not be fully OOP?)
<?php
class User extends Database{
public $username;
public $password;
public $type;
public function register(){
$db = new Database();
if(isset($_POST['reg_submit'])){
$this->username = $_POST['regUsername'];
$this->password = sha1($_POST['regPassword']);
$this->type = 2;
if(!empty($_POST['regUsername']) OR !empty($_POST['regPassword']) OR !empty($_POST['retypePassword']))
{
if($_POST['regPassword'] == $_POST['retypePassword']){
$register_tablename = 'users';
$register_users_cols = array("username", "password", "usertype_id");
$register_post_values = array($this->username, $this->password, $this->type);
$db->insertQuery($register_tablename, $register_users_cols, $register_post_values);
}
}
}
}
public function login(){
$db = new Database();
if(isset($_POST['log_submit']))
{
$this->username = $_POST['loginUsername'];
$this->password = sha1($_POST['loginPassword']);
if(!empty($_POST['loginUsername']) OR !empty($_POST['loginPassword']))
{
$countColumns = array("COUNT(*)");
$loginTable = "users";
$postWhereCols = array("username", "password");
$loginValues = array($this->username, $this->password);
$db->selectQuery($countColumns, $loginTable, $postWhereCols, $loginValues);
if($db->sql->fetchColumn() == 1)
{
$loginColumns = array("*");
$db->selectQuery($loginColumns, $loginTable, $postWhereCols, $loginValues);
$auth = $db->sql->fetch(PDO::FETCH_ASSOC);
$_SESSION['userid'] = $auth['id'];
$_SESSION['username'] = $auth['username'];
$_SESSION['type'] = $auth['usertype_id'];
header("Location: home.php");
}
}
}
}
public function logout(){
unset($_SESSION['userid']);
unset($_SESSION['username']);
unset($_SESSION['type']);
}
}
?>