These connection names are then used to connect as required. Note that the credentials() method does not open a connection to the database so it's safe to set all your credentials up at the start of each script execution without worrying about unecesesarily connecting to a bunch of databases.

Basic Usage

Each time you do something with Plusql you use one of your configured database credential profiles. In the examples below I'll just use a variable called $profile. So for the examples below, we would have done this in order to setup a connection::

Retrieving and iterating over data

PluSQL intuits your database structure based on primary key names. Let's take the following table structure for example. If we were to give a "textual" description of this system we'd say:

We have 5 tables: strong_guy, weak_guy, french_guy, rogue_guy and is_rogue. weak_guy depends on strong_guy in a 1 to many dependent relationship. A weak_guy can be a type of french_guy in a 1 to many foreign relationship (foreign, french ... get it?). A weak_guy can be associated with any number of rogue_guys via the is_rogue table.

The table structure looks like this (note that if you're using InnoDB you will need to put UNIQUE after auto_increment when using a composite primary key):

As you can see, you are responsible for your SELECT clauses, WHERE clauses, etc. but PluSQL dramatically reduces the task of writing joins.

Although there is some stuff we can do in future releases to simplify this for common use cases, the goal is not to abstract away or prevent you from having to write SQL, just to make it more maintanable and productive.

This also automatically works for many-to-many relationships, so above we have the weak_guy and rogue_guy joined via is_rogue:

echoPlusql::from($profile)->weak_guy->rogue_guy->select('*');

This is useful because if you started off, for example, with weak_guy and rogue_guy being one to many, you could change to a many-to-many relationshp in future without having to change your SQL.

Note that you can just echo the query and the __toString() method will build the query. You can also run it:

You can also access fields on joining tables which is useful for storing "date joined" or similar, for example that last line above could be:

echo$rg->is_rogue->strong_guy_id.PHP_EOL;

If you're building a query where you need to join many tables to one table, for example our weak_guy is joined to both french_guy and rogue_guy, you pass the name of the table you wish to join to again as an argument:

Plusql::from($profile)->strong_guy->weak_guy//this will return the weak_guy table so we can join rogue_guy to it->french_guy('weak_guy')->rogue_guy

You can also do LEFT JOINS and it will automatically add an OR (primary_key IS NULL) to your ON clause:

Create and Replace

To insert from a form $_POST:

Plusql::into($profile)->table_name($_POST)->insert();

What happens is:

Any field names that are in table_name in the database will be extracted from $_POST - anything not relevant to table_name will be ignored. This is useful if you have a form that must write to more than one table.

The values are automatically quoted and escaped. The quotes are added according to the target data types in the database and all values are escaped with mysql/mysqli real_escape_string. This can be changed by passing in an anonymous function to the insert() method that accepts three arguments:

$link: a link to the database - either a mysql resource or mysqli object depending on what you have installed

$field: an associative array as loaded from a MySQL DESCRIBE query

$value: the value to be escaped

You can do multi-value inserts by passing in more than one array of values, and these can be "jagged". Any missing values that are used in one array but not another will just use the default provided by the database:

$ins=Plusql::into($profile);$some_constant=1;$names=array('name1','name2');foreach($namesas$name)$ins->table_name(array('constant'=>$some_constant,'name'=>$name));//now let's get a little jagged ....$values=array('other_field'=>'some value');$ins->table_name($values);//the first two records will use the default value for other_field provided in the database//the third record will use the default value for constant and name provided in the database$ins->insert();

In order to prevent the default filtering behaviour, eg. if you have already done your escaping and quoting somewhere else, a dummy implementation of the filter is provided:

Update

The primary difference here is the method you use to start a query which is on() instead of into(). In addition to that, you need to call where() to specify which rows to update. Although the field values are escaped the same way as for an insert/replace query, you need to manually quote/escape your where clause, but this can be done by obtaining a default escape function from Plusql:

//grab the appropriate escape function - either mysql or mysqli depending on what is installed$f=Plusql::escape($profile);//now update table_name with the data from $_POST where field_name is a value from GET. Note I have//manually quoted the value, but I'm using the anonymous function returned from Plusql::escape() to //escape the valuePlusql::on($profile)->table_name($_POST)->where('field_name = \''.$f($_GET['value']).'\'')->update();

Just as with the insert/replace queries, the update() method accepts an anonymous function that can be used to filter the data input array:

This will execute all tests. You can execute a subset by using the include directive:

php index.php Murphy include="packages/plusql/insert"

For more information on RocketSled and Murphy, check out the README files in those projects.

TODO

make from() against() into() and on() use default database credentials

Automated escaping of where clauses for select/update

come up with a good "mix in" style to cast the objects returned from the iterator
to a new class for implementing custom functionality (that one would normally include
as part of the "boilerplate" class)

custom ON clause

custom joining table

simplfying the process of including all relevant primary keys based on from clause

simplfying the default order clause by primary keys to create contiguous blocks