DB_README.md

Examples

Below some examples of the basic functions of the database class. I've included a SQL dump so you can easily test the database class functions.

The persons table

id	firstname	lastname	sex	age
1	John	Doe	M	19
2	Bob	Black	M	41
3	Zoe	Chan	F	20
4	Kona	Khan	M	14
5	Kader	Khan	M	56

Fetching everything from the table

<?php
// Fetch whole table
$persons = $db->query("SELECT * FROM persons");

Fetching with Bindings (ANTI-SQL-INJECTION):

Binding parameters is the best way to prevent SQL injection. The class prepares your SQL query and binds the parameters afterwards.

There are three different ways to bind parameters.

<?php
// 1. Read friendly method  
$db->bind("id","1");
$db->bind("firstname","John");
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id");

// 2. Bind more parameters
$db->bindMore(array("firstname"=>"John","id"=>"1"));
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname AND id = :id"));

// 3. Or just give the parameters to the method
$person   =  $db->query("SELECT * FROM Persons WHERE firstname = :firstname",array("firstname"=>"John","id"=>"1"));

More about SQL injection prevention : http://indieteq.com/index/readmore/how-to-prevent-sql-injection-in-php

Fetching Row:

This method always returns only 1 row.

<?php
// Fetch a row
$ages     =  $db->row("SELECT * FROM Persons WHERE  id = :id", array("id"=>"1"));

Result

id	firstname	lastname	sex	age
1	John	Doe	M	19

Fetching Single Value:

This method returns only one single value of a record.

<?php
// Fetch one single value
$db->bind("id","3");
$firstname = $db->single("SELECT firstname FROM Persons WHERE id = :id");

Result

firstname
Zoe

Fetching Column:

<?php
// Fetch a column
$names    =  $db->column("SELECT Firstname FROM Persons");

Result

firstname
John
Bob
Zoe
Kona
Kader

Delete / Update / Insert

When executing the delete, update, or insert statement by using the query method the affected rows will be returned.

<?php

// Delete
$delete   =  $db->query("DELETE FROM Persons WHERE Id = :id", array("id"=>"1"));

// Update
$update   =  $db->query("UPDATE Persons SET firstname = :f WHERE Id = :id", array("f"=>"Jan","id"=>"32"));


// Insert
$insert   =  $db->query("INSERT INTO Persons(Firstname,Age) VALUES(:f,:age)", array("f"=>"Vivek","age"=>"20"));

// Do something with the data 
if($insert > 0 ) {
  return 'Succesfully created a new person !';
}

Method parameters

Every method which executes a query has the optional parameter called bindings.

The row and the query method have a third optional parameter which is the fetch style. The default fetch style is PDO::FETCH_ASSOC which returns an associative array.

Here an example :

<?php
  // Fetch style as third parameter
  $person_num =     $db->row("SELECT * FROM Persons WHERE id = :id", array("id"=>"1"), PDO::FETCH_NUM);

  print_r($person_num);
  // Array ( [0] => 1 [1] => Johny [2] => Doe [3] => M [4] => 19 )
    

More info about the PDO fetchstyle : http://php.net/manual/en/pdostatement.fetch.php