Page 1 of 2 12 LastLast
Results 1 to 16 of 26

Thread: Writing a pagination script from scratch

  1. #1
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts

    Writing a pagination script from scratch

    I'm trying to get my head around it.

    First off i'd have to find the amount of rows in that particular table. In my case it's different as i'll have to be finding the amount of rows which have the type "blog" (if those of you were following my CMS plans)

    So do a query where i'd assign a variable the value of the amount of rows which correspond to that item type.

    Ok so we'd want to put previous and next links along with the page numbers which can be done using a simple for loop. Previous can be done by doing the current page number minus 1 as next can be current page number plus 1. Both of these are going to have to have an if statement stating whether they should be there or not (by this i mean, if there is no extra page then it shouldn't be displayed or it should have no link and dulled out perhaps?)

    So i get the total value of rows, divide it by however many posts i want per page (15?) so i do the total divided by 15 which will give me the amount of pages there will be. This is quite confusing.

    Any help?

  2. #2
    Pixel Abuser Spunkey's Avatar
    Join Date
    Nov 2003
    Location
    Milton Keynes
    Posts
    1,523
    Thanks
    0
    Thanked
    0 times in 0 posts
    are you using ASP/ADO? if so i can give you a simple bit of code which will take care of it for you.

  3. #3
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    I'm using PHP

  4. #4
    Comfortably Numb directhex's Avatar
    Join Date
    Jul 2003
    Location
    /dev/urandom
    Posts
    17,074
    Thanks
    228
    Thanked
    1,027 times in 678 posts
    • directhex's system
      • Motherboard:
      • Asus ROG Strix B550-I Gaming
      • CPU:
      • Ryzen 5900x
      • Memory:
      • 64GB G.Skill Trident Z RGB
      • Storage:
      • 2TB Seagate Firecuda 520
      • Graphics card(s):
      • EVGA GeForce RTX 3080 XC3 Ultra
      • PSU:
      • EVGA SuperNOVA 850W G3
      • Case:
      • NZXT H210i
      • Operating System:
      • Ubuntu 20.04, Windows 10
      • Monitor(s):
      • LG 34GN850
      • Internet:
      • FIOS
    blob, not blog

    and i'm still not sure why, blobs are for arbitrary binary data (images attached in forums are blobs)

  5. #5
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Quote Originally Posted by directhex
    blob, not blog

    and i'm still not sure why, blobs are for arbitrary binary data (images attached in forums are blobs)
    What are you on about? I'm referring to my BLOG with a "G". I'm implementing the pagination for my BLOG. Nice assumption though

  6. #6
    Pixel Abuser Spunkey's Avatar
    Join Date
    Nov 2003
    Location
    Milton Keynes
    Posts
    1,523
    Thanks
    0
    Thanked
    0 times in 0 posts
    doh

    Well, heres the code anyways, if some kindly folk could translate it for you...

    Code:
    	<% 
    	sPage = request.querystring("page")							' pick up passed page
    	
    	set rsDemo = server.createobject("ADODB.RecordSet")
    	SQL = "SELECT * FROM Articles WHERE Type = 'Blog';"
    	rsDemo.Open SQL, Conn, 3, 1
    		if not rsDemo.EOF then
    			' set up recordset for paging
    			sPageSize = 10															' Rows/Articles/Items per page. Can easily be set to user input value.
    			rsClaims.PageSize = sPageSize
    			sPgCount = cint(rsClaims.PageCount)					' Work out total number of pages.
    			if (sPgCount > 0) then
    				rsClaims.AbsolutePage = cint(trim(sPage))	' set current page. 
    			end if
    
    			do while ((rsDemo.EOF = false) and (i < sPageSize )) 
    				' ShowData
    				i = i + 1
    				rsDemo.MoveNext
    			loop
    
    			' Page selectors
    			if sPage > 1 then %>
    				<td>&nbsp;</td>				
    				<td><a href="page.asp?page=<%= sPage -1 %>">< Previous Page</a></td>	
    			<% else %>
    				<td>&nbsp;</td>
    				<td>&nbsp;</td>
    			<% end if 
    				
    			if ((cint(sPage) < cint(sPgCount)) and (cint(sPgCount) > 0)) then %>
    				<td align="right"><a href="page.asp?page=<%= sPage + 1 %>">Next Page ></a></td>
    			<% else %>
    				<td align="right">&nbsp;</td>
    			<% end if 
    		end if
    	rsDemo.Close
    	set rsDemo = nothing 
    	%>
    HTH
    Last edited by Spunkey; 08-02-2005 at 06:23 PM.

  7. #7
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Cheers rubbishrubbishrubbishrubbishrubbishey, i'll have a look through it and see if i can figure it out

  8. #8
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable
    Heres a rough translation of the code, this assumes the following type database in MySQL.
    The database is called 'my_blog_db' which contains a table called 'Entries' with two fields called 'entry_time' and 'entry_text'.
    I know its a bit sloppy but i threw the translation together from the top of my head, I havn't tested it yet, still waiting for my new mobo before i sort out my test server, I hope it helps or gives you an idea of what you need. excuse the whitespacing, i didn't bother with it much since its only a snippet.

    PHP Code:
    <?php
    $pagesize 
    10  // max entries in each page.
    $requestpage $_HTTP_GET_VARS["page"]; // fetch the page the user wants to see.
    // standard MySQL initalisation junk.
    $conn mysql_connect("host","user","password");
    if(!
    $conn) die("Unable to connect to MySQL DB, Error: ".mysql_error());
    $db mysql_select($conn"my_blog_db");
    if(!
    $db) die("Unabled to select DB, Error: ".mysql_error());
    $results mysql_query($conn,"SELECT * FROM Entries;");
    if(!
    $results) die("Query failed, Error: ".mysql_error());
    // done with mysql stuff.
    $entries mysql_num_rows($results) / $pagesize;
    if(
    $entries $pagesize) {
      if(
    fmod($maxpages))
        
    $maxpages floor($maxpages) + 1;
    // calculated the maximum pages based on the number of entries you have.
    else $singlepage true;
    mysql_field_seek($results,$requestpage); // move the pointer to the row for this page.
    echo "<table>\n\t<td>\n";
    for(
    $i=0;$i++;$i>$pagesize) {
      
    $array mysql_fetch_assoc($results);
      echo 
    "\t\t<tr>Posted: ".$array["entry_time"]."</tr>\n";
      echo 
    "\t\t<tr>".$array["entry_text"]."</tr>\n";
    }  
    // throw out the specified ammount of entries.
    echo "\t</td>\n</table>";
    if(
    $singlepage) die("listing done");
    if(
    $requestpage 1) echo "<a href=\"./page.php?page=".$requestpage-1."\">Prev Page</a> | \n";
    else echo 
    "... \n";
    for(
    $i=0;i++;$i>$maxpages) {
      echo 
    "<a href=\"./page.php?page=".$i."\">".$i."</a> | \n";
    }
    if(
    $requestpage $maxpages) echo "<a href=\"./page.php?page=".$requestpage+1."\">Next Page</a>\n";
    else echo 
    "... \n";
    mysql_free_result($results);
    mysql_close($conn);
    ?>
    *edit* fixed the syntax of the die() functions up top.
    Last edited by aidanjt; 09-02-2005 at 05:45 PM.

  9. #9
    Bigger than Jesus Norky's Avatar
    Join Date
    Feb 2005
    Posts
    1,579
    Thanks
    1
    Thanked
    8 times in 8 posts
    Another way to do it is with LIMITs in the MySQL query. Say if you wanted to display 15 per page:

    $pageno = $_GET[pageno]
    $limit = $pageno * 15
    SELECT * FROM `blah_table` LIMIT $limit , 15

    Not by any means working code, but you should be able to understand it.

    Please ignore me if I've been rambling on about completely the wrong thing, but it sounds very similar to something I've done before, in which I used the above method.

  10. #10
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable
    Norky, no its good to make better use of SQL queries to narrow down the resultset. I just couldn't be bothered thinking about it for a rough translation
    Bravo chap.

  11. #11
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    Quote Originally Posted by Norky
    Another way to do it is with LIMITs in the MySQL query. Say if you wanted to display 15 per page:

    $pageno = $_GET[pageno]
    $limit = $pageno * 15
    SELECT * FROM `blah_table` LIMIT $limit , 15

    Not by any means working code, but you should be able to understand it.

    Please ignore me if I've been rambling on about completely the wrong thing, but it sounds very similar to something I've done before, in which I used the above method.
    Yeh but how are you going to get certain entries to display on the second page? Can you limit values by doing LIMIT 16 - 20 ? Sure, that limits them, but how are you going to display entries 16 - 25 or whatever on the second page?

  12. #12
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    aidanjt, where did you get that script from? I've been going through it and there seems to be a few errors which would inevitably cause the script not to run. It's good, but some really obvious errors. Look at the for loop while $i++? And the echo function near the top hasn't got a closing bracket which would cause an error too.

  13. #13
    bored out of my tiny mind malfunction's Avatar
    Join Date
    Jul 2003
    Location
    Lurking
    Posts
    3,923
    Thanks
    191
    Thanked
    187 times in 163 posts
    • malfunction's system
      • Motherboard:
      • Gigabyte G1.Sniper (with daft heatsinks and annoying Killer NIC)
      • CPU:
      • Xeon X5670 (6 core LGA 1366) @ 4.4GHz
      • Memory:
      • 48GB DDR3 1600 (6 * 8GB)
      • Storage:
      • 1TB 840 Evo + 1TB 850 Evo
      • Graphics card(s):
      • 290X
      • PSU:
      • Antec True Power New 750W
      • Case:
      • Cooltek W2
      • Operating System:
      • Windows 10
      • Monitor(s):
      • Dell U2715H
    In MySQL 'limit' can have 2 params, start pos and number of rows to limit to - so

    "limit 0, 25" means first 25 rows (0-24)


    "limit 25, 25" means next 25 rows (25-49)

    So if you have a page size variable, and keep count of what page you are on (easy to do in the URL, can do it in the post data or session if you want), you want something like:

    limit (pagenum * pagesize), pagesize

    If you want to know how many rows the query would return without the limit query use this:

    select SQL_CALC_FOUND_ROWS field1, field2 from table 1...

    (note there's no comma between SQL_CALC_FOUND_ROWS and the first field name in the query... It doesn't have to be in caps either - just reminds me what it is when I glance at a query)

    The total number of rows is returned under the name "total" (i.e. access it like any other field name)

    This is all based on my work with ASP - using MySQL via ODBC - so might be different in PHP...

  14. #14
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable
    KeZZeR, i just wrote it quickly from the top of my head, I didn't test it because I still havn't got my test server sorted out, the mobo on my last one died
    should have my test rig sorted by next week.
    other than the bugs, there is room for improvment like more efficent SQL queries, it would cut down the amount of php code somewhat.
    But it gives you some idea of what you need which is what you were after I guess.

    I think it was a good attempt considering I only had 3 hours sleep just before a 9 hour shift at work and no php engine to help catching bugs
    Last edited by aidanjt; 09-02-2005 at 05:46 PM.

  15. #15
    Senior Member Kezzer's Avatar
    Join Date
    Sep 2003
    Posts
    4,863
    Thanks
    12
    Thanked
    5 times in 5 posts
    lol, well if i did it the SQL way how would i go about doing it? I'd need to add another 10 or so to the LIMIT query perhaps? I'm not sure :S

  16. #16
    Gentoo Ricer
    Join Date
    Jan 2005
    Location
    Galway
    Posts
    11,048
    Thanks
    1,016
    Thanked
    944 times in 704 posts
    • aidanjt's system
      • Motherboard:
      • Asus Strix Z370-G
      • CPU:
      • Intel i7-8700K
      • Memory:
      • 2x8GB Corsiar LPX 3000C15
      • Storage:
      • 500GB Samsung 960 EVO
      • Graphics card(s):
      • EVGA GTX 970 SC ACX 2.0
      • PSU:
      • EVGA G3 750W
      • Case:
      • Fractal Design Define C Mini
      • Operating System:
      • Windows 10 Pro
      • Monitor(s):
      • Asus MG279Q
      • Internet:
      • 240mbps Virgin Cable
    yep, "SELECT * FROM Entries LIMIT 9,10;" for eg would return the blog entries for page 2
    and something like "SELECT COUNT(*) FROM Entries;" to get the total number of records on the table.

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. CD-RW writing under XP
    By Anders in forum Software
    Replies: 7
    Last Post: 02-12-2003, 01:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •