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

so started little more practice in php , want create object oriented forum. therefor want have 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 $table(" . join(',', array_keys($data)) . ')values('. str_repeat('?,', count($data)-1). '?)', $data);         return $this->databaseconnection->lastinsertid();     } } 

then in 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 user id = 0');  $username = $userdata->fetchall();  print_r(array_values($username));  ?> 

i wanted see how things working. important part of code class. needed little bit time figure out how information wanted. get.

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

everythings working, don´t think best way informations. array array. also, there no validation first want focus on functions query , insert.

so, can explain how last part username => g. [0] => g. occured ? know how can improve functions , please tell me when totally wrong.

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

you're getting result both names columns (so $row['username']) , numerically (so can $row[0]). default pdo behavior. need use pdo::fetch_* constants in order change fetched results. pdo::fetch_both default value.

you can either set when you're fetching:

$username = $userdata->fetchall(pdo::fetch_assoc); 

or globally @ point:

$this->databaseconnection->setattribute(pdo::attr_default_fetch_mode, pdo::fetch_assoc); 

in case want single column, can use fetch mode:

$usernames = $userdata->fetchall(pdo::fetch_column); // $usernames = array('gulaschsuppe'); 

this fetches single column each row, without wrapping each result in array (column 0 fetched default).

see pdostatement::fetch() documentation more details.