Forum Moderators: coopster
I have, on my website, a MySQL database. I have a page that lists all of the records in the database. It is a fairly large database so I split it into pages with the LIMIT MySQL function.
This worked fine for the main page. But for user convenience, I would've liked to add a search box at the top of the page. A user can type text into this search box and display results that contain this text. Also, I added an alphabet array to the top of the page so people could search for 'name' beginning with certain letters. Queries all working fine, form working fine.
Alas, pagination again. I have it set so that when the post goes through, a general query is modified using variables. The query changes back to default when I change the page, so everything appears correctly on the first page, but anything after that is the 'default' query information.
I'm asking now, what kind of script would I use to create paged queries from a HTML submit form (POST) and selection of letters (GET)? Here is my current code to give you an idea of what I'm looking for:
<?php
$alphabet = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
foreach ($alphabet as $letter) {
echo "<a href=\"?letter=" . $letter . "\">" . $letter . "</a> ¦ ";
}
echo "<a href=\"?\">Show All</a></p><br />
<form method=\"post\" action=\"?\">
<input type=\"text\" name=\"search\" /> <select name=\"class\">
<option>Weapon Class</option>
<option>---</option>
<option value=\"arrow\">Arrow</option>
<option value=\"axe\">Axe</option>
<option value=\"bow\">Bow</option>
<option value=\"dagger\">Dagger</option>
<option value=\"katar\">Katar</option>
<option value=\"knuckles\">Knucles</option>
<option value=\"mace\">Mace</option>
<option value=\"rod\">Rod</option>
<option value=\"spear\">Spear</option>
<option value=\"sword\">Sword</option>
<option value=\"whip\">Whip</option>
</select>
<input type=\"submit\" name=\"submit\" value=\"Search\" class=\"submit\" />
</form>";
?>
<!--TABLE HEADINGS-->
<table class="dbs">
<tr class="dbhead">
<td class="dbhead75">Name</td>
<td class="dbhead">Description</td>
<td class="dbhead65">Class</td>
<td class="dbhead175">Stats</td>
<td class="dbhead110">Obtain</td>
<td class="dbhead80">Buy/Sell NPC</td>
<td class="dbhead80">Buy/Sell Player</td>
</tr>
<!--END TABLE HEADINGS--><!--MYSQL-->
<?php
if(empty($_POST)) {
$letter = $_GET['letter'];
$letter .= "%";
$search = $letter;
$sdesc = "*";
$classquery = "";
} else {
$search = $_POST['search'];
$search = "%" . $search . "%";
$sdesc = "%" . $search . "%";
$sclass = $_POST['class'];
$classquery = "AND `class` = CONVERT( _utf8 '" . $sclass . "' USING latin1 )";
}
mysql_connect("localhost", "root", "****");
mysql_select_db("ragnarokitemdb");
// If current page number, use it
// if not, set one!
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 5;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
// Perform MySQL query on only the current page number's results
$result = mysql_query("SELECT * FROM rag_weapons WHERE
(`name` LIKE CONVERT( _utf8 '$search'
USING latin1 )
OR `desc` LIKE CONVERT( _utf8 '$sdesc'
USING latin1 ))
$classquery
AND `approve` = CONVERT( _utf8 '1'
USING latin1 ) ORDER BY `name` ASC LIMIT $from, $max_results");
//TO PRINT OUT THE DATA
while ($row = mysql_fetch_array($result)) {
echo "<tr class=\"dbentry\">
<td class=\"dbentry1\" align=\"center\"><img src=\"";
$imageurl = "images/itemdb/" . $row["name"] . ".gif";
if (!is_file($imageurl)) {
$imageres = "images/itemdb/no image.gif";
} else {
$imageres = $imageurl;
}
echo $imageres;
echo "\" alt=\"";
echo htmlentities($row["name"]);
echo "\" /><br />";
echo htmlentities($row["name"]);
$smallimageurl = "images/itemdb/small/" . $row["name"] . ".gif";
if (!is_file($smallimageurl)) {
echo "<img src=\"";
$smallimage = "images/itemdb/small/no image.gif";
} else {
echo "<br /><img src=\"";
$smallimage = $smallimageurl;
}
echo $smallimage;
echo "\" alt=\"";
echo htmlentities($row["name"]);
echo "\" /></td><td class=\"dbentry\" valign=\"top\">";
echo htmlentities($row["desc"]);
echo "</td><td class=\"dbentry\" valign=\"top\">";
echo htmlentities($row["class"]);
echo "</td><td class=\"dbentry\" valign=\"top\"><strong>Weight:</strong> ";
echo htmlentities($row["weight"]);
echo "<br /><strong>Attack Power:</strong> ";
echo htmlentities($row["attack"]);
echo "<br /><strong>Lvl/Req.Lvl:</strong> ";
echo htmlentities($row["wlvl"]);
echo "/";
echo htmlentities($row["rlvl"]);
echo "<br /><strong>Req. Job:</strong> ";
echo htmlentities($row["jobs"]);
echo "<br /><strong>Equip:</strong> ";
echo $row["Equip"];
echo "</td><td class=\"dbentry\" valign=\"top\">";
$where = $row["where"];
echo $where;
echo "</td><td class=\"dbentry\" valign=\"top\">";
echo htmlentities($row["bnpc"]);
echo "z/";
echo htmlentities($row["snpc"]);
echo "z</td><td class=\"dbentry\" valign=\"top\">";
echo htmlentities($row["splayer"]);
echo"z</td></tr><tr class=\"div\"><td colspan=\"7\">Submitted by: <a href=\"";
echo htmlentities($row["url"]);
echo "\">";
echo htmlentities($row["submitter"]);
echo "</a></td></tr>";
}
echo"</table>";
//STOP PRINTING OUT THE DATA
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM rag_weapons
WHERE
(`name` LIKE CONVERT( _utf8 '$search'
USING latin1 )
OR `desc` LIKE CONVERT( _utf8 '$sdesc'
USING latin1 ))
$classquery
AND `approve` = CONVERT( _utf8 '1'
USING latin1 ) ORDER BY `name` ASC"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
// Build Page Number Hyperlinks
echo "<p class=\"center\">Pages: ";
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">«</a> ";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next\">»</a>";
}
echo "</p>";
mysql_close();
?>
Thankyou in advance for your help.
As you have already realized your search variables are lost when the paging links are used by your visitor. In order to maintain those variables during pagination you must add each of the search variables as URL parameters to each of your pagination links.
For example instead of:
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i\">$i</a> ";
Use the following code to make $letter available via $_GET when the paging link is used:
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i &letter=$letter\">$i</a> ";
You can add further parameters to the URL as you need them, simply use "&" in between each one. Each URL parametre you add to your pagination links can now be retrieved using $_GET within your script.
Because it appears you have a fairly small number of search variables simply passing them along the pagination may work for you, but I could see some confusion occuring if your script is expecting to recieve either the POST or GET variables but is not setup to handle both. You may need to change your variables slightly to make this solution work.
A more elegant and flexible solution to your problem would be to use a SESSION as this would allow you to initiate SESSION VARIABLES that would be passed along from page to page, without the need for your variables to be hard-coded into the pagination.
A brief example of the codeflow for a sessions based solution where I am merely using your $letter variable would be:
// Start Session
session_start();
// Register session variables
$_SESSION['letter'] = $_GET['letter'];
// If needed, grab value of session variable back into regular variable
$letter = $_SESSION['letter'];
Once your search variables are registered as session variables they remain available to your script as your user paginates through the resultset - which means you only need to retrieve the $page variable via $_GET and the rest of the script runs the same on each page.
I hope this is helpful, if you need more detail or other help let me know. Happy coding :)
