PHP MySQL pagination query

[minghags]

Hello!

I have a problem with sorting mysql queries into pages. So i wanna set every query that is passed to screen to be divided with 30 rows per page.

Here is what i come up with:

<?php
$con=mysqli_connect("*","*","*","*");
// MySQL connect
if (mysqli_connect_errno()) {
  echo "MySQL: no connection! " . mysqli_connect_error();
}

$order = $_GET['order'];
$clid = $_GET['clid'];
//if (isset($_GET["order"])) { $page  = $_GET["order"]; } else { $order='calldate'; }; 
$queue = $_GET['queue'];
$desc = $_GET['desc'];
$fromdate = $_GET['fromdate'];
$todate = $_GET['todate'];
$agent = $_GET['agent'];
//if (isset($_GET["desc"])) { $page  = $_GET["desc"]; } else { $order='DESC'; };
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * 20; 

if(isset($_GET['agent'])) {
  echo "* " . $agent . "";
}

if(isset($_GET['queue'])) {
  echo "* " . $queue . ".";
}

echo "<br><br>";

//if(isset($_GET['agent']) && isset($_GET['queue'])) {
//  echo "* " . $agent . " * " . $queue . "";
//}

if(empty($_GET)){
$result = mysqli_query($con,"SELECT * FROM cc2_cdr ORDER BY calldate ASC LIMIT $start_from, 30;");
}
if(isset($_GET['clid'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE clid LIKE '$clid' ORDER BY calldate ASC LIMIT $start_from, 30;");
}
if(isset($_GET['order'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr ORDER BY $order LIMIT $start_from, 30;");
}
if(isset($_GET['queue'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE queue LIKE $queue ORDER BY calldate ASC LIMIT $start_from, 30;");
}
if(isset($_GET['agent'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE dst LIKE $agent ORDER BY calldate ASC LIMIT $start_from, 30;");
}
if(isset($_GET['agent']) && isset($_GET['queue'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE dst LIKE $agent AND (queue='$queue') ORDER BY calldate ASC LIMIT $start_from, 30;");
}
if(isset($_GET['fromdate']) && isset($_GET['todate'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE LEFT( calldate, 10 ) >= '$fromdate' AND LEFT( calldate, 10 ) <= '$todate' ORDER BY calldate ASC LIMIT $start_from, 100;");
}
if(isset($_GET['fromdate']) && isset($_GET['todate']) && isset($_GET['queue'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE LEFT( calldate, 10 ) >= '$fromdate' AND LEFT( calldate, 10 ) <= '$todate' AND queue LIKE '$queue' ORDER BY calldate ASC LIMIT $start_from, 100;");
}
if(isset($_GET['fromdate']) && isset($_GET['todate']) && isset($_GET['agent'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE LEFT( calldate, 10 ) >= '$fromdate' AND LEFT( calldate, 10 ) <= '$todate' AND dst LIKE $agent ORDER BY calldate ASC LIMIT $start_from, 100;");
}
if(isset($_GET['fromdate']) && isset($_GET['todate']) && isset($_GET['agent']) && isset($_GET['queue'])) {
$result = mysqli_query($con,"SELECT * FROM cc2_cdr WHERE LEFT( calldate, 10 ) >= '$fromdate' AND LEFT( calldate, 10 ) <= '$todate' AND dst LIKE $agent AND queue LIKE '$queue' ORDER BY calldate ASC LIMIT $start_from, 100;");
}

echo "<table border='1'>
<tr>
<td><center><b><a href='?order=id'>ID</a></b></center></td>
<td><center><b><a href='?order=calldate'>Datum</a></b></center></td>
<td><center><b><a href='?order=clid'>Stevilka</a></b></center></td>
<td><center><b><a href='?order=dst'>Agent</a></b></center></td>
<td><center><b><a href='?order=queue'>Cakalna</a></b></center></td>
<td><center><b><a href='?order=queuesec'>Cas v cakalni</a></b></center></td>
<td><center><b><a href='?order=duration'>Dolzina klica</a></b></center></td>
<td><center><b><a href='?order=disposition'>Odgovorjen</a></b></center></td>

</tr>";

while($row = mysqli_fetch_array($result)) {
  echo "<tr class='rows'>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . date("d. m. Y h:m:s", strtotime($row['calldate'])) . "</td>";
  echo "<td>" . $row['clid'] . "</td>";
  echo "<td>" . $row['dst'] . "</td>";
  echo "<td>" . $row['queue'] . "</td>";
  echo "<td>" . gmdate("H:i:s", $row['queuesec']) . "</td>";
  echo "<td>" . gmdate("H:i:s", $row['duration']) . "</td>";
  #echo "<td>" . $row['disposition'] . "</td>"; 
  if ($row['disposition'] == '1') {
        echo "<td><b><font color='#00CC00'>DA</font></b></td>";
    }else
  {
    echo"<td><b><font color='#FF0000'>NE</font></b></td>";
   }  
}

echo "</table>"; 

$sql = "SELECT COUNT(id) FROM test"; 
$rs_result = mysql_query($sql,$con); 
$row = mysql_fetch_row($rs_result); 
$total_records = $row[0]; 
$total_pages = ceil($total_records / 20); 

for ($i=1; $i<=$total_pages; $i++) { 
            echo "<a href='pagination.php?page=".$i."'>".$i."</a> "; 
}; 

mysqli_close($con);
?>

about the code it will be edited for prepared statements :)

If anyone could help please...

Thanks in advance.

[Adrian_5]

It seems to me that you put a wrong value to $start_from variable. Because you ask for 30 rows, it should be:

$start_from = ($page-1) * 30;

Also, on line 102 you should write: $total_pages = ceil($total_records / 30);