PHP NEXT & PREV buttons

Question

Stefce 146 Posting Pro

7 Years Ago

How do i select the next row in php from mysql table, i have 4 columns ID, video_title, video_desc, video_embed so i have more than 10000 rows in the table with youtube embed videos and i want to scroll though them with next and previous buttons but thats not the limit i will add more in the future. Am little confuzed on how do i change the videos from page to page with new URL's here is an example what i have right now.

index.php

<?php

        $sql = "SELECT ID, video_title, video_desc, video_embed FROM videos";
        $result = $conn->query($sql);

        if (!$result) {
            trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
        } elseif ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $ID = $row["ID"];
                $title = $row["video_title"];
                $desc = $row["video_desc"];
                $embed = $row["video_embed"];
            }
        }
    ?>
<body>
<div class="row">
<div class="col-sm-8 col-md-offset-2 text-center video">
    <h2><?= $title ?></h2>
    <a href="#"><span class="glyphicon glyphicon-menu-left" aria-hidden="true">PREVIOUS VIDEO</span></a>

    <div class="embed-responsive embed-responsive-16by9">
        <?= $embed ?>
    </div>

    <a href="#"><span class="glyphicon glyphicon-menu-right" aria-hidden="true">NEXT VIDEO</span></a>
    <h3><?= $desc ?></h3>

</div>
</div>
</body>

BTW The URL should be the $title of the video
So for example like this
www.example.com/Mercedes-Benz
you click NEXT VIDEO button, opens new page with new url
www.example.com/BMW-M4

php

Edited 7 Years Ago by Stefce

2 Contributors
6 Replies
474 Views
2 Weeks Discussion Span
Latest Post 7 Years Ago Latest Post by Stefce

diafol

7 Years Ago

You have a few options. You can do a complete page refresh on prev/next loading the new data or you can do an ajaxified prev/next. These days ajaxified seems to be ubiquitous and as you're only updating a small section of markup, I'd suggest ajax for updating.

In addition, you can load some "future/past" data into javascript variables so that you can load very quickly while the ajax retrieves the next "set" of data.

Not sure if you are allowing users to filter or sort videos, so that may be a additional consideration.
In addition, if all videos are youtube videos - do you really need to store the whole embed markup or can you get away with just storing the code, e.g. eUili_QXcgA?

I'll have a poke around some old code to see if I've got anything useful. May be a while...

diafol

7 Years Ago

I haven't forgotten! Been working so hopefully tomorrow!

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Stefce 146 Posting Pro · Answer 1 · 2017-07-07T12:59:34+00:00

Nope i dont have options for sorting the videos but maybe in future i will for now its just a simple slider with two buttons and video.
Mostly are youtube videos, but i also have facebook videos and vimeo.
Am using the whole embed markup..

Am a little confuzed on how to change the page, am thinking to make it with $_GET but thats not what i want, i want more advanced method. Thank you @diafol

diafol · Answer 2 · 2017-07-07T23:14:23+00:00

Ok, found one and fiddled a bit:

The PHP file (yt.php) - contains the class and the run code - usually the class would be in a separate file:
It also uses PDO - you'll have to re-jig it for mysqli...

class DiaVid
{
    private $dbh;
    private $filter = '';
    private $sortBy = '';
    private $cnt;
    private $offset;
    private $prev = 0;
    private $next = 0;
    private $sql;

    public function __construct( $dbh )
    {
        $this->dbh = $dbh;
    }

    private function getData()
    {
        $off = $this->offset;
        $num = $this->num;
        $this->sql = "SELECT `ID`, `video_title`, `video_desc`, `video_embed` FROM `video`{$this->filter}{$this->sortBy} LIMIT $off, $num";
        if($data = $this->dbh->query( $this->sql )->fetchAll( PDO::FETCH_ASSOC )) {
            return $data;
        }
        return false;
    }

    private function countAll()
    {
        $cntSql = "SELECT COUNT(`ID`) FROM `video`{$this->filter}{$this->sortBy}";
        if($data = $this->dbh->query( $cntSql )->fetchColumn()) {
            $this->cnt = $data[0];
            return $this->cnt;
        }
        return false;
    }

    public function filter( string $filterClause )
    {
        if( $filterClause ) $this->filter = ' WHERE ' . $filterClause;
        return $this;
    }

    public function sort( string $sortClause )
    {
        if( $sortClause ) $this->sortBy = ' ORDER BY ' . $sortClause;
        return $this;
    }

    private function fixOffsetNum( $offset )
    {
        if($offset <= 0){
            $this->offset = 0;
        }else{
            $this->prev = 1;
            $this->offset = $offset - 1;
        }
        if($offset < $this->cnt - 1){
            $this->next = 1;
        }
        $this->num = 1 + $this->prev + $this->next;
    }

