zhuxianfei.com

﹤首页 > 编程技术 > php

mysql 分页类（带分页条功能）

发布时间：2016-11-25 16:38

 代码如下 复制代码
/function fenyezhixin($table,$pagesize="",$paixu="",$do="",$wwhere=""){
    if(($table == "news_fabu") or ($table == "product_fabu") or ($table == "message") or ($table == "down_fabu") or ($table == "kucun")){
    $query = @mysql_query("select * from $table $wwhere");
    $pagesize = $pagesize;
    $sum = mysql_num_rows($query);
    if($sum == 0){
        $pagesize = 1;
    }
 if($pagesize=="0"){
     $sum = "1";
 }
    if (@($sum % $pagesize) == 0){
     $total = @(int)($sum / $pagesize);
 }else{
     $total = @(int)($sum / $pagesize) + 1;
 }
    if($total == 0){$total=1;}
    if (isset($_get['page'])){
        $p = (int)$_get['page'];
    }else{
        $p = 1;
    }
    $start = $pagesize * ($p - 1);
    $query = @mysql_query("select * from $table $wwhere order by $paixu limit $start,$pagesize") or die ("数据查询失败2!");
    if ($do == 1){
        $queryarray = array($query,$total,$sum,$p);
        return $queryarray;
    }
    if($do == 2){
        $parray = array($total,$sum,$p);
        return $parray;
    }
 }else{
     $query = @mysql_query("select * from $table $wwhere order by $paixu limit $pagesize") or die ("数据查询失败1!");
     if ($do == 1){
            $queryarray = array($query,$total,$sum,$p);
            return $queryarray;
     }
     if($do == 2){
            $parray = array($total,$sum,$p);
            return $parray;
     }
 }
}
//返回分页条  
function fenyedaohang($total="",$sum="",$p="",$menut=""){
 $w = substr($menut,strrpos($menut,"&")+1,2);
 $wr = substr($menut,strrpos($menut,"=")+1,strlen($menut));
 $pindao = $_server["script_name"];$pinstrlen = strrpos($pindao,"/"); $pindao = substr($pindao,$pinstrlen+1,strlen($pindao));
    if($w == "pr"){
     $queryr = mysql_query("select feiye.feiye_what from feiye where feiye.feiye_page = '$pindao'");
  $rows = mysql_fetch_row($queryr); $rrows = $rows[0];
  if(empty($rrows)){
      mysql_query("insert into `feiye` (`feiye_page`, `feiye_what`) values ('$pindao', '$wr')");
   echo "<meta http-equiv='refresh' content='0'>";
  }else{
      if($wr != $rrows){
        mysql_query("update `feiye` set `feiye_what`='$wr' where (`feiye_page`='$pindao')");
     echo "<meta http-equiv='refresh' content='0'>";
   }
  }
 }
 if($w == "ne"){
     $queryr = mysql_query("select feiye.feiye_what from feiye where feiye.feiye_page = '$pindao'");
  $rows = mysql_fetch_row($queryr); $rrows = $rows[0];
  if(empty($rrows)){
      mysql_query("insert into `feiye` (`feiye_page`, `feiye_what`) values ('$pindao', '$wr')");
   echo "<meta http-equiv='refresh' content='0'>";
  }else{
      if($wr != $rrows){
        mysql_query("update `feiye` set `feiye_what`='$wr' where (`feiye_page`='$pindao')");
     echo "<meta http-equiv='refresh' content='0'>";
   } 
  }
 }
    echo "共"."$total"."页&nbsp;"."记录"."$sum"."条&nbsp;当前"."$p"."/"."$total"."页&nbsp;&nbsp;";
    if($total == 1){
        echo "<font  class="page">首页</font>";
    }else{
        echo "<a href='?page=1&menu=$menut' class="page">首页</a>"."&nbsp;";
    }
    if ($p > 1){
        $prev = $p - 1;
        echo "<a href='?page=$prev&menu=$menut' class="page">上一页</a>"."&nbsp;";
    }else{
        echo "<font class="page">上一页</font>"."&nbsp;";
    }
    $page = $_get["page"];
    $pagesum = $page+5;
    if($total >= 11){
        if($pagesum <=11 ){
            $pagesum = 11;
        }
    }
    if($pagesum >= $total){
        $pagesum = $total;
    }
    $pagestart = $page - 5;
    if($pagestart <= 0){
        $pagestart = 1;
    }
    if($total >= 11 and ($total-4) <= $page){
        $pagestart = $total-10;
    }
    for($i=$pagestart;$i<=$pagesum;$i++){
        if($i == $p){
            echo "<font color=cccccc>&nbsp;$i&nbsp;</font>";
        }else{
            echo "<a href='?page=$i&menu=$menut' class="page" >$i</a>";
        }
    }
    if ($p < $total){
        $next = $p + 1;
        echo "&nbsp;<a href='?page=$next&menu=$menut' class="page" >下一页</a>"."&nbsp;";
    }else{
        echo "<font class="page" >下一页</font>"."&nbsp;";
    }
    if($total == 1){
        echo "<font  class="page">尾页</font>";
    }else{
        echo "<a href='?page=$total&menu=$menut' class="page">尾页</a>";
    }
}
 代码如下 复制代码
* 1、连接服务器 2、各类sql动作
*/
class mysql{
    private $host;  //服务器地址
    private $name;  //用户名称
    private $pass;  //密码
    private $table; //连接数据库教程
    private $jiema; //设置解码
    private $ztime; //设置服务器的时区
    //构造函数
    function __construct($host,$name,$pass,$table,$jiema,$ztime){
        $this -> host  = $host  ;
      $this -> name  = $name  ;
      $this -> pass  = $pass  ;
      $this -> table = $table ;
  $this -> jiema = $jiema ;
  $this -> ztime = $ztime ;
      $this -> connect();
     }
     //数据库连接和设置
     function connect(){
         $link=@mysql_connect($this->host,$this->name,$this->pass) or die ("连接服务器失败");
         @mysql_select_db($this->table,$link) or die("连接数据失败");
         @mysql_query("set names '$this->jiema'");
      @date_default_timezone_set("$this->ztime");
     }
    //执行操作
 function query($sql) {
     if(!($query = @mysql_query($sql))) $this->show($sql);
  return $query;
 }
    //显示信息
    function show($message = '', $sql = '') {
  if(!$sql) echo $message;
  else echo $message.'<br>'.$sql;
 }
    //取得数据集的某个值
 function result($query,$row,$values) {
  return @mysql_result($query,$row,$values);
 }
    //取得数据集的某个值
 function get_values($table,$row,$values) {
     $query = $this -> query("select * from $table");
  $returnvalues = mysql_result($query,$row,$values);
  return $returnvalues;
 }
    //取得数据集的行数
 function num_rows($query) {
  return @mysql_num_rows($query);
 }
    //循环读取数据
 function fetch($query) {
  return @mysql_fetch_array($query);
 }
    //最后一次插入纪录的id值
 function insert_id() {
  return mysql_insert_id();
 }
    //取得数据集中的一行
 function fetch_row($query) {
  return mysql_fetch_row($query);
 }
    //插入一条数据
    function fn_insert($table,$name,$value){
     if($this->query("insert into $table ($name) values ($value)")){
      return true;
  }else{
      return false;
  }
    }
    //插入任意数据
    function sql_insert($tbname,$postvalues){
        foreach ($postvalues as $key => $value) {
   $postvalue .= "`".$key."`".",";
   $sqlvalue .= "'".$value."',";
  }
  $sqlfield = mb_substr("$postvalue",0,-1,'gbk');
  $sqlvalue = mb_substr("$sqlvalue",0,-1,'gbk');
  if($this-> fn_insert("$tbname","$sqlfield","$sqlvalue")){
      return true;
  }else{
      return false;
  }
 } 
    //修改万能数据
    function sql_update($table,$postvalues,$wwhere){
  foreach ($postvalues as $key=>$value) {
   $sqlfield .= $key."="."'".$value."'".",";
  }
  $sqlfield= mb_substr("$sqlfield",0,-1,'gbk');
  if($this->fn_update("$table","$sqlfield","$wwhere")){
      return true;
  }else{
      return false;
  }
 }
    //修改一条数据
    function fn_update($table,$value,$wwhere){
     if($this->query("update $table set $value where $wwhere")){
      return true;
     }else{
      return false;
  }
    }
    //删除一条数据
    function sql_delete($table,$wwhere){
     if($this->query("delete from $table where $wwhere")){
      return true;
  }else{
      return false;
  }
 }
 //关闭数据连接
    function close() {
  return mysql_close();
 }
}
$db =  new mysql($location['host'],$location['hostname'],$location['hostpass'],$location['table'],$location['jiema'],$location['ztime']);   
?>
 代码如下 复制代码

set_time_limit(0); //设置超时时间为0，表示一直执行。当php在safe mode模式下无效，此时可能会导致导入超时，此时需要分段导入
$db =  new mysql($location['host'],$location['hostname'],$location['hostpass'],$location['table'],"utf8",$location['ztime']);  
$fp = @fopen($sql, "r") or die("不能打开sql文件 $sql");//打开文件
while($sql=getnextsql()){
    mysql_query($sql);
}
//echo "用户数据导入完成!";
fclose($fp) or die("can't close file $file_name");//关闭文件

//从文件中逐条取sql
function getnextsql() {
    global $fp;
    $sql="";
    while ($line = @fgets($fp, 40960)) {
        $line = trim($line);
        //以下三句在高版本php中不需要，在部分低版本中也许需要修改
        //$line = str_replace("\\","\",$line);
        //$line = str_replace("'","'",$line);
        //$line = str_replace("\r\n",chr(13).chr(10),$line);
        //$line = stripcslashes($line);
        if (strlen($line)>1) {
            if ($line[0]=="-" && $line[1]=="-") {
                continue;
            }
        }
        $sql.=$line.chr(13).chr(10);
        if (strlen($line)>0){
            if ($line[strlen($line)-1]==";"){
                break;
            }
        }
    }
    return $sql;
}
?>

 

先们要下载phpexcelreader，然后再看一个简单的把excel数据读取并显示出,实例二是把excel数据导入到mysql数据，实例三是利用php 导入 csv到mysql数据库。
 代码如下 复制代码

require_once './includes/reader.php';
// excelfile($filename, $encoding);
$data = new spreadsheet_excel_reader();
// set output encoding.
$data->setoutputencoding('gbk');
//"data.xls"是指要导入到mysql中(的)excel文件
$data->read('date.xls');
@ $db = mysql_connect('localhost', 'root', '1234') or
die("could not connect to database.");//连接数据库
mysql_query("set names 'gbk'");//输出中文
mysql_select_db('wenhuaedu'); //选择数据库
error_reporting(e_all ^ e_notice);
for ($i = 1; $i <= $data->sheets[0]['numrows']; $i++) {
//以下注释(的)for循环打印excel表数据
 
for ($j = 1; $j <= $data->sheets[0]['numcols']; $j++) {
echo $data->sheets[0]['cells'][$i][$j].",";
}
echo "n";

//ttp://sourceforge.net/projects/phpexcelreader


//下面为导入程序，注明，mysql数据库结构要与xls文档的结构一样就可以了。

 

 代码如下 复制代码

$data = new spreadsheet_excel_reader();
            $data->setoutputencoding('utf-8');//设置编码
            $data->read('test.xls');//excel,csv文件位置
            error_reporting(e_all ^ e_notice);

            for ($i = 2; $i <= $data->sheets[0]['numrows']; $i++) {//插入数据库开始
                    $sql="insert into {$table}kehu (userid,sortid,danwei,xingming,zuncheng,tel,mobile,fax,email,website,qq,address,postcode,sex,beizhu,addtime) values('$_cookie[wecms_user_id]','$sortid',
                                    '".$data->sheets[0]['cells'][$i][1]."',
                                    '".$data->sheets[0]['cells'][$i][2]."',
                                    '".$data->sheets[0]['cells'][$i][3]."',
                                    '".$data->sheets[0]['cells'][$i][4]."',
                                    '".$data->sheets[0]['cells'][$i][5]."',
                                    '".$data->sheets[0]['cells'][$i][6]."',
                                    '".$data->sheets[0]['cells'][$i][7]."',
                                    '".$data->sheets[0]['cells'][$i][8]."',
                                    '".$data->sheets[0]['cells'][$i][9]."',
                                    '".$data->sheets[0]['cells'][$i][10]."',
                                    '".$data->sheets[0]['cells'][$i][11]."',
                                    '".$data->sheets[0]['cells'][$i][12]."',
                                    '".$data->sheets[0]['cells'][$i][13]."',
                                    '$nowtime')";
                    $res=$db->query($sql);

//插入数据库结束
           

}

//把csv导入到数据库。

 代码如下 复制代码

function   getmicrotime(){    
           
              list($usec,   $sec)   =   explode("   ",microtime());    
           
                    return   ((float)$usec   +   (float)$sec);  
            }  
   
  $time_start   =   getmicrotime();  
   
  include   ("connectdb.php");  
   
   
  function   insert_data   ($id,$summary,$description,$additional_information,$category)  
   
      {  
           
          $my_query1   =   "insert   into   mantis_bug_text_table   (id,description,additional_information)    
                 
          values   ('$id','$description','$additional_information')";  
         
          $first   =   mysql_query($my_query1);      
   
          $my_query2   =   "insert   into   mantis_bug_table   (id,project_id,summary,bug_text_id)   values   ('$id','$category','$summary','$id')";  
                 
          $second   =   mysql_query($my_query2);      
         
          return;  
      }  
   
  $fp   =   fopen("test.csv","r");  
   
  while($data   =   fgetcsv($fp,'1000',',')){  
   
              insert_data   ($data[0],$data[1],$data[2],$data[3],$data[4]);  
         
              echo   "<font   color   =   #ff0000   size   =   20>数据导入成功！</font><br><br>";      
      }  
  fclose   ($fp);  
   
  $time_end   =   getmicrotime();  
   
  $time   =   $time_end   -   $time_start;  
   
  echo   "程序执行时间：".$time."秒";  
本文章提供一款由php pdo mysql操作实例教程了，这是由php pdo查询mysql数据并显示
 代码如下 复制代码
try {
    $dbh = new pdo($beidou_connstr,$beidou_user,$beidou_pass);
    foreach ($dbh->query('select count(*) as count1, count(*) as count2 from bd_reve.user_day') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (pdoexception $e) {
    print "error!: " . $e->getmessage() . "<br/>";
    die();
}
标签：[!--infotagslink--]

您可能感兴趣的文章: