Displaying records from database using enum

[Robert_79]

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

[ryantroop]

What is your methodology that gets the count of all items? (ie, how are you determining you only need 3 pages of 25 a piece?)

[Robert_79]

Thank you for the reply ryantroop and excuse my ignorance but is this what you mean? I'm sorry but I am a beginner when it comes to php:

$page_number *= $per_page_records;

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

[ryantroop]

Yes, that's exactly what I meant.

How are you determining your $per_page_records value?

Edit: actually... since youre using the active='1' in your query, you just wouldn't pull any data if your page values were too high for your limit...

So.. it seems to be a bigger problem than what you are showing in your small bits of code. Somewhere, you are likely forgetting that active='1' flag when pulling the data, or you just have all of them flagged as active, when you think they should have a 0 or 2.

[benanamen]

Didnt you like the answers on all the other forums you cross posted on?

[pty]

This is why mixing code and markup makes things more difficult.

If this was in a Rails controller using an ORM it's simple and, more importantly, debuggable.

PER_PAGE = 20 # should be in a config file, here for clarity

def search
    @results = Video
        .where(active: true) # plus whatever other critera you like
        .limit(20)
        .offset(params[:page_num])

    # now calculate how many pages we'd need to display in the
    # pagination links. In rails we'd do this in a helper, but let's
    # keep it simple
    @pages =  ((@results.length + PER_PAGE) - 1) / PER_PAGE

    # all our info is visible, we can break at this point and inspect
    # the results manually or just dump it out to the log. And we've 
    # not even written any HTML yet! In fact, I could have done all this
    # directly in the console.
    logger.debug("#{@results.length} records retrieved, that's #{@pages} pages!")
end

This clean approach will work just as well in PHP with Laravel or Symfony, or Python and Django, etc.

[alan.davies]

https://www.daniweb.com/programming/web-development/tutorials/499320/common-issues-with-mysql-and-php

Point #6