GuidesChangelog

Mysql

Class \Lib\DB

Suggest Edits

Introduction

Your application has its own dedicated Mysql database. You can add tables to it and use them as you see fit.

The database includes the following generic tables by default:

  • apns_category_sondage : contains the groups defined following a survey
  • apns_devices : list of devices that have your application with all the information about them
  • categories_contact : list of user roles for accessing and using backoffice Appspanel
  • contact_by_app : list of users assigned to roles for accessing and using backoffice Appspanel
  • data_categories : table allowing the management of the categories in all the data modules
  • data_profiles : list of user roles for accessing and using this application
  • data_profiles_rights : list of rights attached to a profile
  • data_profiles_users : list of users assigned to a profile
  • data_pub : table for advertising management / ad-server
  • push_queue : tables for the management of push notifications
  • sondage : table for survey management
  • sondage_answer : table for the management of polls
  • sondage_answer_user : table for managing surveys

❗️

Warning

Do not remove any of the generic tables below, they are essential to the proper functioning between the application and the back office Appspanel.

Basic usage

<?php

$db = \Lib\DB::getInstance();

$db->query('
SELECT
*
FROM
`your_table`
');

$results = $db->fetchAll();

return $results;

Methods

All useful functions for using the application database.

getInstance

<?php public static function \Lib\DB::getInstance(string $encoding = 'utf8')

Description
Method that creates the unique instance of the class if it does not already exist and then returns it.

📘

Note

The default instance is the database of the current application, specified in the app.xml file, you do not need to specify any other thing to connect to it.

Parameters

  • encoding
    Type of encoding.

Return
self::$instances[$encoding]

query

<?php public function query(string $query)

Description
Prepares a query for execution.

Parameters

  • query
    The sql query.

bind

<?php public function bind($param, $value, $type = null)

Description
Associates a value with a parameter.

Parameters

  • param
    Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
  • value
    The value to bind to the param.
  • type
    Explicit data type for the parameter using the PDO::PARAM_* constants.

exec

<?php public function exec(Array $params = null)

Description
Executes a prepared statement.

Parameters

  • params
    An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
    Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.

Return
Returns TRUE on success or FALSE on failure.

fetch

<?php public function fetch(Array $params = null)

Description
Executes a statement and fetches the first row from a result set.

📘

Note

An execution is performed just before, you do not need to call the previous method.

Parameters

  • params
    An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
    Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.

Return
The return value of this function on success depends on the fetch type. In all cases, FALSE is returned on failure.

fetchAll

<?php public function fetchAll(Array $params = null)

Description
Executes a statement and returns an array containing all of the result set rows.

📘

Note

An execution is performed just before, you do not need to call the previous method.

Parameters

  • params
    An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.
    Multiple values cannot be bound to a single parameter; for example, it is not allowed to bind two values to a single named parameter in an IN() clause.

Return
Returns an array containing all of the remaining rows in the result set. The array represents each row as either an array of column values or an object with properties corresponding to each column name. An empty array is returned if there are zero results to fetch, or FALSE on failure.

lastInsertId

<?php public function lastInsertId()

Description
Returns the ID of the last inserted row or sequence value

Return
Returns a string representing the row ID of the last row that was inserted into the database.

rowCount

<?php public function rowCount()

Description
Returns the number of rows affected by the last SQL statement

Return
Returns the number of rows.

Examples

Retrieves a database item from its id

<?php
$db = \Lib\DB::getInstance();

$db->query('
SELECT
*
FROM
`your_table`
WHERE
`id` = :id
');

$db->bind(':id', $id, \PDO::PARAM_INT);
$result = $db->fetch();

return $result;

Updated over 6 years ago