CODEWITHSUNDEEP
phpmysql
Oliver.Kirkpatrick
Oliver.Kirkpatrick

Reputation: 11

Output database results over multiple pages

How would I output the selected data from the database over a certain amount of pages.

For example I'd like 20 result per page and it automatically adds the extra pages needed (bit like google search pages but no search is needed as I am getting everything from the database).

Sorry for a bad explanation and also badly indented code, new to stackoverflow. I've tried putting just the php, rest of the page isn't complete or I removed the unnecessary code, feel free to improve as well.

At the moment I am just calling all the data onto one page using very simple code

    <?php
    session_start();
    if(isset($_POST['logout'])) {
    unset($_SESSION['Username']);
    session_destroy();
    }
    ?>
    <!DOCTYPE html>
    <html>
    <head>
    <title>Backend</title>
    <link rel="stylesheet" type="text/css" href="style.css">
    </head>
    <body>
    <div class="" style="min-width: 1024px; max-width: 1920px; margin: 0 auto; min-height: 1280px; max-height: 1080px;">

    <?php
    if (isset ($_SESSION['Username']))
    {
    ?>

    <button onclick="location.href = 'logout.php';">Logout</button>



    <?php
    if (isset ($_SESSION['Username']))
    {
    echo "";

    $servername = "localhost";
    $username = "root";
    $password = "root";
    $dbname = "request";


    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }




    $sql = "SELECT * FROM request";
    $result = $conn->query($sql);


   $sql = "SELECT * FROM request ORDER BY id DESC";
   $result = $conn->query($sql);

   if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {

    if (isset ($_SESSION['Username']))
    {
    ?>


        <div align="center">
       <div class="requests">
       <p><?php echo $row["Name"]; ?></p>

        <p><?php echo $row["Number"]; ?></p>
        <p><?php echo $row["Song"]; ?></p>
        </div>

    </div>





    <?php
        }else{
            header("Location: index.php");
        }
        }
    } else {
        echo "0 requests";
    }
    }
    mysqli_close($conn);
    ?>

Upvotes: 1

Views: 3660

Answers (1)

Bruno Henrique Gaignoux Gomes
Bruno Henrique Gaignoux Gomes

Reputation: 81

Let's see an example of pagination in PHP. Before that, we need to understand what pagination is. Result pagination is quite simple.

We do a search on a certain DataBase table, and with the result of the search, we divide the number of records by a specific number to display per page. Related: Data pagination in PHP and MVC

For example a total of 200 records, and we want to display 20 per page, we will soon have 200/20 = 10 pages. Simple, right? Well let's go to the code then.

First connect to MySQL:

<?php
$conn = mysql_connect("host","user","pass");
$db = mysql_select_db("database");
?>

Now let's create the SQL clause that should be executed:

<?php
$query = "SELECT * FROM TableName";
?>

Let's get to work ... Specify the total number of records to show per page:

<?php
$total_reg = "10"; // number of records per page
?>

If the page is not specified the variable "page" will take a value of 1, this will avoid displaying the start page 0:

  <?php
    $page=$_GET['page'];
    if (!$page) {
    $pc = "1";
    } else {
    $pc = $page;
    }
    ?>

Let's determine the initial value of the limited searches:

<?php
$begin = $pc - 1;
$begin = $begin * $total_reg;
?>

Let's select the data and display the pagination:

<?php
$limit = mysql_query("$query LIMIT $begin,$total_reg");
$all = mysql_query("$query");

$tr = mysql_num_rows($all); // checks the total number of records
$tp = $tr / $total_reg; // checks the total number of pages

// let's create visualization
while ($dados = mysql_fetch_array($limit)) {
$name = $data["name"];
echo "Name: $name<br>";
}

// now let's create the "Previous and next"
$previous = $pc -1;
$next = $pc +1;
if ($pc>1) {
echo " <a href='?page=$previous'><- Previous</a> ";
}
echo "|";
if ($pc<$tp) {
echo " <a href='?page=$next'>Next -></a>";
}
?>

Ready, your pagination in PHP is created!

Upvotes: 2

Related Questions