Add multiple pages displaying ~10 database records on the same page

Question

i have code that retrieves records from a database and displays them on the pages. i am trying to get it so that when it retrieves more than 10 records it add a sub page as it were where the user can click to see the next 10 records without having to click makiing .php pages.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Good Homes</title>
<link href="CSS.css" rel="stylesheet" type="text/css" media="screen">

<style type="text/css">

</style>
</head>
<script>
</script>

<body>

<?php
$myConnection=mysqli_connect("goosedesignscouk.ipagemysql.com","watsonr","password","webtech");

if (mysqli_connect_errno()) {
    echo "Unable to connect, error: " . mysqli_connect_error();
}

$myResults=mysqli_query($myConnection, "SELECT NAME, SURNAME, DATE, MESSAGE FROM testimonies ORDER BY TID DESC");
?>

<div id="topbar">
<div id="orangebar">

</div>

<div id="logo">
    <img src="logo.png" alt="logo" height="100">
</div>

<div id="navigation">
    <ul id="css3menu1" class="topmenu">
        <li class="topfirst"><a href="index.php" style="width:120px;height:28px;line-height:28px;">Home</a></li>
        <li class="topmenu"><a href="search.php" style="height:28px;line-height:28px;">Search Property</a></li>
        <li class="topmenu"><a href="account.php" style="width:117px;height:28px;line-height:28px;">My Acount</a></li>
        <li class="topmenu"><a href="testimonies.php" style="width:115px;height:28px;line-height:28px;">Testimonies</a></li>
        <li class="toplast"><a href="about.php" style="width:112px;height:28px;line-height:28px;">About us</a></li>
    </ul>
</div>

<div id="login">
    <form>
        <table>
            <tr>
                <td><label><font color="#fff">Username:</font></label></td>
                <td><input type="text" name="username"></td>
            </tr>
            <tr>
                <td><label><font color="#fff">Password:</font></label></td>
                <td><input type="password" name="password"></td>
            </tr>
            <tr>
                <td></td><td align="right"><input type="button" onclick="parent.location='register.php'" value="Register"><input type="submit" value="Submit"></td>
            </tr>
         </table>
    </form>
</div> 

</div>

<div id="textbody">
    <h1>Add Your Testimonial</h1>
        <form method="post" action="addtestimonial.php">
            <table>
            <tr>
                <td>Name:</td>
                <td><input name="name" size="20" maxlength="20"></td>
            </tr>
            <tr>
                <td>Surname:</td>
                <td><input name="surname" size="20" maxlength="20"></td>
            </tr>
            <tr>
                <td>Testimonial:</td>
                <td><textarea name="testimonial" cols="100" rows="5" ></textarea></td>
            </tr>
        </table>
        <br />
        <input  type="submit" value="Add New" name="submit">
        <input type="reset" value="Cancel">
    </form>

<h1>Most Recent Testimonials</h1>
<?php 
    while ($currentRow = mysqli_fetch_array($myResults)) {
?>

<div id"testimonies">
    <p> 
        <?php 
            echo $currentRow['MESSAGE'];
            echo "<br>";
            echo $currentRow['NAME'] . " " . $currentRow['SURNAME'] . " - " . $currentRow['DATE'];
        ?>
    </p>
</div>

<?php
    }
?>
</div>



<div id="footer">

</div>

<?php
    mysqli_close($myConnection);
?>
</body>
</html>

so im trying to get it to display records 1 - 10 on page 1, 11 - 20 on page 2 etc.

score 1 · Answer 1 · edited May 23 '17 at 12:28

You need LIMIT and OFFSET in your query.

Example queries:

select * from table limit 10, 0

This will return 10 rows with 0 offset.

select * from table limit 10, 11

This will return 10 rows with offset 11. Etc etc etc.

How to calculate offset:

offset = (page - 1) * itemsPerPage + 1

Source

So what you need?:

Retrieve page and items per page from link (or you can assign items per page constant value, assign this variable in your script)
Build query, depending on page and items per page.

Your situation:

<? //connection to db

$items = 10;

$page = $_GET['page'];
$offset = ($page-1)*$items+1;

$myResults=mysqli_query($myConnection, "SELECT NAME, SURNAME, DATE, MESSAGE FROM testimonies ORDER BY TID DESC LIMIT $items, $offset");
?>

Note: I didn't work with mysqli, so I don't know, maybe my example using mysqli function not correctly, but I just want to show way how to achieve what you want.

Tell me if something is unclear.

Add multiple pages displaying ~10 database records on the same page

1 Answers1