Introduction

Database for the examples

To demonstrate, we will use a database which contains 3 SQL tables. Here they are:

Table "users"

idnamepseudoagelast_actionalive
1PaulPolo342016-01-141
2MarcelMamar752012-04-240
3JacquesJack222014-08-041
4JulieGrenouille322015-10-281
5HenriRiton302015-09-221

Table "items"

idrefFK_user_IDFK_comment_IDdate_creationcontent
1itemFlask12015-02-28["a","bit","of","json"]
2itemBall322015-07-29{"parts":22,"type":"leather","hardness":5.314,"filled":true}
3itemFlow42015-07-06[]
4itemNiuk312015-01-14{"leski":"mow","gniuk":"gniuk"}
5itemZaa532015-12-05[7,356,20,16]

Table "comments"

iddateFK_user_IDFK_item_IDtext
12015-11-2114What a nice comment, gniuk gniuk!
22015-12-0432I'm writing something about balls.
32015-12-2355Wazzaaaaa!

Configuration for the examples

Before we can use the tools, we must define the configuration contants and variables. We will use the following configuration:

/**
 * Configuration (Globals that are required by Altitude)
 */
define("HOST", "localhost");
define("USER", "username");
define("PASS", "********");
define("BASE", "database");
define("DSN",  "mysql:dbname=".BASE.";host=".HOST);

define("FOREIGNKEYS_PREFIX", "FK_");
$RELATIONS = Array(
    "FK_user_ID"	=> Array('table' => "users",	'alias' => "user"),
    "FK_item_ID"	=> Array('table' => "items",	'alias' => "item"),
    "FK_comment_ID"	=> Array('table' => "comments",	'alias' => "comment")
);
define("DATE_CREATION", "date_creation");
define("LAST_UPDATE", "last_action");
$DATE_FIELDS = Array("date", "last_action", "date_creation");

/**
 * Including classes files
 */
require("classes/Listing.class.php");
require("classes/Infos.class.php");
				

With notably:

  • The constant FOREIGNKEYS_PREFIX allows the tools to do joints automatically. This constant is the prefix used for the columns that contains the ID of relations to find.
  • These joints are described in the variable $RELATIONS : An associative array which describe the relationships between the "FK_" prefixed columns and the other tables. Each entries of this array contains the name of the source column associated to an array, whith key "table" for the destination table name, and key "alias" for the name given to the destination. Make sure the alias is different from the source column name.
  • The two constants DATE_CREATION and LAST_UPDATE are defined to use the automatic update of creation date and last update time. And, the variable $DATE_FIELDS is a list of columns which contains SQL formated dates, which may be reformated into ISO 8601.


Listing

Simple use case

Here is the most simple way to use the object "Listing":

$l = new Listing();
$users = $l->getList("users");

print_r($users);

This will return all the entries of table "users", like so:

Array (
    [0] => Array (
        [id] => 1
        [name] => Paul
        [pseudo] => Polo
        [age] => 34
        [last_action] => 2016-01-14T00:00:00+01:00
        [alive] => 1
    )
    [1] => Array ( .... )
    [2] => Array ( .... )
    [3] => Array ( .... )
    [4] => Array (
        [id] => 5
        [name] => Henri
        [pseudo] => Riton
        [age] => 30
        [last_action] => 2015-09-22T00:00:00+02:00
        [alive] => 1
    )
)

It's a simple list of all entries of the table, unsorted. Each line of this list contains all informations of each entries, on the form of a associative array.

You can use a column as index of the array (associative array). However, make sure that this column is a Unique index for the table, so as not to overwrite some lines. To use the column "name" as index for the array, simply use the method simplyList(), this way:

$l = new Listing();
$l->getList("users");

$users = $l->reindexList("users");

print_r($users);

Which will return:

Array (
    [Paul] => Array (
        [id] => 1
        [name] => Paul
        [pseudo] => Polo
        [age] => 34
        [last_action] => 2016-01-14T00:00:00+01:00
        [alive] => 1
    )
    [Marcel]  => Array ( .... )
    [Jacques] => Array ( .... )
    [Julie]   => Array ( .... )
    [Henri]   => Array (
        [id] => 5
        [name] => Henri
        [pseudo] => Riton
        [age] => 30
        [last_action] => 2015-09-22T00:00:00+02:00
        [alive] => 1
    )
)

