Neevo

Tiny database layer for PHP

Documentation – Basic manipulation

Installation

When you download and unpack the Neevo, you can see the following structure:

Neevo/
  neevo/
    drivers/
      mysql.php
      ...
    Neevo\Result.php
    ...
  loader.php
  neevo.min.php
  readme.txt
  license.txt

To load Neevo into your project, all you have to do is to include the loader.php file. All Neevo classes are automatically loaded by an autoloader when they are needed, so you don't need to care about that.

require_once './libs/Neevo/loader.php';

There is also a special one-file-only minified version of Neevo prepared in the neevo.min.php file. So instead of uploading the whole directory to FTP, you can upload just that one file. However, in development mode, it's recommended to use the full version since the minified version is not very human-readable (there's no whitespace and comments there).

Connecting to database

Example first:

$neevo = new Neevo\Manager(array(
    'driver'   => 'MySQLi',
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '******'
    'database' => 'business'
));

To connect to the database, you need to specify a database driver and its specific options. You can also specify some global options, here is a list of them:

Option Meaning Example
(string) tablePrefix Self-explanatory. 'wp_'
(bool) lazy Establish connection on the first query.
(bool) detectTypes Automaticaly detect and convert column types.
(string) formatDateTime Format of returned date/time values. Empty for DateTime instance, 'U' for timestamp. 'Y-m-d H:i:s'
(string) rowClass Name of a class to use as a returned row class. 'MyRowClass'

Getting data from database

90% of time, you need to retrieve some data from database. Neevo provides great tools for this task. Let's begin with a simple example:

// SELECT `id`, `name`, `mail` FROM `users` LIMIT 20;
$result = $neevo->select(':id, :name, :mail', ':users')->limit(20);

foreach($result as $user){
    echo "No. $user->id: $user->name, $user->mail";
}

Output:

No. 1: John Doe, john.doe@example.net
No. 2: Jack Foo, jackfoo@example.org
...
No. 20: ...

We have just specified fields to retireve from table called users and limited the number of rows to 20. Than we have iterated through the result with a foreach loop, like with an array. Rows are retrieved as instances of Neevo\Row class, or whatever class you specified in connection settings (rowClass option).

Neevo\Row

You can access values of rows as common object properties ($row->value) but also as values of array, if you prefer this way ($row['value']). Neevo\Row is also countable and iterable, so you can do count($row) and also foreach($row as $field => $value).

If you want to retrieve all fields from a table, you can skip the first argument of select() method:

// SELECT * FROM `users`
$neevo->select('users');
...

Identifier escaping

Notice the colon (:) before each field/table name in the previous example. It's there to identify a SQL identifier, properly delimite it and apply table prefix if defined. It's not required to write the colon everywhere – Neevo can guess what's an identifier in some cases, but not everytime, so it's highly recommended to use the colon to avoid problems.

Inserting and modifying data

Those remaining 10% is inserting and/or modifying data in databases. Neevo comes to help.

$data = array(
    'name' => 'Albert Einstein',
    'mail' => 'abletr.e@example.com',
    'profession' => 'physicist'
);

// INSERT INTO `users` (`name`, `mail`, `profession`) VALUES ('Albert Einstein', 'abletr.e@example.com', 'physicist');
$neevo->insert('users', $data)->run();

Whoops, misspelled the e-mail? Let's fix it.

// UPDATE `users` SET `mail` = `albert.e@example.com` WHERE (`name` = 'Albert Einstein');
$neevo->update('users', array('mail' => 'albert.e@example.com'))
      ->where('name', 'Albert Einstein')
      ->run();

Ok, but I don't like physics. Let's remove all physicists.

// DELETE FROM `users` WHERE (`profession` = 'physicist');
$neevo->delete('users')->where('profession', 'physicist')->run();

To discover how many rows were affected, you can use affectedRows() method. You can use it instead of run(), because the query will be automatically executed before returning number of affected rows.

For inserts, you can use insertId() method to discover the INSERT ID of the inserted row.

Statement clauses

I'm sure you have noticed use of method where() to set the WHERE SQL clause. There are more similar methods:

