php: Database Class with PDO. How can i make it better?

Question

So I started to get a little more practice in php and want to create a object oriented forum. Therefor I want to have a Database class such like:

<?php

    class Database {

        public $databaseConnection;

        function __construct(){
        $this->databaseConnection = new PDO('sqlite:test.sq3', 0, 0);
        $this->databaseConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $params = NULL;
        $pdoStatement = $this->databaseConnection->prepare('CREATE TABLE IF NOT EXISTS user(
                id INTEGER PRIMARY KEY,
                username VARCHAR(40) NOT NULL UNIQUE,
                numberoflogins INTEGER DEFAULT 0,
                bannedstatus BOOLEAN DEFAULT FALSE,
                dateofjoining TIME
            )');
        $pdoStatement->execute(array_values((array) $params));
    }

    function query($sql, $params = NULL){
        $s = $this->databaseConnection->prepare($sql);
        $s->execute(array_values((array) $params));
        return $s;
    }   

    function insert($table, $data){
        self::query("INSERT INTO $table(" . join(',', array_keys($data)) . ')VALUES('. str_repeat('?,', count($data)-1). '?)', $data);
        return $this->databaseConnection->lastInsertId();
    }
}

Then I do this in the same script:

$database = new Database();


$database->insert('user',array( 'id' => 0,
                        'username' => 'gulaschsuppe',
                        'numberoflogins' => 23,
                        'bannedstatus' => TRUE,
                        'dateofjoining' => time()));

$searchID = 0;

$userData = $database->query('SELECT username FROM user WHERE id = 0');

$username = $userData->fetchAll();

print_r(array_values($username));

?>

I just wanted to see how things working. The most important part of the code is the class. I needed a little bit time to figure out how I get the information I wanted. This is what I get.

Array ( [0] => Array ( [username] => gulaschsuppe [0] => gulaschsuppe ) ) 

Everythings working, but I don´t think this is best way to get the informations. I get an Array with an Array. Also, now there is no validation but first I want to focus on the functions query and insert.

So, can you explain how the last part with username => g. [0] => g. occured ? I would like to know how I can improve the functions and please tell me when I do something totally wrong.

Answer 1

Array (
    [0] => Array (
        [username] => gulaschsuppe
        [0] => gulaschsuppe
    )
) 

You're getting the result with both names columns (so you could do $row['username']) and numerically (so you can do $row[0]). This is the default PDO behavior. You need to use the PDO::FETCH_* constants in order to change the fetched results. PDO::FETCH_BOTH is the default value.

You can either set it when you're fetching:

$username = $userData->fetchAll(PDO::FETCH_ASSOC);

Or globally at some point:

$this->databaseConnection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

In the case that you want only a single column, you can use this fetch mode:

$usernames = $userData->fetchAll(PDO::FETCH_COLUMN);
// $usernames = array('gulaschsuppe');

This fetches only a single column for each row, without wrapping each result in another array (column 0 is fetched by default).

See the PDOStatement::fetch() documentation for more details.