Home arrow PHP arrow Page 2 - Utilizing the LIMIT Clause with the Active Record Pattern

Review: fetching database records using the LIKE clause - PHP

Welcome to the fifth installment of a series focused on using the active record pattern with PHP. Made up of seven episodes, this series walks you through the progressive development of a MySQL abstraction class that utilizes the active record approach to perform CRUD (Create, Remove, Update, Delete) database operations and SELECT queries, without having to explicitly code any SQL statements.

TABLE OF CONTENTS:
  1. Utilizing the LIMIT Clause with the Active Record Pattern
  2. Review: fetching database records using the LIKE clause
  3. Enhancing the functionality of the MySQL class with the LIMIT clause
  4. Example: fetching database rows with the LIMIT clause
By: Alejandro Gervasio
Rating: starstarstarstarstar / 6
March 31, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As I said in the beginning, the sample "MySQL" class that I used in the previous article was capable of fetching rows from a specified database table by using a straightforward method, called simply "fetchLike()."

It'd be helpful to recall briefly how the signature of this class looked originally, however, before I proceed to extend its existing functionality via the active record pattern. Therefore, below I included the definition of the class, along with a simple example that demonstrates how it can be used to retrieve all the records from a "users" MySQL table whose "firstname" field contain the "a" character. Here it is:


class MySQL{

private $result;

public function __construct($host='localhost',$user='user',$password='password',$database='database'){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception('Error connecting to the server');

}

if(!mysql_select_db($database,$conId)){

throw new Exception('Error selecting database');

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception('Error performing query '.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table='default_table'){

$this->query('SELECT * FROM '.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// fetch rows using 'WHERE' clause

public function fetchWhere($where,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$where);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// fetch rows using 'LIKE' clause

public function fetchLike($field,$like,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$field.' LIKE '.$like);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table='default_table'){

$sql='INSERT INTO '.$table.' ('.implode(',',array_keys($params)).') VALUES (''.implode("','",array_values($params)).'')';

$this->query($sql);

}

// update row

public function update($params=array(),$where,$table='default_table'){

$args=array();

foreach($params as $field=>$value){

$args[]=$field.'=''.$value.''';

}

$sql='UPDATE '.$table.' SET '.implode(',',$args).' WHERE '.$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where='',$table='default_table'){

$sql=!$where?'DELETE FROM '.$table:'DELETE FROM '.$table.' WHERE '.$where;

$this->query($sql);

}

}


try{

// connect to MySQL and select a database

$db=new MySQL('host','user','password','mydatabase');

// display users where first name contains the 'a' character

$result=$db->fetchLike('firstname',"'%a%'",'users');

foreach($result as $row){

echo $row['firstname'].' '.$row['lastname'].' '.$row['email'].'<br />';

}


/* displays the following


Alejandro Gervasio alejandro@domain.com
Susan Norton susan@domain.com
Marian Wilson marian@domain.com
Mary Smith mary@domain.com
Amanda Bears amanda@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com


*/


}

catch(Exception $e){

echo $e->getMessage();

exit();

}


As you'll recall, the above example illustrates how to utilize the pertinent "MySQL" class to fetch a few trivial records from a "users" MySQL table. In this case, the method used to retrieve this data is called "fetchLike()" and, as its name suggests, performs SELECT queries that include a LIKE clause.

Naturally, the most important detail to spot here is this method's ability to execute the aforementioned SQL query through a friendly interface that eliminates the need for coding SQL statements from scratch.

Now that you've recalled how the prior "MySQL" class does its business, it's time to continue extending its existing functionality. As I stated in the introduction, I'm going to define a new method within this class, which will be charged with retrieving rows from a targeted MySQL table by using a LIMIT clause. This could be pretty useful when it's necessary to build a record pagination mechanism.

This method will be built in the next section, so click on the link shown below and keep reading.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: