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

Member Avatar for diafol

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...

  • 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

Member Avatar for diafol

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

Member Avatar for diafol

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.

Member Avatar for diafol

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 );

Thank you @diafol i will have fun tonight :P

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.