php alphabetical pagination help

surfing by clicking on letters of the alphabet

[tekkmonk]

Hi, I need major help. I'm a php rookie and i'm trying to create a alphabetical paginated page where surfers can click on letters of the alphabet A B C D E F G ..... to search a database of names. I've got the pagination to work for the numbers at the bottom by copying a code i found online but i need help with he Alphabetical. Here's the code i have so far

<?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>&nbsp;¦&nbsp;";
}
echo "<a href=\"?\">Show All</a></p><br />
<form method=\"post\" action=\"?\">
<input type=\"text\" name=\"search\" /> <select name=\"class\">
<option>Select Category</option>
<option>---</option>
<option value=\"restaurant\">restaurant</option>
</select>
<input type=\"submit\" name=\"submit\" value=\"Search\" class=\"submit\" />
</form>";
?>

<!--mysql-->
<?php
if(empty($_POST)) {
$letter = $_GET['letter'];
$letter .= "%";
$search = $letter;
$sdesc = "*";
$classquery = "";
} else {
$search = $_POST['search'];
$search = "%" . $search . "%";
$sdesc = "%" . $search . "%";
$sclass = $_POST['category_eng'];
$classquery = "AND 'category_eng' = CONVERT( _utf8 '" . $sclass . "' USING latin1 )";
}
mysql_connect("localhost", "root", "password");
mysql_select_db("mydb");
// 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 saba ORDER BY name DESC LIMIT $from, $max_results")
or die(mysql_error());

//TO PRINT OUT THE DATA
echo "<table border='1'>";
echo "<tr> <th>ID</th> <th>Section</th> <th>List Type</th> <th>Category English</th> <th>Category French</th> <th>Name</th> <th>Address</th> <th>Phone_Number</th> <th>Phone Number2</th> <th>Fax</th> <th>Website</th> <th>Email</th> </tr>";
// keeps getting the next row until there are no more to get
while($saba = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "<tr><td>";
echo $saba['ID'];
echo "</td><td>";
echo $saba['section'];
echo "</td><td>";
echo $saba['listtype'];
echo "</td><td>";
echo $saba['category_eng'];
echo "</td><td>";
echo $saba['category_french'];
echo "</td><td>";
echo $saba['name'];
echo "</td><td>";
echo $saba['address'];
echo "</td><td>";
echo $saba['phone_number'];
echo "</td><td>";
echo $saba['phone_number2'];
echo "</td><td>";
echo $saba['fax'];
echo "</td><td>";
echo $saba['website_url'];
echo "</td><td>";
echo $saba['email'];
echo "</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 saba ORDER BY name DESC"),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\">&laquo;</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['php_SELF']."?page=$i &letter=$letter\">$i</a> ";
}
}

// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['php_SELF']."?page=$next\">&raquo;</a>";
}
echo "</p>";

mysql_close();
?>

[eelixduppy]

Welcome to WebmasterWorld!

It's all in your query when it comes to sorting alphabetically. The simplest example would be something like this following:

$query = "SELECT * FROM `table_name` WHERE `col` LIKE '".$starting_letter."%' ORDER BY `col` DESC";

There are variations of the above query that would also account for case-sensitivity however that pretty much shows the basics :)

Note that the percent sign (%) when used in a LIKE means any characters.

[tekkmonk]

thank you very much eelixduppy . Now i'm trying to do the same thing but with categories. So i guess in other words i would like the page to give the results and then if the user wishes they can sort the results by Alphabetical or by categories and with either choice they also get the pagination <previous 1 2 3 4 next>(at the bottom of the page).. any idea on how to do this?

[jatar_k]

you can add extra parameters to your query using AND

$query = "SELECT * FROM `table_name` WHERE `col` LIKE '".$starting_letter."%' AND category='catname' ORDER BY `col` DESC";