ia doc & examples
Index
Examples List
/**
* mysqli db interface and convience methods.
*
* @version 1.0.2
* 2017-05-20 rjsb add selectMultiKey functions
* 2018-05-23 rjsb close mysqli on destruct, on some php errors connection not closing
*//**
* IaMysqli::__construct()
* constructor.
*
* @param string $host host to connect, defaults to php.ini setting
* @param string $username login, to connect, defaults to php.ini setting
* @param string $passwd password, to connect, defaults to php.ini setting
* @param string $dbname databasename
* @param string $port to connect, defaults to php.ini setting
* @param string $socket socket to use for connection, defaults to php.ini setting
* @return void
*//**
* IaMysqli::__destruct()
* Resests mysqli_report_mode to MYSQLI_REPORT_OFF
*
* @return void
*//**
* Adds connection information & credentials for database connection to the connection array.
*
* @param string $host host to connect, defaults to php.ini setting
* @param string $username login, to connect, defaults to php.ini setting
* @param string $passwd password, to connect, defaults to php.ini setting
* @param string $dbname databasename
* @param string $port to connect, defaults to php.ini setting
* @param string $socket socket to use for connection, defaults to php.ini setting
* @return void
*
*//**
* A description of the connection.
*
* @param array $con connection info
* @return string text display for connection info
*//**
* connect or reconnect to the db.
*
* @return bool true connected, false connection failed
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::reconnect()
*
* @return bool true on success, false on failure
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::close()
* close db connection, silently.
*
* @return bool true on success, false on failure
*//**
* start or begin a transaction.
*
*
* @param int $flags MYSQLI_TRANS_START_READ_WRITE, MYSQLI_TRANS_START_READ_ONLY, MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT
* @return bool true ok, false error encountered
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::commit()
* commit a transaction.
*
* @return bool true ok, false error encountered
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*
*//**
* IaMysqli::rollback()
* rollback a transaction.
*
* @return bool true ok, false error encountered
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::autocommit()
* set autocommit.
*
* @param bool $mode true set autocommit, false no autocommit
* @param string $comment
* @return int sucess
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::transaction()
* Runs commands in an array as a transaction running first begin() and after the last command commit or rollback on Sql error
* If servers sends a "rollbacked, resend transaction error" the entire transaction will be resent upto $this->retries times.
*
* @param array $sqlArray an array of Sql commands
* if the index is a string from then on {{key}} will be replaced by that commands lastInsertId
* @return bool true transaction succeeded, false failed
*
*
*
* @example
*
* $Sql->transaction( array(
* "UPDATE person SET title = 'Mr' WHERE id = 2",
* "INSERT INTO person(name, relationship) VALUES('Susan', 'My wife')"
* "UPDATE person SET spouse_id = 'ella' WHERE id=1",
* ));
* Will run within a transaction issuing a commit at the end or rollback on Sql error
* If servers sends a "rollbacked, resend transaction error" the entire transaction will be resent upto $this->retries times
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::queryArray()
* Execues all queries in $sqlArray, substituing string keys for result if $doSubstitute=true.
*
* @param array $sqlArray an array of Sql commands
* if the index is a string from then on {{key}} will be replaced by that commands lastInsertId, when $doSubstitute=true
* for begin/commit use instead transaction()
* @return bool true success, false failed
*
* @example $Sql->queryArray( array(
* "UPDATE person SET title = 'Mr' WHERE id = 2",
* 'SPOUSE' => "INSERT INTO person(name, relationship) VALUES('Susan', 'My wife')"
* "UPDATE person SET spouse_id = 'gato' WHERE id=1",
* ));
* Will run all commands, stopping if an Sql error is encountered
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::query()
* Executes a query like update/insert/delete.
*
* @param string $sql string: Sql command, mysqli_stmt binded statement
* @param int $resultmode MYSQLI_STORE_RESULT [default], MYSQLI_USE_RESULT may use less memory
* @return mixed bool|array, true success, false on error, associative array if Sql returns result object
*
* @example query("UPDATE table SET col1=1 WHERE table_id=1");
* returns true on success, false on Sql error
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::insertAndGetId()
* Executes a query (normally insert) if succesfull returns last inserted id, false on error.
*
* @param string $sql
* @return int last inserted id or false on error
* @throws IacSqlException
*//**
* IaMysqli::single_read()
* read first column of first returned row.
*
* @param string $sql Sql command
* @param string $onNotFound on no rows found return $onNotFound defaults to ''
* @return string first column of first row in select written in $Sql, on empty returns $onNotFound
*
* @exmpale single_read('SELECT name, last_name from person ORDER BY id')
* returns 'Dana'
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::singleton()
* reads first row.
*
* @param string $sql Sql command
* @param array $onNotFound on no rows found return $onNotFound defaults to array()
* @param int $resultType MYSQLI_ASSOC [default], MYSQLI_NUM, or MYSQLI_BOTH
* @return mixed first row of select command in $Sql, on empty returns $onNotFound, false on error
*
* @exmpale singleton('SELECT id, name, last_name from person ORDER BY id')
* returns array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith')
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*
*//**
* IaMysqli::singleton_full()
* reads first row, on not found returns fields with '' as value.
*
* @param string $sql Sql command
* @param mixed $onNull on Null fill value with
* @return mixed first row of select command in $Sql, on empty returns $onNotFound, false on error
*
* @exmpale singleton_full('SELECT id, name, last_name from person ORDER BY id')
* returns array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith')
*
*
* @exmpale singleton_full('SELECT id, name, last_name from person WHERE id = -99.9 ORDER BY id -- non existant id')
* returns array('id'=>'', 'name'=>'', 'last_name'=>'')
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::selectVector()
* returns select as a vector.
*
* @param string $sql Sql command
* @param mixed $onNotFound on no rows returned return $onNotFound
* @return mixed array(col_1_row_1, col_1_row_2, ...) or false on error
*
* @exmpale selectVector('SELECT id, name, last_name from person ORDER BY id')
* returns array( 1, 3 )
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::selectKeyValue()
* returns select as key value array.
*
* @param string $sql Sql command
* @param mixed $onNotFound on no rows returned return $onNotFound
* @return array|bool array or false on error
*
* @exmpale selectArrayIndex('SELECT id, name, last_name from person ORDER BY id')
* returns array( 1=>'Dana', 3=>'Paul' )
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::selectArrayKey()
* returns select indexed by key.
*
* @param string $sql Sql command
* @param string $key for associate array
* @param mixed $onNotFound on no rows returned return $onNotFound
* @param int $resultType
* @return array associative array indexed by $key, each entry contains a selected row
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
* @exmpale selectArrayKey('SELECT id, name, last_name from person ORDER BY id', 'id')
* returns array(1=>array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith'),
* 3=>array('id'=>3, 'name'=>'Paul', 'last_name'=>'Jones')
* )
*
*//**
* IaMysqli::selectArrayIndex()
* returns selecet as a numeric array.
*
* @param string $sql Sql command
* @param mixed $onNotFound on no rows returned return $onNotFound
* @param int $resultType MYSQLI_ASSOC [default], MYSQLI_NUM, or MYSQLI_BOTH
* @return mixed false on error
*
* @exmpale selectArrayIndex('SELECT id, name, last_name from person ORDER BY id')
* returns array(0=>array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith'),
* 1=>array('id'=>3, 'name'=>'Paul'', 'last_name'=>'Jones')
* )
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* @param $table
* @param $UniquekeyValue
* @param $primaryKeyValue
* @param string $QueryComment
* @return string
* @throws IacSqlException
*//**
* IaMysqli::last_insert_id()
* returns last inserted id (auto increment value).
*
* @return int last inserted id
*//**
* IaMysqli::found_rows()
* returns found rows for last select.
*
* @return int Last select found rows, if issueed with SQL_CALC_FOUND_ROWS
*
* @example
* $db->singleton("SELECT SQL_CALC_FOUND_ROWS col1,col2 FROM table WHERE col1=1 LIMIT 1");
* $db->found_rows() => returns number of rows matching query, not considering limit clause
*
* @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error
*//**
* IaMysqli::metaData_get()
* Get metadata on last select query
*
* @return array field info, metadata, of last select query
*
*
*//**
* IaMysqli::metaData_clear()
* Clears last metadata retreived
*
* @return void
*
*//**
* IaMysqli::throwSqlException_set()
*
*
* @param bool|int $mysqli_report_mode true throws excpetions setting: MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, , functions with errors trhow
* false sets MYSQLI_REPORT_OFF, functions with errors return false
* int sets $mysqli_report_mode
* options: MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ALL | MYSQLI_REPORT_OFF
* @return void
*//**
* IaMysqli::throwSqlException_get()
* Does mysqli throw exceptions for Sql errors
*
* @return bool true mysqli_driver sends exceptions for Sql errors, false no exceptions thrown
*//**
* IaMysqli::trace_get()
* Returns an array with Sql commands issued
*
* @return array log of Sql commands issued
*//**
* IaMysqli::log_trace()
* Stores issued Sql commands in
*
* @param string $sql command or message to store in trace
* @param int $retries number of times this command has been re-sent to the server
* @return void
* @see IaMysqli::$traceOn IaMysqli::$traceOn
*//**
* IaMysqli::errorLog_get()
* Returns an array with the Sql errors
*
* @return array Sql errors
*//**
* IaMysqli::begins_get()
* Return number of begin transactions waiting a commit or rollback statement
*
* @return int number of begin transactions waiting a commit or rollback statement
*//**
* IaMysqli::log_sql_error()
* Stores an error for later processing
*
* @param string $sql Sql command that caused the error
* @param int $retries number of times this command has been re-sent to the server
* @param bool $putMysqliError true include the error from mysqli, false the error is included in $Sql
* @return void
* @see IaMysqli::$errorLog IaMysqli::$errorLog
*//**
* $arr[key_1][...]['key_'.$numKeys]=$col[$numKeys+1] or =[colName=>v,...] if total columns > $numKeys+1
*
* @param string|array $sql
* @param int $numKeys
* @param int $resultType MYSQLI_ASSOC || MYSQLI_NUM
* @return array |bool $arr[key_1][...]['key_'.$numKeys]=$col[$numKeys+1] or =[colName=>v,...] if total columns > $numKeys+1
* @throws IacSqlException
*//**
* IaMysqli::prepare()
*
* @param string $sql
* @return bool false on failure, prepared mysqli_stmt on success
*//**
* $mysqli_options
*
* @var array $mysqli_options, change before connecting, default: [[MYSQLI_OPT_CONNECT_TIMEOUT, 5], [MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT=1'] ),
*
* @link http://php.net/manual/en/mysqli.options.php
* @link http://php.net/manual/en/mysqli.constants.php
*//**
* Character set for connection, usually set to the same as database's characterset.
*
* @var string $charset connection character set, defaults to ut8, change before connecting, default utf8
*//**
* $coalition
* Coalition for conection, usually set to the same coallition as database's.
*
* @var string $coalition connection coalition defaults to ut8_general_ci, change before connecting, default: utf8_general_ci
*//**
* $metDataOn
* On true stores field_info of last select statement
*
* @var bool $metaDataOn on true store field_info for each query, default false
* @see IaMysqli::metaData_get() IaMysqli::metaData_get()
*//**
* $traceOn
* True stores Sql commands.
*
* @var bool $traceOn on true keep log of Sql commands issued, default false
*//**
* $register_warnings
* On true stores last 5 warnings.
*
* @var bool $register_warnings on true keep small log of Sql warnings
*
*//**
* $mysqli
* php class mysqli for connection
*
* @var object $mysqli mysqli instance used
*//**
* $affected_rows
* Affected rows of last SQL statement (insert, update, delete, ...)
*
* @var int $affected_rows number of affected rows by last command
*//**
* $num_rows
* Rows retreived in last select stament
*
* @var int $num_rows number of retreived rows by last command
*//**
* $autocommitDefault
* After a commit or rollback resets autocommit mode to this value
*
* @var bool $autocommitDefault default true. autocommit mode to set on connect, reconnect, commit & rollback
*//**
* $retries
* Number of times to resend transaction (or single query outside a transaction) when instructed by server
*
* @var int $retries number of retries to do, default 3
*//**
* $connInfo
* Connection info and credentials
*
* @var array $connInfo
* @see IaMysqli::connectionInfo_set IaMysqli::connectionInfo_set
*//**
* $connected_to
* Last connected to, connection info and credentials.
*
* @var array $connected_to
* @see IaMysqli::real_connect IaMysqli::real_connect
*//**
* $retry_usleep
* milli-seconds to wait before re-issuing statements or trying to reconnect to db
*
* @var int $retry_sleep number milli-seconds to wait before re-issuing statements
*//**
* $preparedLast
* Last prepared statement's Sql command
*
* @var string $preparedLast last prepared Sql command
*
* @see IaMysqli::prepare() IaMysqli::prepare()
*/Parse error: syntax error, unexpected token "=" in /lamp/www/iaLib/ia_examples/code/ia/Sql/Mysql/IaMysqli_example_code.php on line 524 |