Tutorial

• include PersistClass
• open database connections
• execute SQL queries
• object persistence
• errorhandling
• transaction handling

Execute SQL queries

You can access the database through a DbConnection object. The following examples show you how to use the DbConnection to execute SQL queries and iterate through results.

Acquire a connection object

$con = DbConnectionPool::instance()->getConnection(); 

Execute an SQL query

$sql = 'INSERT INTO TESTTABLE(testcolumn) VALUES("INSERTSUCCESS")';
$con->query($sql); 

Perform a select, iterate the results

$sql = 'SELECT * FROM TESTTABLE';
$con->query($sql);
while($row = $con->next()) {
  $testColumn = $row['testcolumn'];
} 

Parse all rows into an array (memory consuming if there are many rows in the result)

$rows = $con->queryResultArr($sql); 

Select a single row

$row = $con->queryFirstRow($sql); 

Select a single object (first column of first row)

$sql = 'SELECT count(*) FROM TESTTABLE'; 
$count = $con->queryFirstRowCol($sql);

Select an array containing all rows' first columns

$sql = 'SELECT TESTID FROM TESTTABLE';
$ids = $con->queryFirstColumnSet($sql); 

Retrieving ID of last inserted row

$sql = 'INSERT INTO TESTTABLE(TESTCOLUMN) VALUES("INSERTSUCCESS")';
$con->query($sql);
$lastId = $con->getLastId(); 

Escaping database input

$dangerousData = '"; DROP TABLES';
$sql = 'INSERT INTO TESTTABLE(TESTCOLUMN) VALUES('. $con->escape($dangerousData) .')'; 

Retrieving database link

$dbLink = $con->getLink();

Note: error handling is described in the error handling section

Check out the next example on how to

• map PHP classes to SQL tables (object persistence)