Available for SELECT, UPDATE and DELETE statements:

  • $statement->where($expr[, $...]) – Sets the WHERE clause
  • $statement->order($rule, $order) – Sets the ORDER BY clause
  • $statement->rand() – Randomizes order.
  • $statement->limit($limit, $offset) – Sets the LIMIT and OFFSET clauses

Available only for SELECT statements:

  • $statement->group($expr, $having) – Sets the GROUP BY clause and optionaly HAVING
  • $statement->join($expr, $cond) – Performs JOIN on tables
  • $statement->leftJoin($expr, $cond) – Performs LEFT JOIN on tables
  • $statement->innerJoin($expr, $cond) – Performs INNER JOIN on tables

Let's talk about them more. They all provide fluent interface, so you can write them one after another.

$statement->order()

This one takes up to two arguments – first beeing a field to sort by, second beeing a sort direction: Manager::ASC or Manager::DESC. More calls to this method append new rules.

// SELECT * FROM `users` ORDER BY `id` ASC, `name`;
$neevo->select('users')
      ->order(':id', Manager::ASC)
      ->order(':name');

$statement->rand()

Simply randomizes order of the result in a driver-specific way. This also removes all other order rules.

// MySQL:  SELECT * FROM `users` ORDER BY RAND();
// SQLite: SELECT * FROM [users] ORDER BY RANDOM();
$neevo->select('users')->rand();

$statement->limit()

Limits the result and optionally offset it. More calls override previous settings.

// SELECT * FROM `users` LIMIT 5;
$neevo->select('users')->limit(5);

// SELECT * FROM `users` LIMIT 5 OFFSET 10;
$neevo->select('users')->limit(5, 10);

$statement->group()

The method is used to group result by given field, and optionaly can set the HAVING clause with the second argument. More calls override previous settings.

// SELECT `profession`, SUM(`monthly_fee`) AS `sum_fee` FROM `users` GROUP BY `profession` ORDER BY `sum_fee` DESC;
$neevo->select(':profession, SUM(:monthly_fee) AS :sum_fee', 'users')
      ->group(':profession')
      ->order(':sum_fee', Manager::DESC);

Joins

You can of course write more complex statements, for example those joining more tables together. Available JOIN methods are join(), leftJoin() and innerJoin(), all with the same arguments. More calls of course create more joins.

// SELECT `articles`.*, `users`.`name` AS `user_name` FROM `articles`
// LEFT JOIN `users` ON `articles`.`user_id` = `users`.`id`;
$neevo->select(':articles.*, :users.name AS :user_name', 'articles')
      ->leftJoin(':users', ':articles.user_id = :users.id');

WHERE filters – $statement->where()

This is a bit complex method. When calling $statement->where()->where(), conditions will be joined with default AND glue. When you call $statement->where()->or(), conditions are joined with OR glue. There is also and() method.

This method can be used in two ways – Let's call them simple mode and modifier mode.

Simple mode

In simple mode, the first argument is a field, and the second is a value. Possible combinations are:

Condition SQL code
where('field') WHERE ( field )
where('field', true) WHERE ( field )
where('field', false) WHERE ( NOT field )
where('field', null) WHERE ( field IS NULL )
where('field', 'value') WHERE ( field = 'value' )
where('field', array(1, 2)) WHERE ( field IN(1, 2) )
where('field', new Neevo\Literal('NOW()')) WHERE ( field = NOW() )

Modifier mode

Modifier mode is used if there is a ‚%‘ (percent) sign detected in the first argument. In this mode, first argument represents a SQL command with modifiers instead of real values which are then given as following arguments. These values are then properly type-casted and inserted to statement.

Condition SQL code
where(':field != %s', 'value') WHERE ( field != 'value' )
where(':field != %s OR :field < %i', 'value', 15) WHERE ( field != 'value' OR field < 15 )
where(':field LIKE %s', '%value%') WHERE ( field LIKE '%value%' )
where(':field NOT %a', array(1, 2)) WHERE ( field NOT IN(1, 2) )

Possible modifiers:

Modifier Type
%b Boolean value
%i Integer
%f Float
%s String
%bin Binary data
%d Date/time
%a Array
%l SQL literal value
% No type specified

Data retrieval »

Fork me on GitHub