[SOLVED] Splitting a large table in to smaller chunks.

[mfallon]

So folks, I am sort of a newbie on a steep learning curve. I don't need someone to write this for me, I just want some direction on how I can do this. If anyone has any examples or code snippets I can look at then I can work out how to integrate it in to my page. Alternatively if anyone can tell me what functions I need to use I'm sure I can find something to get it working.

 

I've got the following table being generated on my page but I currently don't have a full set of data in my database so I don't have any problems with the size of the table.

 

Unfortunately when I import the data which I currently have distributed around hundreds of Excel spreadsheets I am going to have around 5000 rows returned for this query.

 

Do you know how I can get say only 50 records at a time to display with next  and previous links, and / or numbered links for the number of pages of results returned.

 

The code is below and hopefully someone can help me with this.

 

Many thanks in advance.

 

Matt

 

/* Display Straights Table */
function displayStraights(){
   global $database;
   $q = "SELECT straights.id,straights.username,straights.ground,straights.typeid,DATE_FORMAT(straights.date,'%d/%m/%Y') AS sdate,straights.status,users.username,users.firstname,users.lastname,straight_type.id,straight_type.type,status_type.id,status_type.type "
       ."FROM straights "
   ."JOIN users ON users.username = straights.username "
   ."JOIN straight_type ON straight_type.id = straights.typeid "
   ."JOIN status_type ON status_type.id = straights.status "
   ."ORDER BY users.lastname,users.firstname";
   $result = $database->query($q);
   $num_rows = mysql_numrows($result);
   if(!$result || ($num_rows < 0)){
      echo "Error displaying info";
      return;
   }
   if($num_rows == 0){
      echo "<br>There are no straights currently listed in the Database.";
      return;
   }
   /* Display table contents */
   echo "<p><table align=\"left\" border=\"1\" cellspacing=\"0\" cellpadding=\"3\" class=\"sortable\">\n";
   echo "<tr bgcolor=\"#CCCCCC\"><td><b>ID</td><td><b>Username</td><td><b>First Name</td><td><b>Surname</td><td><b>Location</td><td><b>Type</td><td><b>Date</td><td><b>Status</td></tr>\n";
   for($i=0; $i<$num_rows; $i++){
      $sid  = mysql_result($result,$i,"straights.id");
      $uname  = mysql_result($result,$i,"users.username");
      $fname  = mysql_result($result,$i,"users.firstname");
      $lname  = mysql_result($result,$i,"users.lastname");
      $sgrnd  = mysql_result($result,$i,"straights.ground");
      $stype  = mysql_result($result,$i,"straight_type.type");
      $sdate  = mysql_result($result,$i,"sdate");
      $status = mysql_result($result,$i,"status_type.type");

   echo "<tr><td>$sid</td><td><a href=\"../userinfo.php?user=$uname\">$uname</a></td><td>$fname</td><td>$lname</td><td>$sgrnd</td><td>$stype</td><td>$sdate</td><td>$status</td></tr>\n";
   }
   echo "</table><br><br></p>\n";
}

[Zhadus]

What you're looking for is referred to as 'pagination.'

 

Even with that there are several ways of doing things, only a few will be useful to you.

 

What you'll want to do in general, is use LIMIT in your MySQL query.

 

For example:

mysql_query("SELECT * FROM table LIMIT 0, 50");

That will display the first 50. You can pass a variable into the address bar and use $_GET to retrieve it for the 'page' of the search.

 

$page = $_GET['page'];
$page = $page * 50;
mysql_query("SELECT * FROM table LIMIT $page, 50");

With that, it will display the first 50 if 'page' is 0, the second 50 if 'page' is 1, and so on.

 

Then just run a query for the next and previous for the records before and after. If $page = 0, you'd be running record -1 and record 51, and see if it returns a row. If it does, add a previous button, or add a next.

[mfallon]

OK, so I understand the SQL side of things with the Limit but what I don't get is how to sort the links.

 

I'm assuming that I need to have a link which creates a page value which I am assuming I would do through the URL.

 

../table.php?page=1

../table.php?page=2

 

What I don't know how to do is automatically create the links based on the number of rows in the database. The number of rows is going to be continually growing so I don't want to have to modify the page everytime there are another 50 records added.

 

Basically, I'm still not clear on how to actually put this on the page. I'm pretty sure I know how to incorporate it in to my SQL statement.

 

Appreciate the help.

 

Matt

[redarrow]

i thort the end of the links, was the users id.

 

page=$row['id'];

[mfallon]

I'm not looking to show a single row but want to show say 50 rows at a time. But want the number of links to be based on the number of records, so links would be auto generated as follows;

 

next 1 2 3 4 prev

 

With the example having 183 rows and the 3rd page currently being displayed.

 

I'm pretty sure how I can get the SQL to work now, and have an idea of how to get the page link interpreted based on "page.php?page=3" type setup, but just don't know how I can create the links based on the number of rows returned.

 

I think I mentioned previously I have almost 5000 records in one table, which will only increase over time.

[.josh]

http://www.phpfreaks.com/tutorial/basic-pagination

[mfallon]

Thanks very much, I'll have a look and update if I have any problems.

 

Matt