Quick API reference

MDB_QueryTool available methods

MDB_QueryTool provides the following methods:

• autoJoin()

  Join the given tables, using the column names, to find out how to join the tables; i.e., if table1 has a column named "table2_id", this method will join "WHERE table1.table2_id=table2.id". All joins made here are only concatenated via AND.

• getDbInstance()

  Return a PEAR::DB object

• setDbInstance()

  Pass an existing PEAR::DB object to MDB_QueryTool

• get($id, $column)

  Get the data of a single entry. If the second parameter is only one column, the result will be returned directly, not as an array!

• getMultiple($ids, $column)

  Same as get(), but for all the elements in the $ids array.

• getAll()

  Get all the entries from the db.

• getCol($column)

  This method only returns one column, so the result will be a one dimensional array. This does also mean that using setSelect() should be set to *one* column, the one you want to have returned. A common use case for this could be:

  $table->setSelect('id'); $ids = $table->getCol(); //OR $ids = $table->getCol('id');

  so ids will be an array with all the id's.

• getCount()

  Get the number of entries.

• getDefaultValues()

  return an empty element where all the array elements do already exist corresponding to the columns in the DB

• getQueryString()

  Render the current query and return it as a string.

• save($data)

  Save data, calls either update() or add(). If the primaryCol is given in the data this method knows that the data passed to it are meant to be updated (call update()), otherwise it will call the method add(). If you don't like this behaviour simply stick with the methods add() and update() and ignore this one here. This method is very useful when you have validation checks that have to be done for both adding and updating, then you can simply overwrite this method and do the checks in here, and both cases will be validated first.

• update($data)

  Update the member data of a data set.

• add($data)

  Add a new member in the db.

• addMultiple($data)

  Adds multiple new members in the db.

• remove($data, $whereCol)

  Removes a member from the db. data is the value of the column that shall be removed (integer/string); if an array is used, it must contain multiple columns that shall be matched (in this case, the second parameter will be ignored); $whereCol: the column to match the data against, only if data is not an array

• removeAll()

  Empty a table.

• removeMultiple($ids, $colName)

  Remove the datasets with the given ids. If colName is set, it is used as the primary key column name.

• removePrimary($ids, $colName, $atLeastOneObject)

  Removes a member from the db and calls the remove() methods of the given objects so all rows in another table that refer to this table are erased too.

• setLimit($from=0, $count=0)

  Set the limits for the following query.

• getLimit()

  Get the limits for the following query.

• setWhere($whereCondition)

  Sets the where condition which is used for the current instance.

• getWhere()

  Gets the where condition which is used for the current instance.

• addWhere($whereCondition, $condition)

  Adds a string to the where clause. The default condition is AND.

• addWhereSearch($column, $stringToSearch, $condition)

  Add a where-like clause which works like a search for the given string; i.e. calling it like this:

  $this->addWhereSearch('name', 'otto hans')

  produces a where clause like this one

  UPPER(name) LIKE "%OTTO%HANS%"

  so the search finds the given string.

• setOrder($orderCondition, $desc=FALSE)

  Sets the order condition which is used for the current instance.

• getOrder()

  Gets the order condition which is used for the current instance.

• addOrder($orderCondition, $desc=FALSE)

  Adds an order parameter to the query.

• setHaving($havingCondition)

  Sets the having condition which is used for the current instance.

• getHaving()

  Gets the having condition which is used for the current instance.

• addHaving($what, $connectString)

  Adds an having parameter to the query.

• setJoin($table, $where, $joinType)

  Sets the join condition which is used for the current instance.

• setLeftJoin($table, $where)

  Sets a left join on $this->table.

• addLeftJoin($table, $where, $type)

  Adds a left join to the query.

• setRightJoin($table, $where)

  Sets a right join on $this->table.

• getJoin($what)

  Gets the join-condition.

• addJoin($table, $where, $type)

  adds a table and a where clause that shall be used for the join instead of calling

  setJoin(array(table1, table2), '<where clause1> AND <where clause2>');

  you can also call

  setJoin(table1,'<where clause1>'); addJoin(table2,'<where clause2>');

• setTable($table)

  Sets the table this class is currently working on.

• getTable()

  Gets the table this class is currently working on.

• setGroup($group)

  Sets the group-by condition.

• getGroup()

  Gets the group-by condition.

• setSelect($what)

  Limit the result to return only the columns given in what.

• addSelect($what, $connectString)

  Add a string to the select-part of the query and connects it to an existing string using the connectString, which by default is a comma. ("SELECT xxx FROM..." xxx is the select-part of a query)

• getSelect()

  Gets the select-part of the query.

• setDontSelect($what)

  Exclude some columns from the resultset.

• getDontSelect()

  Gets the columns excluded from the resultset.

• reset($what)

  Reset all the set* settings, with no parameter given it resets them all.

• setOption($option, $value)

  Set mode the class shall work in. The 'raw' mode does not quote the data before building the query

• getOption($option)

  Get the given option.

• debug($string)

  override this method and i.e. print the queryString to see the final query.

• getTableShortName($table)

  Gets the short name for a table.

• execute($query, $method)

  Execute a query (the current query is executed when query is null.

• writeLog($text)

  Write events to the logfile. It does some additional work, like time measuring etc. to see some additional info.

• returnResult($result)

  Return the chosen result type

• setIndex($key)

  Format the result to be indexed by key. NOTE: be careful, when using this you should be aware, that if you use an index which's value appears multiple times you may loose data since a key can't exist multiple times! The result for a result to be indexed by a key(=columnName) (i.e. 'relationtoMe') which's values are 'brother' and 'sister' or alike normally returns this:

  $res['brother'] = array('name' => 'xxx'); $res['sister'] = array('name' => 'xxx');

  but if the column 'relationtoMe' contains multiple entries for 'brother' then the returned dataset will only contain one brother, since the value from the column 'relationtoMe' is used and which 'brother' you get depends on a lot of things, like the sort order, how the db saves the data, and whatever else. You can also set indexes which depend on 2 columns, simply pass the parameters like 'table1.id,table2.id' it will be used as a string for indexing the result and the index will be built using the 2 values given, so a possible index might be '1,2' or '2108,29389' this way you can access data which have 2 primary keys. Be sure to remember that the index is a string!

• getIndex()

  Gets the index.

• useResult($type)

  Choose the type of the returned result ('array', 'object', 'none')

• setErrorCallback($param)

  Set both callbacks.

• setErrorLogCallback($param)

  Set the error log callback.

• setErrorSetCallback($param)

  Set the error set callback.