Infinit Scroll - does not bring all DB data

0

I'm trying to implement an infinit scroll in my project, but I'm having trouble solving this problem

The connection is perfect and I'm getting the DB information, but the code only shows one entry, what should I change to resolve this?

Code index.php

<!DOCTYPE html>
<html>
    <head>
            <meta charset="utf-8">
            <title>jQuery Infinite Scroll</title>
    </head>
    <body>
            <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script><scripttype="text/javascript">
            var start = 0;
            var working = false;
            $(document).ready(function() {
                    $.ajax({
                            type: "GET",
                            url: "data.php?start="+start,
                            processData: false,
                            contentType: "application/json",
                            data: '',
                            success: function(r) {
                                    r = JSON.parse(r)
                                    for (var i = 0; i < r.length; i++) {
                                            $('body').append("<div><h1>"+r[i].videoName+"</h1><h2>Video View: "+r[i].videoViews+"</h2></div>")
                                    }
                                    start += 6;
                            },
                            error: function(r) {
                                    console.log("Something went wrong!");
                            }
                    })
            })
            $(window).scroll(function() {
                    if ($(this).scrollTop() + 1 >= $('body').height() - $(window).height()) {
                            if (working == false) {
                                    working = true;
                                    $.ajax({
                                            type: "GET",
                                            url: "data.php?start="+start,
                                            processData: false,
                                            contentType: "application/json",
                                            data: '',
                                            success: function(r) {
                                                    r = JSON.parse(r)
                                                    for (var i = 0; i < r.length; i++) {
                                                            $('body').append("<div><h1>"+r[i].videoName+"</h1><h2>Video View: "+r[i].videoViews+"</h2></div>")
                                                    }
                                                    start += 6;
                                                    setTimeout(function() {
                                                            working = false;
                                                    }, 4000)
                                            },
                                            error: function(r) {
                                                    console.log("Something went wrong!");
                                            }
                                    });
                            }
                    }
            })
            </script>
    </body>
    </html>

Data.php code:

<?php

class Video {
    public $videoName = "";
    public $videoViews = "";

    public function __construct($videoName, $videoViews) {
            $this->videoName = $videoName;
            $this->videoViews = $videoViews;
    }
}
$conectar = mysqli_connect("localhost", "***", "***", "***") or die("Erro 001 - Conection lost");
if ($conectar->connect_errno) {
     echo "Falha ao conectar com o mysql: (" . $conectar->connect_errno . ") " . $conectar->connect_error;
}

$start = $_GET['start'];
$data = array();
$query = "SELECT * FROM videos ORDER BY id DESC";
$resultado = mysqli_query($conectar, $query);
while($linhas = mysqli_fetch_assoc($resultado)){


$possibleVideos = array(
     new Video($linhas['nome'],$linhas['tempo']),
    );

    }

for ($i = $start; $i < $start+6; $i++) {
    if ($i < count($possibleVideos)) {
            array_push($data, $possibleVideos[$i]);
    }
}

//echo "<pre>";
echo json_encode($data);
?>
    
asked by anonymous 05.02.2018 / 22:56

1 answer

1

Use OFFSET or LIMIT to capture from a particular record. Ex:

/* Dessa forma você irá capturar os 5 primeiros registros */
SELECT * FROM 'users' LIMIT 5;

/* Dessa forma você irá capturar os 5 primeiros registros após o décimo registro */
SELECT * FROM 'users' LIMIT 5 OFFSET 10;

/* Mesma coisa que o item acima */
SELECT * FROM 'users' LIMIT 10, 5;

Because your variable $start will always receive a offset , then just use this mode. This will save server resources.

  

Imagine if you had more 10,000 posts. Have to return 10,000 several times and then grab only the last ones. Now imagine 1,000 people accessing your site at the same time ...

Full and refactored code:

<?php

$conectar = mysqli_connect("localhost", "***", "***", "***") or die("Falha ao conectar com o mysql: (" . $conectar->connect_errno . ") " . $conectar->connect_error);

/* Verifica se o parâmetro 'start' existe, caso não exista adiciona 0 à variável */
$start = isset($_GET['start']) ? $_GET['start'] : 0;

$data = array();

$query = "SELECT * FROM videos ORDER BY id DESC LIMIT {$start}, 5";

$resultado = mysqli_query($conectar, $query);

if ( $error = mysqli_error($conectar) ) {
    die( $error );
}

while($linhas = mysqli_fetch_assoc($resultado)){
    $data[] = array(
        "videoName" => $linhas['nome'],
        "videoViews" => $linhas['tempo']
    );
}

echo json_encode($data);

To add this data within div.row , you need to replace $('body').append() with $('div.row').append() .

But this is not recommended. If you have more than div with class row , text will be added in all of them. Ideally, you always use the id attribute for this. Ex:

Html:

<div id="videos" class="row"></div>

Js:

$('#videos').append(...);

Complete and refactored example:

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>jQuery Infinite Scroll</title>
    </head>

    <body>

        <div id="videos" class="row"></div>

        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script><scripttype="text/javascript">
            let start = 0;
            let working = false;

            $(document).ready(function() {
                loadData();
            })

            $(window).scroll(function() {
                if ($(window).scrollTop() + $(window).height() == $(document).height()) {
                    loadData();
                }
            })

            function loadData() {
                if (working == false) {                    
                    $.ajax({
                        type: "GET",
                        url: "index3.php",
                        contentType: "application/json",
                        data: {start: start},
                        beforeSend: function() {
                            working = true;
                        },
                        success: function(r) {
                            r = JSON.parse(r)

                            /* Percorre todos os valores que estão no 'array' r */
                            for (data of r) {
                                $('#videos').append("<div><h1>"+data.videoName+"</h1><h2>Video View: "+data.videoViews+"</h2></div>")
                            }

                            working = false;

                            start += 6;
                        },
                        error: function(r) {
                            console.log("Something went wrong!");
                        }
                    });
                }
            }
        </script>
    </body>
</html>
    
06.02.2018 / 14:27