sql.class.php
sql wrapper class
Methods:
- debug - outputs debug informations
- query - executes query
- mquery - executes multi query
- error - outputs error string
- errno - outputs error number
- num_rows - gets number rows form query
- affected_rows - gets number of affetched rows
- fetch - fetching querx results
- insert_id - get insert id from query
- get_insert_id - get insert id
- get_results - array of results
- get_row - roq result
- get_num_rows - number of rows
- get_col - colone result
- get_val - one value result
- get_assoc - associated results
- escape - escape string
- close - close connection
- quote - quote array values
- prepare_data - prepare data for query
- insert - insert query
- update - update query
Connection
$dbConnect = Array( 'driver' => 'mysqli', 'pconnect' => true, 'host' => 'localhost', 'user' => 'root', 'pass' => '', 'database' => 'myportal', 'charset' => 'utf8', 'log' => false // if true log queries to error log (debugging option) ); $DB = new sql($dbConnect); // with argument $DB = new sql(); // without argument gets connection settings from global $dbConnect
debug($type=false)
if $type true return debug information otherwise echoes
$DB->query("...."); $DB->debug(); /* Debug info query no. : 15 query : SELECT articles.* FROM articles time : 0.0010349750518799 sec result : 1 error : 0 from : C:\wamp\www\myportal2\myportal\libraries\abstractsql.class.php /myportal2/index.php */ or $DB->debug = true; $DB->query("....");
query($sql)
execute query, result referenece is stored into $this->result
$DB->query("DROP TABLE xxx;");
mquery($sql)
execute multi query. queries must bi separated with ;\n or ;\r\n
$DB->mquery("DROP TABLE xxx; CREATE TABLE xxx2 ...");
error()
return error string
$DB->mquery("DROP TABLE xxx; CREATE TABLE xxx2 ..."); if($DB->error()) echo $DB->error();
errno()
return error number
$DB->query("CREATE TABLE xxx2 ..."); if($DB->errno()) echo $DB->errno();
num_rows()
return number of rows returned
$DB->query("SELECT * FROM articles"); echo $DB->num_rows();
affected_rows()
return number of rows affected
$DB->query("UPDATE articles SET published=1 WHERE ...."); echo $DB->affected_rows();
fetch($type='ASSOC',$result=null)
fetch row from result. Default type is ASSOC, type can also be NUM and BOTH
$DB->query('SELECT * FROM aaaaa'); while($row=$DB->fetch('NUM')) echo $row[0]."".$row['1']; $DB->query('SELECT * FROM aaaaa'); while($row=$DB->fetch()) echo $row[id]."".$row['name']; // external result $result = $DB->query('SELECT * FROM aaaaa'); while($row=$DB->fetch('ASSOC',$result)) echo $row[id]."".$row['name'];
insert_id()
return insert id from last insert
$DB->query('INSERT INTO table ....'); echo $DB->insert_id();
get_insert_id($sql)
executes query and return insert id
echo $DB->get_insert_id('INSERT INTO table ....');
get_results($query,$type='ASSOC',$assockey='')
executes query and return array of results. Fetch type can be defined as optional parameter. It also accept $assockey as optional parameter where you can specify colone name which goes to result key insted of incremental
print_r($DB->get_results("SELECT * FROM aaaa")); print_r($DB->get_results("SELECT * FROM aaaa",'NUM')); print_r($DB->get_results("SELECT * FROM aaaa",'ASSOC','id'));
get_row($sql,$type='ASSOC')
executes query and return row as a array, type of array can be optionaly defined
print_r( $DB->get_row("SELECT * FROM articles WHERE id='1'") );
get_num_rows($sql)
executes query and return number of rows found
echo $DB->get_num_rows("SELECT * FROM articles WHERE cid='1'");
get_col($sql)
executes query and return array of one colone results
print_r( $DB->get_col("SELECT id FROM articles WHERE cid='1'") );
get_val($sql)
executes query and return first row, first colone value
echo $DB->get_val("SELECT id FROM articles WHERE id='1'");
get_assoc($sql)
executes query and return array as first colone array key, second array value
print_r( $DB->get_assoc("SELECT id as value,name FROM articles WHERE cid='1'") );
escale($value)
return escaped value
echo $DB->escape($value);
close()
close database connection
$DB->close();
quote($data){
return array with single quoted string values
prepare_data($keys,$data){
prepare valuse by key prefixes that can be combined
? - key is only set if value == true _ - value is number : - value is date, if not set is NULL default - quoted string
insert($table,$data)
generates and executes insert query. array keys associates with table field names. function returns insert id.
$data = Array('name'=>'editor','description'=>'web'); $insert_id = $DB->insert('articles',$data);
update($table,$data,$where)
generates and executes update query. array keys associates with table field names. function returns number of affected rows.
$data = Array('name'=>'editor','description'=>'web'); $rows_updated = $DB->update('articles',$data,'WHERE id=1 LIMIT 1');
