MySQL PHP Pagination - Getting the same results on every page or no results at all

Question

I'm trying to query data from MySQL. I need the data table to be paginated. See the code below; you can see that I use "OFFSET" after the limit. I can also use ","

However, I tried both but OFFSET does not bring up any results and "," brings up results but the same 10 results no matter which page I'm on.

Here is the code:

// define how many results you want per page
$results_per_page = 10;

// find out the number of results stored in database
$sql='SELECT * FROM ETF';
$result = mysqli_query($con,$sql);

$number_of_results = mysqli_num_rows($result);

// determine number of total pages available
$number_of_pages = ceil($number_of_results/$results_per_page);

// determine which page number visitor is currently on
if (!isset($_GET["page"])) {
  $page = 1;
} else {
  $page = $_GET["page"];
}

// determine the sql LIMIT starting number for the results on the displaying page
$this_page_first_result = ($page-1)*$results_per_page;

// retrieve selected results from database and display them on page
$sql='SELECT * FROM ETF LIMIT '. $this_page_first_result . " OFFSET " . $results_per_page;

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

while($row = mysqli_fetch_assoc($result)) {
  echo $row['ETF'] . ' ' . $row['ETF NAME']. '<br>';
}


// display the links to the pages
for ($page=1;$page<=$number_of_pages;$page++) {
  echo '<a href="index.php/stocks/sec-forms/?page=' . $page . '">' . $page. '</a>';
}

Answer 1

I'm not sure what you mean by using "," but:

1. The LIMIT should be 10 - i.e. $results_per_page (your constant)

2. The OFFSET should be dynamic depending on what page you're on. On page 1, OFFSET=0 (or you can omit OFFSET on page 1 if you want). On page 2, the OFFSET in your example should be 10.

3. Where is your ORDER BY clause? It's important to do this for LIMIT/OFFSET to work consistently.

4. I'm not sure if this is your final production code, but just a side note: in production, never use $_GET directly in your query. You aren't at the moment, but depending on what you do for the above point, make sure to verify that your $_GET parameter is an integer before you do anything with it, to guard against SQL injection.