0

I'm trying to add pagination on my data but there is an error like this :

sqlsrv_has_rows() expects parameter 1 to be resource, boolean given

But when I deleted the LIMIT $start,$limit on my query, the page is running smoothly..

I have no idea about it, I already tried another method for pagination but the result is still the same.. why is this happened ? Could somebody help me to explain and fix it please ?

Here is my code :

            <tbody>

          <?php

          //pagination
          $sqlcount   =   "SELECT COUNT(id_pelanggaran) FROM pelanggaran";
          $rscount    =   sqlsrv_has_rows(sqlsrv_query($conn,$sqlcount));
          $totaldata  =   $rscount[0];
          $page       =   isset($_GET['page']) ? $_GET['page']:1;
          $limit      =   50;
          $start      =   $limit * ($page-1);

          // Data Source
          $query    = "SELECT
                      dbo.pelanggaran.id_pelanggaran,
                      dbo.pelanggaran.waktu_pelanggaran,
                      dbo.pelanggaran.nama_pelanggar,
                      dbo.pelanggaran.bukti_screenshot,
                      dbo.pelanggaran.bukti_rekaman,
                      dbo.pelanggaran.bukti_dokumen,
                      dbo.jenis_pelanggaran.pelanggaran,
                      dbo.daftar_outlet.nama_outlet
                      FROM
                      dbo.pelanggaran
                      INNER JOIN dbo.jenis_pelanggaran ON dbo.pelanggaran.id_jenis_pelanggaran = dbo.jenis_pelanggaran.id
                      INNER JOIN dbo.daftar_outlet ON dbo.pelanggaran.id_lokasi_outlet = dbo.daftar_outlet.id
                      WHERE
                      dbo.pelanggaran.id_jenis_pelanggaran = dbo.jenis_pelanggaran.id ORDER BY id_pelanggaran ASC LIMIT $start,$limit";

          $result = sqlsrv_query($conn,$query);

          if (sqlsrv_has_rows($result) > 0) {
            while ( $data = sqlsrv_fetch_array($result)){

              ?>

              <tr>
                <?php echo "<td>" . $data['id_pelanggaran'] . "</td>"; ?>
                <?php echo "<td>" . $data['waktu_pelanggaran'] . "</td>"; ?>
                <?php echo "<td>" . $data['nama_pelanggar'] . "</td>"; ?>
                <?php echo "<td>" . $data['pelanggaran'] . "</td>"; ?>
                <?php echo "<td>" . $data['nama_outlet'] . "</td>"; ?>
                <?php echo "<td>" . $data['bukti_screenshot'] . "</td>"; ?>
                <?php echo "<td>" . $data['bukti_rekaman'] . "</td>"; ?>
                <?php echo "<td>" . $data['bukti_dokumen'] . "</td>"; ?>
                <td class="text-center">
                  <button class="btn btn-sm btn-primary" type="button" name="button">Edit</button>
                </td>
                <td>
                <button class="btn btn-sm btn-danger" type="button" name="button">Del</button>
                </td>
              </tr>

              <?php
            }
          }
          ?>
        </tbody>
Donny K. Pratama
  • 93
  • 2
  • 12

1 Answers1

0

Two things... First, clearly sqlsrv_query() is resulting in an error. You need to check for errors after executing a query. Take a look at sqlsrv_errors().

Second,

But when I deleted the LIMIT $start,$limit on my query, the page is running smoothly..

That's because LIMIT is invalid in MS SQL Server. (You're thinking of MySQL.) There are other ways to paginate results in MS SQL Server.

David
  • 208,112
  • 36
  • 198
  • 279
  • Is that mean i must do the pagination via SQL script rather than PHP script sir ? Or just changing the SQL function and keep working on PHP script ? Could you explain more detail please sir ? – Donny K. Pratama Nov 30 '17 at 16:28
  • @DonnyK.Pratama: Well, how you do your paging is up to you. It's certainly recommended to only request from the database the data you actually need, as opposed to requesting the entire result set and then filtering to the "page" you want in application code. How you *define* your paging logic is another story entirely. By adding a `LIMIT` clause to the query, it looks like you want to page your results from the database. However, that's not how you do that in SQL Server. There are other strategies for doing that, and the linked question is a good place to explore some. – David Nov 30 '17 at 16:31