多数据表分页

关于多表分页，返回总记录的分页记录和总数的数组方法

<?
class Model_DbTable_Buyers extends Zend_Db_Table_Abstract {
 private static $b_db_arr = array('a18','a19','a12','a11','a10','a09','a08','a07','a06','a21','a20','a01');
 /**
  *
  * Enter description here ...连接数据库,
  * @param unknown_type $dbname
  */
  public function getDbObject($dbname){
  $database = array(
   'adapter'=>'PDO_MYSQL','params'=>array(
   'host'=>'localhost',
   'username'=>'root',
   'password'=>'mypass7789',
   'dbname'=>$dbname,
   'charset'=>'utf8') );
  $db = Zend_Db::factory ( $database['adapter'], $database['params']);
  return $db;
 }

 public function getBuyersListAll($username,$userid,$keyword,$country,$searchtype,$lang,$start,$size,$business_type=NULL){  //可以采取分组方法获取。数组大小受限5000；
  $keystr = serialize(array("keyword"=>$keyword,"country"=>$country,"searchtype"=>$searchtype,"lang"=>$lang,"business_type"=>$business_type));
  $keymark = substr(md5($keystr),1,25);
  $issearch=1;
  //做缓存
  $frontOptions = array(
   'lifeTime'=>3600*2,//缓存时间设置为2小时，半年更新一次广交会数据
   'automatic_serialization'=>true
  );
  $backendOptions = array(
   'cache_dir' => "tmp/"//缓存目录
  );
  $cache = Zend_Cache::factory(
   'Core',
   'File',
   $frontOptions,
   $backendOptions
  );

  //结果在缓存中
  if($return = $cache->load($keymark."_".$start."_".$size)){
   return $return;
  }

  $return = array();
  //problem
  if(!$count_array = $cache->load($keymark."countarray")){
   //这里是获取结果的统计数组，然后进行缓存
   //构造数组
   //$count_array = array(0=>array('dbname'=>'a18','start'=>0,'end'=>5000,'length'=>5000,'result'=>array()),
   //1=>array('dbname'=>'a18','start'=>5000,'end'=>6234,'length'=>1235'result'=>array()),
   //....
   //n=>array('dbname'=>'a12','start'=>0,'end'=>2323,'length'=>2324,'result'=>array()),
   //)
   $count_array = array();
   $db_array = self::$b_db_arr;
   foreach($db_array as $onedb){
    $onedbresult = $this->getBuyersList($username,$userid,$keyword,$country,$searchtype,$lang,0,0,$onedb,$business_type,'total');
    $onedbcount = $onedbresult;
    $onedbcountstart = 0;

    while($onedbcountstart<$onedbcount){
     //分组大小
     $groupsize = 5000;
     $one_count_array = array();
     $one_count_array['dbname'] = $onedb;
     $one_count_array['start'] = $onedbcountstart;
     $onedbcountstart +=$groupsize;
     if($onedbcountstart>$onedbcount){
      $one_count_array['end'] = $onedbcount-1;
      $one_count_array['length'] = $onedbcount%$groupsize;
     }else{
      $one_count_array['end'] = $onedbcountstart-1;
      $one_count_array['length'] = $groupsize;
     }
     $count_array[] = $one_count_array;
    }
   }
   $cache->save($count_array,$keymark."countarray");
  }
  $return['total'] = 0;//记录总数
  foreach($count_array as $key=>$value){
   $return['total'] += $value['length'];
  }
  //根据start和size确定返回结果位置以及数据库等信息，组成的新数组。
  $getresult = array();
  $tt = 0;//标记总共的值，每个数组增加
  $startdb = "";
  $startnum = ($start-1)*$size;
  $endnum = $start*$size-1;
  $length = $size;
  foreach($count_array as $key=>$value){
   //每个数组元素的result最多存放5000条记录数组大小限制。
   $tt1 = $tt + $value['length'];
   if($tt<=$startnum && $tt1>$startnum){
    //有结果，需要的结果在这个数组确定的范围内
    $onegetresult = array();
    $onegetresult['dbname'] = $value['dbname'];
    $onegetresult['getstart'] = $startnum-$tt;
    $onegetresult['allstart'] = $value['start'];
    $onegetresult['allend'] = $value['end'];

    if($tt1>=$endnum){
     $onegetresult['getlength'] = $length;
     $getresult[]=$onegetresult;
     break;
    }else{
     $onegetresult['getlength'] = $value['length']-($startnum-$tt);
     $getresult[]=$onegetresult;
    }
    $startnum = $tt1;
    $length = $length - $onegetresult['getlength'];
   }
   $tt = $tt1;
  }
  $return['result'] = array();
  //foreach后获取的$getresult数组是需要读取的数据库以及limit的限制值。start length
  foreach($getresult as $onegresult){
   $odb = $onegresult['dbname'];
   $allstart = $onegresult['allstart'];
   $allend = $onegresult['allend'];
   $getstart = $onegresult['getstart'];
   $getlength = $onegresult['getlength'];
   if(!$onereturn = $cache->load($keymark."_".$odb."_".$allstart."_".$allend)){
    $onereturn = $this->getBuyersList($username,$userid,$keyword,$country,$searchtype,$lang,$allstart,$allend-$allstart+1,$odb,$business_type,'result');
    $cache->save($onereturn,$keymark."_".$odb."_".$allstart."_".$allend);
   }
   foreach($onereturn as $key=>$value){
    $onereturn[$key]['dbname'] = $odb;
   }
   $getonereturn = array_slice($onereturn,$getstart,$getlength);
   $return['result'] = array_merge($return['result'],$getonereturn);
  }
  $cache->save($return,$keymark."_".$start."_".$size);
  return $return;
 }

