Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    6

    Question Unanswered: Paging MSSQL database result with PHP..Can Any One help me plz..

    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..........................

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One method is to write a stored procedure that takes a starting record ID and a numrecords value as input parameters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2007
    Posts
    6

    Talking About Paging in MSSQL with php

    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm a stored procedure person, not a PHP person. Sorry.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2007
    Posts
    6

    Unhappy paging with php & mssql

    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

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313

  7. #7
    Join Date
    Apr 2007
    Posts
    6

    Talking hello..

    thanx for your assistance...hope u will assist more in future..

    thanking you

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    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?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  9. #9
    Join Date
    Apr 2007
    Posts
    6

    Question

    thanx for reply..

    how the security issues are coming using the sa?? plz let me know..

    bye

  10. #10
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    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.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  11. #11
    Join Date
    Feb 2005
    Posts
    14
    You can simulate paging fairly easily using a single statement.

    Code:
    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.

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    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.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and possibly your network as well, depending upon the admin account for your server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •