How would I do an alphabetical search with pagination

[c-o-d-e]

I have an Admin Control Panel, with a complete users table to begin with.

 

I want to insert a alphabetical search.

Eg: 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

When you click on a letter, it displays the usernames with that letter at the start of there username.

Which users Pagination at 10 results per page.

 

How would I go about doing this?

[mikesta707]

you should use the like keyword, like so

$sql = "SELECT ..... WHERE column LIKE '$letter%' ... ";

 

The key is the LIKE. LIKE searches for anything that is... well.. Like the variable $letter. the % tells mysql to match any words that start with $letter. it would be similar to the regex patter /^A/ which would match anything that starts with an A

[c-o-d-e]

I don't quite understand.

It might be easier to understand once I see an Alphabetical Search script.

 

Have you seen any tutorials for it?

 

I seen this

http://www.phpfreaks.com/forums/index.php?topic=151300

 

Though the tutorial quoted in there, is not a valid link now. It just redirects to the tutorials homepage.

 

[mikesta707]

this is the search tutorial: http://www.phpfreaks.com/tutorial/simple-sql-search

 

the query I wrote above is more or less how you could get all the info from the db with whatever column starting with whatever letter. if you don't understand that than I suggest you read up on some mysql tutorials

[c-o-d-e]

Do you mean..

If I edit the pagination I have, have it using the same sort of query as you stated.

 

Then when I click on a letter, it'll set $letter to that letter clicked, then it'll go to the pagination part, search the database for $letter, then display the results etc?

[mikesta707]

well assuming your pagination works, if if you change the way the query works, then the pagination should still work (assuming it sends the same type of data) I would have to see some code before I gave a definite answer tho

[c-o-d-e]

Well, here is what I have at this current moment.

	include("config.php");
echo('<p><strong>Registered Users Table</strong></p>');

	$query = mysql_query("SELECT * FROM Users ORDER BY Username")or die(mysql_error());
        $totalrows = mysql_num_rows($query);
        $numberofpages = $totalrows / '10';
        if(isset($_GET['page']))
	{
		$currentpage = $_GET['page'] * '10';
	}
		else
		{
			$currentpage = 0 * '10';
			};
			$query = mysql_query("SELECT * FROM Users ORDER BY Username DESC LIMIT $currentpage, 10 ")or die(mysql_error());

	$pages=0;
	while($pages < $numberofpages)
	{
		if($pages == $_GET['page']){echo $pages.' '; $pages ++;
	}
		else
		{
			echo'<a href="admin.php?user=$username&change=regusers&page='.$pages.'">'.$pages.'</a> ';
			$pages ++;
			};

	echo'<table border=1><center><tr<td><strong>User</strong></td><td><strong>Email</strong></td><td><strong>Admin</strong></td><td><strong>Active</strong></td></tr>';
	while($row = mysql_fetch_array($query))
	{
		echo('<tr><td>'.$row['Username'].'</td><td>'.$row['Email'].'</td><td><center>'.$row['Admin'].'</center></td><td><center>'.$row['Activated'].'</center></td></tr>');
	};
	echo('</center></table>');

[mikesta707]

Oh, yeah it would appear you just need to change your queries, and the pagination would work the same

[c-o-d-e]

I changed the first query to

$query = mysql_query("SELECT * FROM Users WHERE Username LIKE '$letter%'")or die(mysql_error());

 

And the second query to

$query = mysql_query("SELECT * FROM Users WHERE Username LIKE '$letter%' DESC LIMIT $currentpage, 10 ")or die(mysql_error());

 

I think it's wrong.. though I replaced the queries with what you said, and filled in the rest of it. Though I'm guessing im not very good at SQL. I've only learnt about inserting, selecting, and changing.

 

I'd obviously create the alphabet links.

<a href=" ... ">A</a> and so on.

 

If I create the link as a get or post. So I was able to set it as something like this

$letter% = $_GET['letter'];

Etc.