PtcDb User Guide

MYSQL QUERY HELPER - VERSION 0.7

Introduction

The goal of this class is to ease the process of parsing and manipulating data with mysql queries.Methods from this class allow to access mysql tables with just 2 or 3 lines of code.

Main Features:

Fast and clean programming for mysql queries
Stores references of queries for later use
Implements mysql_real_escape_string in every query(to prevent sql injection)
Uses the PtcDebug class for logging all queries

Getting Started

To get started, we need to initialize the class and connect to the db.Next will try to show off the methods so that we can have a basic understanding of the possibilities that this class offers.

Connecting To Database

To establish a connection, we use the method dbConnect(), after initializing the class.

# initialize the class
require_once('../PtcDb.php');
$ptc=new PtcDb();
# connect to db
$ptc->dbConnect($db['host'],$db['user'],$db['pass'],$db['database']);

Closing The Connetion

The method dbClose() will close the connection to the db. If we used this class to connect, there is no need to use the "$dbLink" parameter.

# close connection
$ptc->dbClose();

Selecting Data

The following examples, will show us how to select data with the help of this class.

Read Row

The method readRow() is used to retrieve 1 row from any given table. It will return an array or null if query was empty.

# query based on record id
$field_id=1;
$data=$ptc->readRow("test_table",$field_id);
print "readRow() result(query based on record id)";
print "<pre>".print_r($data,true)."</pre>";
# single value search
$value="somevalue1";
$data=$ptc->readRow("test_table","field1:".$value);
print "readRow() result(single value search)";
print "<pre>".print_r($data,true)."</pre>";
# array of values
$fields=array("field1"=>"somevalue1","field2"=>"somevalue2");
$data=$ptc->readRow("test_table",$fields);
print "readRow() result(array of values)<pre>".print_r($data,true)."</pre>";

Read Table

The method readTable() is used to retrieve multiple rows from any given table. It will return a 2 dimensions array filled with rows or null if query was empty.

# all rows
$data=$ptc->readTable("test_table");
print "readTable() result(all records)";
print "<pre>".print_r($data,true)."</pre>";
# single value search
$value="somevalue1";
$data=$ptc->readTable("test_table","field1:".$value);
print "readTable() result(single value search)";
print "<pre>".print_r($data,true)."</pre>";
# array of values search
$fields=array("field1"=>"somevalue1","field2"=>"somevalue2");
$data=$ptc->readTable("test_table",$fields);
print "readTable() result(array of values)";
print "<pre>".print_r($data,true)."</pre>";
# order and limit
$order="ORDER BY `id` ASC";
$limit="LIMIT 2";
$data=$ptc->readTable("test_table","",$order,$limit);
print "readTable() result(with order and limit)";
print "<pre>".print_r($data,true)."</pre>";
# count rows for last select query
echo "number of rows (select statement): ".$ptc->countRows()." ";

Retrieve only 1 Value

The method goFast() is used to retrieve only 1 field value, from a single record. It uses the method readRow() but only returns the specified field value(record id by default).it is particularly useful when we are in a hurry and only need to retrieve 1 value based on 1 field query.

# returns 1 value instead of an array like readRow()
$value="somevalue1";
$data=$ptc->goFast("test_table","field1",$value,"field2");
echo "goFast() result: ".$data." ";

Count Rows

The method countRows() will count the rows from a select statement, that has been defined with "$ref" variable(defaults to last select).

# order and limit
$order="ORDER BY `id` ASC";
$limit="LIMIT 2";
$data=$ptc->readTable("test_table","",$order,$limit);
print "readTable() result(with order and limit)";
print "<pre>".print_r($data,true)."</pre>";
# count rows for last select query
echo "number of rows (select statement): ".$ptc->countRows()." ";

Manipulating Data

The following examples will demonstrate how we can use this class to manipulate data in the database.

Insert Row

The method insertRow() can be used to insert rows in any given table.

