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 theWHERE
clause$statement->order($rule, $order)
– Sets theORDER BY
clause$statement->rand()
– Randomizes order.$statement->limit($limit, $offset)
– Sets theLIMIT
andOFFSET
clauses
Available only for SELECT statements:
$statement->group($expr, $having)
– Sets theGROUP BY
clause and optionalyHAVING
$statement->join($expr, $cond)
– PerformsJOIN
on tables$statement->leftJoin($expr, $cond)
– PerformsLEFT JOIN
on tables$statement->innerJoin($expr, $cond)
– PerformsINNER 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 |
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 |