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
• getinsertid - get insert id
• get_results - array of results
• get_row - roq result
• getnumrows - 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

{{lang:php}}
$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) {#debug}

if $type true return debug information otherwise echoes

{{lang:php}}
$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) {#query}

execute query, result referenece is stored into $this->result

{{lang:php}}
$DB->query("DROP TABLE xxx;");

mquery($sql) {#mquery}

execute multi query. queries must bi separated with ;\n or ;\r\n

{{lang:php}}
$DB->mquery("DROP TABLE xxx; CREATE TABLE xxx2 ...");

error() {#error}

return error string

{{lang:php}}
$DB->mquery("DROP TABLE xxx; CREATE TABLE xxx2 ...");
if($DB->error()) echo $DB->error();

errno() {#errno}

return error number

{{lang:php}}
$DB->query("CREATE TABLE xxx2 ...");
if($DB->errno()) echo $DB->errno();

numrows() {#numrows}

return number of rows returned

{{lang:php}}
$DB->query("SELECT * FROM articles");
echo $DB->num_rows();

affectedrows() {#affectedrows}

return number of rows affected

{{lang:php}}
$DB->query("UPDATE articles SET published=1 WHERE ....");
echo $DB->affected_rows();

fetch($type='ASSOC',$result=null) {#fetch}

fetch row from result. Default type is ASSOC, type can also be NUM and BOTH

{{lang:php}}
$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'];

insertid() {#insertid}

return insert id from last insert

{{lang:php}}
$DB->query('INSERT INTO table ....');
echo $DB->insert_id();

getinsertid($sql) {#getinsertid}

executes query and return insert id

{{lang:php}}
echo $DB->get_insert_id('INSERT INTO table ....');

getresults($query,$type='ASSOC',$assockey='') {#getinsert_id}

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

{{lang:php}}
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'));

getrow($sql,$type='ASSOC') {#getrow}

executes query and return row as a array, type of array can be optionaly defined

{{lang:php}}
print_r( $DB->get_row("SELECT * FROM articles WHERE id='1'") );

getnumrows($sql) {#getnumrows}

executes query and return number of rows found

{{lang:php}}
echo $DB->get_num_rows("SELECT * FROM articles WHERE cid='1'");

getcol($sql) {#getcol}

executes query and return array of one colone results

{{lang:php}}
print_r( $DB->get_col("SELECT id FROM articles WHERE cid='1'") );

getval($sql) {#getval}

executes query and return first row, first colone value

{{lang:php}}
echo $DB->get_val("SELECT id FROM articles WHERE id='1'");

getassoc($sql) {#getassoc}

executes query and return array as first colone array key, second array value

{{lang:php}}
print_r( $DB->get_assoc("SELECT id as value,name FROM articles WHERE cid='1'") );

escale($value) {#escale}

return escaped value

{{lang:php}}
echo $DB->escape($value);

close() {#close}

close database connection

{{lang:php}}
$DB->close();

quote($data){ {#quote}

return array with single quoted string values

preparedata($keys,$data){ {#preparedata}

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) {#insert}

generates and executes insert query. array keys associates with table field names. function returns insert id.

{{lang:php}}
$data = Array('name'=>'editor','description'=>'web');
$insert_id = $DB->insert('articles',$data);

update($table,$data,$where) {#update}

generates and executes update query. array keys associates with table field names. function returns number of affected rows.

{{lang:php}}
$data = Array('name'=>'editor','description'=>'web');
$rows_updated = $DB->update('articles',$data,'WHERE id=1 LIMIT 1');