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

Enhancing the functionality of the MySQL class with the LIMIT 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

Undoubtedly, defining a method within the previous "MySQL" class that permits us to fetch a limited number of rows from a specified MySQL table would be a neat add-on. Moreover, if this method allows us to perform this task without having to write SQL code directly, this is even better.

Therefore, the next thing that I'm going to do will be exactly that, that is, include a method that runs SELECT queries by using a LIMIT clause. The signature of the "MySQL" class, after coding this brand new method, will be the following:


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);

}

// fetch rows using 'LIMIT' clause

public function fetchLimit($offset=1,$numrows=1,$table='default_table'){

$this->query('SELECT * FROM '.$table.' LIMIT '.$offset.','.$numrows);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}

}


Now the above class is becoming a pretty lengthy, right? Don't worry; I listed separately for you the signature of this new method, called "fetchLimit()." Here it's what it looks like:


// fetch rows using 'LIMIT' clause

public function fetchLimit($offset=1,$numrows=1,$table='default_table'){

$this->query('SELECT * FROM '.$table.' LIMIT '.$offset.','.$numrows);

$rows=array();

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

$rows[]=$row;

}

return $rows;

}


As you can see, all that the "fetchLimit()" method does is encapsulate the logic required to fetch a specified number of database records from a selected MySQL table by means of the classic LIMIT clause.

Since this method is indeed very simple to grasp, I'm not going to bore you with additional explanations about how it functions. Instead, I'll develop a practical example, where you can see it in action. The last section of this tutorial will be dedicated exclusively to setting up this concrete example.

Please click on the link below and read the next section. We're almost finished!



 
 
>>> 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: