I am using RedbeanPHP ORM to design a personal text based web game.
So what I am trying to do is list 10 Rows of users from the Database Ordered by my rank field.
I will have a list of users. I want to put in a button for the next page that will allow the user to change to the next page of 10 users.
Right now it displays my list of users but is not ordering the list according to rank. Also I need to figure out how to only display 10 users and add a next page button that displays the next 10.
<?php
$userCount = R::count( 'users' ); // Get RedBean User Count
echo "Player Count is Currently: ".$userCount."<br />"; // Display how many users are in the database
for($i = 1; $i <= $userCount; $i++) { // Print Rows using the Count
$galaxyuser = R::getRow("SELECT * FROM `users` WHERE `id` = '$i' ORDER BY `rank` ASC"); // Get Each User Info
//Get User Information into Variables
$pt = $galaxyuser['planettype'];
$money = $galaxyuser['money'];
$covtroops = $galaxyuser['covtroops'];
$atktroops = $galaxyuser['atktroops'];
$deftroops = $galaxyuser['deftroops'];
$covlvl = $galaxyuser['covlvl'];
$yourcovstrength = ($data['covtroops']*$data['covlvl']*3000);
$theircovstrength = ($galaxyuser['covtroops']*$galaxyuser['covlvl']*3000);
// Determine Whether Your Covert Strength is higher than theirs so you can see what resources are onhand.
if($yourcovstrength >= $theircovstrength){
$covmoney = number_format($money);
$size = number_format($galaxyuser['atktroops']+$galaxyuser['deftroops']+$galaxyuser['untrained']+$galaxyuser['miners']+$galaxyuser['covtroops']);
}else{
$covmoney = "?????????";
$size = "?????????";
}
// Get Planet Image Name Depending on Planet Type.
if($pt==1){
$pl = "planet1_small";
}elseif($pt==2){
$pl= "planet2_small";
}elseif($pt==3){
$pl = "planet3_small";
}elseif($pt==4){
$pl = "planet4_small";
}elseif($pt==5){
$pl = "planet5_small";
}elseif($pt==6){
$pl = "planet6_small";
}
//Print User Information
print "<div id='galaxyuser'>";
print "<img src='images/".$pl.".png' />";
print "<a href='player.php?id=".$galaxyuser['id']."'>".$galaxyuser['username']."</a><br />";
print "<span id='borderleft'>
Army Size:".$size."</span><br />";
print " Onhand Crystals:".$covmoney."<br />";
print "<a href='attack.php?id=".$galaxyuser['id']."' class='button'>Crystal Attack</a> | <a href='civattack.php?id=".$galaxyuser['id']."' class='button'>Civillian Raid</a> | <a href='intellegence.php?id=".$galaxyuser['id']."' class='button'>Intellegence Report</a>";
print "<br/><br/></div>";
}
?>
Thank you for any help regarding this subject.
Brian
What you are asking about is called pagination. Pagination involves four tasks -
A query to get the total number of matching rows. The R::count() method apparently does this.
A data retrieval query that gets a logical page of data, in the order that you want it… This query needs a LIMIT x,y term in it to return the correct rows from the result set.
Code to display the logical page of data.
Code to build and output pagination links.
There are countless php/mysql pagination examples to be found on the web to get you started. If I/others have time, they will post an example.
Some points about the existing code -
Don’t run queries inside of loops. This is extremely inefficient, due to the number of communications between the web server and database server. For what you are doing, there is no need. If you did have a need to query for data matching a set of ids, for example, you would do it all in one query by using a WHERE clause that matches all the ids that you want.
There’s no guarantee that the ids are contiguous, so looping from 1 to the count of the number of users may not actually match all the user ids.
Keep variable names generic. If the purpose of this code is to get and display galaxy user data, after you have a major comment stating so, a row of fetched data just needs to be named $row.
Don’t copy variables to other variables for nothing. This is just a waste of typing time. Just use the original variables. There’s nothing wrong with using a variable like $row[‘planettype’], and in fact after you have made the copy of these variables, you are using the originals anyway, so, those lines of code are a complete waste of time.
Don’t write out code for every possible value. e.g. the planet types. Instead, use a mapping array, where the array index is the input value and the array element is the output value, then just directly use the input value to retrieve the output value from the mapping array. This data can actually be stored in a database table, then you would JOIN this table in the SELECT query to get the planet type names, instead of needing any php code at all.
You can put php variables directly into a double-quoted string. This will eliminate all the extra quotes and concentration dots, which typically result in large number of typo mistakes.
Here is typical pagination code for what you have shown us that should (untested) work, with most of the points that were made above -
<?php
// initialization
// number of items per page
$items_per_page = 10;
// number of +/- links around the current page number
$range = 3;
// define planet planettype to image map - this would be put into a configuration file
$planet_map = [];
$planet_map[1] = "planet1_small";
$planet_map[2] = "planet2_small";
$planet_map[3] = "planet3_small";
$planet_map[4] = "planet4_small";
$planet_map[5] = "planet5_small";
$planet_map[6] = "planet6_small";
// get method business logic - get/produce data needed to display the page
// query to get the total number of matching rows
$total_rows = R::count( 'users' ); // Get RedBean User Count
// calculate total number of pages
$total_pages = ceil($total_rows / $items_per_page);
// 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));
// query to get a logical page of data
$start = ($page-1) * $items_per_page;
// note: you should list out the columns you are selecting
$sql = "SELECT * FROM `users` ORDER BY `rank` ASC LIMIT ?,?";
$params = [$start,$items_per_page];
$result_data = R::getAll($sql, $params);
// pagination links -
// 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,'', '&');
$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,'', '&');
$pagination_links .= $link == $page ? "$link " : "<a href='?$qs'>$link</a> ";
}
// produce next
$get['page'] = $page + 1;
$qs = http_build_query($get,'', '&');
$pagination_links .= $page == $total_pages ? 'next' : "<a href='?$qs'>next</a>";
// html document starts here...
?>
<?php
// at the point of outputting the data
if(empty($result_data))
{
echo "<p>There is no data to display.</p>";
}
else
{
// there is data to display
echo "Player Count is Currently: $total_rows<br />"; // Display how many users are in the database
// calculate this only once, before the start of any looping
$yourcovstrength = $data['covtroops']*$data['covlvl']*3000;
// loop over the logical page of data
foreach($result_data as $row)
{
$theircovstrength = $row['covtroops']*$row['covlvl']*3000;
// Determine Whether Your Covert Strength is higher than theirs so you can see what resources are onhand.
if($yourcovstrength >= $theircovstrength){
$covmoney = number_format($row['money']);
$size = number_format($row['atktroops']+$row['deftroops']+$row['untrained']+$row['miners']+$row['covtroops']);
}else{
$covmoney = "?????????";
$size = "?????????";
}
// Get Planet Image Name Depending on Planet Type.
$pl = isset($planet_map[$row['planettype']]) ? $planet_map[$row['planettype']] : 'no planet image';
//Print User Information
print "<div id='galaxyuser'>";
print "<img src='images/$pl.png'>";
print "<a href='player.php?id={$row['id']}'>{$row['username']}</a><br />";
print "<span id='borderleft'>
Army Size:$size</span><br />";
print " Onhand Crystals:$covmoney<br />";
print "<a href='attack.php?id={$row['id']}' class='button'>Crystal Attack</a> | <a href='civattack.php?id={$row['id']}' class='button'>Civillian Raid</a> | <a href='intellegence.php?id={$row['id']}' class='button'>Intellegence Report</a>";
print "<br/><br/></div>";
}
}
?>
<?php
// at the point of outputting the pagination links
echo $pagination_links;
?>
However, we do ask that you don’t use the message system to request assistance, as that is not in the spirit of the forums. Public discussion means the thread helps not only the person who asked the question, but also anybody else with a similar issue.
Php uses references when coping variables. $var = $array[‘index’]; places a reference to the array element in $var as its value. When you reference $var in code, php looks up that variable in the internal hash symbol table to find the value in it. In this case the value is a reference, rather than a direct value, so the reference is then used to find the actual value, repeat as needed until you get to the actual memory location where the data is stored.
When you alter the value in some way, i.e. write a different value/type to the variable, the entry in the symbol table is changed so that it now points to where the the new values is stored at. This is called copy-on-write.
It is, provided you put place-holders in the sql query statement for each value and supply an array of values as the second parameter in the R::getAll() call. It uses the PDO extension for the database interface.
