Pagination - Counting from 0 without mysql_result (shows error)

Question

I am working on paginating my MySQL results to 10 per page. So far, I have been able the count how many results there are in the column, count how many pages there will be if there are 10 results/page, display the number of pages and also add links to each page (ex: Page1, Page2, Page 3, Page 4...)

Currently, all 38 results are showing on one page. When I click the page number, I am taken to the correct link but the content with the 38 results is the same. I know I need to divide the 38 results into the four pages. This is where I'm stuck at:

To find the number of pages, I have variable:

$pages = ceil($items_number / $per_page )

However, on multiple tutorials I've seen:

$pages = ceil(mysql_results($items_number,0) / $per_page )

which allows the data count to start at 0. When I tried this, I get an error

Fatal error: Call to undefined function mysql_results()

So I don't have the option to start counting results from 0 (using mysql_result) and break them into 10 per page.

How can I get around the mysql_result and be able to show the appropriate data/number of items for each page number?

Here's a link, clickable page numbers are on top: http://test.ishabagha.com/classic_cars/pag_test.php

Code:

<?php
require_once("./includes/database_connection.php");

    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    $page = "";

    $per_page = 10;
    $query = "SELECT productCode, productName, productLine, productScale, productVendor, productDescription, buyPrice FROM products WHERE `productLine` = 'Classic Cars'";
                $result = mysqli_query($dbc, $query)
                or die(mysqli_error($dbc));

    $query_count = "SELECT count(productLine) FROM products WHERE productLine = 'Classic Cars'";
    $items = mysqli_query($dbc, $query_count)
                or die(mysqli_error($dbc));
    $row = mysqli_fetch_row($items);

    $items_number = $row[0];

    $pages = ceil($items_number / $per_page )


?>

<!DOCTYPE html>
<html>
<head>
    <meta charset='utf-8'>
    <title>Home</title>
    <link type="text/css" rel="stylesheet" href="classic_cars.css" />
</head>

<body>
    <?php
        require_once("./includes/navigation.php");
    ?>

    <?php

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

        while ($row = mysqli_fetch_array($result)) { 
            $product_code = $row['productCode'];
            $product_name = $row['productName'];
            $product_line = $row['productLine'];
            $product_vendor = $row['productVendor'];
            $product_description = $row['productDescription'];
            $buy_price = $row['buyPrice'];

                echo "<tr>
                <td><p>$product_name</p></td>
                </tr>";

        } // end while ($row = mysqli_fetch_array($result))

    ?>

    <?php
        require_once("./includes/footer.php");
    ?>      
</body>
</html>

`mysql_` + `mysqli_` = no love. That translates to: You can't mix different MySQL APIs. — Funk Forty Niner, Jun 18 '15 at 00:45
@Fred-ii- yes, I would like to know if there is an alternative way of using mysql_result for mysqli — , Jun 18 '15 at 00:48
have a look at http://php.net/manual/en/class.mysqli-result.php and this Q&A http://stackoverflow.com/q/17707331/ and http://stackoverflow.com/q/2089590/ - more results can be found by Googling "mysqli_result alternative" or "mysql_result alternative" — Funk Forty Niner, Jun 18 '15 at 00:51

Logan Wayne · Answer 1 · 2015-06-18T01:47:32.577

Note:

It means that you are mixing deprecated mysql_* API with mysqli_*. You should use only one API in your project, and I would recommend to use mysqli_* only.
You forgot ; in your ceil($items_number / $per_page )
All the results are showing because you did not set a LIMIT on your query inside $query
In order to do this, you should know what page you are currently in your pagination

Get first the number of total rows (replace your previous one):

$items_number = mysqli_num_rows($items);

Let us determine what page you are currently, using $_GET["page"]. If no page is found in your URL, the default page to show is the first one.

Put this after your $pages = ceil($items_number / $per_page);

if (isset($_GET['page']) && is_numeric($_GET['page'])) { /* IF PAGE NUMBER IS VALID */
  $currentpage = (int) $_GET['page']; /* STORE THE CURRENT PAGE */
} else {
  $currentpage = 1; /* DEFAULT PAGE IS SET TO 1 */
} 

if ($currentpage > $pages) { /* IF CURRENT PAGE IS 2 OR MORE */
  $currentpage = $pages;
} 
if ($currentpage < 1) { /* IF CURRENT PAGE IS LESS THAN 1, WHICH WE SHOULD PREVENT */
  $currentpage = 1; /* DEFAULT PAGE IS SET TO 1 */
} 

$offset = ($currentpage - 1) * $per_page; /* SET AN OFFSET FOR YOUR QUERY LATER */

Your query should look like this instead:

$query = "SELECT productCode, productName, productLine, productScale, productVendor, productDescription, buyPrice
          FROM products
          WHERE `productLine` = 'Classic Cars'
          LIMIT $offset, $per_page"; /* LIMITS THE NUMBER TO SHOW IN A PAGE */

But we should move this query, and also the execution after the first code I had given above. So the arrangement is:

$query_count = "SELECT productLine FROM products WHERE productLine = 'Classic Cars'";
$items = mysqli_query($dbc, $query_count) /* EXECUTE THE QUERY */
            or die(mysqli_error($dbc)); 
$items_number = mysqli_num_rows($items); /* GET THE NUMBER OF RESULTS */
$items_number = number_format($items_number); /* SECURE THE FORMAT THAT IT IS A NUMBER */

$per_page = 10; /* NUMBER OF ROWS TO SHOW PER PAGE */
$pages = ceil($items_number / $per_page );

/* THE CODE I HAD GIVEN THAT GETS THE CURRENT PAGE AND DEFINES THE $offset */

/* THEN THE QUERY THAT LIMITS THE NUMBER OF ROWS TO FETCH */

/* THEN YOU FETCH THE RESULT, AND THEN THE HTML */

Pagination - Counting from 0 without mysql_result (shows error)

1 Answers1

Note: