By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,694 IT Pros & Developers. It's quick & easy.

How next button in search works - Design problem

P: n/a
Hi,

May be I am a newbie, or may be i dont have that much insight in following
systems ..i.e. why i have some confusions as below:

In many websites, when search is performed on some keywords (not only
including google which perform general search but other sites which perform
seach on a particular type of item in their database) then many search
results are obtained (for example more than 100 or even more than 1000) but
only limited amount of results are displayed in a page (like for example 10
results per page). Thus in order to display the next 10 results, a user has
to click on 'Next' button. Similarly, it is possible that a user can click
on 'Previous' button to display the previous 10 results. My question is what
are different mechanisms to perform this and which one would be most optimal
than others?

Why I am asking this question because, if we are performing a search by
sending a query of keywords to a website then we get following procedure:

Browser->Send Query->ASP.NET page(considering we are using ASP.NET although
it could be PHP or ASP or JSP etc)

Now internally from ASP.NET page, a database connection is opened (say using
ADO.NET) and search is performed by sending a query to database and getting
the results. So we get:

ASP.NET page->Send query to database (using ADO.NET)

Now database returns recordset containing more than lets say 100 records (or
1000 records)

Database->Send back 1000 records->ASP.NET page

And then ASP.NET page send 10 records to client and drop the rest. So:

ASP.NET page->Send 10 records->Client Browser.

So now when the client press Next button what happens? It seems illogical to
send same search query to database and then dropping first 10 records from
recordset and only passing recordset from 11th record to 20th records to
client browser.

So, can anyone please tell me what are different approaches to handle this.

Thanks,

Arsalan
Mar 28 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi,

Have you used paging in the past? Here is how you would do a paging query in
T-SQL:

//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
SELECT TOP <pageSize> *
FROM
(
SELECT TOP (<pageSize> * <pageIndex>)
< columns names>
FROM
< table name >
WHERE
< condition >
ORDER BY < PK column name > DESC
)
AS anyNameYouWant ORDER BY < PK column name > ASC
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
<pageSize> = the number of records to be retrieved
<pageIndex> = the desired page index (1 based)
<column names> = list of the column names
<table name> = source table name and optional joined tables
<condition> = optional filter condition
< PK column name > = the primary key column name (it has to be also present
into the <column names> list)

Sample T-SQL for getting 5 records from the Northwind Employee table from
the second page:

//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~
USE Northwind
SELECT TOP 5 *
FROM(
SELECT TOP 10
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~

USE Northwind

-- Page 1
SELECT TOP 3 *
FROM(
SELECT TOP 3
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

-- Page 2
SELECT TOP 3 *
FROM(
SELECT TOP 6
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

-- Page 3
SELECT TOP 3 *
FROM(
SELECT TOP 9
EmployeeID,
LastName,
FirstName,
Title,
BirthDate,
HireDate
FROM
Employees

ORDER BY EmployeeID DESC

)
AS Employees ORDER BY EmployeeID ASC

If using AJAX you could simply return the results you need depending on page
clicked using Callbacks... You'd be amazed at the speed of execution...
Hope this helps,

Yama Kamyar






"Arsalan Ahmad" <ar*****@hotmail.com> wrote in message
news:OT**************@TK2MSFTNGP11.phx.gbl...
Hi,

May be I am a newbie, or may be i dont have that much insight in following
systems ..i.e. why i have some confusions as below:

In many websites, when search is performed on some keywords (not only
including google which perform general search but other sites which
perform seach on a particular type of item in their database) then many
search results are obtained (for example more than 100 or even more than
1000) but only limited amount of results are displayed in a page (like for
example 10 results per page). Thus in order to display the next 10
results, a user has to click on 'Next' button. Similarly, it is possible
that a user can click on 'Previous' button to display the previous 10
results. My question is what are different mechanisms to perform this and
which one would be most optimal than others?

Why I am asking this question because, if we are performing a search by
sending a query of keywords to a website then we get following procedure:

Browser->Send Query->ASP.NET page(considering we are using ASP.NET
although it could be PHP or ASP or JSP etc)

Now internally from ASP.NET page, a database connection is opened (say
using ADO.NET) and search is performed by sending a query to database and
getting the results. So we get:

ASP.NET page->Send query to database (using ADO.NET)

Now database returns recordset containing more than lets say 100 records
(or 1000 records)

Database->Send back 1000 records->ASP.NET page

And then ASP.NET page send 10 records to client and drop the rest. So:

ASP.NET page->Send 10 records->Client Browser.

So now when the client press Next button what happens? It seems illogical
to send same search query to database and then dropping first 10 records
from recordset and only passing recordset from 11th record to 20th records
to client browser.

So, can anyone please tell me what are different approaches to handle
this.

Thanks,

Arsalan

Mar 29 '06 #2

P: n/a
Arsalan,

In ASPNET are for that nice solutions named Paging (as the name is for a
windforms as well).
Here a sample on our page for a webpage (there are as well for windowforms)

http://www.vb-tips.com/default.aspx?...8-6750bb0390f8

For the others search on 'Paging"

I hope this helps,

Cor

"Arsalan Ahmad" <ar*****@hotmail.com> schreef in bericht
news:OT**************@TK2MSFTNGP11.phx.gbl...
Hi,

May be I am a newbie, or may be i dont have that much insight in following
systems ..i.e. why i have some confusions as below:

In many websites, when search is performed on some keywords (not only
including google which perform general search but other sites which
perform seach on a particular type of item in their database) then many
search results are obtained (for example more than 100 or even more than
1000) but only limited amount of results are displayed in a page (like for
example 10 results per page). Thus in order to display the next 10
results, a user has to click on 'Next' button. Similarly, it is possible
that a user can click on 'Previous' button to display the previous 10
results. My question is what are different mechanisms to perform this and
which one would be most optimal than others?

Why I am asking this question because, if we are performing a search by
sending a query of keywords to a website then we get following procedure:

Browser->Send Query->ASP.NET page(considering we are using ASP.NET
although it could be PHP or ASP or JSP etc)

Now internally from ASP.NET page, a database connection is opened (say
using ADO.NET) and search is performed by sending a query to database and
getting the results. So we get:

ASP.NET page->Send query to database (using ADO.NET)

Now database returns recordset containing more than lets say 100 records
(or 1000 records)

Database->Send back 1000 records->ASP.NET page

And then ASP.NET page send 10 records to client and drop the rest. So:

ASP.NET page->Send 10 records->Client Browser.

So now when the client press Next button what happens? It seems illogical
to send same search query to database and then dropping first 10 records
from recordset and only passing recordset from 11th record to 20th records
to client browser.

So, can anyone please tell me what are different approaches to handle
this.

Thanks,

Arsalan

Mar 29 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.