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.