    public function get( int $offset = 0 )
    {
        $output = [];
        if($cnt = $this->countAll()){
            $this->fixOffsetNum( $offset );
            if($data = $this->getData()){
                if($this->prev) {
                    $output['prev'] = $data[0];
                    $output['prev']['pos'] = $this->offset;
                    $output['current'] = $data[1];
                    if(isset( $data[2] )){
                        $output['next'] = $data[2];
                        $output['next']['pos'] = $this->offset + 2;
                    }
                }else{
                    $output['current'] = $data[0];
                    if(isset( $data[1] )){
                        $output['next'] = $data[1];
                        $output['next']['pos'] = $this->offset + 1;
                    }
                }
                return json_encode( $output );
            }
        }
        return false;
    }
}

$pdo = new PDO("mysql:host=localhost;dbname=dw","root","");
$pos = (isset($_GET['pos'])) ? intval( $_GET['pos'] ) : 0;
$dv = new DiaVid( $pdo );
echo $dv->get( $pos );

Now the markup page:

<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Page Title</title>
</head>
<body>
<div>
    <div id="video">
        <h3 id="videotitle"></h3>
        <p id="videodesc"></p>
        <div id="videoembed"></div>
    </div>
    <button id="prev" style="display: none;"></button> <button id="next" style="display: none;"></button>
</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>

    $('#prev, #next').click(function(){
        var pos = $(this).data('pos');
        getVideo(pos);
    });

    function getVideo( id )
    {
        $.getJSON('yt.php', {pos: id}).done(function(data){
            var p = $('#prev');
            var n = $('#next');

            if(data.prev){
                p.html('Previous: ' + data.prev['video_title']);
                p.data('pos', data.prev['pos']);
                p.show();
            }else{
                p.hide();
            }

            if(data.next){
                n.html('Next: ' + data.next['video_title']);
                n.data('pos', data.next['pos']);
                n.show();
            }else{
                n.hide();
            }

            if(data.current){
                $('#videotitle').html(data.current['video_title']);
                $('#videodesc').html(data.current['video_desc']);
                $('#videoembed').html(data.current['video_embed']);
            }
        });
    }

    getVideo(0);

</script>
</body>
</html>

It's not production code - have a play with it.

diafol · Answer 3 · 2017-07-11T09:02:37+00:00

Re-jigged for mysqli:

class DiaVid
{
    private $dbh;
    private $filter = '';
    private $sortBy = '';
    private $cnt;
    private $offset;
    private $prev = 0;
    private $next = 0;
    private $sql;

    public function __construct( $dbh )
    {
        $this->dbh = $dbh;
    }

    //Affected Method
    private function getData()
    {
        $off = $this->offset;
        $num = $this->num;
        $this->sql = "SELECT `ID`, `video_title`, `video_desc`, `video_embed` FROM `video`{$this->filter}{$this->sortBy} LIMIT $off, $num";
        if($result = $this->dbh->query( $this->sql )){
            $data = $result->fetch_all(MYSQLI_ASSOC);
            $result->free();
            return $data;
        }
        return false;
    }

    //Affected Method
    private function countAll()
    {
        $cntSql = "SELECT COUNT(`ID`) FROM `video`{$this->filter}{$this->sortBy}";
        if($result = $this->dbh->query( $cntSql )){
            $row = $result->fetch_array(MYSQLI_NUM);
            $this->cnt = $row[0];
            $result->free();
            return $this->cnt;
        }
        return false;
    }

    public function filter( string $filterClause )
    {
        if( $filterClause ) $this->filter = ' WHERE ' . $filterClause;
        return $this;
    }

    public function sort( string $sortClause )
    {
        if( $sortClause ) $this->sortBy = ' ORDER BY ' . $sortClause;
        return $this;
    }

    private function fixOffsetNum( $offset )
    {
        if($offset <= 0){
            $this->offset = 0;
        }else{
            $this->prev = 1;
            $this->offset = $offset - 1;
        }
        if($offset < $this->cnt - 1){
            $this->next = 1;
        }
        $this->num = 1 + $this->prev + $this->next;
    }

    public function get( int $offset = 0 )
    {
        $output = [];
        if($cnt = $this->countAll()){
            $this->fixOffsetNum( $offset );
            if($data = $this->getData()){
                if($this->prev) {
                    $output['prev'] = $data[0];
                    $output['prev']['pos'] = $this->offset;
                    $output['current'] = $data[1];
                    if(isset( $data[2] )){
                        $output['next'] = $data[2];
                        $output['next']['pos'] = $this->offset + 2;
                    }
                }else{
                    $output['current'] = $data[0];
                    if(isset( $data[1] )){
                        $output['next'] = $data[1];
                        $output['next']['pos'] = $this->offset + 1;
                    }
                }
                return json_encode( $output );
            }
        }
        return false;
    }
}

//New mysqli object instead of PDO
$mysqli = new mysqli('localhost','root','','dw');
$pos = (isset($_GET['pos'])) ? intval( $_GET['pos'] ) : 0;
//Parameter is mysqli not PDO object
$dv = new DiaVid( $mysqli );
echo $dv->get( $pos );

Stefce 146 Posting Pro · Answer 4 · 2017-07-21T18:56:14+00:00

Thank you @diafol i will have fun tonight :P