What is wrong with my code?

[Revolutsio]

I am trying to do pagination on my site, I have over 3,000 entries and would like the page to show 12 images per page and the code below gives me an error

: Uncaught TypeError: mysqli_fetch_array(): Argument #1 ($result) must be of type mysqli_result, bool given

if(!isset($_GET['page'])) {
    $page_number = 1;
} else {
    $page_number = $_GET['page'];
}
$limit = 12;
$initial_page = ($page_number-1) * $limit; 
$getQuery = "SELECT * FROM games";
$result = mysqli_query($db, $getQuery);
$total_rows = mysqli_num_rows($result);
$total_pages = ceil($total_rows / $limit)

$getQuery = "SELECT * FROM games LIMIT" . $initial_page.','.$limit;
        $result = mysqli_query($db,$getQuery);
        while ($row = mysqli_fetch_array($result)) {
            echo $row['image'];
         }

I have tried about 20 different tutorials on pagination and get get none to work.

[Barand]

The error message tells you the query failed. Try adding a space after "LIMIT"

$getQuery = "SELECT * FROM games LIMIT $initial_page, $limit";

 

[Revolutsio]

Thank You

[Revolutsio]

As it now works and I get 12 images, I have two more questions.

1.. how do i stop the code below showing all 261 buttons 