 public function  getBuyersList($username,$userid,$keyword,$country,$searchtype,$lang,$start,$size,$dbname="a18",$business_type=NULL,$output=NULL){
  $return = array();
  if( ! $dbname ){
   return $return;
  }
  if( ! $business_type ){
   $business_type = "";
  }

  $size1 = 20;//默认每次读取20条
  $db = $this->getDbObject('ciol_business');
  $select = $db->select();
  $select->from(array('a'=>$dbname));

  if(strlen(trim($keyword))>0){
   $keyword = trim($keyword);
   $keyword = str_replace("|",",",$keyword);
   $arr_words = explode(",",$keyword);
   $keyword_str = '';

    foreach($arr_words as $key=>$word){
     if(strlen($word)>0){
      if($searchtype==""){
       //$word_array = $this->getApartWord($word);
       $word_array = (array)$word;
      }else{
       $word_array = (array)$word;
      }
      foreach($word_array as $key1=>$value1){
       if($searchtype==''){
        $astr = "  a.en_name like '%{$value1}%'
          OR a.cn_name like '%{$value1}%'
          OR a.cn_Country like '%{$value1}%'
          OR a.en_Country like '%{$value1}%'
          OR a.en_Address like '%{$value1}%'
          OR a.cn_Address like '%{$value1}%'
          OR a.en_Type like '%{$value1}%'
          OR a.cn_Type like '%{$value1}%'
          OR a.en_Introduction like '%{$value1}%'
          OR a.cn_Introduction like '%{$value1}%'
          OR a.cn_Purchase like '%{$value1}%'
          OR a.en_Purchase like '%{$value1}%'
          OR a.en_Product like '%{$value1}%'
          OR a.cn_Product like '%{$value1}%'
          OR a.Content like '%{$value1}%' ";
       }else{

        if(strpos($searchtype,"|")==-1){
         $searchtype1="";
         if(strpos($searchtype,"cn_")!=-1){
          $searchtype1 = str_replace("cn_","en_",$searchtype);
         }
         if(strpos($searchtype,"en_")!=-1){
          $searchtype1 = str_replace("en_","cn_",$searchtype);
         }
         $astr = "  a.{$searchtype} like '%{$value1}%' ";
         if(strlen($searchtype1)>0){
          $astr = $astr." or a.{$searchtype1} like '%{$value1}%' ";
         }
        }else{
         $searchtypearr = explode("|",$searchtype);
         $astr="";
         foreach($searchtypearr as $kva=>$va){
          $va1="";
          if(strpos($va,"cn_")!=-1){
           $va1 = str_replace("cn_","en_",$va);
          }
          if(strpos($va,"en_")!=-1){
           $va1 = str_replace("en_","cn_",$va);
          }
          if($kva==0){
           $astr = "  a.{$va} like '%{$value1}%' ";
          }else{
           $astr .= " or  a.{$va} like '%{$value1}%' ";
          }
          if(strlen($va1)>0){
           $astr = $astr." or a.{$va1} like '%{$value1}%' ";
          }
         }
        }
       }
       if($key==0 && $key1==0){
        $keyword_str = $astr;
       }else{
        $keyword_str = $keyword_str . " OR " . $astr;
       }
      }
     }
    }
   if(strlen($keyword_str)>0){
    $select->where($keyword_str);
   }
  }
  
  if(strlen($country)>0){
   $select->where(" a.cn_Country like '%{$country}%' ");
  }
  if(strlen($business_type)>0){
   $select->where(" a.cn_Type like '%{$business_type}%'");
  }
  $select->where(" trim(a.en_name) !='' ");
  $select->where(" trim(a.Content) !='' ");

  //$select->group('a.en_name');

  if($lang=="php")
  {
   //$select_total = clone($select);
   $select_total_sql = preg_replace("/^select.*?from/i","select count(*) as `total` from ",$select->__toString());
   $total = $db->fetchAll($select_total_sql);
   if($output=='total')
   {
    //$return['total'] = count($total);
    return $total[0]['total'];
   }
   else
   {
    //$return['total'] = count($total);
    $return['total'] = $total[0]['total'];
   }
  }
  /*
  if(count($arr_words)==1){
   $select->order(" a.en_name ASC,(
   (case when INSTR(a.Content,'".$keyword."')>0 then 16 else 0)+
   (case when INSTR(a.cn_Product,'".$keyword."')>0 then 8 else 0)+
   (case when INSTR(a.en_name,'".$keyword."')>0 then 4 else 0)+
   (case when INSTR(a.cn_Country,'".$keyword."')>0 then 2 else 0)+
   (case when INSTR(a.cn_Purchase,'".$keyword."')>0 then 1 else 0)
   ) DESC ");
  }else{
   $select->order(" a.en_name ASC ");
  }
  */
  if(count($arr_words)==1){
   $select->order("LENGTH(a.Content)-LENGTH(REPLACE(a.Content,'{$keyword}','')) DESC,LENGTH(a.cn_Product)-LENGTH(REPLACE(a.cn_Product,'{$keyword}','')) DESC,LENGTH(a.en_name)-LENGTH(REPLACE(a.en_name,'{$keyword}','')) DESC,LENGTH(a.cn_Country)-LENGTH(REPLACE(a.cn_Country,'{$keyword}','')) DESC,LENGTH(a.cn_Purchase)-LENGTH(REPLACE(a.cn_Purchase,'{$keyword}','')) DESC,a.Email DESC,a.Telephone DESC,a.cn_Address DESC,a.en_Address DESC, a.en_name ASC ");
  }else{
   $select->order("LENGTH('a') DESC,a.Email DESC,a.Telephone DESC,a.cn_Address DESC,a.en_Address DESC, a.en_name ASC ");
  }
  
  if(!$size && $size!=0)
  {
   $size = $size1;
  }
 
  if($size!=0){
   if(!$start)
   {
    $start = 0;
   }
   $select->limit($size,$start);
  }
  $result = $db->fetchAll($select);
 
  if($lang=="php"){
  //PHP
   if($output=='result'){
    return $result;
   }else{
    $return['result']=$result;
    return $return;
   }
  }else{
  //java
   foreach($result as $key=>$value){
    $result[$key]['Content'] = mb_substr($value['Content'],0,200);
   }
   return $result;
  }
 }
}