Forum Moderators: coopster
Currently am able to display and search the result when i retrieve them from the database but am unable to search according to a letter when I select it from the alphabets that display on top of the page.
eg. when I select A, all names starting with the letter A should display.
Please look at the code.
<?php
include 'library/config.php';
include 'library/opendb.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> ¦ ";
}
?>
<html>
<head>
<title>Implementing Paging With More Than One Column</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table width="543" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="78" valign="top">Search</td>
<td width="292"><form name="form1" method="post" action="">
<input type="text" name="searchfield" value="<?php
if (isset($_POST['searchfield'])) {
echo $_POST['searchfield'];
}
?>">
</form><script language="JavaScript" type="text/JavaScript">
document.form1.searchfield.focus();
</script>
</td>
<td width="173"> </td>
</tr>
<tr>
<td colspan="3"><?php
$mysearch=$_POST['searchfield'];
//echo $mysearch;
// how many rows to show per page
$rowsPerPage = 3;
// by default we show first page
$pageNum = 1;
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$query = "SELECT id, name, address, age, register_date FROM student WHERE name LIKE '%$mysearch%'";
$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');
// print the student info in table
echo '<table border="1"><tr><td>Student Id</td><td>Name</td><td>Address</td><td>Age</td><td>Register Date</td></tr>';
while(list($id, $name, $address, $age, $regdate) = mysql_fetch_array($result))
{
echo "<tr><td>$id</td><td>$name</td><td>$address</td><td>$age</td><td>$regdate</td></tr>";
}
echo '</table>';
echo '<br>';
// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed. Thank you');
$numrows = mysql_num_rows($result);
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
$self = $_SERVER['PHP_SELF'];
// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link
// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' [Prev] '; // we're on page one, don't enable 'previous' link
$first = ' [First Page] '; // nor 'first page' link
}
// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' [Next] '; // we're on the last page, don't enable 'next' link
$last = ' [Last Page] '; // nor 'last page' link
}
// print the page navigation link
echo $first . $prev . " Showing page <strong>$pageNum</strong> of <strong>$maxPage</strong> pages " . $next . $last;
include 'library/closedb.php';
?></td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>
</body>
</html>
I can see where you are setting the query string, but I can`t see where you are accessing it in your code.
To query the database, you can use LIKE:
$query = "SELECT id, name, address, age, register_date FROM student WHERE name LIKE '%".$_GET['letter']."'";
Or SUBSTRING:
$query = "SELECT id, name, address, age, register_date FROM student WHERE SUBSTRING(name,1,1)=='".$_GET['letter']."';
Note that the offset of mysql SUBSTRING is slightly different to PHP`s function, where you would specify 0,1 for the first letter.
Hope that helps?
dc
