PHP/mySQL paging (Limit)

chrisdee

Below is my script that list out all the entries of my mysql table (hit counter). My mySQL table has over 70 000 entries and I want to list them out. The problem with the script below is that it lists out all the over 70 000 entries. This takes ages to do. Can anybody help me with this script so I can only view eg 50 enrties a time. I guess I have to use pages and limit, but dont know how to attack this.

<?php 
include("connect.php"); 

$order=$_GET['order']; 
 if(!$order) { 
 $order="id"; 
} 

$sql = mysql_query("SELECT * FROM $table order by $order desc limit 0,20"); 
$head = array('Number','IP','Date_Now','Date_First','Browser'); 

echo "<table border=1><tr>"; 
foreach($head as $part) { 
 if($order==$part) { 
  echo "<td bgcolor=lightgreen><a class=cd href=vis_teller.php?order=$part>$part</a></td>"; 
 } 
 elseif($part=="Number") { 
  echo "<td bgcolor=lightgrey>$part</td>"; 
 } 
 else { 
  echo "<td bgcolor=lightgrey><a class=cd href=vis_teller.php?order=$part>$part</a></td>"; 
 } 
} 
echo "</tr>"; 

while ($row = mysql_fetch_array($sql)) { 
 $teller++; 
 echo "<tr>"; 
 echo "<td>$teller</td>"; 
 echo "<td>".$row['ip']."</td>"; 
 echo "<td>".$row['date_now']."</td>"; 
 echo "<td>".$row['date_first']."</td>"; 
 echo "<td>".substr($row['browser'],0,83)."</td>"; 
 echo "</tr>"; 
} 
echo "</table>"; 
?>

HuwPioden

http://www.codewalkers.com/c/a/Datab...nd-Next-Links/

Is this what you want?

chrisdee

The link you posted is broke🙁

HuwPioden

Soz. Try this

chrisdee

Thanks, but I could not get it to work🙁

chrisdee

I'v tidyed up the script. Any idea how I incorporate a next/previous paging system into my script ?

<?php 
$username = "user"; 
$password = "pass"; 
$host     = "localhost"; 
$database = "mydb"; 
$table    = "hits"; 

mysql_connect($host,$username,$password) or die(mysql_error()); 
mysql_select_db($database) or die(mysql_error()); 

$sql = mysql_query("SELECT * FROM $table limit 0, 20"); 

echo "<table border=1>"; 

while ($row = mysql_fetch_array($sql)) { 
 $teller++; 
 echo "<tr>"; 
 echo "<td>$teller</td>"; 
 echo "<td>".$row['ip']."</td>"; 
 echo "<td>".$row['date_now']."</td>"; 
 echo "<td>".$row['date_first']."</td>"; 
 echo "<td>".substr($row['browser'],0,83)."</td>"; 
 echo "</tr>"; 
} 
echo "</table>"; 
?>

laserlight

A simple example would be:

<?php
$records_per_page = 50;
$total = mysql_result(mysql_query("SELECT COUNT(*) FROM $table"), 0);
$page_count = ceil($total / $records_per_page);

$page = 1;
if (isset($_GET['page']) && $_GET['page'] >= 1 && $_GET['page'] <= $page_count) {
    $page = (int)$_GET['page'];
}

$skip = ($page - 1) * $records_per_page;

$result = mysql_query("SELECT * FROM $table ORDER BY $order DESC LIMIT $skip, $records_per_page");
// Print the records
?>

Of course, you also need to print the page numbers as links, using the $page_count variable. You have to take into account that you will have 1400 pages, so printing all the page numbers at once could be problematic. Take some inspiration from this forum's thread listing for a possible solution.

chrisdee

Thanks, but I can't get it to work. Hmm, can you se what Im doing wrong ?

<?php
$username = "user";
$password = "pass";
$host     = "localhost";
$database = "mydb";
$table    = "hits";

mysql_connect($host,$username,$password) or die(mysql_error());
mysql_select_db($database) or die(mysql_error());

$records_per_page = 50;
$total = mysql_result(mysql_query("SELECT COUNT(*) FROM $table"), 0);
$page_count = ceil($total / $records_per_page);

$page = 1;
if (isset($_GET['page']) && $_GET['page'] >= 1 && $_GET['page'] <= $page_count) {
    $page = (int)$_GET['page'];
}
$skip = ($page - 1) * $records_per_page;

$result = mysql_query("SELECT * FROM $skip, $records_per_page");

echo "<table border=1>";
while ($row = mysql_fetch_array($result)) {
 echo "<tr>";
 echo "<td>$teller</td>";
 echo "<td>".$row['ip']."</td>";
 echo "<td>".$row['date_now']."</td>";
 echo "<td>".$row['date_first']."</td>";
 echo "<td>".substr($row['browser'],0,83)."</td>";
 echo "</tr>";
}
echo "</table>";
?>

laserlight

At a glance, this:

$result = mysql_query("SELECT * FROM $skip, $records_per_page");

should be:

$result = mysql_query("SELECT * FROM $table LIMIT $skip, $records_per_page");

chrisdee

Thanks. Now It lists out 50 entries, but I cant se the next/preview links.

Do I have to create a new file for each result I want to view ?
(Sorry for asking all the questions (im a bit of a novise).).

laserlight

What did you write for the page listing (and next/preview) links? I did not include it in my example (I hope you actually read examples before running them).

chrisdee

Hello again.

I have not written any code for paging. My inital question was how I incorporate that into my script. Maby I can use the example from the link that was posted above ?

laserlight

Well, since you know the number of pages (as given by $total), a naive listing of pages is extremely simple:

for ($i = 1; $i <= $total; ++$i) {
    echo '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . $i . '">'
        . $i . '</a> ';
}

In other words, print the numbers from 1 to $total inclusive, turning them into links.

chrisdee

Thank you very much. This is just what I wanted🙂)

Now I have to figure out how to only list 10 links a time, hehe.
But Ill try to figure out that myself.

Thanks again🙂

dogtown

laserlight wrote:
Well, since you know the number of pages (as given by $total), a naive listing of pages is extremely simple:
for ($i = 1; $i <= $total; ++$i) {
    echo '<a href="' . $_SERVER['PHP_SELF'] . '?page=' . $i . '">'
        . $i . '</a> ';
}
In other words, print the numbers from 1 to $total inclusive, turning them into links.

just a minor fix, but I think

for ($i = 1; $i <= $total; ++$i)

should read

for ($i = 1; $i <= $page_count; ++$i)

chrisdee

Excellent. Thank you🙂

I wouldn't call that a minor fix. Its a major fix.

The next / previous links didnt work correctly befor you fixed this.