MDB_QueryTool
[ class tree: MDB_QueryTool ] [ index: MDB_QueryTool ] [ all elements ]

Class: MDB_QueryTool_Query

Source Location: /MDB_QueryTool-1.2.3/MDB/QueryTool/Query.php

Class Overview


MDB_QueryTool_Query class


Author(s):

Copyright:

  • 2004-2008 Lorenzo Alberton

Variables

Methods


Child classes:

MDB_QueryTool_EasyJoin
MDB_QueryTool_EasyJoin class

Inherited Variables

Inherited Methods


Class Details

[line 56]
MDB_QueryTool_Query class

This class should be extended



[ Top ]


Class Variables

$db =  null

[line 78]

  • Var: db-object, a PEAR::MDB[2] object instance

Type:   object the


[ Top ]

$primaryCol =  'id'

[line 63]

  • Var: the name of the primary column

Type:   string


[ Top ]

$sequenceName =  null

[line 73]

  • Var: the name of the sequence for this table

Type:   string


[ Top ]

$table =  ''

[line 68]

  • Var: the current table the class works on

Type:   string


[ Top ]

$tableSpec = array()

[line 181]

this array contains information about the tables those are
  • 'name' => the real table name
  • 'shortName' => the short name used, so that when moving the table i.e.

onto a provider's db and u have to rename the tables to longer names this name will be relevant, i.e. when autoJoining, i.e. a table name on your local machine is: 'user' but online it has to be 'applName_user' then the shortName will be used to determine if a column refers to another table, if the colName is 'user_id', it knows the shortName 'user' refers to the table 'applName_user'


Type:   mixed


[ Top ]



Method Detail

MDB_QueryTool_Query (Constructor)   [line 274]

MDB_QueryTool_Query MDB_QueryTool_Query( [mixed $dsn = false], [array $options = array()], [integer $MDBversion = 1])

Constructor
  • Access: public

Parameters:

mixed   $dsn     DSN string, DSN array or MDB object
array   $options     db options
integer   $MDBversion     MDB version (1=MDB, 2=MDB2)

[ Top ]

add   [line 768]

mixed add( array $newData)

add a new member in the DB
  • Return: the inserted id on success, or false otherwise
  • Access: public

Parameters:

array   $newData     contains the new data that shall be saved in the DB

[ Top ]

addHaving   [line 1232]

void addHaving( [string $what = '*'], [string $connectString = ' AND '])

Extend the current HAVING clause. This is very useful, when you are building this clause from different places and don't want to overwrite the currently set HAVING clause, but extend it.
  • Access: public

Parameters:

string   $what     this is a HAVING clause, i.e. 'column' or 'table.column' or 'MAX(column)'
string   $connectString     the connection string, which usually stays the default, which is ',' (a comma)

[ Top ]

addJoin   [line 1406]

void addJoin( string $table, string $where, [string $type = 'default'])

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>') or where it makes more sense is to build a query which is made out of a left join and a standard join setLeftJoin(table1,'<where clause1>') // results in ... FROM $this->table LEFT JOIN table ON <where clause1> addJoin(table2,'<where clause2>') // results in ... FROM $this->table,table2 LEFT JOIN table ON <where clause1> WHERE <where clause2>

  • Access: public

Parameters:

string   $table     the table to be joined
string   $where     the where clause for the join
string   $type     the join type

[ Top ]

addLeftJoin   [line 1312]

void addLeftJoin( string $table, string $where, [string $type = 'left'])

Add a LEFT JOIN clause
  • Access: public

Parameters:

string   $table     the table(s) to be left-joined
string   $where     the where clause for the join
string   $type     join type

[ Top ]

addMultiple   [line 819]

mixed addMultiple( array $data)

adds multiple new members in the DB
  • Return: the inserted IDs on success, or false otherwise
  • Access: public

Parameters:

array   $data     contains an array of new data that shall be saved in the DB the key-value pairs have to be the same for all the data!!!

[ Top ]

addOrder   [line 1162]

void addOrder( [string $orderCondition = ''], [boolean $desc = false])

Add a ORDER BY parameter to the query.
  • Access: public

Parameters:

string   $orderCondition     the ORDER BY condition
boolean   $desc     sorting order (TRUE => ASC, FALSE => DESC)

[ Top ]

addSelect   [line 1512]

