SitePoint Sponsor

User Tag List

Page 1 of 3 123 LastLast
Results 1 to 25 of 56
  1. #1
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Prev/Next Links - FULL CODE!

    Hi there,
    I noticed that there are a lot of folks around that are asking how to do "Prev/Next" links in PHP from a MySQL resultset. There are also a lot of tutorials/sample code around that didn't work for me and a lot of other folks.

    I took the code from an article at PHPBuilder that basically didn't work. Somebody may be able to make this more efficient or tidy it up a little, but I haven't found a query that this didn't work with...yet!

    Hope this helps some folks out.

    PHP Code:
    <?

    $db 
    "<<DBNAME>>";
    $user "<<USERNAME>>";
    $pass "<<PASSWORD>>";
    $server "<<SERVER>>";

    $link mysql_connect($server,$user,$pass);
    if (! 
    $link)
    die (
    "Couldn't connect to Database");
    mysql_select_db($db);
    or die(
    "Couldn't open DB Table");
    }

    $limit=<<ITEMS PER PAGE>>; 
    $numresults=mysql_query("select * FROM <<DATABASE TABLE>><<ARGUMENTS>>",$link);
    $numrows=mysql_num_rows($numresults);

    if (empty(
    $offset)) {
        
    $offset=0;
    }

    $result=mysql_query("select * FROM <<DATABASE TABLE>><<ARGUMENTS>> limit $offset,$limit",$link);

    while (
    $data=mysql_fetch_array($result)) {
        <<
    DISPLAY DATA IN HERE>>
    ";
    }


    if (
    $offset==0) { 
    } else { 
        
    $prevoffset=$offset-20;
        print "
    <a href=\"$PHP_SELF?offset=0\"><<</a> &nbsp; \n";
        print 
    "<a href=\"$PHP_SELF?offset=$prevoffset\"><</a> &nbsp; \n";
    }

    $pages=intval($numrows/$limit);

    if (
    $numrows%$limit) {
        
    $pages++;
    }

    for (
    $i=1;$i<=$pages;$i++) { 
        
    $newoffset=$limit*($i-1);
        print 
    "<a href=\"$PHP_SELF?offset=$newoffset\">$i</a> &nbsp; \n";
    }

    $nh $offset $numrows;
    $n $nh $nh $nh;

    if (
    $n $limit) {
    } else {
        
    $newoffset=$offset+$limit;
        print 
    "<a href=\"$PHP_SELF?offset=$newoffset\">></a> &nbsp; \n";
    $finallink $numrows $limit;
    print 
    "<a href=\"$PHP_SELF?offset=$finallink\">>></a> &nbsp; \n";;
    }


    ?>
    This code produces paged resultsets from your MySQL query. "First Page" and "Last Page" navigation links are included automatically. "Next" and "Previous" links are removed if there are no more results or it's the first page respectively.

    Usage:
    Change <<DBNAME>>,<<USERNAME>>,<<PASSWORD>>,<<SERVER>> with your DB details.
    Change <<ITEMS PER PAGE>> with the amount of items you want to be displayed from your results. IE..10 would split your 50 item resultset into 50 pages.
    Change <<DATABASE TABLE>> with your, well, database table...
    Change <<ARGUMENTS>> with your resultset arguments. IE. ORDER BY Title.
    Change <<DISPLAY DATA IN HERE>> with your output format. Fields are in the $data array. IE -
    PHP Code:
    print $data["Field1"]." - ".$data["field2"]; 
    I hope this helps somebody!

  2. #2
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Someone should write an article on it and submit it to SitePoint.. *hits lightbulb above head*
    Jeroen Mulder

    w: www.jeroenmulder.com

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One thing missing from the above code. If you use variables in a where clause in your query, you must send those variables off to the next and previous pages. As far as Ican tell that step has been omitted from the above.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is another approach. It doesn't show the << and >> but that can be easily added and it shows the numbered page links instead of < and >

    So here are two examples, anyone have any others?

    PHP Code:
    <?

        
    //Number of records to show per page
        
    $limit 5;

        
    //Set the default offset = 0
        
    $offset = ($offset) ? $offset 0;

        
    //Encoded query string that needs to be passed to each page
        
    $query "&var=".urlencode($var);
        
        
        
    $db mysql_connect("""""") or DIE(mysql_error());
        
    mysql_select_db("");
        
        
    $r1 mysql_query("SELECT COUNT(*) from <<TABLENAME>> WHERE <<FIELD>> LIKE '$var%'") or die(mysql_error());
        
    $totalnum mysql_result($r100);
        

        
    $r2 mysql_query("SELECT * from <<TABLENAME>> WHERE <<FIELD>> LIKE '$var%' LIMIT $offset$limit") or die(mysql_error());
        while(
    $row mysql_fetch_array($r2)) {
            
    extract($row);
            print 
    "<<FIELD>><br>";
            }

        print 
    '<br>';
        
    freddyShowNav($offset$limit$totalnum$query);
        
        
            function 
    freddyShowNav($offset$limit$totalnum$query) {
                global 
    $PHP_SELF;
                if (
    $totalnum $limit) {
                        
    // calculate number of pages needing links 
                        
    $pages intval($totalnum/$limit);

                        
    // $pages now contains int of pages needed unless there is a remainder from division 
                        
    if ($totalnum%$limit$pages++;
                
                        if ((
    $offset $limit) > $totalnum) {
                            
    $lastnum $totalnum;
                            }
                        else {
                            
    $lastnum = ($offset $limit);
                            }
                        
    ?>
                            <table cellpadding="4"><tr><td>Page </td>
                        <?
                        
    for ($i=1$i <= $pages$i++) {  // loop thru 
                            
    $newoffset=$limit*($i-1);
                            if (
    $newoffset != $offset) {
                        
    ?>
                                <td><a href="<?=$PHP_SELF?>?offset=<?=$newoffset?><?=$query?>"><?=$i?></a></td>
                        <?
                                
    }     
                            else {
                        
    ?>
                                <td><?=$i?></td>
                        <?
                                
    }
                            }
                        
    ?>
                                </tr></table>
                        <?
                    
    }
                return;
                }

    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Enthusiast WizyWyg's Avatar
    Join Date
    Sep 2001
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A modification of that script if you want to pass them as links to a template:

    Code:
    <?
    
        //Number of records to show per page
        $limit = 5;
    
        //Set the default offset = 0
        $offset = ($offset) ? $offset : 0;
    
        //Encoded query string that needs to be passed to each page
        $query = "&var=".urlencode($var);
        
        
        $db = mysql_connect("..yourhost..", "user", "password") or DIE(mysql_error());
        mysql_select_db("database");
        
        $r1 = mysql_query("SELECT COUNT(*) from tablename WHERE field LIKE '$var%'") or die(mysql_error());
        $totalnum = mysql_result($r1, 0, 0);
        
    
        $r2 = mysql_query("SELECT * from tablename WHERE field LIKE '$var%' LIMIT $offset, $limit") or die(mysql_error());
        while($row = mysql_fetch_array($r2)) {
            extract($row);
            echo "<font face=\"Arial, Helvetica, sans-serif\" size=2><a href=\"page.php?tableId=".$row["tableId"]."\">".$row["field"]."</a></font><br>";
            }
    
        print '<br>';
        freddyShowNav($offset, $limit, $totalnum, $query);
        
        
            function freddyShowNav($offset, $limit, $totalnum, $query) {
                global $PHP_SELF;
                if ($totalnum > $limit) {
                        // calculate number of pages needing links 
                        $pages = intval($totalnum/$limit);
    
                        // $pages now contains int of pages needed unless there is a remainder from division 
                        if ($totalnum%$limit) $pages++;
                
                        if (($offset + $limit) > $totalnum) {
                            $lastnum = $totalnum;
                            }
                        else {
                            $lastnum = ($offset + $limit);
                            }
                        ?>
                            <table cellpadding="4"><tr><td><font face="Arial, Helvetica, sans-serif" size=2>Page</font></td>
                        <?
                        for ($i=1; $i <= $pages; $i++) {  // loop thru 
                            $newoffset=$limit*($i-1);
                            if ($newoffset != $offset) {
                        ?>
                                <td><a href="<?=$PHP_SELF?>?offset=<?=$newoffset?><?=$query?>"><?=$i?></a></td>
                        <?
                                }     
                            else {
                        ?>
                                <td><?=$i?></td>
                        <?
                                }
                            }
                        ?>
                                </tr></table>
                        <?
                    }
                return;
                }
    
    ?>
    Last edited by WizyWyg; Sep 26, 2001 at 13:37.

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmmm... I guess I didn't take into account EVERYONE'S database structure and table names.

    I didn't expect you to copy it word for word. You need to actually put in valid table names and field names the <<LABEL>> was meant as a place holder, you need to replace the LABEL including the <<>> with actual table and field anmes.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Enthusiast WizyWyg's Avatar
    Join Date
    Sep 2001
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    seems as if my post was deleted

    Also, when posting code, never include extraneous symbols (ie <<>> ) to signal where a person is supposed to substitute their information for. Some are just beginning in php and will think its "part" of the code. Just place them in capitals or assign them to a variable (ie $db_name = " ") and use the varialbe within your query. Less editing. (sorry being picky)

    though, on your coding Freddy, how does one use ORDER BY if they want a specific field in their table to be listed in order?

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm.. with your code I got

    Code:
    Warning: Unknown MySQL Server Host '..yourhost..' (0) in c:\wwwroot\hs~synop.php on line 18
    I think it has to do with the ..host..

    You should try to avoid including .. .. in your code as people may not get it.


    PHP Code:
    $r2 mysql_query("SELECT * from tablename WHERE field LIKE '$var%' ORDER BY fieldname LIMIT $offset$limit") or die(mysql_error()); 

    As a side note what does this mean?

    A modification of that script if you want to pass them as links to a template:

    If its a template shouldn't you be using style sheets anywas. Kinda takes away from the purpose of a template, if you still need to change font tags everytime you need to change a font size.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  9. #9
    SitePoint Enthusiast WizyWyg's Avatar
    Join Date
    Sep 2001
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    Hmm.. with your code I got

    Code:
    Warning: Unknown MySQL Server Host '..yourhost..' (0) in c:\wwwroot\hs~synop.php on line 18
    I think it has to do with the ..host..

    You should try to avoid including .. .. in your code as people may not get it.
    Sorry, that was not put in by me, guess it was the board, since its "yourhost" in my coding.


    PHP Code:
    $r2 mysql_query("SELECT * from tablename WHERE field LIKE '$var%' ORDER BY fieldname LIMIT $offset$limit") or die(mysql_error()); 

    As a side note what does this mean?

    table and within tables there are fields (sorry, this is how the 3 books I have show and refer to them as)
    table = your table your want to query in your DB
    Field = on which field you're doing your query on (or order by on)




    If its a template shouldn't you be using style sheets anywas. Kinda takes away from the purpose of a template, if you still need to change font tags everytime you need to change a font size.
    Um, the "previous/next" isn't a template (its just a "link list"). Which takes you to a template based on the values of a link (which can be css or whatever the person has set up for their template). I hope you're only joking.

  10. #10
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope I was merely responding to your remarks in your first post about how I should code my scripts. And actually I stripped out all the css support and any application specific code from the example to give people a base to work from. It is not meant to work out of box. No database script can unless the script creates the db schema. So you can rewrite it all day long and make it work for your app as when others want to use it they can modify it to work in their app.

    But you came back to say that your modified code would actually work and it may work for you but it isn't gonna work for everyone.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    echo "<font face=\"Arial, Helvetica, sans-serif\" size=2><a href=\"page.php?tableId=".$row["tableId"]."\">".$row["field"]."</a></font><br>";
    This is application specific as not everyone will have a page.php that uses tableID. KNow what I mean.

    table and within tables there are fields (sorry, this is how the 3 books I have show and refer to them as)
    table = your table your want to query in your DB
    Field = on which field you're doing your query on (or order by on)
    Not everyone has read the same books and it shouldn't matter what someone uses for an alias. There is no standard only what is set forth by the author of the book and within the scope of the group who have read the book.


    Um, the "previous/next" isn't a template (its just a "link list"). Which takes you to a template based on the values of a link (which can be css or whatever the person has set up for their template). I hope you're only joking.
    link list? that goes to another template. It should be reloading the same page and passing an offset so the correct records can be displayed. The next/prev links shouldn't be used to send someone to a template.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Enthusiast WizyWyg's Avatar
    Join Date
    Sep 2001
    Posts
    76
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey I believe in giving examples of everything possible for a script or code. Why create more problems and have people come asking "Well how can i do --this--?" if it can be answered at the same time?

    MY "modded" script may not work for others, but those looking may say "hey, i could use that at a later date" . You never know.

    I've saved scripts that i never thought I would use, but glad to have "kept" instead of "searching" for it over again.

    To each his own.

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey I believe in giving examples of everything possible for a script or code.
    Thats gonna be a hell of a script if it can take into account every possibility that one might want to use the script for.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    Feel my RewiredMind KMxRetro's Avatar
    Join Date
    Jan 2001
    Location
    Exeter, Devon, UK
    Posts
    477
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Woah! Calm down folks!

    I wasn't trying to say that this would work with EVERYBODY'S DB structure, just that it is the most reliable version for me and that it might help some folks out.

    With regards to code editing, I agree with Freddy here. Use what you like. I went with << >> because it seemed to stand out. I also said "replace <<TABLENAME>>" etc in my usage instructions.

    Thanks all,
    Ken

  15. #15
    Digital Warrior Renegade's Avatar
    Join Date
    Nov 2000
    Location
    Portland, OR
    Posts
    480
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, good post idea. And an even better SP article idea...

    One questions though, FreddydoesPHP:

    $offset = ($offset) ? $offset : 0;

    What in the world does that do? I have never seen anything like that before. I am guessing it is shorthand for something. *shrug* Could you take the time to explain it?

    Thanks

  16. #16
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is what is called making use of the ternary operator. Look here where you see ternary operator
    http://www.php.net/manual/en/languag...comparison.php

    PHP Code:
    $a 20;

    //This would print "the number is greater than 10"
    print ($a 10) ?  "the number is greater than 10" "the number is less than 10";

    //or

    $str = ($a 10) ?  "the number is greater than 10" "the number is less than 10";
    print 
    $str
    So whatever is in the parantheses is evaluated if its true use what ever comes after the ? if not use whatever is after the :

    In my case its saying if $offset is set then use it or else give it a value of 0
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  17. #17
    SitePoint Zealot sonique's Avatar
    Join Date
    Sep 2001
    Location
    Missouri [U.S.]
    Posts
    135
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by WizyWyg
    A modification of that script if you want to pass them as links to a template:

    Code:
    <?
    
        //Number of records to show per page
        $limit = 5;
    
        //Set the default offset = 0
        $offset = ($offset) ? $offset : 0;
    
        //Encoded query string that needs to be passed to each page
        $query = "&var=".urlencode($var);
        
        
        $db = mysql_connect("..yourhost..", "user", "password") or DIE(mysql_error());
        mysql_select_db("database");
        
        $r1 = mysql_query("SELECT COUNT(*) from tablename WHERE field LIKE '$var%'") or die(mysql_error());
        $totalnum = mysql_result($r1, 0, 0);
        
    
        $r2 = mysql_query("SELECT * from tablename WHERE field LIKE '$var%' LIMIT $offset, $limit") or die(mysql_error());
        while($row = mysql_fetch_array($r2)) {
            extract($row);
            echo "<font face=\"Arial, Helvetica, sans-serif\" size=2><a href=\"page.php?tableId=".$row["tableId"]."\">".$row["field"]."</a></font><br>";
            }
    
        print '<br>';
        freddyShowNav($offset, $limit, $totalnum, $query);
        
        
            function freddyShowNav($offset, $limit, $totalnum, $query) {
                global $PHP_SELF;
                if ($totalnum > $limit) {
                        // calculate number of pages needing links 
                        $pages = intval($totalnum/$limit);
    
                        // $pages now contains int of pages needed unless there is a remainder from division 
                        if ($totalnum%$limit) $pages++;
                
                        if (($offset + $limit) > $totalnum) {
                            $lastnum = $totalnum;
                            }
                        else {
                            $lastnum = ($offset + $limit);
                            }
                        ?>
                            <table cellpadding="4"><tr><td><font face="Arial, Helvetica, sans-serif" size=2>Page</font></td>
                        <?
                        for ($i=1; $i <= $pages; $i++) {  // loop thru 
                            $newoffset=$limit*($i-1);
                            if ($newoffset != $offset) {
                        ?>
                                <td><a href="<?=$PHP_SELF?>?offset=<?=$newoffset?><?=$query?>"><?=$i?></a></td>
                        <?
                                }     
                            else {
                        ?>
                                <td><?=$i?></td>
                        <?
                                }
                            }
                        ?>
                                </tr></table>
                        <?
                    }
                return;
                }
    
    ?>
    aaaaaah....the smell of open source...
    jmulder[THE MENTOR]'s nice guy online!
    Rig: AMD Athlon 800mhz; 384mb SDRam; 32mb nVidia @ 1024x768; ie 6; Windows ME; 15 gig; Cd-RW

  18. #18
    1-800-JMULDER JMulder's Avatar
    Join Date
    May 2001
    Location
    The Netherlands
    Posts
    1,745
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have once posted a snippet of the code for swimm5001, here's the thread.
    Jeroen Mulder

    w: www.jeroenmulder.com

  19. #19
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If anyone wanted to submit an article on this (which is a great idea), then I would suggest doing a search on "previous AND next AND page" for this forum, because there have been several threads discussing this over the past six or so months - and everyone has their own little creative ways of thinking out logic which is what makes coding so interesting.

  20. #20
    Digital Warrior Renegade's Avatar
    Join Date
    Nov 2000
    Location
    Portland, OR
    Posts
    480
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Who can and can't post an article? Are there any restrictions? Where might I find the rules/guidelines?

    Thanks

  21. #21
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    In my case its saying if $offset is set then use it or else give it a value of 0
    You should probably replace ( $offset ) with ( $offset >= 0 ) or the like.

    I could somefile.php?offset=aksjlksdjf and break it.

  22. #22
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a lot more to worry about than the offset if you want to try and break it. Again let me reiterate.



    This code was not meant to work out-of-box it was merely a basis for you to make your own script. There are many methods and techniques that I chose to use. They all work for me, but they may not all work for you. I suggest you use this code as a basis to develop your own navigation script.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  23. #23
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW Matt,


    By using

    ( $offset >= 0 )

    setting $offset to 'adfadfgdasf'

    will still pass. This may work a little better

    PHP Code:
    $offset = ($offset && ereg("^[0-9]+$"$offset)) ? $offset 0
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  24. #24
    SitePoint Wizard
    Join Date
    Jul 2001
    Location
    The Netherlands
    Posts
    2,617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Renegade
    Who can and can't post an article? Are there any restrictions? Where might I find the rules/guidelines?

    Thanks
    All articles should be submitted to either editor@sitepoint.com or to the Community Crier, which is crier@sitepoint.com if I am not mistaking.

    Georgina, the SitePoint editor will review the article and see if it is fit to be placed on SitePoint. You should ask for the StyleGuide2001 as well, since it gives you a lot of information on the formatting and style of your article.

    Tell me if you need to know more .

  25. #25
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    BTW Matt,

    By using

    ( $offset >= 0 )

    setting $offset to 'adfadfgdasf'

    will still pass. This may work a little better

    PHP Code:
    $offset = ($offset && ereg("^[0-9]+$"$offset)) ? $offset 0
    REALLY?

    Ahh, I guess 'asdad' gets cast to numeric and then set to 0? I would hope it would be 'false' so it wouldn't work.

    ereg is a painful performer so maybe if we can do something like this:
    ( int( $offset ) === 0 and int( $offset ) > 0 )

    So check and see if it is an integer and greater than 0?


Bookmarks

Posting Permissions

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