Data::Links are to database connections as Syntactical Sugar is to PHP syntax: it provides an extra level of abstraction offering streamlined functionality in database communication that removes much of the drudgery and some of the challenges associated with escaping special characters often associated with SQL queries.

Initializing a Link

//Sets the connection of your MyDb1 Data:Link
Data::$Links->MyDb1 = new DataConnection(Data::Postgres, 'MyDb1', 'user');
//Note that MyDb1 is the name you which to reference your database with. It can be any valid Property name.

You can very easily create multiple Data::Links that communicate with different databases and from within your application you'll be able to easily communicate with these databases:

Data::$Links->MyDb1 = new DataConnection(Data::Postgres, 'MyDb1', 'user');
Data::$Links->MyDb2 = new DataConnection(Data::MySQL, 'MyDb2', 'user');
Data::$Links->MyDb3 = new DataConnection(Data::MSSQL, 'MyDb3', 'user');
Data::$Links->MyDb4 = new DataConnection(Data::ODBC, 'MyDb4', 'user');

In our Database documentation we showed how connections, commands, and data readers work. With Data::Links you can bypass all that, and simply communicate with your database. Using the four Data::$Links set up above, lets execute a query:

//Executes a regular SQL Query
Data::$Links->MyDb1->ExecSQL('SELECT * FROM sometable');

The above example will execute the statement, but we're not storing the results.

// The $results variable is a DataReader containing the results of the query.
$results = Data::$Links->MyDb1->ExecSQL('SELECT * FROM sometable');

Furthermore, we can use ExecSQL to pass in data or variables to our query. ExecSQL will automatically format the query properly for your replacement.

As an example, lets look at a common SQL statement:

Data::$Links->MyDb1->ExecSQL("SELECT * FROM people WHERE state = 'New York'");

Using parameters with ExecSQL we can change the above to:

Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = $1', 'New York');

We can see from above code that NOLOH will automatically replace the $1 with a properly quoted string. The variable number corresponds to the order of your parameters. For instance:

Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = $1 AND zip = $2', 'New York', '10065');
//Same as 
Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = $2 AND zip = $1','10065', 'New York');

Furthermore, ExecSQL allows you to pass in an array to replace your own tokens. Your array can be ordered or associative.

//Ordered
Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = :state', array('state', 'New York'));
//Associative
Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = :state', array('state' => 'New York'));

We can also mix and match any of the above methods:

Data::$Links->MyDb1->ExecSQL('SELECT * FROM people WHERE state = $1 zip = :zip AND is_male = :male', 'New York', array('zip', '10065'), array('male' => true));

With Data::$Links you see how easy it is to progress from setting up a database connection to communicating and retrieving results.

Taking this one step further lets say your application uses a database that supports stored procedures (a definite prerequisite in our opinion). This is usually a cumbersome task in PHP, and other platforms. Lets see how Data::$Links makes it easy to execute stored procedures.

$results = Data::$Links->MyDb1->ExecFunction('public.sp_get_user', $id);

Lets examine the above statement. ExecFunction takes in the fetch type, i.e, Data::Both, Data::Assoc, Data::Num, or the name of the stored procedure as the first parameter, and then any parameters the stored procedure expects. You can pass in as many parameters as you like, of any type you want. There's no need to add quotes, or any of the normal drudgery you're used to.

Data::$Links->MyDb1->ExecFunction('public.sp_add_user', 'Asher', 'Snyder', 10021, array(1,2,3), null);

or with fetch options

Data::$Links->MyDb1->ExecFunction(Data::Assoc, 'public.sp_add_user', 'Asher', 'Snyder', 10021, array(1,2,3), null);

The above example calls the database's sp_add_user stored procedure, which takes in a VARCHAR, VARCHAR, INTEGER, INTEGER[], and some other datatype. Rather than having to do complex escapes, or quotes around null, Data::$Links even allows for arrays (if your database supports arrays), and no complex delimiters are necessary.

These points can be demonstrated by using variables:

$firstName = 'Asher';
$lastName = 'Snyder';
$zipcode = 10021;
$roles = array(1,2,3);
$email = null;
 
Data::$Links->MyDb1->ExecFunction('public.sp_add_user', $firstName, $lastName, $zipcode, $roles, $email);

Just as with stored procedures, views can easily be called in a similar fashion:

//$results will contain a data reader containing the contents of the specified view.
$results = Data::$Links->MyDb1->ExecView('public.v_get_all_users');

In the above examples we're communicating with MyDb1, but we can just as easily communicate with our other databases the same way:

$results = Data::$Links->MyDb1->ExecView('public.v_get_all_users');
$results = Data::$Links->MyDb2->ExecView('v_get_all_users');
$results = Data::$Links->MyDb3->ExecView('v_get_all_users');
$results = Data::$Links->MyDb4->ExecView('v_get_all_users');

Result Options

Data::$Links also allows you to specify how you want your results returned. You can specify ASSOCIATED indices (Data::Assoc), NUMERICAL indices (Data::Num), or BOTH (Data::Both) which is also the default if no return type is specified.

The following are the corresponding functions in Data::Links:

ExecSQL(Data::Assoc, 'SELECT * FROM users');
ExecView(Data::Assoc, 'public.v_get_all_users');
ExecFunction(Data::Assoc, 'public.sp_add_user', $firstName, $lastName, $zipcode, $roles, $email);

ExecSQL, ExecView and ExecFunction allow for the return option as an optional first parameter

Generating Commands without executing

If you want to create a command and not execute it right away, or store it for later use, you can call the CreateCommand function:

$cmd = Data::$Links->MyDb1->CreateCommand('public.sp_add_user', $firstName, $lastName, $zipcode, $roles, $email);

The above code will generate a DataCommand for you in the same way that ExecFunction does, but without executing. This allows you to store the command for later use, or pass it around to other objects if necessary. See the DataCommand documentation for more information.

Furthermore, we can use CreateCommand to generate a command for both SQL and Views. When doing so, we need to pass in the Query type to CreateCommand as the first parameter. CreateCommand operates similar to the corresponding Exec functions. For instance, when using it in the context of SQL, or Function, you can natively pass in as many parameters to your function as you wish through the dotdotdot syntactic sugar. For instance we can use CreateCommand in the following ways:

Using CreateCommand to generate a SQL command for later use

$cmd = $Data::$Links->MyDb1->CreateCommand(Data::SQL, "SELECT * FROM people WHERE state = 'NY'");

Using CreateCommand to generate a SQL command with parameters

$cmd = $Data::$Links->MyDb1->CreateCommand(Data::SQL, "SELECT * FROM people WHERE state = $1", 'NY');

Note that the above is similar to

$state = 'NY';
$cmd = $Data::$Links->MyDb1->CreateCommand(Data::SQL, "SELECT * FROM people WHERE state = $1", $state);

Using CreateCommand to generate a View command for later use

$cmd = $Data::$Links->MyDb1->CreateCommand(Data::View, "v_get_all_people");

Similar to Exec, CreateCommand can take in an optional ResultType as the first, or second parameter:

$cmd = Data::$Links->MyDb1->CreateCommand(Data::Assoc, 'public.sp_add_user', $firstName, $lastName, $zipcode, $roles, $email);

$cmd = $Data::$Links->MyDb1->CreateCommand(Data::SQL, Data::Assoc, "SELECT * FROM people WHERE state = $1", 'NY');

Conclusion

That's all there is to it. You don't have to deal with creating your own commands, manually executing them, or any other intricacies associated with creating a proper SQL statement. Furthermore, you can communicate with your database from anywhere in your application once the connections are set; neither commands nor data readers are necessary.

NOLOH suggests you use Data::$Links for all aspects of database communication.