Query


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.

MySQL querypdoQuery()
MySQL select query select()
MySQL insert query insert()
MySQL insert batch insertBatch()
MySQL update query update()
MySQL delete query delete()
MySQL truncate table truncate()
MySQL drop table drop()
MySQL describe table describe()
MySQL count records count()
Show/debug executed query showQuery()
Get last insert id getLastInsertId()
Get all last insert id getAllLastInsertId()
Get MySQL results results()
Get MySQL result result()
Get status of executed query affectedRows()
MySQL begin transactions start()
MySQL commit the transaction end()
MySQL rollback the transaction back()
Debugger PDO Error setErrorLog()

pdoQuery()

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()

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()

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

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

InsertBatch()

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()

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()

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()

Clears table

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

drop()

Deletes table

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

describe()

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

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

count()

Counts the number of rows in the simpler queries

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

showQuery()

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()

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()

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

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

Edit page (Query)