Displaying records from database using enum

[Robert_Craig]

Hi

I am struggling with some code and was wondering any someone could please help me. My php skills are VERY limited and only get stuff done by reading forums like this and watching YouTube videos  :-[. I am enjoying learning so that is why I am sticking with it and not reverting back to html which I am pretty well accomplished at.

I have a phpMyAdmin database with 75 entries. My site has pagination setup where it shows 10 entries per page. I have setup one column in my database with enum and the values are 0, 1, 2, with the header 'active'

My query code is:

$sql = "SELECT * FROM videos WHERE active='1' AND title LIKE '%$q%' ORDER BY title";

Now my pages are only showing entries from the database where the active enum column is showing 1 (45 entries / 5 pages) which is fine, however my pagination is still showing 3 blank pages because for some reason it is picking up the entries that have the enum 0 or 2. So even though the entries with 0 and 2 are not showing, the pagination is still picking them up.

My guess is because in my sql I have SELECT * which is selecting all the entries and only displaying the active='1'. But how do I not get it to select the entries that have 0 and 2?

I am sorry if I have not been able to clearly convey my problem but if anyone has understood my problem please could you point me in the right direction, thank you very much.

Robert

[Barand]

The SELECT clause determines which columns are fetched by the query; the WHERE clause determines which rows. That query will only fetch rows where active =1.

How are you determining how many pages you will need?

If you are paginating, your query needs a LIMIT clause too.

[Robert_Craig]

Thank you Barand for the reply and apologies but the code I entered above was not complete:

    $page_number *= $per_page_records;

    $sql  = "SELECT * FROM videos WHERE active='1' ORDER BY title LIMIT {$page_number},{$per_page_records}";

One of my friends helped me with the code so that is why I a unsure if the above code is the part you are referring to

[Barand]

The limit clause should be

... LIMIT $offset, $per_page_records

where

$offset = ($page_number - 1) * $per_page_records

 

[Robert_Craig]

I think it will make more sense if I add the whole code here. I changed the code to how you mentioned but it still didn't work. I'm guessing because you did not see the code you could not get a full understanding.

 

<?php

/* Including the config.php file, which is holding the configuration variable details */
include_once 'lectures-config.php';

/* Establishing connection with database and returning connection object */
function connect_db(){
    $connection = mysqli_connect( DB_HOST, DB_USER, DB_PASS, DB_NAME );
    if(!$connection){
        die('Failed to establish connection with database!');
    }
    return $connection;
}

/* Getting the count of total rows in the videos table */
function get_rows_count(){
    
    $connection = connect_db();
    $sql = "SELECT COUNT(*) AS `row_count` FROM `videos`";
    $result = mysqli_query($connection, $sql);
    $result = mysqli_fetch_assoc($result);

    return $result['row_count'];
}

/* Creating pagination links */
function create_pagination(){
    
    global $per_page_pagination_links;
    global $per_page_records;

    $start = 0; $end = 0;

    $connection = connect_db();

    $page_number      = (isset($_GET['page']) AND !empty($_GET['page']))?intval($_GET['page']):1;

    $pagination_buttons  = $per_page_pagination_links; 

    if(($pagination_buttons%2) == 0){ die('Error: The count of pagination buttons should be an odd number'); }

    $rows                = get_rows_count(); 
    $last_page           = ceil($rows/$per_page_records); 
    $pagination          = '<ul>';

    if($page_number < 1 OR $page_number == '' OR $page_number == NULL){
        $page_number = 1;
    }
    else if($page_number > $last_page){
        $page_number = $last_page;
    }

    $half = floor($pagination_buttons/2);

    

    if( $pagination_buttons < $last_page ){

        if( $page_number == 1 ){
            // echo 1;
            for($i=1; $i<=$pagination_buttons; $i++){

                if($i == $page_number){
                    $pagination .= '<li class="current"><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                }
                else{
                    $pagination .= '<li><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                }
            }

            $pagination .= '<li><a href="index.php?page=' . ($pagination_buttons+1) . '">»</a></li>';
        }
        else if( $page_number > 1 ){

            // echo 2;
            if($page_number > $half){
                $start = abs($page_number - $half); 
                $end   = abs($page_number + $half);
            }
            else{
                $start = 1;
                $end   = $pagination_buttons;
            }

            //$start = abs($page_number - $half); 
            

            if( $end < $last_page){
                // echo 2.1;
                $pagination .= '<li><a href="index.php?page=' . ($start - 1) . '">«</a></li>';
                
                for($i=$start; $i<=$end; $i++){

                    if($i == $page_number){
                        $pagination .= '<li class="current"><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                    else{
                        $pagination .= '<li><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                }

                $pagination .= '<li><a href="index.php?page=' . ($end + 1) . '">»</a></li>';                
            }

            else if( $end == $last_page ){
                // echo 2.2;
                $pagination .= '<li><a href="index.php?page=' . ($start - 1) . '">«</a></li>';
                
                for($i=$start; $i<=$end; $i++){

                    if($i == $page_number){
                        $pagination .= '<li class="current"><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                    else{
                        $pagination .= '<li><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                }
            }

            else if( $end > $last_page ){
                // echo 2.3;
                $pagination .= '<li><a href="index.php?page=' . ($start - 1) . '">«</a></li>';
                
                $start = abs($last_page - $pagination_buttons)+1;

                for($i=$start; $i<=$last_page; $i++){

                    if($i == $page_number){
                        $pagination .= '<li class="current"><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                    else{
                        $pagination .= '<li><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
                    }
                }
            }
        }

    }
    else if( $pagination_buttons >= $last_page ){
        // echo 3;
        for($i=1; $i<=$last_page; $i++){

            if($i == $page_number){
                $pagination .= '<li class="current"><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
            }
            else{
                $pagination .= '<li><a href="index.php?page=' . $i . '">' . $i . '</a></li>';
            }
        }
    }

    $pagination .= '</ul>';

    echo $pagination;

}

/* Selecting and displaying content from database  */
function get_contents(){

    global $per_page_records;

    $connection = connect_db();

    $page_number      = (isset($_GET['page']) AND !empty($_GET['page']))?intval($_GET['page']):1;

    $rows                = get_rows_count(); 
    $last_page           = ceil($rows/$per_page_records); 
    
    if($page_number < 1 OR $page_number == '' OR $page_number == NULL){
        $page_number = 1;
    }
    else if($page_number > $last_page){
        $page_number = $last_page;
    }

    $page_number--;

    $page_number *= $per_page_records;

    $sql  = "SELECT * FROM `videos` WHERE active='1' ORDER BY timestamp DESC LIMIT {$page_number},{$per_page_records}";

    $result = mysqli_query($connection, $sql);

    $num_rows = mysqli_num_rows($result);

    if($num_rows){
        $str = '';
        while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

             $str .= '<div class="col-sm-6 video-outer equal-height"><div class="video-block"><a href="' . $row['url'] . '"><img src="' . $row['image'] . '" alt="' . $row['title'] . '"></a>
              <ul>
               <li>
                <a href="' . $row['url'] . '"><h3>' . $row['title'] . '</h3></a>' . $row['linebreak'] . '
               </li>
               <li>' . $row['speaker'] . '</li>
               <li><strong>Language:</strong> ' . $row['language'] . '</li>
               <li><strong>Date:</strong> ' . $row['date'] . '</li>
               <li><strong>Running Time:</strong> ' . $row['runningtime'] . '</li>
               <li><strong>Video Size:</strong> ' . $row['videosize'] . '</li>
               <li><strong>Audio Size:</strong> ' . $row['audiosize'] . '</li>
               <li> </li>
              </ul>
              </div>
            </div>';
          }

          echo $str;
    }
    else{
        echo '<p>Sorry no records exsit</p>';
    }

}

?>

 

[mac_gyver]

you will want to do something that was posted about an hour ago on one of the other help forums, and use the same where clause in both queries.

[Barand]

Your get_rows_count() function is getting the total number of records in the videos table. What you want know is how many records

WHERE active='1'

 

You are expecting too many pages, hence the empty ones

[Robert_Craig]

Thanks mac_gyver and Barand for the replies.

Barand I understand what you are saying and I will do some tweaks with my little php knowledge and get back to you if it works.

What about if I wanted to make active='1 AND 2' but not 0. I have tried AND but that doesn't work. What would be the correct 'term' (if that's the right word) to use in that instance?

[Barand]

WHERE active IN (1, 2)

which is equivalent to (but easier than)

WHERE active = 1 OR active = 2

 

[Robert_Craig]

Wow! Thank you so much Barand. I have made the few changes that you suggested and it is all working perfectly.

Thank you again