To get only some specific columns, you must set the 2nd parameter of the method getList(), namely $want. For instance, to get only the name and pseudo of all users, we'll do:

$l = new Listing();
$users = $l->getList("users", "name,pseudo");

print_r($users);

Which will give us:

Array(
    [0] => Array (
        [name] => Paul
        [pseudo] => Polo
    )
    [1] => Array (
        [name] => Marcel
        [pseudo] => Mamar
    )
    [2] => Array (
        [name] => Jacques
        [pseudo] => Jack
    )
    [3] => Array (
        [name] => Julie
        [pseudo] => Grenouille
    )
    [4] => Array (
        [name] => Henri
        [pseudo] => Riton
    )
)

To get all columns, you just have to ommit $want, or give it the string "*" (it's the default behavior).

Sorting data

Let's see now the use of sorting. To sort data, we just have to add the parameters $sortBy and $order. Here we'll sort the list of users according to their age, from the oldest to the youngest:

$l = new Listing();
$users = $l->getList("users", "*", "age", "desc");

print_r($users);

Which will gie us:

Array (
    [0] => Array (
        [id] => 2
        [name] => Marcel
        [pseudo] => Mamar
        [age] => 75
        [last_action] => 2012-04-24T00:00:00+02:00
        [alive] => 0
    )
    [1] => Array ( ... /* Paul, age => 34 */ ... )
    [2] => Array ( ... /* Julie, age => 32 */ ... )
    [3] => Array ( ... /* Henri, age => 30 */ ... )
    [4] => Array (
        [id] => 3
        [name] => Jacques
        [pseudo] => Jack
        [age] => 22
        [last_action] => 2014-10-28T00:00:00+01:00
        [alive] => 1
    )
)

Please note that by default, the sort order is "asc" (ascending). If you want ascending, just then ignore $order, or give it the string "asc".

Filtering data

To filter data, we'll use the three parameters $filter_key, $filter_comp, and $filter_val of method getList().
Thereby, to quickly get only the users whose age exceeds or equals 34 years old, we can do:

$l = new Listing();
$users = $l->getList("users", "*", "last_action", "desc", "age", ">=", 34);

print_r($users);

It will results in:

Array (
    [0] => Array (
        [id] => 1
        [name] => Paul
        [pseudo] => Polo
        [age] => 34
        [last_action] => 2016-01-14T00:00:00+01:00
        [alive] => 1
    )
    [1] => Array (
        [id] => 2
        [name] => Marcel
        [pseudo] => Mamar
        [age] => 75
        [last_action] => 2012-04-24T00:00:00+02:00
        [alive] => 0
    )
)

So we have only the two users whose age exceeds or equals 34 years old, sorted by their last activity date ('last_action'). Please note that we have used the operand ">=". This operand corresponds to thoses used in SQL. So it's also possible to use "<=", "<", ">", "=", "!=", "LIKE", "BETWEEN", etc.

OK now, how to only get the users aged at least 30 years but who are still alive?
Here comes the multiple filtering! To do so, we'll use the method addFilter(). This method can be used as many times as we want, before the call to getList(). Once a filter is defined with this method, it's no longer necessary to use filtering inside getList().
Let's return to our example:

$l = new Listing();

$l->addFilter("age",   ">", 30);
$l->addFilter("alive", "=",  1);

$users = $l->getList("users", "*", "last_action", "desc");
print_r($users);

Which will returns:

Array (
    [0] => Array (
        [id] => 1
        [name] => Paul
        [pseudo] => Polo
        [age] => 34
        [last_action] => 2016-01-14T00:00:00+01:00
        [alive] => 1
    )
    [1] => Array (
        [id] => 4
        [name] => Julie
        [pseudo] => Grenouille
        [age] => 32
        [last_action] => 2015-10-28T00:00:00+01:00
        [alive] => 1
    )
)

Next, if for one reason or another, you must remove or modify filters, you just have to use the method resetFilter().
Example of use:

$l = new Listing();

$l->addFilter("age",   ">", 30);
$l->addFilter("alive", "=",  1);
$older_users = $l->getList("users");

$l->resetFilter();

$l->addFilter("age",   "<=", 30);
$youger_users = $l->getList("users");

Thereby the variable $older_users will contain an array with users aged at least 30 years old, but who are still alive, and the variable $youger_users will contain users whose age is below or equals to 30 years old.

It's of course possible to modify the way filters are added. By default, the operand used between each filter is "AND". You can specify any other operan with the 4th parameter, $logic ('OR', 'NAND', 'NOR'...).

Finally, if you need to use a SQL function in the filter, you have the possibility to use the method setFilterSQL().
Here is an example:

$l = new Listing();

$l->setFilterSQL("`age` >= 30 AND `last_action` <= DATE_ADD(NOW(), INTERVAL -6 MONTH)");
$users = $l->getList("users");

Which will give us the list of users aged at least 30 years old, and whose the most recent activity is at least 6 month old.
However, use this method carefully, especially if you include some variables in the parameter $filtre, because it opens possibilities of SQL injection. Make sure beforehand that the variables are safe.

Joints

To illustrate joints, let's take the table comments. This table has two columns, prefixed with string "FK_". This prefix has been given in constant FOREIGNKEYS_PREFIX. The two columns that interest us are "FK_user_ID" et "FK_item_ID". Those two columns are present in the array $RELATIONS, allowing Altitude to establish the relationship between these columns and the associated tables. If this array doesn't exists, joints will be ignored, and the columns will only contain the IDs.
Reminder of the array, written at configuration time:

$RELATIONS = Array(
    "FK_user_ID"	=> Array('table' => "users",	'alias' => "user"),
    "FK_item_ID"	=> Array('table' => "items",	'alias' => "item"),
    "FK_comment_ID"	=> Array('table' => "comments",	'alias' => "comment")
);

Right. Now, let's say we want to get the list of comments, but we want at the same time to retreive all informations of the user who wrote it, and the informations about the item which was commented on. To do so, nothing more simple:

$l = new Listing();

$comments = $l->getList("comments");
print_r($comments);

And we've got:

Array (
    [0] => Array (
            [id] => 1
            [date] => 2015-11-21T00:00:00+01:00
            [FK_user_ID] => 1
            [FK_item_ID] => 4
            [text] => What a nice comment, gniuk gniuk!
            [user] => Array
                (
                    [id] => 1
                    [name] => Paul
                    [pseudo] => Polo
                    [age] => 34
                    [last_action] => 2016-01-14
                    [alive] => 1
                )

            [item] => Array
                (
                    [id] => 4
                    [ref] => itemNiuk
                    [FK_user_ID] => 3
                    [FK_comment_ID] => 1
                    [date_creation] => 2015-01-14
                    [content] => {"leski":"mow","gniuk":"gniuk"}
                )
        )
    [1] => Array (...
    [2] => Array (...
    ... etc.

Basically, there is nothing to do, the joints are automatically resolved. Note that columns "FK_user_ID" et "FK_item_ID" are present, but the array contains two additionnal lines: "user", and "item". These key names correspond to the value "alias" of the correspondence table $RELATIONS.

If, for some reason, you don't want to get joints, you just have to set the parameter $withFK (9th position) to FALSE.



Infos

Get an entry

To retreive informations of a particular entry of a table in the database, we must first know its unique identifier, for instance its 'ID', or the name or email of an user, or the reference of an item. Indeed, if object "Infos" find several entries with the given identifier, it will throw an error.

In our example we'll use the unique identifier of the column "id". Here is how to load an entry of the database in memory:

$i = new Infos("users");

$i->loadInfos("id", "3");
$user = $i->getManyInfos();

print_r($user);

It will result in the following array:

Array (
    [id] => 3
    [name] => Jacques
    [pseudo] => Jack
    [age] => 22
    [last_action] => 2014-10-28T00:00:00+01:00
    [alive] => 1
)

Using the method getManyInfos() without parameter allows to retreive all columns of the table. It's the default behavior. However, it's possible to get only one column, thanks to paramter $column, like so:

$i = new Infos("users");
$i->loadInfos("id", 3);

$userPseudo = $i->getManyInfos("pseudo");

print_r($userPseudo);

Which will give:

Jack

... So simple. But what about joints? Just like object "Listing", joints are automatic. Thereby, when we call method loadInfos(), joints are resolved directly, and are available in memory. To show you that, let's use the same call as precedently but on the "comments" table:

$i = new Infos("comments");
$i->loadInfos("id", 3);

$comment = $i->getManyInfos();

print_r($comment);

Which gives:

Array (
    [id] => 3
    [date] => 2015-12-23T00:00:00+01:00
    [FK_user_ID] => 5
    [FK_item_ID] => 5
    [text] => Wazzaaaaa!
    [user] => Array (
        [id] => 5
        [name] => Henri
        [pseudo] => Riton
        [age] => 30
        [last_action] => 2015-09-22
        [alive] => 1
    )

    [item] => Array (
        [id] => 5
        [ref] => itemZaa
        [FK_user_ID] => 5
        [FK_comment_ID] => 3
        [date_creation] => 2015-12-05
        [content] => [7,356,20,16]
    )
)

Wonderful. This means that later in code, we can also do:

$i->getManyInfos("user")

Which will give us only one array, containing all informations about the user who wrote the comment!

Modify an entry

We will change pseudo of the user named "Marcel". To do so, we first need to load it in memory, then modify its pseudo, and finaly save the modification in the database. Here's how to:

$i = new Infos("users");
$i->loadInfos("id", 2);

$i->setInfo("pseudo", "Marcello");
$i->save();

Method setInfo() allows to add or modify a column for the entry loaded in memory. Note that if you give the name of an inexistant column for first parameter, it will be automatically added to the table's structure.
Method save() is the one which will save the entry's modification in database. Once this method has been executed, it's impossible to go back (undo it).
If you want to prevent the automatic creation of columns (in case of parameter $key of setInfo() is the name of an inexistant column), you just have to set the 3rd parameter ($autoAddCol) of save() to FALSE.

It's also possible to modify all columns at once, thanks to the method setManyInfos(). Parameter $newInfos must be an associative array, its key being the column name. For instance:

$i = new Infos("users");
$i->loadInfos("id", 2);

$newInfos = Array(
    "name" => "Marcelle",
    "pseudo" => "Marcie",
    "age" => 69
);
$i->setManyInfos($newInfos);
$i->save();

Likewise setInfo(), you must save the changes so they are reflected in database with save(). Once saved, we can't go back.

Create an entry

To insert an entry in a table of the database, simply do like precedently, but without method loadInfos(). For instance, let's add an user named "Alex" in table "users" :

$i = new Infos("users");

$newInfos = Array(
    "name" => "Alex",
    "pseudo" => "AK",
    "age" => 29
);
$i->setManyInfos($newInfos);
$i->save();

$user = $i->getManyInfos();
print_r($user);

It will result in the following entry:

Array (
    [id] => 6
    [name] => Alex
    [pseudo] => AK
    [age] => 29
    [last_action] => 0000-00-00T00:00:00+00:00
    [alive] => 0
)

You may have noticed that the two column we didn't give to the array took the default values of the SQL table.

Remove an entry

To remove an entry from database, we will use the method delete(). But be careful! This action is irreversible. For our example, we will delete the entry we just created at the previous paragraph:

$i = new Infos("users");
$i->loadInfos("id", 6);

$i->delete();

And voilĂ , our entry is gone out of the database. You can also use this function this way:

$i = new Infos("users");
$i->delete("id", 6);

This will have the same effect like precedently, but we have specified the key and value of the entry to remove directly in the method delete(), without using loadInfos(). This basic filtering mau be usefull to delete many entries at once, by specifying for instance:

$i = new Infos("users");
$i->delete("alive", 0);

This will delete all users with column "alive" set to 0.
So the use of method loadInfos() is optional, but it allows to avoid errors and to be sure to only remove one entry.