void addSelect( [string $what = '*'], [string $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)

  • Access: public

Parameters:

string   $what     the string that shall be added to the select-part
string   $connectString     the string to connect the new string with the existing one

[ Top ]

addWhere   [line 1090]

void addWhere( string $where, [string $condition = 'AND'])

Only adds a string to the WHERE clause
  • Access: public

Parameters:

string   $where     the WHERE clause to add to the existing one
string   $condition     the condition for how to concatenate the new WHERE clause to the existing one

[ Top ]

addWhereSearch   [line 1117]

void addWhereSearch( string $column, string $string, [string $connectString = 'AND'])

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

  • Access: public

Parameters:

string   $column     the column to search in for
string   $string     the string to search for
string   $connectString     the condition for how to concatenate the new WHERE clause to the existing one [default AND]

[ Top ]

connect   [line 318]

void connect( mixed $dsn, [array $options = array()], [integer $MDBversion = 1])

use this method if you want to connect manually

Parameters:

mixed   $dsn     DSN string, DSN array or MDB object
array   $options     db options
integer   $MDBversion     MDB version (1=MDB, 2=MDB2)

[ Top ]

debug   [line 1773]

void debug( string $string)

overwrite this method and i.e. print the query $string to see the final query
  • Access: public

Parameters:

string   $string     the query mostly

[ Top ]

execute   [line 2449]

resultSet execute( [string $query = null], [string $method = 'queryAll'])

Execute the query
  • Return: or false on error
  • Access: public

Parameters:

string   $query     querystring
string   $method     method

[ Top ]

get   [line 395]

mixed get( integer $id, [string $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!
  • Return: (1) an array of the retrieved data (2) if the second parameter is given and its only one column, only this column's data will be returned (3) false in case of failure
  • Access: public

Parameters:

integer   $id     the id of the element to retrieve
string   $column     if this is given only one row shall be returned, directly, not an array

[ Top ]

getAll   [line 476]

mixed getAll( [int $from = 0], [int $count = 0], [string $method = 'getAll'])

get all entries from the DB for sorting use setOrder!!!, the last 2 parameters are deprecated
  • Return: an array of the retrieved data, or false in case of failure when failing an error is set in $this->_error
  • Access: public

Parameters:

int   $from     to start from
int   $count     the number of rows to show
string   $method     the MDB[2]-method to use

[ Top ]

getCol   [line 506]

mixed getCol( [string $column = null], [int $from = 0], [int $count = 0])

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 most 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

  • Return: an array of the retrieved data, or false in case of failure when failing an error is set in $this->_error
  • Access: public

Parameters:

string   $column     the column that shall be retrieved
int   $from     to start from
int   $count     the number of rows to show

[ Top ]

getCount   [line 532]

mixed getCount( )

get the number of entries
  • Return: an array of the retrieved data, or false in case of failure when failing an error is set in $this->_error
  • Access: public

[ Top ]

getDbInstance   [line 349]

object MDB &getDbInstance( )

Get a MDB[2] object instance
  • Return: Object
  • Access: public

[ Top ]

getDefaultValues   [line 576]

array getDefaultValues( )

return an empty element where all the array elements do already exist corresponding to the columns in the DB
  • Return: an empty, or pre-initialized element
  • Access: public

[ Top ]

getDontSelect   [line 1561]

string getDontSelect( )

Get the columns that shall not be selected
  • Access: public

[ Top ]

getEmptyElement   [line 596]

array getEmptyElement( )

this is just for BC
  • Deprecated:

[ Top ]

getGroup   [line 1477]

string getGroup( )

gets the group condition which is used for the current instance
  • Return: the group condition
  • Access: public

[ Top ]

getHaving   [line 1211]

string getHaving( )

gets the HAVING definition which is used for the current instance
  • Return: the HAVING definition
  • Access: public

[ Top ]

getIndex   [line 2687]

string getIndex( )

get index
  • Return: index
  • Access: public

[ Top ]

getJoin   [line 1353]

array getJoin( [string $what = null])

gets the join-condition
  • Return: gets the join parameters
  • Access: public

Parameters:

string   $what     [null|''|'table'|'tables'|'right'|'left'|'inner']

[ Top ]

getLimit   [line 1037]

array getLimit( )

gets query limits
  • Return: (start index, number of results)
  • Access: public

[ Top ]

getMultiple   [line 429]

mixed getMultiple( array $ids, [string $column = ''])

gets the data of the given ids
  • Return: an array of the retrieved data, or false in case of failure when failing an error is set in $this->_error
  • Access: public

Parameters:

array   $ids     this is an array of ids to retrieve
string   $column     the column to search in for

[ Top ]

getOne   [line 455]

mixed getOne( )

get the first value of the first row
  • Return: (1) a scalar value in case of success (2) false in case of failure
  • Access: public

[ Top ]

getOption   [line 1635]

string getOption( string $option)

Get the given option
  • Return: value of the option
  • Access: public

Parameters:

string   $option     name of the option to retrieve

[ Top ]

getOrder   [line 1181]

string getOrder( )

gets the ORDER BY condition which is used for the current instance
  • Return: the ORDER BY condition, this can be complete like 'ID,TIMESTAMP DESC'
  • Access: public

[ Top ]

getQueryString   [line 609]

string getQueryString( )

Render the current query and return it as a string.
  • Return: the current query

[ Top ]

getSelect   [line 1531]

string getSelect( )

Get the SELECT clause
  • Access: public

[ Top ]

getTable   [line 1443]

string getTable( )

gets the table this class is currently working on
  • Return: the table name
  • Access: public

[ Top ]

getTableShortName   [line 1996]

string getTableShortName( string $table)

Gets the short name for a table

get the short name for a table, this is needed to properly build the 'AS' parts in the select query

  • Return: the table's short name
  • Access: public

Parameters:

string   $table     the real table name

[ Top ]

getTableSpec   [line 2024]

array getTableSpec( [boolean $shortNameIndexed = true], [array $tables = array()])

gets the tableSpec either indexed by the short name or the name returns the array for the tables given as parameter or if no parameter given for all tables that exist in the tableSpec
  • Return: the tableSpec indexed
  • Access: public

Parameters:

boolean   $shortNameIndexed     if true the table is returned indexed by the shortName, otherwise indexed by the name
array   $tables     table names (not the short names!)

[ Top ]

getWhere   [line 1072]

string getWhere( )

Gets the WHERE condition which is used for the current instance
  • Return: the WHERE condition, this can be complete like 'X=7 AND Y=8'
  • Access: public

[ Top ]

metadata   [line 1795]

resultSet metadata( [string $table = ''])

!!!! query COPIED FROM db_oci8.inc - from PHPLIB !!!!
  • Return: or false on error
  • See: db_oci8.inc - PHPLIB
  • Access: public

Parameters:

string   $table     table to get the metadata for

[ Top ]

newEntity   [line 2859]

new newEntity( )

Returns a new entity including an instance to QueryTool
  • Return: entity
  • Access: public

[ Top ]

remove   [line 899]

boolean remove( mixed $data, [string $whereCol = ''])

removes a member from the DB
  • Access: public

Parameters:

mixed   $data     
    • array: multiple columns that shall be matched, the second parameter will be ignored
    string   $whereCol     the column to match the data against, only if $data is not an array

    [ Top ]

    removeAll   [line 937]

    resultSet removeAll( )

    empty a table
    • Return: or false on error [execute() result]
    • Access: public

    [ Top ]

    removeMultiple   [line 955]

    resultSet removeMultiple( array $ids, [string $colName = ''])

    remove the datasets with the given IDs
    • Return: or false on error [execute() result]
    • Access: public

    Parameters:

    array   $ids     the IDs to remove
    string   $colName     the column to match the data against

    [ Top ]

    removePrimary   [line 987]

    boolean removePrimary( integer $id, string $colName, object $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
    • Access: public

    Parameters:

    integer   $id     the value of the primary key
    string   $colName     the column name of the tables with the foreign keys
    object   $atLeastOneObject     just for convinience, so nobody forgets to call this method with at least one object as a parameter

    [ Top ]

    reset   [line 1577]

    void reset( [array $what = array()])

    reset all the set* settings; with no parameter given, it resets them all.
    • Access: public

    Parameters:

    array   $what     settings to reset

    [ Top ]

    returnResult   [line 2558]

    mixed returnResult( object $result)

    Return the chosen result type
    • Return: [boolean, array or object]
    • Access: public

    Parameters:

    object   $result     object reference

    [ Top ]

    save   [line 688]

    mixed save( array $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 dont 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.

    • Return: the data returned by either add or update-method
    • Access: public

    Parameters:

    array   $data     contains the new data that shall be saved in the DB

    [ Top ]

    setDbInstance   [line 366]

    void setDbInstance( object &$dbh)

    Setup using an existing connection.

    this also sets the MDB[2]_FETCHMODE_ASSOC since this class needs this to be set!


    Parameters:

    object   &$dbh     a reference to an existing DB-object

    [ Top ]

    setDontSelect   [line 1547]

    void setDontSelect( [string $what = ''])

    Set the columns that shall not be selected
    • Access: public

    Parameters:

    string   $what     what should NOT be selected

    [ Top ]

    setErrorCallback   [line 2737]

    void setErrorCallback( [string $param = ''])

    set both callbacks
    • Access: public

    Parameters:

    string   $param     callback function name

    [ Top ]

    setErrorLogCallback   [line 2753]

    void setErrorLogCallback( [string $param = ''])

    set error log callback

    Parameters:

    string   $param     callback function name

    [ Top ]

    setErrorSetCallback   [line 2769]

    void setErrorSetCallback( [string $param = ''])

    set error set callback

    Parameters:

    string   $param     callback function name

    [ Top ]

    setGroup   [line 1459]

    void setGroup( [string $group = ''])

    sets the group-by condition
    • Access: public

    Parameters:

    string   $group     the group condition

    [ Top ]

    setHaving   [line 1197]

    void setHaving( [string $having = ''])

    sets the HAVING definition
    • Access: public

    Parameters:

    string   $having     the HAVING definition

    [ Top ]

    setIndex   [line 2659]

    void setIndex( [string $key = null])

    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 cant 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 sortorder, 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!

    • Access: public

    Parameters:

    string   $key     key to use as index

    [ Top ]

    setJoin   [line 1255]

    void setJoin( [mixed $table = null], [string $where = null], [string $joinType = 'default'])

    sets a join-condition
    • Access: public

    Parameters:

    mixed   $table     either a string or an array that contains the table(s) to join on the current table
    string   $where     the WHERE clause for the join
    string   $joinType     the JOIN type

    [ Top ]

    setLeftJoin   [line 1294]

    void setLeftJoin( [string $table = null], [string $where = null])

    if you do a left join on $this->table you will get all entries

    from $this->table, also if there are no entries for them in the joined table if both parameters are not given the left-join will be removed NOTE: be sure to only use either a right or a left join

    • Access: public

    Parameters:

    string   $table     the table(s) to be left-joined
    string   $where     the where clause for the join

    [ Top ]

    setLimit   [line 1019]

    void setLimit( [integer $from = 0], [integer $count = 0])

    sets query limits
    • Access: public

    Parameters:

    integer   $from     start index
    integer   $count     number of results

    [ Top ]

    setOption   [line 1614]

    void setOption( string $option, mixed $value)

    set mode the class shall work in currently we have the modes: 'raw' does not quote the data before building the query
    • Access: public

    Parameters:

    string   $option     the mode to be set
    mixed   $value     the value of the mode

    [ Top ]

    setOrder   [line 1145]

    void setOrder( [string $orderCondition = ''], [boolean $desc = false])

    sets the ORDER BY condition which is used for the current instance
    • Access: public

    Parameters:

    string   $orderCondition     the ORDER BY condition
    boolean   $desc     sorting order (TRUE => ASC, FALSE => DESC)

    [ Top ]

    setReturnClass   [line 2587]

    boolean setReturnClass( string $name)

    Sets the name of the class to use as result object
    • Return: true on success, false otherwise
    • Access: public

    Parameters:

    string   $name     class name

    [ Top ]

    setRightJoin   [line 1337]

    void setRightJoin( [string $table = null], [string $where = null])

    see setLeftJoin for further explaination on what a left/right join is

    NOTE: be sure to only use either a right or a left join //FIXXME check if the above sentence is necessary and if SQL doesn't //allow the use of both


    Parameters:

    string   $table     the table(s) to be right-joined
    string   $where     the where clause for the join

    [ Top ]

    setSelect   [line 1493]

    void setSelect( [string $what = '*'])

    limit the result to return only the columns given in $what
    • Access: public

    Parameters:

    string   $what     fields that shall be selected

    [ Top ]

    setTable   [line 1429]

    void setTable( string $table)

    sets the table this class is currently working on
    • Access: public

    Parameters:

    string   $table     the table name

    [ Top ]

    setWhere   [line 1054]

    void setWhere( [string $whereCondition = ''])

    Sets the WHERE condition which is used for the current instance
    • Access: public

    Parameters:

    string   $whereCondition     the WHERE condition, this can be complete like 'X=7 AND Y=8'

    [ Top ]

    update   [line 708]

    boolean update( array $newData)

    update the member data of a data set
    • Return: true on success, or false otherwise
    • Access: public

    Parameters:

    array   $newData     contains the new data that shall be saved in the DB the id has to be given in the field with the key 'ID'

    [ Top ]

    useResult   [line 2705]

    void useResult( [string $type = 'array'])

    Choose the type of the returned result
    • Access: public

    Parameters:

    string   $type     ['array' | 'object' | 'none'] For BC reasons, $type=true is equal to 'array', $type=false is equal to 'none'

    [ Top ]

    writeLog   [line 2497]

    void writeLog( [string $text = 'START'])

    Write events to the logfile.

    It does some additional work, like time measuring etc. to see some additional info

    • Access: public

    Parameters:

    string   $text     log message

    [ Top ]

    _checkColumns   [line 1734]

    array _checkColumns( array $newData, [integer $method = 'unknown'])

    checks if the columns which are given as the array's indexes really exist if not it will be unset anyway
    • Access: public

    Parameters:

    array   $newData     array of data to save
    integer   $method     method name

    [ Top ]

    _makeIndexed   [line 2608]

    mixed &_makeIndexed( mixed &$data)

    Make data indexed
    • Return: or array $indexedData
    • Access: public

    Parameters:

    mixed   &$data     data to index

    [ Top ]


    Documentation generated on Wed, 11 Apr 2012 22:30:06 +0000 by phpDocumentor 1.4.3. PEAR Logo Copyright © PHP Group 2004.