Database access

constructQuery() function provides an instance of \Construct\Db class for connecting to the database and executing SQL queries.

To get a raw PDO connection and use default PHP functions

$dbh = constructQuery()->getConnection();

or use several handy functions to make your queries shorter and easier

Select multiple records

Use selectAll to fetch data without writing an SQL Query

//select all records from ip_tableName table. Prefix will be added automatically
$results = constructQuery()->selectAll('tableName', '*');

//select all records with condition
$results = constructQuery()->selectAll('tableName', '*', array('age' => 25));

//ordering and limiting the records
$results = constructQuery()->selectAll('tableName', '*', array('age' => 25), ' ORDER BY age LIMIT 5');

Use fetchAll function if you want to have full controll over SQL query

$table = ipTable('example');
$sql = "SELECT `firstName` FROM $table WHERE `lastName` = :lastName";
$condition = array('lastName' => 'Smith');
$results = constructQuery()->fetchAll($sql, $condition);

Get a single row from a result set

By table name

$results = constructQuery()->selectRow('tableName');
$results = constructQuery()->selectRow('tableName', '*', array('id' => 25));

Using custom SQL query

$table = ipTable('example');
$sql = "SELECT `firstName`, `lastName` FROM $table WHERE id = :id";
$results = constructQuery()->fetchRow($sql, array('id' => 25));

Get a single value from a result set

By table name

$results = constructQuery()->selectValue('table', 'firstName', array('id' => 25));

Using custom SQL query

$table = ipTable('example');
$sql = "SELECT `firstName` FROM $table WHERE `id` = :id";
$params = array('id' => 25);
$results = constructQuery()->fetchValue($sql, $params);

Select a column

By table name

constructQuery()->selectColumn('example', 'firstName', array('age' => 25));

Using custom SQL query

$table = ipTable('example');
$sql = "SELECT `firstName` FROM $table WHERE `age` = :age";
$params = array('age' => 25);
$results = constructQuery()->fetchValue($sql, $params);

By default fetchColumn fetches the first column of a result set. Specify a column name as second argument, if needed.

Insert a new record

/** Insert `John Smith` into example table */
$personId = constructQuery()->insert('example', array('firstName' => 'John', 'lastName' => 'Smith'));

Delete records

constructQuery()->delete('example', array('age' => 25));

Update table

Increase the sallary for all Johns in table 'ip_example'

constructQuery()->update(
    'example', 
    array('salary' => 40000),
    array('FirstName' => 'John')
);

Execute SQL query

/** Create example table */
$table = ipTable('example');
$sql = "
    CREATE TABLE IF NOT EXISTS $table
    (
    `PersonId` int(11) NOT NULL AUTO_INCREMENT,
    `FirstName` varchar(255),
    `LastName` varchar(255),
    PRIMARY KEY (`PersonId`)
    )
";
constructQuery()->execute($sql);

Handle database query errors

        try{
            constructQuery()->execute($sql);
        }catch (\Construct\DbException $e){
            ipLog()->log('yourPluginGroup/yourPluginName', 'Error while executing my database query: '.$e);
        }

Database access

See also