hi guys..
i have got stucked into one programming problems which is called paging.i m using mssql as my database.i m using php for displaying data in to several pages but it is not supporting LIMIT functions, so can any one help me how to create php+mssql paging with accurate coding..? plz help me its urgent
bye....................One method is to write a stored procedure that takes a starting record ID and a numrecords value as input parameters.|||hello,
thanx for your reply.u have said about stored procedures...but i m confused and dnt know how to do it.so for your help i m sending you the coding that i have done and took the help of tutorials in MSSQL database using php as front end.
<?php
$dbusername="sa";
$dbpassword="automated";
$servername="203.82.207.175";
$link = mssql_connect("203.82.207.175","sa","automated");
mssql_select_db("B_Employer2000")or die("Unable to connect");
echo $BDYear;
$SQL="SELECT COUNT(*) AS trow FROM empBirthdayWish WHERE (BDYear = 2007)";
$result=mssql_query($SQL,$link) ;
$rs=mssql_fetch_array($result);
$totalrow=$rs["trow"];
$SQL="SELECT Sl_no, EmployeeCode, PCode, BDYear, WishLine, InsertDate FROM empBirthdayWish WHERE(BDYear = 2007)";
$result=mssql_query($SQL,$link) ;
$rs=mssql_fetch_array($result);
$sl_no=$rs["Sl_no"];
//$EmpCode=$rs["EmpCode"];
//$PCode=$rs['PCode'];
$BDYear=$rs['BDYear'];
$WishLine=$rs['WishLine'];
$InsertDate=$rs['InsertDate'];
$numrows = $row['numrows'];
$rowsPerPage = $row['10'];
//$TOP = ['10'];
$rowsPerPage = 10;
$pageNum=1;
for($j=10;$j<=$totalrow;$j=$j+10){
$pageNum = $pageNum+1;
$maxPage =$pageNum;
}
echo $pageNum."Total pages are available";
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
$offset = ($pageNum - 1) * $rowsPerPage;
echo "<table border='1' cell padding='1' cell spacing='1' bgcolor='#CBE643'>
<tr>
<th>Sl.No#</th>
<th>Birthday Year</th>
<th>Your Birthday Wishes</th>
<th>Posted Date</th>
</tr>";
$i=0;
//$SQL = " SELECT * from empBirthdayWish where BDYear='2007' "." LIMIT $offset, $rowsPerPage";
$query = " SELECT * from empBirthdayWish where BDYear='2007' "." TOP $offset, $rowsPerPage";
$result=mssql_query($SQL,$link) or die('Sorry Birthday Wish Search Query failed,Try after some time');
while($row = mssql_fetch_array($result))
{
$i++;
if ($i<=$rowsPerPage) {
echo "<tr>";
echo "<td>".$row['Sl_no'].'<br>';
//echo "<td>".$row['EmpCode'].'<br>';
//echo "<td>".$row['PCode'].'<br>';
echo "<td>".$row['BDYear'].'<br>';
echo "<td>".$row['WishLine'].'<br>';
echo "<td>".$row['InsertDate'].'<br>';
echo "</tr>";
}
else
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
{
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=1\">[Last Page]</a> ";
}
}
else
{
{
$prev = ' [Prev] ';
$first = ' [First 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] ';
$last = ' [Last Page] ';
}
}
//$result = mssql_query($query) or die('Sorry Birthday Wish Search Query failed,Try after some time');
//$maxPage = ceil($numrows/$rowsPerPage);
//$self = $_SERVER['PHP_SELF'];
}
echo "</table>";
echo $first . $prev . " Showing page <strong>1</strong> of <strong>$maxPage</strong> pages " . $next . $last;
?>
pls do suggest me or rewrite your coding in my coding to make it executable.
thanking you|||I'm a stored procedure person, not a PHP person. Sorry.|||well thanx for your reply..
if you are not a php person than i m sorry tht i have requested you without knowing that..as bcoz u r a stored procedure person ...guide me or help me in your own way coz i m confused and not familiar with stored procedures...u can guide me in coding at least..
thanking you|||http://www.google.com/search?q=paging+sql+server|||thanx for your assistance...hope u will assist more in future..
thanking you|||One "little" detail that caught my attention: You are using PHP connecting to a SQL Server as ... sa? You are well aware of the fact that you're having a severe security issue?|||thanx for reply..
how the security issues are coming using the sa?? plz let me know..
bye|||You don't know that sa has all rights in all databases on the instance of SQL Server? That in combination with the threat of SQL-injection, or security issues coming from a script language as PHP makes it a killer-combo for attackers. PHP scripts should really connect to a SQL Server using a login that has only the strictly neccessary access.|||You can simulate paging fairly easily using a single statement.
SELECT TOP $limit * FROM mytable WHERE table_id NOT IN (SELECT TOP $offset table_id FROM mytable ORDER BY test_name)
This requires that table_id be a unique integer value. Usually this is a PK with an IDENTITY(1,1) data type that will auto generate an incrementing value on INSERT.|||also sa has rights to enable xp_cmdshell. if that's enabled, and an attacker has access to it, your entire machine (not just sql server) will be owned by the attacker.|||...and possibly your network as well, depending upon the admin account for your server.
Showing posts with label displaying. Show all posts
Showing posts with label displaying. Show all posts
Monday, March 12, 2012
Friday, March 9, 2012
Pagination question - retrieve only records for each page
I want to provide paged access to data. Rather than displaying the
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/
Pagination of data
Hi
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
--
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
--
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
I am developing a vb2005/sql server 2005 winform app which involves
displaying records in a list, one page at a time. The total number of
records is large. I am wondering if there is a way either in vb/ado or sql
server that automatically pages a certain number of records at a time and
when user scrolls down (or up) pages the next set of records? I guess I can
possibly program it manually but it may be complicated specially when the
records in the next/previous set are different due to the different sort
orders. Ideally I am looking for giving a select statement to include all
records as data source and then expect system to handle any pagination and
bringing only one page of record from server at any one time.
Thanks
RegardsTake a look at this article.
http://www.aspfaq.com/show.asp?id=2120
--
David Portas
SQL Server MVP
--|||I am doing it for a winform app and asp may not be relevant but I will have
a look.
Thanks
Regards
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IZidne6mqKZJLJreRVn-ow@.giganews.com...
> Take a look at this article.
> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>|||Of the various solutions given, most of them are not ASP-specific. Mostly
they use TSQL.
--
David Portas
SQL Server MVP
--|||Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>|||Check out:
http://www.aspfaq.com/show.asp?id=2120
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Phil G." <Phil@.nospam.com> wrote in message
news:de9hlf$9fl$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
Hi John,
I am not a DBA or even half-experienced db developer but, I guess you could
consider achieving your goal by using views. You will still, as you state,
return the whole recordset and possibly 'store it' as a dataset. You can
then create the required views as needed. If there are any DBA's reading
PLEASE don't lecture on the bad practice of returning more records than
required...IT'S NOT MY IDEA! :-) :-)
I know you asked if there was a way to do this 'automatically', but I don't
know of one, other than the built-in methods within the asp datagrid. Sorry
if this is not helpful.
Good luck.
Phil
"John" <John@.nospam.infovis.co.uk> wrote in message
news:eWQ3DddpFHA.2976@.TK2MSFTNGP12.phx.gbl...
> Hi
> I am developing a vb2005/sql server 2005 winform app which involves
> displaying records in a list, one page at a time. The total number of
> records is large. I am wondering if there is a way either in vb/ado or sql
> server that automatically pages a certain number of records at a time and
> when user scrolls down (or up) pages the next set of records? I guess I
> can possibly program it manually but it may be complicated specially when
> the records in the next/previous set are different due to the different
> sort orders. Ideally I am looking for giving a select statement to include
> all records as data source and then expect system to handle any pagination
> and bringing only one page of record from server at any one time.
> Thanks
> Regards
>
Monday, February 20, 2012
Page Number problem
Hello,
I have a problem displaying the correct page numbers (or number of pages). I've inserted the page number field into the page header b, it will always shows 1/1, no matter how many pages i got. on the other hands...how do I solve this problem?
Thanks!Make sure you're using the 'Page N of M' field instead of the 'Page Number' field.
Then use the section expert to check if any sections have the 'Reset Page Number After' box ticked.|||erm...i tried it. it still didn't work.
i tried putting it into the report header, it displays the pages correctly, just that the problem is i dont want it to appear on Report Header, i wanted the page number in the Page Header...due to the way the report was designed (customer's format).
Plz advise?|||oh wait...
i works!
thanks!|||ok..now another problem
let say there are 2 page. the first page displays 1/2. but the 2nd page also displays 1/2
how to solve it? thanks~~~
I have a problem displaying the correct page numbers (or number of pages). I've inserted the page number field into the page header b, it will always shows 1/1, no matter how many pages i got. on the other hands...how do I solve this problem?
Thanks!Make sure you're using the 'Page N of M' field instead of the 'Page Number' field.
Then use the section expert to check if any sections have the 'Reset Page Number After' box ticked.|||erm...i tried it. it still didn't work.
i tried putting it into the report header, it displays the pages correctly, just that the problem is i dont want it to appear on Report Header, i wanted the page number in the Page Header...due to the way the report was designed (customer's format).
Plz advise?|||oh wait...
i works!
thanks!|||ok..now another problem
let say there are 2 page. the first page displays 1/2. but the 2nd page also displays 1/2
how to solve it? thanks~~~
Subscribe to:
Posts (Atom)