Creating queries for the database

The library is a Wrapper, so a class that calls a main class. Its' purpose is simplifying the handling and creating queries for the database, automating a lot of functions. A list of available methods and their descriptions below.

pdoQuery($sql = '', $bindWhereParam = [])

Basic methods for most queries

$result = $db->pdoQuery('SELECT * FROM users WHERE user_id = ?', ['1'])->result();

Note: It's important to point out that the result() method at the end returns the first row of the table. To receive all rows, you should use results() instead of result()

$results = $db->pdoQuery('SELECT * FROM users')->results();

select($table = '', $column = [], $arrayWhere = [], $other = '')

A method that's used for collection

$select = $pdo->select('users')->results();

You can use it for simpler queries with defined data and filters

// Collects all rows
$select = $pdo->select('users', '*')->results();

// Collects rows from the given columns
$select = $pdo->select('users', ['user_id', 'user_name'])->results();

insert($table, $data = [])

A method that's use only for adding data to the database

$dataArray = ['user_name' => 'Jack'];
$insert = $db->insert('users', $dataArray)->getLastInsertId();

insertBatch($table, $data = [], $safeModeInsert = true)

Works on a similar basis as insert(), but it gives us the means to add multiple items

$dataArray = [];
$dataArray[] = ['user_name' => 'Eli'];
$dataArray[] = ['user_name' => 'Jack'];
$dataArray[] = ['user_name' => 'Mati'];
$insert = $db->insertBatch('users', $dataArray)->getAllLastInsertId();

update($table = '', $data = [], $arrayWhere = [], $other = '')

The most convenient method for updating data in the whole wrapper

$dataArray = ['user_name' => 'Monana', 'user_age' => '35'];
$where = ['id' => 23];
$update = $db->update('users', $dataArray, $where)->affectedRows();

delete($table, $arrayWhere = [], $other = '')

delete is used for deleting simple data

$where = ['age' => 35];
$delete = $db->delete('test', $where)->affectedRows();

In case of deleting more complicated data, related to greater/lesser/similar we use pdoQuery with recommendation of using whereChunkString.

truncate($table = '')

Clears table

$truncate = $db->truncate('users');

drop($table = '')

Deletes table

$drop = $db->drop('users');

describe($table = '')

Shows a list of columns in the database, along with their types

$describe = $db->describe('users');

count($table = '', $where = '')

Counts the number of rows in the simpler queries

$count = $db->count('employees');
$bindWhere = ['user_name' => 'Jack'];
$count = $db->count('users', $bindWhere);

showQuery($logfile = false)

showQuery is a very useful method with big queries: thanks to it, instead of the result()/results() parameter, we use showQuery(), which shows us the Query with the basic variables.

$results = $db->pdoQuery('SELECT * FROM users')->showQuery();
echo $results;

getLastInsertId()

Returns the last row id added

$getLastInsertId = $db->insert('users', $dataArray)->getLastInsertId();
echo $getLastInsertId;

getAllLastInsertId()

Returns a table of all recently added ids for the insertBatch method.

results()

Returns data in the default array format. Also available xml/json

$data = $db->results();
$data = $db->results('xml');
$data = $db->results('json');

result($row = 0)

The same principle as results, and, as previously mentioned, returns only the first row.

$data = $db->result();
$data = $db->result('xml');
$data = $db->result('json');

affectedRows()

Returns the number of modified rows

$data = $db->affectedRows();

start()

Start of the msql transaction

$data = $db->start();

end()

End of the msql transaction

$data = $db->end();

back()

REversing the changes in case of error during start/end

$data = $db->back();

setErrorLog($mode = false)

Set to false by default during the configuration, it turns debug mode on/off

$db->setErrorLog(true);     // true/false