for($page_number = 1; $page_number<= $total_pages; $page_number++) {  

        echo '<a href = "home3.php?page=' . $page_number . '">' . $page_number . ' </a>';  

2. how do i also get text below the images with the follow code?

<a href="details.php?id=<?php echo $row['id']?>">

 

[ginerjm]

If I read  you correctly, you want to make sure that the anchor tag is located below the image?  Each image and text should be output inside of a div tag and you can position each pair as you like inside of that.   Perhaps something like:

<div>
<img>
<br>
<center>
anchor
</center>
</div>

Repeat that block of code for each image you wish to appear.

As for the 261 images, don't know what you are referring to.  But if it is because you are involved in the pagination here, then keep track of what id you are querying for and start your output loop with that value and only go for that number plus you limit value. 

Edited June 6, 2022 by ginerjm

[Barand]

12 minutes ago, Revolutsio said:

how do i stop the code below showing all 261 buttons 

I do it by just showing buttons for first and last and a few either side of the current page

 +------+   +------+         +------+    +------+    +------+                +------+    +------+    +------+         +------+   +------+
 | Prev |   |   1  |   .  .  |  97  |    |  98  |    |  99  |      100       | 101  |    | 102  |    | 103  |   .  .  | 261  |   | Next |
 +------+   +------+         +------+    +------+    +------+                +------+    +------+    +------+         +------+   +------+

 

[Revolutsio]

12 minutes ago, ginerjm said:

If I read  you correctly, you want to make sure that the anchor tag is located below the image?  Each image and text should be output inside of a div tag and you can position each pair as you like inside of that.   Perhaps something like:

<div>
<img>
<br>
<center>
anchor
</center>
</div>

Repeat that block of code for each image you wish to appear.

As for the 261 images, don't know what you are referring to.  But if it is because you are involved in the pagination here, then keep track of what id you are querying for and start your output loop with that value and only go for that number plus you limit value. 

Sorry what i meant was how do i add each text under the image to go to another page?

[ginerjm]

And I think that is what I showed you.  The 'anchor' is the <a> tag that you build to do just what you asked.

[cyberRobot]

Have you considered using a CSS grid layout? A quick example can be found here:
https://css-tricks.com/how-do-you-make-a-layout-with-pictures-down-one-side-of-a-page-matched-up-with-paragraphs-on-the-other-side/

A more in-depth description of grid layouts can be found here:
https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Grid_Layout/Basic_Concepts_of_Grid_Layout

 

Side note: the <center> tag mentioned in an earlier post should be avoided since it has been deprecated (https://developer.mozilla.org/en-US/docs/Web/HTML/Element/center). CSS is usually the way to go when changing how something visually appears on the page.

[Strider64]

I have gotten inspiration out of this person that I found on CodePen - https://codepen.io/annastasshia especially this pen https://codepen.io/annastasshia/pen/YzpEajJ in the past.

I no longer used my modification of that code, but for something that you are doing I think would fit very well as I easily modified to include pagination.

https://github.com/Strider64/phototechguru/blob/master/photogallery.php

 

[Revolutsio]

17 hours ago, cyberRobot said:

Have you considered using a CSS grid layout? A quick example can be found here:
https://css-tricks.com/how-do-you-make-a-layout-with-pictures-down-one-side-of-a-page-matched-up-with-paragraphs-on-the-other-side/

A more in-depth description of grid layouts can be found here:
https://developer.mozilla.org/en-US/docs/Web/CSS/CSS_Grid_Layout/Basic_Concepts_of_Grid_Layout

 

Side note: the <center> tag mentioned in an earlier post should be avoided since it has been deprecated (https://developer.mozilla.org/en-US/docs/Web/HTML/Element/center). CSS is usually the way to go when changing how something visually appears on the page.

I am using a grid system . And the code works but i can not get it not to show the full 261 buttons as the code to show the buttons will show all

<?php 
        echo "<div class='pagination'>";
          for($page_number = 1; $page_number<= $total_pages; $page_number++) {  

        echo '<a href = "home3.php?page=' . $page_number . '">' . $page_number . ' </a></div>';  

    }    
     ?>

it shows 113 114 etc.

<div class="wrapper">       
        <div class="span-col-4"><img src="images/logo2.png"></div>
        <div class="span-col-4"><?php include 'includes/navbar.php'; ?></div>
        <div class="span-col-4"><?php include 'includes/a-z.php'; ?></div>
        <div class="span-col-4"> <!--- Pagination code goes here ---></div>
        <?php 
        $getQuery = "SELECT * FROM games LIMIT $initial_page, $limit";
        $result = mysqli_query($db,$getQuery);
        while ($row = mysqli_fetch_array($result)) {
            echo "<div class='picture'><img class='img' src='images/" . $row['image'] ."'>
                    <div class='text text-shadow box-shadow'>{$row['game']}</div>
                        </div>";
                        
         }         
     ?>   
     
     <?php 
        echo "<div class='pagination'>";
          for($page_number = 1; $page_number<= $total_pages; $page_number++) {  

        echo '<a href = "home3.php?page=' . $page_number . '">' . $page_number . ' </a></div>';  

    }    
     ?>

here is the code for the page

														|Logo|
											
													  |Nav bar|
														|a-z|

	                                |image 1|   |image 2|  |image 3|  |image 4|
 													|1| |2| |3|

 

[Barand]

3 hours ago, Revolutsio said:

but i can not get it not to show the full 261 buttons as the code to show the buttons will show all

Then change the code. As long as you are telling it to show all buttons, that, strangely, is what it's going to do.

[mac_gyver]

if you are interested in displaying a range of links around the current page number (there's no guarantee the OP even saw the post suggesting that), here's some example code -

<?php

// build pagination links: prev, 1, range around current page, total_pages, next

// number of +/- links around the current page number
$range = 3;

// number of items per page
$limit = 12;

// get total number of rows using a SELECT COUNT(*) ... query
$total_rows = 3121; 

// calculate total number of pages
$total_pages = ceil($total_rows / $limit);

// get current page, default to page 1
$page = $_GET['page'] ?? 1; 

// limit page to be between 1 and $total_pages
$page = max(1,min($total_pages,$page));

// produce array of pagination numbers: 1, range around current page, total_pages, without duplicates, between 1 and total_pages
$links = array_filter(array_unique(array_merge([1],range($page-$range, $page+$range),[$total_pages])),
function ($val) use ($total_pages) { return $val >= 1 && $val <= $total_pages; });

// build pagination links
$pagination_links = '';

// get a copy of any existing get parameters
$get = $_GET;

// produce previous
$get['page'] = $page - 1;
$qs = http_build_query($get,'', '&amp;');
$pagination_links .= $page == 1 ? 'prev ' : "<a href='?$qs'>prev</a> ";

// produce numerical links
foreach($links as $link)
{
	$get['page'] = $link;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $link == $page ? "$link " : "<a href='?$qs'>$link</a> ";
}

// produce next
$get['page'] = $page + 1;
$qs = http_build_query($get,'', '&amp;');
$pagination_links .= $page == $total_pages ? 'next' : "<a href='?$qs'>next</a>";

// output pagination
echo $pagination_links;

note: your query to get the total number of rows should NOT select all the columns and all the rows of data. you should use a SELECT COUNT(*) ... query, then fetch the counted value.

 

Edited June 7, 2022 by mac_gyver

[Revolutsio]

1 hour ago, Barand said:

Then change the code. As long as you are telling it to show all buttons, that, strangely, is what it's going to do.

That is the problem with the for statement how do i do that

[Barand]

7 minutes ago, Revolutsio said:

That is the problem with the for statement how do i do that

Have you tried @mac_gyver's example

[Revolutsio]

I have tried the @mac_gyver code 

Now I cannot get the next 12 when I press the number 2

[mac_gyver]

32 minutes ago, Revolutsio said:

Now I cannot get the next 12 when I press the number 2

wouldn't that mean that you need to investigate why it isn't working in order to find and fix what's causing the incorrect operation?

[Revolutsio]

19 hours ago, mac_gyver said:

wouldn't that mean that you need to investigate why it isn't working in order to find and fix what's causing the incorrect operation?

Could you tell me what I need to add in my Select query, I have tried all the arrays from your code and can not get to the second page

$getQuery = "SELECT * FROM games LIMIT $total_pages, $limit";

 

[mac_gyver]

aside from that query needing the columns you are SELECTing listed, nothing about that select query itself needed to be changed. didn't the previous pagination code cause the correct page 2, 3, ... content to be queried for and displayed?

the initial sql query used a variable named $initial_page. what is the code setting the value in that variable? when you integrated the code that i gave into your existing code, did you make sure that the $initial_page variable was producing the expected result? hint: i intentionally kept the name for the page value the same throughout the code, rather than to use two different names for it. you will need to go though the code and reconcile any variable name differences for that value.

 

 

[Barand]

I've been trawling my archive and found a sample pagination script from a few years ago.

Outputs...

Code (There is code at the end of the script to create the test data if required)

<?php
/*  PDO CONNECTION *********************************************/
    $host = 'localhost';
    $username = '????';
    $password = '????';
    $database = 'jointute';
    
    $dsn = "mysql:dbname=$database; host=$host; charset=utf8";

    $db = new pdo($dsn, $username, $password, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
/***************************************************************/        

const PERPAGE = 2;

$res = $db->query("SELECT COUNT(*) 
                   FROM pupil
                   ");
$total = $res->fetchColumn();

$page = $_GET['page'] ?? 1;

$stmt = $db->prepare("SELECT  fname
                            , lname
                            , DATE_FORMAT(dob, '%b %D') as birthday
                      FROM pupil
                      ORDER BY MONTH(dob), DAY(dob)
                      LIMIT ?,?
                      ");
$stmt->execute( [ ($page-1) * PERPAGE,
                   PERPAGE
                ]);
$output = '';
foreach ($stmt as $rec) {
    $output .= "<div class='pupil'>
                    <div class='label'>Name:</div> {$rec['fname']} {$rec['lname']}<br>
                    <div class='label'>Birthday:</div> {$rec['birthday']}
                </div>\n";
}

/**************************************************************************************
* function to output page selection buttons
*                     
* @param int $total   total records
* @param int $page    current page number
* @return string      selection buttons html
*/
function page_selector($total, $page)
{
    if ($total==0) {
        return '';
    }
    $kPages = ceil($total/PERPAGE);
    $filler = '&nbsp;&middot;&nbsp;&middot;&nbsp;&middot;&nbsp;';
    $lim1 = max(1, $page-2);
    $lim2 = min($kPages, $page+3);
    $p = $page==1 ? 1 : $page - 1;
    $n = $page== $kPages ? $kPages : $page + 1;;
    $out = "$kPages page" . ($kPages==1 ? '' : 's') . " &emsp;";
    if ($kPages==1) {
        return $out;
    }
    $out .= ($page > 1) ? "<div class='pagipage' data-pn='$p'>Prev</div>&ensp;" : '';
    if ($page > 4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div> $filler";
    }
    elseif ($page==4) {
        $out .= "<div class='pagipage' data-pn='1'>1</div>";
    } 
    for ($i=$lim1; $i<=$lim2; $i++) {
        if ($page==$i)
            $out .= "<div class='pagicurrent'>$i</div>";
        else
            $out .= "<div class='pagipage' data-pn='$i'>$i</div>";
    }
    if ($page < $kPages-3) {
        $out .= "$filler <div class='pagipage' data-pn='$kPages'>$kPages</div>";
    }
    $out .= $page < $kPages ? "&ensp;<div class='pagipage' data-pn='$n'>Next</div>" : '';
    return $out;
}


?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 18.0 (Build 18044, 64bit)">
<title>Pagination sample</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="07/02/2018">
<style type="text/css">
    body {
        font-family: verdana;
        font-size: 10pt;
    }
    #title {
        font-size: 16pt;
        background-color: #369;
        color: #FFF;
        text-align: center;
        padding: 10px;
        margin-bottom: 40px;
    }
    .pagipage {
        display: inline;
        width: 25px;
        height: 15px;
        padding: 3px 5px;
        text-align: center;
        font-size: 9pt;
        border: 1px solid #3C9DBA ;
        color: #3C9DBA;
        background-color: #FFF;
        cursor: pointer;
        margin-left: -1px;
    }
    .pagipage:hover {
        background-color: #3C9DBA;
        border-color: #F0F;
        color: white;
    }
    .pagicurrent {
        display: inline;
        width: 25px;
        height: 15px;
        text-align: center;
        font-size: 9pt;
        font-weight: 600;
        border: 1px solid #3C9DBA;
        background-color: #3C9DBA;
        color: white;
        padding: 3px 5px;
    }
    .paginate_panel {
        text-align: center;
        margin: 20px 0;
        width: 100%;
        color: #3C9DBA;
    }
    .pupil {
        width: 300px;
        padding: 10px;
        margin-top: 5px;
        margin-left: auto;
        margin-right: auto;
        border: 1px solid gray;
    }
    .label {
        width: 100px;
        font-weight: 600;
        display: inline-block;
    }
</style>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script type="text/javascript">
$().ready( function() {
    
      $(".pagipage").click( function() {
          $("#page").val( $(this).data("pn") );
          $("#form1").submit();
      })

})
</script>
</head>
<body>
<div id='title'>
Pagination Sample
</div>
<form id='form1'>
   <input type="hidden" name="page" id="page" value="0">
</form>
   <?=$output?>
   <div class="paginate_panel">
        <?=page_selector($total, $page)?>
   </div>
<!--  DATA ---
CREATE TABLE `pupil` (
  `pupilID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fname` varchar(45) NOT NULL,
  `lname` varchar(45) NOT NULL,
  `houseID` int(10) unsigned NOT NULL DEFAULT '1',
  `classid` char(1) NOT NULL DEFAULT 'A',
  `dob` date DEFAULT NULL,
  PRIMARY KEY (`pupilID`),
  KEY `house` (`houseID`),
  KEY `idx_pupil_classid` (`classid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `pupil` VALUES 
(1,'Adam','Simms',1,'A','2001-06-22'),
(2,'Allan','Blair',2,'B','2001-03-04'),
(3,'Anna','Hamilton',4,'B','2002-01-16'),
(4,'Anne','Bailey',3,'D','2001-08-02'),
(5,'Anthony','Bell',2,'E','2001-10-01'),
(6,'Caroline','Freeman',2,'F','2000-12-13'),
(7,'David','Powell',1,'A','2001-05-03'),
(8,'Emma','Watson',4,'C','2001-11-20'),
(9,'George','Wilson',1,'C','2001-06-30'),
(10,'Henry','Irving',4,'D','2001-08-12'),
(11,'Jane','Morrison',1,'E','2001-08-24'),
(12,'John','Patterson',3,'F','2001-09-06'),
(13,'John','Tully',3,'A','2001-09-03'),
(14,'John','Watson',2,'B','2001-09-30'),
(15,'Jack','Williams',2,'D','2001-09-08'),
(16,'Margaret','Norton',4,'D','2001-04-23'),
(17,'Mary','Blake',4,'E','2001-10-04'),
(18,'Mary','Sheldon',3,'F','2001-06-14'),
(19,'Mary','Whitehouse',2,'A','2001-09-06'),
(20,'Michael','Grove',3,'B','2001-08-11'),
(21,'Peter','Adamson',1,'C','2001-09-18'),
(22,'Peter','Appleby',3,'D','2001-04-26'),
(23,'Wayne','Jones',1,'E','2001-05-06'),
(24,'William','Smith',4,'F','2001-12-08');
-->
</body>
</html>

 

[Revolutsio]

Thank I got the @mac_gyvercode to work thanks