Mixing
Sorting MySQL data into limit sized pages, and onscreen option to change pages? How is it done? [on hold]
Sorting MySQL data into limit sized pages, and onscreen option to change pages? How is it done? [on hold]
I am new to HTML and CSS, however I have some basic knowledge of programming games in C# and Java (using frameworks like LibGDX, Unity etc).
I've built a database in MySQL and the corresponding php code that displays a html article for each entry in the table.
Everything is working great but the whole database loads onto one big webpage.
I have been reading around a lot but cannot seem to find where to start on building it into pages. The only idea I can imagine right now is that I will need a field somewhere on my site to hold the current page number the user selected, and then in my SQL SELECT statement use that figure to somehow pull only say 20 pages starting from 'currentPage * 20' for example.
I've found small parts of the info I need like I see there may be a LIMIT clause I can use. But how can the
SELECT * FROM table ORDER BY id DESC LIMIT (currentPage*20);
know what currentpage is in the index.php? (If that is even the correct syntax/way to write that I don't even know!).
Here is the part of my PHP code I already have to help you see what I mean.
TL;DR: question - How to use this code below but also include a paging system to display only n rows at one time?
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question. You could use a URL parameter to pass the page number to your SQL query. For instance, each link in your pagination could include ?page=1, ?page=2, etc. Then look if the "page" parameter is set and use this to return the results for the given page number. Example: Then to build your pagination, you can query the games table for a count of records to determine how many links you need. Example: // create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$currentPage = 0;
$rowsPerPage = 20;
$startRow = $currentPage * $rowsPerPage;
// check connection
if ($conn->connect_error) {
die("connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM games ORDER BY id DESC LIMIT $startRow, $rowsPerPage";
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
// output data of each row
while($row = $result->fetch_assoc())
{
echo '<section class="wrapper style1">';
echo '
echo '
echo ''. $row["title"] . '
';
echo '
echo '';
echo '
echo '<a href="'.$row["downloadlink"].'" target="_blank">Download Link </a> <br /> <a href="downloadhelp.php">click here to learn more</a> <br /> <a href="fileshelp.php">click here for help running the files</a> <br /> <br /> <br />';
$VidLink = $row["vidlink"];
if ($VidLink != 'not found' && $VidLink != '')
{
echo '
}
echo '</header>';
echo '</div>';
echo '</section>';
}
}
else
{
echo "0 results";
}
$conn->close();
sadly i can't understand any of the documentation. I need a barebones example lol. Also quite sad because many websites seem to have similar implentations of this and therefore it seems like im reinventing the wheel (or trying to at least!!)
– Super MegaBroBro
Jun 29 at 22:33
There are thousands of examples of PHP-based pagination on the web. Would you try searching for some, and try copying an example? Many of them can be copied out verbatim, so you can see how they work?
– halfer
Jun 29 at 22:44
can you point me to a simple easy to follow one? because granted there are thousands, but i have read several dozen and none I can exactly relate to my code and I get lost part way through because it deviates to another topic. If you could link me to one of the ones you mention would be great
– Super MegaBroBro
Jun 29 at 22:45
1 Answer
1
$rowsPerPage = 20;
if (isset($_GET["page"])) {
$currentPage = $_GET["page"];
}
else {
$currentPage = 0;
}
$startRow = $currentPage * $rowsPerPage;
$sql = "SELECT * FROM games ORDER BY id DESC LIMIT $startRow, $rowsPerPage";
$result = $conn->query($sql);$sql = "SELECT COUNT(id) FROM games";
$results = mysql_query($sql);
$row = mysql_fetch_row($results);
$recordCount = $row[0];
$totalPages = ceil($recordCount / $rowsPerPage);
$pagination = "
for ($i = 0; $i " . $i . "";
}
$pagination .= "
echo $pagination;
thank you so much mate. i knew i was part way there and that there could be a nice simple solution. thanks to you and the other commenters for the help
– Super MegaBroBro
Jun 30 at 9:43
Sadly I get this error, which I had last night also with certiain things i'd tried. Its to do with mysql_query() being deprecated. Deprecated: mysql_query(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in H:websitesxyz123wwwindex.php on line 86
– Super MegaBroBro
Jun 30 at 10:02
ok, so i changed lines 2-3 in your code to: $results = $conn->query($sql); $row = $result->fetch_assoc(); BUT now I dont understand line 4 ($recordCount = $row[0]). How is $recordCount going to end up as int there? surely it will return the actual value of $row at index 0? I now get this new error " Notice: Undefined offset: 0 " -- note I also tried $recordCount = Count($row); but that just returns 01 every time.
– Super MegaBroBro
Jun 30 at 10:37
actually the 01 was the pagination part working. However my $recordCount is still 7 for some insane reason. Also it only seems to find around 12 of the games in my database now where there is actually over 100. Thanks for your help tho pal, its got me well n truly on the right path
– Super MegaBroBro
Jun 30 at 10:57
Glad to help! Wasn't able to test the code, posted on my phone while in transit, but the basic idea is there. Cheers!
– Steve Mulvihill
2 days ago
What you are looking for is called 'pagination' and there are many articles available online. Also see stackoverflow related issues: stackoverflow.com/search?q=pagination+php For your question, the currentpage could either be stored in a GET parameter like so mypage.com/?page=2 and injected into your MySQL query using prepared statements php.net/manual/en/mysqli.prepare.php or you can save the currentpage in a $_SESSION if you do not want to include it into the URL (I recommend the URL/GET)
– xyz
Jun 29 at 22:10