Format of $id and $values for Database Functions

The database functions share two common parameters.

The checkRowExists(), deleteRow(), getRow(), getRows(), setRow() and updateRow() functions all take the $id parameter, which is used to match rows.

The insertRow(), setRow() and updateRow() functions take the $values parameter, which is used to update or insert rows.

Matching Rows with $id

The $ids parameter should be an associative array of keys (column names) to values (values to search for).

If your specify more than one column an "AND" logic is used; every column specified must match in a row for that row to match. You may enter an empty array to match any row from the table.

From Zenario 7.0.0 onwards, if you do not provide an array and the table you are querying has a single-column primary key, then the CMS will assume that you are referring to this column and convert your value into an array for you. (If there is not a single-column primary key then the CMS will attempt to use a column called "id", which will result in a database error if this column does not exist.)

Setting Columns using $values

The $values parameter should be an associative array of keys (column names) to values (values to set).

When inserting a new row into a table using insertRow() or setRow(), any columns not specified will be set to their default values. (Note that if a NOT NULL column does not have a default value then this will cause a database error.)

When updating an existing row in a table using setRow() or updateRow(), any columns not specified will not be changed.

True, False and Null Values

From version 5.2.8 onwards, you can provide a null value in PHP to check for a NULL value in MySQL.

From version 6.0.3 onwards, a value of true or false will be automatically converted into a 1 or a 0 for numeric columns.

Escaping

Values entered will be automatically escaped using the mysql_real_escape_string() function where needed.

Note that this means if you wish to check for or insert a null value, you must enter a null value in PHP, rather than entering "NULL" as a string.

Advanced Matching Logic

From version 6.0.4 onwards, you may enter an array in place of a number or string for a column's value. Doing so will enable you to use logic other than an equality check.

Entering a non-associative array - or an associative array with some numeric keys - will allow you to do a MySQL IN statement, for example:

checkRowExists('content', array('status' => array('hidden', 'hidden_with_draft')))

Entering associative array with the logical operators <>, <=, <, >, >= or != as keys will allow you to check those operators against the column using their values, for example:

 checkRowExists('content', array('first_created_datetime' => array('>' => '2012-01-01')))

You can also use ! more than once to do a NOT IN, for example:

checkRowExists(
'content',
array('status' => array('!=' => 'hidden', '!2' => 'deleted', '!3' => 'trashed')))

Note that you cannot use this logic when using the setRow() function.

Using LIKE and NOT LIKE

From Zenario 7.1 onwards, you can use a LIKE and a NOT LIKE, e.g.

selectCount('content_items', array('alias' => array('LIKE' => 'fruit-%')));
checkRowExists('content_items', array('alias' => array('NOT LIKE' => '%-apple')));