Paging dengan PHP – Oracle

En

Paging adalah proses untuk menampilkan sebagian dari keseluruhan data. Misalkan, hanya menampilkan 10 dari 1000 baris data hasil query.  Diantara alasan melakukan paging adalah untuk mempercepat proses penampilan data dan meringankan beban server.

Berikut ini adalah catatan paging yang saya lakukan menggunakan PHP dan database Oracle. Referensi yang saya gunakan adalah dari website Oracle.

Ada beberapa fungsi yang digunakan untuk melakukan paging ini.

Menghitung Total Halaman

function total_pages($total_rows, $rows_per_page) {
    if ( $total_rows < 1 ) $total_rows = 1;
    return ceil($total_rows/$rows_per_page);
}

Mendapatkan Baris Data Awal

function page_to_row($current_page, $rows_per_page) {
    $start_row = ($current_page-1) * $rows_per_page + 1;
    return $start_row;
}

Menghitung Total Baris Data

function count_rows() {
	$conn = ocilogon('user','password','database');
    $sql = "SELECT COUNT(*) AS num_rows FROM table_database";
    $stmt = ociparse($conn,$sql);
    ociexecute($stmt);
    ocifetch($stmt);
	return OCIResult($stmt,'NUM_ROWS');
}

Mencetak Navigasi Paging

function draw_pager($url, $total_pages, $current_page) {

    if ( $current_page <= 0 || $current_page > $total_pages ) {
        $current_page = 1;
    }

    if ( $current_page > 1 ) {
        printf( "<a href='$url?page=%d'>[Start]</a> \n" , 1);
        printf( "<a href='$url?page=%d'>[Prev]</a> \n" , ($current_page-1));
    }

    for( $i = ($current_page-3); $i <= $current_page+3; $i++ ) {

        if ($i < 1) continue;
        if ( $i > $total_pages ) break;

        if ( $i != $current_page ) {
            printf( "<a href='$url?page=%1\$d' style=\"color:#0000CC\">%1\$d</a> \n" , $i);
        } else {
            printf("<a href='$url?page=%1\$d' style=\"color: #FF0000\"><strong>%1\$d</strong></a> \n",$i);
        }

    }

    if ( $current_page < $total_pages ) {
        printf( "<a href='$url?page=%d'>[Next]</a> \n" , ($current_page+1));
        printf( "<a href='$url?page=%d'>[End]</a> \n" , $total_pages);
    }

}

Mencetak Data Sesuai Halaman Paging

function paged_result($start_row, $rows_per_page) {
	$conn = ocilogon('user','password','database');
    $sql = "SELECT *
         FROM ( SELECT r.*, ROWNUM as row_number FROM table_database r
		 		WHERE ROWNUM <= :end_row)
         WHERE :start_row <= row_number";
    $stmt = ociparse($conn,$sql);
    ocibindbyname($stmt, ':start_row', $start_row);

    // Calculate the number of the last row in the page
    $end_row = $start_row + $rows_per_page - 1;
    ocibindbyname($stmt, ':end_row', $end_row);
    ociexecute($stmt);

    // Fetch the number of rows per page
    $rowResult = ocifetch($stmt);
	settype($arrayResult,"array");
	if($rowResult == null){
		echo 'Tidak ditemukan';
		$numRow = 0;
	}else{
		echo "<br/>";
		echo "<br/>";
		echo "<table border=\"1\" cellpadding=\"3\">";
echo "<tr>";
echo "<td>PERIODE</td>";
echo "<td>NO TELPON</td>";
echo "<td>ABODEMEN</td>";
echo "<td>AREA</td>";
echo "</tr>";
		echo "<tr>";
		echo "<td>".OCIResult($stmt,'PERIODE')."</td>";
		echo "<td>".OCIResult($stmt,'NOTELPON')."</td>";
		echo "<td>".OCIResult($stmt,'ABODEMEN')."</td>";
		echo "<td>".OCIResult($stmt,'AREA')."</td>";
		echo "</tr>";
		while(ocifetch($stmt)){
			echo "<tr>";
			echo "<td>".OCIResult($stmt,'PERIODE')."</td>";
			echo "<td>".OCIResult($stmt,'NOTELPON')."</td>";
			echo "<td>".OCIResult($stmt,'ABODEMEN')."</td>";
			echo "<td>".OCIResult($stmt,'AREA')."</td>";
			echo "</tr>";
		}
		echo "</table>";
	}
}

Sintaks Utama

session_start();

$current_page = 1;
if(isset($_GET['page'])){
	$current_page = $_GET['page'];
}

$total_rows = count_rows();
$rows_per_page = 15;
$total_pages = total_pages($total_rows, $rows_per_page);
$start_row = page_to_row($current_page,$rows_per_page);

draw_pager("", $total_pages, $current_page);
paged_result($start_row, $rows_per_page);

Contoh Hasil

Berikut ini adalah contoh hasil paging yang dilakukan :

Tabel Hasil Paging

Iklan

25 thoughts on “Paging dengan PHP – Oracle

  1. Assalamualaikum mba,
    aku mau tanya arti dri query ini
    SELECT *
    FROM ( SELECT r.*, ROWNUM as row_number FROM SALES.TELIN_MONITOR_ENTRY r
    WHERE ROWNUM <= :end_row)
    WHERE :start_row <= row_number

    trus klo kta mau ganti sama table kt make table kita apa aja yang harus di ganti dari script di atas?
    makasih sebelumnya,
    ")

    • Wassalamu’alaikum,
      Artinya, ambil semua data dari table SALES.TELIN_MONITOR_ENTRY. Mulai dari baris ke-x (start_row) sampai baris ke-y (end_row).
      Bisa disesuaikan dg kebutuhan, ganti saja bagian : SELECT r.*, ROWNUM as row_number FROM SALES.TELIN_MONITOR_ENTRY. Nama tablenya diganti, field yg diangkat pun bisa disesuaikan.

  2. assalamuailaikum bu
    mau nanya ni
    kok ga bisa ya yg disintak utamanya
    $total_rows = count_rows(); klu dijalankan yg keluar ”
    Fatal error: Call to undefined function count_rows() in C:\xampp\htdocs\web\pag.php on line 9″

    mohon penjelasannya
    terima kasih

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s