Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts

    next / previous links & multiple pages

    Hi,

    I'm making a product gallery, It's job is to pull out the matching products for that particular collection/product range and display them on the page in 2 rows, 3 to a row max.

    this is an bare bones example in progress

    So this dynamic gallery is fine if i have 6 or less products returned but if i have anymore, how can i make it so that instead of just outputting them down the page (i only want 2 rows of 3 per page) it outputs a next link and makes a new page?

    the code for m example linked above is below:

    PHP Code:
    <?php

    include "dbConnect.php";

    $sql "SELECT p.potID, p.potName, p.image, c.collectionID
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID "
    ;

    $result mysql_query($sql);

    $pictures="";

    echo (
    "

    <table border=\"1\" width=\"630\" height=\"600\">
        <tr height=\"90\" width=\"630\">
            <td height=\"90\" colspan=\"3\">
            nav
            <td>
        </tr>    
        <tr>
         "
    );

    $currentDone $totalDone 0;

    while (
    $row mysql_fetch_array($result)){
    $potName $row['potName'];
    $pic $row['image'];
    $potID $row['potID'];

    if (
    $currentDone == 3){
    $pictures .="
        </tr>
        <tr >"
    ;
    }

    $pictures .="

            <td height=\"200\" width=\"210\">
                <table align=\"center\" border=\"1\" width=\"160\">
                    <tr>
                        <td colspan=\"2\"><img src=\"{$pic}_small.jpg\"></td>
                    </tr>
                    <tr>    
                        <td><A HREF='detail.php?pot=$potID' target='popup' onClick=\"window.open('detail.php?pot=$potID','popup','width=300,height=500'); return false\">Detail</a></td>
                        <td>1</td>
                    </tr>
                </table>
            </td> "
    ;
    $currentDone ++;
    $totalDone ++;
    }

    if (
    $totalDone == 0$pictures ="Nothing returned";
    echo 
    $pictures;
    echo (
    "
        <tr height=\"100%\">
            <td colspan=\"3\">&nbsp;</td>
        </tr>
        </tr>
    </table>
    "
    );
    ?>

    I have spotted 2 tutorials on this but they seem a bit complicated, I wonder if it can be done simply (so i can understand). something like

    if totalDone == 6
    then output a next link
    and generate a new page to display the rest of the products

    on the new page display a previous link etc..


    Simple way of doing it?, i'd appreciate some help
    Last edited by mat; 09-12-2002 at 09:04 AM.

  • #2
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    I beleive the function you're looking for is mysql_num_rows(). This will return the total number of results from a query. So were you're using :
    Code:
    $row = mysql_fetch_array($result)
    add this
    Code:
    $resultTotal = myswl_num_rows()
    Additionally, you could add a LIMIT clause to your SQL statment. Something like,
    Code:
    SELECT p.potID, p.potName, p.image, c.collectionID
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID LIMIT $startNumber, $LimitNumber
    Where $startNumber is the number your would like to start at (ie prod #1, or prod #7, or prod #13 etc) and $LimitNumber is the total result set you're looking for (ie 6).
    (I may have that syntax backwards, it may be LIMIT $LimitNumber , #startNumber. Can't remember right now.)

    Hope this helps.

  • #3
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts
    so,

    SELECT p.potID, p.potName, p.image, c.collectionID
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID LIMIT $startNumber, $limitNumber

    $row = mysql_fetch_array($result)

    $resultTotal = mysql_num_rows($row)

    $startNumber = ? the first result
    $limitNumber = ? the sixth result

    if the $resultTotal is more than 6 then make a next link ?
    on the "next page" it can be similar code except it gets all results after $limitNumber (the rest of the results)

    but where does the next link go? wouldn't the next page actually be the same gallery.php reloaded, how does this work

    mat,

  • #4
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    This really belongs in the MySQL forum as stated by my Must Read thread so I will move it over there.....
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #5
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yeah sorry, i didn't know it would involve changing the SQL i thought it would all be php. (how php outputs data not how php interacts with a DB)
    Last edited by mat; 09-14-2002 at 08:00 AM.

  • #6
    mat
    mat is offline
    Regular Coder
    Join Date
    Jul 2002
    Posts
    199
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well this is the code i am using now in case anyone cares. It works but i wonder if it could be done using less code (1 sql query instead of 2) this is the only way i can think of ?


    Edit: code has now been fixed


    PHP Code:

    <?php

    include "dbConnect.php";


    if(!isset(
    $page)){
        
    $page 1;
    }


    $sqlNum "SELECT COUNT(*) 
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID"
    ;

    $resultNum mysql_query($sqlNum);

    $numRows mysql_num_rows($resultNum);



    $sql "SELECT p.potID, p.potName, p.image, c.collectionID
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID LIMIT "
    .(($page-1)*6).",".($page*6);

    $result mysql_query($sql);

    $pictures="";

    echo (
    "

    <table border=\"1\" width=\"630\" height=\"600\">
        <tr height=\"90\" width=\"630\">
            <td height=\"90\" colspan=\"3\">
            nav
            <td>
        </tr>
        <tr>
         "
    );

    $currentDone $totalDone 0;

    while (
    $row mysql_fetch_array($result)){
    $potName $row['potName'];
    $pic $row['image'];
    $potID $row['potID'];
    $collection $row['collectionID'];

    if (
    $currentDone == 3){
    $pictures .="
        </tr>
        <tr >"
    ;
    }

    $pictures .="

            <td height=\"200\" width=\"210\">
                <table align=\"center\" border=\"1\" width=\"160\">
                    <tr>
                        <td colspan=\"2\"><img src=\"{$pic}_small.jpg\"></td>
                    </tr>
                    <tr>
                        <td>$potName</td>
                        <td><A HREF='detail.php?pot=$potID' target='popup' onClick=\"window.open('detail.php?pot=$potID','popup','width=400,height=500'); return false\">DT</a></td>
                    </tr>
                </table>
            </td> "
    ;

    $currentDone ++;
    $totalDone ++;
    }

    if (
    $totalDone == 0$pictures ="Nothing returned";
    echo 
    $pictures;
    echo (
    "
        <tr height=\"100%\">
            <td colspan=\"3\">&nbsp;</td>
        </tr>
        </tr>
    </table>
    "
    );

    if(
    $page 1){
        echo 
    "<a href=\"output.php?page=".($page-1)."&collection=$collection\">Previous</a>";
    }
    if(
    $numRows > ($page*6)){
        echo 
    "<a href=\"output.php?page=".($page+1)."&collection=$collection\">Next</a>";
    }

    ?>

    I'd be interested if anyone knows a more efficient way of doing it

    mat.
    Last edited by mat; 09-15-2002 at 01:04 AM.

  • #7
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,278
    Thanks
    4
    Thanked 83 Times in 82 Posts
    For your first query you seem to just want the number of records so instead of actually pulling in all the data and then using PHP to count it you can use SQL to count the records:

    $sqlNum = "SELECT COUNT(*)
    FROM pots p, collection c
    WHERE p.collection = c.collectionID
    AND $collection = c.collectionID";

    If this is going to be a common hardcoded query and there will be many records what you could do is create another table to store a number value corresponding to the number of records of that query. So any time a record is added increment the number or when a record is deleted decrement the number. This is a common practice in large systems. I use it at work, of course our main DB is around 2 TeraBytes in size and has billions of records. We call our tracking table a LoadControlStatus. It keeps track of what records were processed previously so that the next time only the newest records get processed.

    Anyways that would save you some time and use less server resources as you would only have to do short query for that single value of that tracking table vereses having to either pull in all the records and count them or use SQL to count them.
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster


  •  

    Posting Permissions

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