PHP oracle 分页函数介绍

网络整理 - 08-01

我们都知道在mysql分页很方便，我们可以使用limit，我们可以这样limit 30,40,而oracle没有limit，用其他的方法来代替－－rownum.

一，底层方法

查看复制打印?
1./**
2. * 数据连接
3. */
4.function connect($db_user, $db_pwd, $db_name, $db_nls)
5.{
6. $this->dbh = oci_connect($db_user, $db_pwd, $db_name, $db_nls);
7.
8.}
9.
10.function getall($query, $start=0, $rows=-1)
11.{
12. $this->dbh = emptyempty($this->dbh) ? $this->connect() : $this->dbh;
13. $this->sth = oci_parse($this->dbh, $query);
14. oci_execute($this->sth, OCI_DEFAULT) or $this->error($this->sth, $query);
15. oci_fetch_all($this->sth, $res, $start, $rows, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);
16. return $res;
17.}
18.
19./**
20.* 获取分页数
21.*
22.* $query   string     查询语句
23.* $total   int        总条数
24.* $page    int        页数
25.* $rows    int        每页显示数
26.* @return integer 总行数
27.*/
28.function getpage($query, $total, $page, $rows=VAR_PAGENAV_ROWS)
29.{
30. $start_row = ($page - 1) * $rows + 1;
31. $start_row = $start_row > $total ? 1 : $start_row;
32. $end_row    = $start_row + $rows - 1;
33. $end_row    = $end_row > $total ? $total : $end_row;
34. $query      = "SELECT * FROM ( SELECT ROWNUM as row_num, r.* FROM ( $query ) r WHERE ROWNUM <= $end_row ) WHERE $start_row <= row_num";
35. return $this->getall($query);
36.}
37.
38./**
39.* 获取一个查询的总行数
40.*
41.* $string $sql 查询语句
42.*
43.* return integer 总行数
44.*/
45.function getcount($sql){
46.
47. $subSQL = 'select count(*) '.stristr($sql, 'from');
48.
49. return $this->getone($subSQL);
50.}
51.
52.function getone($query)
53.{
54. $row = $this->getrow($query);
55. if (is_array($row))
56. $one = current($row);
57. else
58. $one = '';
59. unset($row);
60. return $one;
61.}
62.
63.function getrow($query)
64.{
65. if (false === stripos($query, 'COUNT(')) {
66. if (false === stripos($query, 'WHERE')) {
67. $query .= ' WHERE ROWNUM = 1';
68. } else {
69. $query .= ' AND ROWNUM = 1';
70. }
71. }
72. $result = $this->getall($query, 0, 1);
73. if (emptyempty($result))
74. $row = array();
75. else
76. $row = $result[0];
77. unset($result);
78. return $row;
79.}
/**
* 数据连接
*/
function connect($db_user, $db_pwd, $db_name, $db_nls)
{
$this->dbh = oci_connect($db_user, $db_pwd, $db_name, $db_nls);

}

function getall($query, $start=0, $rows=-1)
{
$this->dbh = empty($this->dbh) ? $this->connect() : $this->dbh;
$this->sth = oci_parse($this->dbh, $query);
oci_execute($this->sth, OCI_DEFAULT) or $this->error($this->sth, $query);
oci_fetch_all($this->sth, $res, $start, $rows, OCI_FETCHSTATEMENT_BY_ROW+OCI_ASSOC);
return $res;
}

/**
* 获取分页数
*
* $query   string     查询语句
* $total   int        总条数
* $page    int        页数
* $rows    int        每页显示数
* @return integer 总行数
*/
function getpage($query, $total, $page, $rows=VAR_PAGENAV_ROWS)
{
$start_row = ($page - 1) * $rows + 1;
$start_row = $start_row > $total ? 1 : $start_row;
$end_row    = $start_row + $rows - 1;
$end_row    = $end_row > $total ? $total : $end_row;
$query      = "SELECT * FROM ( SELECT ROWNUM as row_num, r.* FROM ( $query ) r WHERE ROWNUM <= $end_row ) WHERE $start_row <= row_num";
return $this->getall($query);
}

/**
* 获取一个查询的总行数
*
* $string $sql 查询语句
*
* return integer 总行数
*/
function getcount($sql){

$subSQL = 'select count(*) '.stristr($sql, 'from');

return $this->getone($subSQL);
}

function getone($query)
{
$row = $this->getrow($query);
if (is_array($row))
$one = current($row);
else
$one = '';
unset($row);
return $one;
}

二，调用方法

查看复制打印?
1.$total = $this->db->getcount($sql);
2.$result = $this->db->getpage($sql,$total,$page,$page_number);
$total = $this->db->getcount($sql);
$result = $this->db->getpage($sql,$total,$page,$page_number);上面只是贴出部分代码，也是最核心的。