<?php
require_once('./classpgDB.php');
//by creating a new instance of the pgDB class we also connect to the database
$db = new pgDB('localhost', 'postgres', 'pass', 'test_db');
//this call turns on debbuging which means that on instance destruct it will print out some debug dump also with possible errors that occured during querying the database
//instead of this method you can use ShowErrors that will display only error messages. This is fine to use during development
$db->DebugOn();
//by calling of Query method you run a custom query.
$db->Query("
CREATE TABLE test_table
(
id serial NOT NULL,
nick varchar(50) NOT NULL,
name varchar(50) NOT NULL,
surname varchar(50) NOT NULL
)
WITHOUT OIDS;
ALTER TABLE test_table OWNER TO postgres;
");
//By following two methods you can lock data operations that are executed by pgDB class. There are two types of operations that can be locked.
//The first method loks all operations that would modify data where the secod locks data selection
$db->LockModData(false);
$db->LockSelData(false);
//In addition to operation locking you can use table fields locking. I found this very useful while working with access rights that required high granularity
//The first call locks one field from test_table
//The second call of this method locks two fields in mentioned table
//Every other call of this method will rewrite the previous setting so the second call in this example will release lock of field 'nick' and will lock 'name' and 'surname' fields
$db->LockTableFields(array('test_table'=>'nick'));
//or
$db->LockTableFields(array('test_table'=>array('name','surname')));
//To make life easier pgDB provides method InsertObject.
//set name of the table we will insert data in
$table = 'test_table';
//specify which fields will be inserted. This might seem redundant but by doing so you can set insert fields and take data directly from request knowing that the insert query will look like it should
$fields = array('name', 'surname');
//setup insert data as an associative array ($data might be directly replaced by REQUEST as before the insert query is build all data used in the query are escaped)
$data = array('name'=>'whatever', 'surname'=>'something');
//call the method that builds and runs the insert query
$db->InsertObject($table, $fields, $data);
//in case you need to insert more then one row...
$data[0] = array('name'=>'whatever2', 'surname'=>'something2');
$data[1] = array('name'=>'whatever3', 'surname'=>'something3');
$db->InsertObject($table, $fields, $data);
//There is a little helper for updates too...
//I works the same as the insert method with one major difference. It is able to update only same data for given where statement
$table = 'test_table';
$fields = array('surname', 'name');
$data = array('surname'=>'new_surname', 'name'=>'new_name');
$db->UpdateObject('id = 3', $table, $fields, $data);
//Deleting is easy especially in combination with the IN and NOT_IN methods. IN and NOT_IN methods do same thing as their SQL friends but also understand arrays and
//do not let in empty values.
$db->DeleteObject('id'.$db->IN('1,5'), 'test_table');
$db->DeleteObject('id'.$db->NOT_IN(array(2,3)), 'test_table');
//One nice things about all the data manipulation methods is that they automatically escape all incoming data so that you do not have to worry about SQL injection
//To make selects flexible, methods that handle selects accept as a parameter a full SQL query. On the other hand do nice job in returing the results in neat arrays.
//For best understanding run these select methods with debug on. You will get a table with data selected as they are in arrays produced by these methods.
//GetResults method returs whole set of data as an associative array.
$select_data = $db->GetResults("SELECT * FROM test_table WHERE 1 = 1");
//GetRow returns one row. In case more then one is returned by the database, only the first one is returned by the call of this method
$select_data = $db->GetRow("SELECT * FROM test_table WHERE id = 1");
//GetCol returns one column. In addition you can specify the column offset by passing second parameter to the method
$select_data = $db->GetCol("SELECT * FROM test_table WHERE 1 = 1", 1);
//GetVar returns value of a exact field of a table
//In case the query will return more more then one 1 x 1, you can specify row and column offset as additional parameters
$select_data = $db->GetVar("SELECT name FROM test_table WHERE id = 1");
//For escaping data that are passed into select queries you can use method Escape
$escaped_data = $db->Escape($_REQUEST['name']);
//Sometimes it is helpful to use IN and NOT_IN methods in select queries
$select_data = $db->GetResults("SELECT * FROM test_table WHERE name".$db->IN($_REQUEST['name_list']), 1);
//To do custom logging you need to extend the classpgDB and owervrite the method WriteError with your custom one.
?>
|
Download
