problem with empty rows of db to echo (with certain ids if For loop)

Question

Niloofar24 15 Posting Whiz

9 Years Ago

Hi.
I'm working on my own CMS, i want the script to echo 3 posts in every page, and want the script to echo from the recent post and back to the oldest post. I mean like this:

For example there is totaly 30 posts;

First page => posts 30-29-28
Second page => posts 27-26-25
....

This is my script:

<?php

    include('connection.php');
    $error = FALSE;
    $result = FALSE;
    try {
        $row = $conn->query("SELECT MAX(id) AS last_id FROM Posts")->fetch(PDO::FETCH_OBJ);
        echo $row->last_id;
        echo "<hr>";
        $last_id = $row->last_id;



         $total = $conn->query("SELECT COUNT(id) as rows FROM Posts")
                 ->fetch(PDO::FETCH_OBJ);

        $perpage = 3;
        $posts = $total->rows;
        $pages = floor($posts / $perpage);

        $get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

        $number = trim($get_pages);
        $number = filter_var($number, FILTER_VALIDATE_INT, $data);

        $prev = $number - 1;
        $next = $number + 1;



        if($get_pages == 0){
        $b = $last_id - 3;
        for($a = $last_id ; $a > $b ; $a--){

            $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
            $stmt->bindParam(':id', $a, PDO::PARAM_INT);
            $stmt->execute();

            $result = $stmt->fetchAll();
            foreach($result as $key => $row) {
                echo $row['Title'] . "<br>";
            }
        }
        }else{
            echo $get_pages . "<br>";
            $b = ($last_id - (3 * $get_pages));
            echo $b . "<br>";
            $c = $b - 3;
            echo $c . "<br>";
            for($a = $b ; $a > $c ; $a--){
                echo $a . "<br>";
                $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
                $stmt->bindParam(':id', $a, PDO::PARAM_INT);
                $stmt->execute();

                $result = $stmt->fetchAll();
                foreach($result as $key => $row) {
                    echo $row['Title'] . "<br>";
                }

            }
        }

    } catch(PDOException $e) {
        echo "Query error:".$sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>


<html>
<body>

            <?php

                if($result && count($result) > 0)
                {
                    echo "<h4>Total pages ($pages)</h4>";

                    # first page
                    if($number <= 0)
                        echo "<span>« prev</span> | <a href=\"?page=$next\">next »</a>";

                    # last page
                    elseif($number >= $pages)
                        echo "<a href=\"?page=$prev\">« prev</a> | <span>next »</span>";

                    # in range
                    else
                        echo "<a href=\"?page=$prev\">« prev</a> | <a href=\"?page=$next\">next »</a>";
                }

                else
                {
                    echo "<p>No results found.</p>";
                }

            ?>

</body>
</html>

It works but there is only a problem with the IDs that their posts has been deleted. When script send the id number to the db, if the post of the id doesn't exist, so there is nothing to be echo on the page and because of this part:

                $c = $b - 3;
                for($a = $b ; $a > $c ; $a--){

the script won't look for and call for more than 3 posts even if those posts were empty.

cms php

3 Contributors
16 Replies
405 Views
2 Days Discussion Span
Latest Post 9 Years Ago Latest Post by Niloofar24

lps 71 Posting Pro in Training

9 Years Ago

For me, I will not use the case of $b = $last_id - 3;for($a = $last_id ; $a > $b ; $a--). Instead, I will use a while loop to do it

$per_page = 3;
$count = 0;
while($count < $per_page){
    $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
    $stmt->bindParam(':id', $a, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll();
    foreach($result as $key => $row) {
        echo $row['Title'] . "<br>";
        $count++;
    }
}

coreyavis 13 Dev Poster

9 Years Ago

Why don't you grab all the data you want displayed all in one sql call instead of grabbing just the ID's, then grabbing the data that goes with the ID. You can use the 'ORDER BY' sql command to order from highest ID, and you can use the 'LIMIT' sql command to pull only the three posts you want to display defined by the pagination.

lps 71 Posting Pro in Training

9 Years Ago

Errr.....Don't tell me you actually copy paste my codes, to test? The blank page is mainly because it hit the infinity loop. As you can see, you original code is $b = $last_id - 3;for($a = $last_id ; $a > $b ; $a--) and my code will be

$per_page = 3;
$count = 0;
while($count < $per_page){
    $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
    $stmt->bindParam(':id', $a, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll();
    foreach($result as $key => $row) {
        echo $row['Title'] . "<br>";
        $count++;
    }
}

First of all, the $a is not set to id yet, and then, $a is not reduced along the while loop which causing the page hit the infinity loop.
Try this:

$per_page = 3;
$count = 0;
while($count < $per_page){
    $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
    $stmt->bindParam(':id', $last_id, PDO::PARAM_INT);
    $stmt->execute();
    $result = $stmt->fetchAll();
    foreach($result as $key => $row) {
        echo $row['Title'] . "<br>";
        $count++;
    }
    $last_id--;
}

lps 71 Posting Pro in Training

9 Years Ago

Hi there, there is a few problem if using the method above. First of all storing the last_id in to session is not a correct way. As when you navigate to 3rd page, you maybe navigate back to 2nd page or 4th page. Thus, the session variable cannot be used.
Another problem is $conn->prepare inside a while loop. This will cause the page to made connection for n-th times which may slow down the page loading time.

Here is a practice on what I previously done:

$per_page = 3;
$pages = isset($_GET['page']) ? $_GET['page'] : 1; //default the page to page number 1
$from = ($pages - 1) * $per_page;
$to = $pages * $per_page;

Instead of getting max id number, we should query all data using query like SELECT * FROM Posts ORDER BY ID DESC
Store result into an array(for better performance, we will store this array to server cache and remember to delete the cache whenever got new post)
Using the array, crop the data needed using $from and $to

Normally my result will seem like

$result = array(
    "1" => array("Title"=>"Title1","Author"=>"lps","Content"=>"Testing1"),
    "2" => array("Title"=>"Title2","Author"=>"lps","Content"=>"Testing2"),
    "3" => array("Title"=>"Title3","Author"=>"lps","Content"=>"Testing3")
); //1,2,3 is the ID

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.

Niloofar24 15 Posting Whiz · Answer 1 · 2015-04-20T01:10:08+00:00

Niloofar24 15 Posting Whiz

9 Years Ago

Anny suggestion?!

Niloofar24 15 Posting Whiz · Answer 2 · 2015-04-20T16:56:15+00:00

Well, it didn't work, nothing happend just the page try to load! And finally a blank page will be loaded.

lps 71 Posting Pro in Training · Answer 3 · 2015-04-21T00:35:35+00:00

I only post the code for the 'if' part, have you change the 'else' part in same manner?

Niloofar24 15 Posting Whiz · Answer 4 · 2015-04-21T03:41:57+00:00

@lps, i removed the else part just to check the if part but it didn't work.

I used this but why it doesn't work?!

        if($get_pages == 0){

            $sql = "SELECT TOP 3 * FROM Posts ORDER BY ID DESC";
            $result = $conn->query($sql);

            foreach($result as $key => $row) {
                echo $row['Title'] . "<br>";
                }
        }

It should works but it gives this error:

Query error:SELECT TOP 3 * FROM Posts ORDER BY ID DESC
SQLSTATE: Syntax error or access violation:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 * FROM Posts ORDER BY ID DESC' at line 1

@coreyavis, how can i use LIMIT x,y in this?

$sql = "SELECT * FROM Posts ORDER BY ID DESC";

The x part sets the start point, but i have set it by ORDER BY ID DESC before.

coreyavis 13 Dev Poster · Answer 5 · 2015-04-21T04:39:38+00:00

The x part sets the start point, but i have set it by ORDER BY ID DESC before.

I would assume the whole point you are using pagination is to display a different 3 posts per page, so while using ORDER BY ID DESC does set the starting point for the first 3 posts, it won't set the starting point for the 4th-6th posts or the 7th-9th posts. That is what the pagination is for. So the pagination will change your sql queries per page like this:

$sql = "SELECT * FROM Posts ORDER BY ID DESC LIMIT 0,3";
and
$sql = "SELECT * FROM Posts ORDER BY ID DESC LIMIT 3,3";
and
$sql = "SELECT * FROM Posts ORDER BY ID DESC LIMIT 6,3";

Niloofar24 15 Posting Whiz · Answer 6 · 2015-04-21T04:55:10+00:00

Of course not @lps! I didn't copy and past your codes!
This is what i did:

    $count = 0;
    while($count < 3){
        $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
        $stmt->bindParam(':id', $last_id, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetchAll();
        foreach($result as $key => $row) {
            echo $row['Title'] . "<br>";
            $count++;
            $a--;
        }
        }

The problem backs to $a-- which must be $last_id-- and out of the Foreach loop :|

Now please look at this:

<?php

    include('connection.php');
    $error = FALSE;
    $result = FALSE;
    try {
        $row = $conn->query("SELECT MAX(id) AS last_id FROM Posts")->fetch(PDO::FETCH_OBJ);
        echo $row->last_id;
        echo "<hr>";
        $last_id = $row->last_id;




         $total = $conn->query("SELECT COUNT(id) as rows FROM Posts")
                 ->fetch(PDO::FETCH_OBJ);

        $perpage = 3;
        $posts = $total->rows;
        $pages = floor($posts / $perpage);

        $get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

        $number = trim($get_pages);
        $number = filter_var($number, FILTER_VALIDATE_INT, $data);

        $prev = $number - 1;
        $next = $number + 1;





        if($get_pages == 0){
        $per_page = 3;
        $count = 0;
        while($count < $per_page){
            $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
            $stmt->bindParam(':id', $last_id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            foreach($result as $key => $row) {
                echo $row['Title'] . "<br>";
                $count++;
            }
            $last_id--;
        }
        $n = $last_id;
        echo "---" . $n . "---";
        session_start();
        $_SESSION["Number"] = $n;

        }else{

        session_start();
        $a = $_SESSION["Number"];
        $per_page = 3;
        $count = 0;
        while($count < $per_page){
            $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
            $stmt->bindParam(':id', $a, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            foreach($result as $key => $row) {
                echo $row['Title'] . "<br>";
                $count++;
            }
            $a--;
        }
        $n = $a;
        echo "---" . $n . "---";
        $_SESSION["Number"] = $n;
        }

    } catch(PDOException $e) {
        echo "Query error:".$sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>


<html>
<body>

            <?php

                if($result && count($result) > 0)
                {
                    echo "<h4>Total pages ($pages)</h4>";

                    # first page
                    if($number <= 0)
                        echo "<span>&laquo; prev</span> | <a href=\"?page=$next\">next &raquo;</a>";

                    # last page
                    elseif($number >= $pages)
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";

                    # in range
                    else
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <a href=\"?page=$next\">next &raquo;</a>";
                }

                else
                {
                    echo "<p>No results found.</p>";
                }

            ?>

</body>
</html>

I h ave problem with lines 34 to 75.
Everything works ok for if($get_pages == 0){ part but not for else part.

Whit the end of if($get_pages == 0){ part, $_SESSION["Number"] will be set to $n; but in the else part $a can't be set to $_SESSION["Number"]. Any suggestion? Where is the problem?
Please don't give me the answer just guide me with pointing to where problem comes from, thank you my friends:)

Niloofar24 15 Posting Whiz · Answer 7 · 2015-04-21T07:32:41+00:00

@lps, would you please guide me more with the array part?
How should i set data after foreach($result as $key => $row) { line in an array?

And please give me an example of how i can call for example the fifth index of that array wich is the fifth ID of posts, to set it into a variable or to echo it... i have a little problem with working array in php, thank you.

@coreyavis, i tried but it didn't work.

lps 71 Posting Pro in Training · Answer 8 · 2015-04-21T07:56:12+00:00

To store data

foreach($result as $key => $row) {
    $final_result[$row['id']] = array(
        "Title"=>$row['Title'],
        "Author"=>$row['Author'],
        "Content"=>$row['Content']
    );
}

With this, the $final_result will be populated with what you want. By this, we can even do filter to let user to choose to display things in asc or desc manner using asort() related function.

Then using the $from and $per_page, we can easily get the pagination done by using array_slice().

Niloofar24 15 Posting Whiz · Answer 9 · 2015-04-21T09:55:56+00:00

How can i echo the value of $output variable?

        $output = array_slice($final_result, $from, $to);

        echo "--- output:--- <br>" . $output;
        print_r($output);

Line 3 doesn't echo anything and line 4 echo like this:

--- output:---
ArrayArray ( [] => Array ( [Title] => Each morning [Author] => Groucho Marx [Content] => Each morning when I open my eyes I say to myself: I, not events, have the power to make me happy or unhappy today. I can choose which it shall be. Yesterday is dead, tomorrow hasn't arrived yet. I have just one day, today, and I'm going to be happy in it. ) )

Niloofar24 15 Posting Whiz · Answer 10 · 2015-04-21T10:52:59+00:00

Well i changed it a little:

    $output = array_slice($final_result, $from, $to);

    echo "--- output:--- <br>" . $output . "<br> --- <br>";
    print_r($output);

Still didn't find how to echo the variable:

--- output:---
Array
---
Array ( [0] => Array ( [Title] => The Gift of the Magi [Author] => O. Henry ) [1] => Array ( [Title] => line break [Author] => writter ) [2] => Array ( [Title] => delete test [Author] => me ) )

How can i echo array index in php?

lps 71 Posting Pro in Training · Answer 11 · 2015-04-21T12:06:28+00:00

Refer http://php.net/manual/en/function.array-slice.php for array_slice api.
First of all, the 3rd parameter is length, not ending. Then we need the id, so set preserve key to true.
To echo data from the array, simply use a for loop to echo it.

foreach($output as $id => $data) {
    echo $data['Title'] . "<small>by ".$data['Author']."</small>";
}

Niloofar24 15 Posting Whiz · Answer 12 · 2015-04-21T13:56:36+00:00

Thank you i finally could edit my script as well as possible and now it works well, the script will echo 3 posts in every page and will start from recent posts.

@lps do you remember this part:

    $per_page = 3;
    $pages = isset($_GET['page']) ? $_GET['page'] : 1; //default the page to page number 1
    $from = ($pages - 1) * $per_page;
    $to = $pages * $per_page;

As the 3th parameter in $output = array_slice($final_result, $from, $to); is length, not ending, so there is no need to calculate $to variable, because the $to variable is going to set how many posts should be printed in the page, so must be fixed and shouldn't change in different pages.

So i changed it into: (if ($get_pages == 0))

        $pages = 1;
        $per_page = 3;
        $from = ($pages - 1) * $per_page;

and this for else part:

         $pages = 1 + $get_pages;
         $per_page = 3;
         $from = ($pages - 1) * $per_page;

and there is this part too in the script for setting $get_pages variable:

        $get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

and finally i used $per_page instead of $to:

         $output = array_slice($final_result, $from, $per_page);

Thank you alot @lps for answers, helps, guidance and for the linkes.

Thank you @coreyavis for your helps too.