0

I'm trying to get this PHP script to paginate the data pulled from the mysql database.

It is going wrong somewhere in the second for loop. Instead of pulling the data through it is just returning blank or empty fields.

I need it to display the title, description and content fields from the database, along with the ID.

        require_once("../controls/config.php");
        $connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

        if ($connection->connect_errno) {
            printf("Connect failed: %s\n", $connection->connect_error);
            exit();
}

        // number of results to show per page
        $per_page = 3;

        // figure out the total pages in the database
        $result = $connection->query("SELECT * FROM pages");
        $total_results = $result->num_rows;
        $total_pages = ceil($total_results / $per_page);

        // check if the 'page' variable is set in the URL (ex: view-paginated.php?page=1)
        if (isset($_GET['page']) && is_numeric($_GET['page']))
        {
                $show_page = $_GET['page'];

                // make sure the $show_page value is valid
                if ($show_page > 0 && $show_page <= $total_pages)
                {
                        $start = ($show_page -1) * $per_page;
                        $end = $start + $per_page; 
                }
                else
                {
                        // error - show first set of results
                        $start = 0;
                        $end = $per_page; 
                }               
        }
        else
        {
                // if page isn't set, show first set of results
                $start = 0;
                $end = $per_page; 
        }

        for ($i = 1; $i <= $total_pages; $i++)
        {
                echo "<a href='?page=$i'>$i</a><br>";
        }

        // loop through results of database query, displaying them in the table 
        for ($i = $start; $i < $end; $i++)
        {
                // make sure that PHP doesn't try to show results that don't exist
                if ($i == $total_results) { break; }

                echo $i["id"].' ';
                echo $i["title"].' ';
                echo $i["description"].' ';
                echo $i["content"].' ';
                echo '<a href="edit.php?id='.$i["id"].'">Edit</a> ';
                echo '<a href="delete.php?id='.$i["id"].'">Delete</a><br>';

        }

?>
<p><a href="new.php">Add a new record</a></p>

Can anyone point me in the right direction?

Improve this question
2
  • If you use $i as an index, how on earth are you trying to access some stuff like $i["id"]? :) Commented Mar 12, 2014 at 0:10
  • @Jari i've only been working with PHP for a few days, it seemed logical as I am trying to pull data for id, title, description etc, for each matched record of the database. Can you shed any light on what I should be doing? Commented Mar 12, 2014 at 0:13

1 Answer 1

Reset to default
2

It looks like you are trying to treat $i as an associative array. Though $i is only a integer. Additionally, you do not have an array the contains the results from your mysqli query. You should try:

// this will loop through results and assign to $rows array
while($row = $result->fetch_array())
{
   $rows[] = $row;
}

// this will loop through $rows array and provide each column result
foreach($rows as $row)
{
    echo $row["id"];
    echo $row["title"];
    echo $row["description"];
    echo $row["content"];
}

For further information please refer to: https://www.php.net/mysqli_fetch_array

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This works to pull the data correct but then doesn't fit into the pagination structure. How can I make it do that?
You could try the following: for ($i = $start; $i < $end; $i++) { echo $rows[$i]["id"]; echo $rows[$i]["title"]; echo $rows[$i]["description"]; echo $rows[$i]["content"]; } This will loop through the $rows array in a different manor.
Additionally, I would encourage you to review arrays in PHP. It will help alot when parsing results from a mysql query. Check here: W3 School on PHP arrays