# single row insert
$ins['field1']="somevalue1";
$ins['field2']="somevalue2";
$ptc->insertRow("test_table",$ins);
# multiple rows insert
$arr1=array("field1"=>"somevalue3","field2"=>"somevalue5");
$arr2=array("field1"=>"somevalue4","field2"=>"somevalue6");
$arr3=array($arr1,$arr2); # 2 dimensions array
foreach($arr3 as $ins){ $ptc->insertRow("test_table",$ins); }

Update Row

The method updateRow() can be used to update rows in any given table.

# single row update
$up['field1']="somevalue5";
$up['field2']="somevalue6";
$record_id=1; # the record id
$ptc->updateRow("test_table",$up,$record_id);
# multiple rows update
$arr1=array("field1"=>"somevalue7","id"=>1);
$arr2=array("field1"=>"somevalue8","id"=>2);
$arr3=array($arr1,$arr2); # 2 dimensions array
foreach($arr3 as $up){ $ptc->updateRow("test_table",$up,$up['id']); }

Delete Row

The method deleteRow() can be used to delete rows in any given table.

# single row
$record_id=1; # the record id
$ptc->deleteRow("test_table",$record_id);
# multiple rows
$arr3=array("2","3"); # array of id's
foreach($arr3 as $k=>$v){ $ptc->deleteRow("test_table",$v); }

Last Inserted Id

With the lastId() method, we can retrieve the last id of an insert query.

# multiple rows insert
$arr1=array("field1"=>"somevalue3","field2"=>"somevalue5");
$arr2=array("field1"=>"somevalue4","field2"=>"somevalue6");
$arr3=array($arr1,$arr2); # 2 dimensions array
foreach($arr3 as $ins){ $ptc->insertRow("test_table",$ins); }
# get last inserted id
echo "last inserted id is: ".$ptc->lastId()." ";

More Complex Queries

This chapter will try to cover circumstances where we will need to use more complex queries.

SqlToArray

The sqlToArray() method, can be used to execute any type of select query, with a custom statement. It willl return a 2 dimensions array or null if query was empty. This method covers any type of query where the return should be an array.

# just a select statement
$sql="SELECT `field1` FROM test_table WHERE `field2` != 'somevalue'";
$data=$ptc->sqlToArray($sql); # return a 2 dimensions array
print "sqlToArray() result(complex select)<pre>".print_r($data,true)."</pre>";

ExecuteSql

The method executeSql(), can execute any query, and will return the result.

# just an update query
$sql="UPDATE test_table SET `field1` = 'somevalue9' WHERE `field2` = 'somevalue'";
$ptc->executeSql($sql);

# drop example table
$ptc->executeSql("DROP TABLE IF EXISTS `test_table`");
# create example table
$ptc->executeSql("CREATE TABLE `test_table`
(
`id` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(`id`),`field1` varchar(15),`field2` varchar(15)
)");

Using References

The "$ref" parameter can be used to retrieve more information about a query, as show in the examples below.

# just a select query
$data=$ptc->readTable("test_table",'','','',"testReference");
print "readTable() result adding reference \"testReference\"";
print "<pre>".print_r($data,true)."</pre>";
# DO MORE QUERIES HERE
# count rows from a select query with reference
echo "countRows (select statement) with reference: ";
echo $ptc->countRows("testReference")." ";
# show field id of first record with reference
echo "show field1 of second record with reference(mysql_resultt): ";
echo mysql_result($ptc->queryResults["testReference"],1,1)." ";
# show number of fields
$num_fields=mysql_num_fields($ptc->queryResults["testReference"]);
echo "number of fields in previous query \"testReference\": ";
echo $num_fields." ";
# get fields information
echo "dump fields information (mysql_fetch_field) \"testReference\": ";
for($a=0;$a<$num_fields;$a++)
{
echo "<pre>";
var_dump(mysql_fetch_field($ptc->queryResults["testReference"],$a));
echo "</pre>";
}

mySqlFlag Option

The variable mySqlFlag controls the mode for mysql.You can change the default with the following flags:

MYSQL_BOTH
MYSQL_ASSOC
MYSQL_NUM

It is adviced to use "MYSQL_ASSOC".

	PhpToolCase

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