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

PtcDb User Guide

MYSQL QUERY HELPER - VERSION 0.8.3

Table of Contents

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.

  1. # initialize the class
  2.     require_once('../PtcDb.php');
  3.     $ptc=new PtcDb();            
  4.     
  5.     # connect to db
  6.     $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.

  1. # close connection
  2.     $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.

  1. # query based on record id
  2.     $field_id=1;
  3.     $data=$ptc->readRow("test_table",$field_id);    
  4.     print "<b>readRow() result(query based on record id)</b>";
  5.     print "<pre>".print_r($data,true)."</pre>";    
  6.     # single value search
  7.     $value="somevalue1";
  8.     $data=$ptc->readRow("test_table","field1:".$value);
  9.     print "<b>readRow() result(single value search)</b>";
  10.     print "<pre>".print_r($data,true)."</pre>";    
  11.     # array of values
  12.     $fields=array("field1"=>"somevalue1","field2"=>"somevalue2");
  13.     $data=$ptc->readRow("test_table",$fields);
  14.     print "<b>readRow() result(array of values)</b><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.

  1. # all rows
  2.     $data=$ptc->readTable("test_table");                            
  3.     print "<b>readTable() result(all records)</b>";
  4.     print "<pre>".print_r($data,true)."</pre>";
  5.     # single value search
  6.     $value="somevalue1";
  7.     $data=$ptc->readTable("test_table","field1:".$value);
  8.     print "<b>readTable() result(single value search)</b>";
  9.     print "<pre>".print_r($data,true)."</pre>";
  10.     # array of values search
  11.     $fields=array("field1"=>"somevalue1","field2"=>"somevalue2");
  12.     $data=$ptc->readTable("test_table",$fields);
  13.     print "<b>readTable() result(array of values)</b>";
  14.     print "<pre>".print_r($data,true)."</pre>";
  15.     # order and limit
  16.     $order="ORDER BY `id` ASC";
  17.     $limit="LIMIT 2";
  18.     $data=$ptc->readTable("test_table","",$order,$limit);                            
  19.     print "<b>readTable() result(with order and limit)</b>";
  20.     print "<pre>".print_r($data,true)."</pre>";
  21.     # count rows for last select query
  22.     echo "<b>number of rows (select statement):</b> ".$ptc->countRows()."<br><br>";


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.

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


Count Rows

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

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


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.

  1. # single row insert
  2.     $ins['field1']="somevalue1";
  3.     $ins['field2']="somevalue2";
  4.     $ptc->insertRow("test_table",$ins);
  5.     # multiple rows insert
  6.     $arr1=array("field1"=>"somevalue3","field2"=>"somevalue5");
  7.     $arr2=array("field1"=>"somevalue4","field2"=>"somevalue6");
  8.     $arr3=array($arr1,$arr2);    # 2 dimensions array
  9.     foreach($arr3 as $ins)$ptc->insertRow("test_table",$ins)}


Update Row

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

  1. # single row update
  2.     $up['field1']="somevalue5";
  3.     $up['field2']="somevalue6";
  4.     $record_id=1;    # the record id
  5.     $ptc->updateRow("test_table",$up,$record_id);
  6.     # multiple rows update
  7.     $arr1=array("field1"=>"somevalue7","id"=>1);
  8.     $arr2=array("field1"=>"somevalue8","id"=>2);
  9.     $arr3=array($arr1,$arr2);    # 2 dimensions array
  10.     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.

  1. # single row
  2.     $record_id=1;    # the record id
  3.     $ptc->deleteRow("test_table",$record_id);
  4.     # multiple rows
  5.     $arr3=array("2","3");    # array of id's
  6.     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.

  1. # multiple rows insert
  2.     $arr1=array("field1"=>"somevalue3","field2"=>"somevalue5");
  3.     $arr2=array("field1"=>"somevalue4","field2"=>"somevalue6");
  4.     $arr3=array($arr1,$arr2);    # 2 dimensions array
  5.     foreach($arr3 as $ins)$ptc->insertRow("test_table",$ins)}
  6.     # get last inserted id
  7.     echo "<b>last inserted id is:</b> ".$ptc->lastId()."<br><br>";


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.

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


ExecuteSql

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

  1. # just an update query
  2.     $sql="UPDATE test_table SET `field1` = 'somevalue9' WHERE `field2` = 'somevalue'";
  3.     $ptc->executeSql($sql);
  1. # drop example table
  2.     $ptc->executeSql("DROP TABLE IF EXISTS `test_table`");
  3.     
  4.     # create example table
  5.     $ptc->executeSql("CREATE TABLE `test_table` 
  6.     (
  7.         `id` int NOT NULL AUTO_INCREMENT, 
  8.         PRIMARY KEY(`id`),`field1` varchar(15),`field2` varchar(15)
  9.     )");


Using References

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

  1. # just a select query
  2.     $data=$ptc->readTable("test_table",'','','',"testReference");                            
  3.     print "<b>readTable() result adding reference <i>\"testReference\"</i></b>";
  4.     print "<pre>".print_r($data,true)."</pre>";
  5.     # DO MORE QUERIES HERE
  6.     # count rows from a select query with reference
  7.     echo "<b>countRows (select statement) with reference:</b> ";
  8.     echo $ptc->countRows("testReference")."<br>";    
  9.     # show field id of first record with reference
  10.     echo "<b>show field1 of second record with reference(mysql_resultt):</b> ";
  11.     echo mysql_result($ptc->queryResults["testReference"],1,1)."<br>";
  12.     # show number of fields
  13.     $num_fields=mysql_num_fields($ptc->queryResults["testReference"]);
  14.     echo "<b>number of fields in previous query <i>\"testReference\"</i>:</b> ";
  15.     echo  $num_fields."<br>";
  16.     # get fields information
  17.     echo "<b>dump fields information (mysql_fetch_field) <i>\"testReference\"</i>:</b> ";
  18.     for($a=0;$a<$num_fields;$a++)
  19.     {
  20.         echo "<pre>";
  21.         var_dump(mysql_fetch_field($ptc->queryResults["testReference"],$a));
  22.         echo "</pre>";    
  23.     }

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".

Prev   Next
PtcDebug User Guide PtcForms User Guide

Documentation generated on Fri, 30 Aug 2013 15:28:53 +0200 by phpDocumentor 1.4.3