Download
Topics
  1. Home
  2. Docs
  3. Manual
  4. Database

Database access

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

To get a raw PDO connection and use default PHP functions

$dbh = ipDb()->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 = ipDb()->selectAll('tableName', '*');
 
//select all records with condition
$results = ipDb()->selectAll('tableName', '*', array('age' => 25));
 
//ordering and limiting the records
$results = ipDb()->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 = ipDb()->fetchAll($sql, $condition);

Get a single row from a result set

 By table name

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

Using custom SQL query 

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

Get a single value from a result set

By table name 

$results = ipDb()->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 = ipDb()->fetchValue($sql, $params);

Select a column

 By table name

ipDb()->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 = ipDb()->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 = ipDb()->insert('example', array('firstName' => 'John', 'lastName' => 'Smith'));

Delete records

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

Update table

Increase the sallary for all Johns in table 'ip_example'

ipDb()->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`)
    )
";
ipDb()->execute($sql);

Handle database query errors

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

See also