001.<html>
002.<head>
003.<title>ThaiCreate.Com PHP & SQL Server (sqlsrv)</title>
004.</head>
005.<body>
006.<?php
007. ini_set('display_errors', 1);
008. error_reporting(~0);
009.
010. $strKeyword = null;
011.
012. if(isset($_POST["txtKeyword"]))
013. {
014. $strKeyword = $_POST["txtKeyword"];
015. }
016. if(isset($_GET["txtKeyword"]))
017. {
018. $strKeyword = $_GET["txtKeyword"];
019. }
020.?>
021.<form name="frmSearch" method="post" action="<?php echo $_SERVER['SCRIPT_NAME'];?>">
022. <table width="599" border="1">
023. <tr>
024. <th>Keyword
025. <input name="txtKeyword" type="text" id="txtKeyword" value="<?php echo $strKeyword;?>">
026. <input type="submit" value="Search"></th>
027. </tr>
028. </table>
029.</form>
030.<?php
031. $serverName = "localhost";
032. $userName = "sa";
033. $userPassword = '';
034. $dbName = "mydatabase";
035.
036. $connectionInfo = array("Database"=>$dbName, "UID"=>$userName, "PWD"=>$userPassword, "MultipleActiveResultSets"=>true);
037.
038. $conn = sqlsrv_connect( $serverName, $connectionInfo);
039.
040. if( $conn === false ) {
041. die( print_r( sqlsrv_errors(), true));
042. }
043.
044. $stmt = "SELECT * FROM customer WHERE Name LIKE '%".$strKeyword."%' ";
045.
046. $params = array();
047. $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
048. $query = sqlsrv_query( $conn, $stmt , $params, $options );
049.
050. $num_rows = sqlsrv_num_rows($query);
051.
052. $per_page = 2;
053. $page = 1;
054.
055. if(isset($_GET["Page"]))
056. {
057. $page = $_GET["Page"];
058. }
059.
060. $prev_page = $page-1;
061. $next_page = $page+1;
062.
063. $row_start = (($per_page*$page)-$per_page);
064. if($num_rows<=$per_page)
065. {
066. $num_pages =1;
067. }
068. else if(($num_rows % $per_page)==0)
069. {
070. $num_pages =($num_rows/$per_page) ;
071. }
072. else
073. {
074. $num_pages =($num_rows/$per_page)+1;
075. $num_pages = (int)$num_pages;
076. }
077. $row_end = $per_page * $page;
078. if($row_end > $num_rows)
079. {
080. $row_end = $num_rows;
081. }
082.
083.
084. $stmt = " SELECT c.* FROM (
085. SELECT ROW_NUMBER() OVER(ORDER BY CustomerID) AS RowID,* FROM customer WHERE Name LIKE '%".$strKeyword."%'
086. ) AS c
087. WHERE c.RowID > $row_start AND c.RowID <= $row_end
088. ";
089. $query = sqlsrv_query( $conn, $stmt );
090.
091.?>
092.<table width="600" border="1">
093. <tr>
094. <th width="91"> <div align="center">CustomerID </div></th>
095. <th width="98"> <div align="center">Name </div></th>
096. <th width="198"> <div align="center">Email </div></th>
097. <th width="97"> <div align="center">CountryCode </div></th>
098. <th width="59"> <div align="center">Budget </div></th>
099. <th width="71"> <div align="center">Used </div></th>
100. </tr>
101.<?php
102.while($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC))
103.{
104.?>
105. <tr>
106. <td><div align="center"><?php echo $result["CustomerID"];?></div></td>
107. <td><?php echo $result["Name"];?></td>
108. <td><?php echo $result["Email"];?></td>
109. <td><div align="center"><?php echo $result["CountryCode"];?></div></td>
110. <td align="right"><?php echo $result["Budget"];?></td>
111. <td align="right"><?php echo $result["Used"];?></td>
112. </tr>
113.<?php
114.}
115.?>
116.</table>
117.<br>
118.Total <?php echo $num_rows;?> Record : <?php echo $num_pages;?> Page :
119.<?php
120.if($prev_page)
121.{
122. echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$prev_page&txtKeyword=$strKeyword'><< Back</a> ";
123.}
124.
125.for($i=1; $i<=$num_pages; $i++){
126. if($i != $page)
127. {
128. echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&txtKeyword=$strKeyword'>$i</a> ]";
129. }
130. else
131. {
132. echo "<b> $i </b>";
133. }
134.}
135.if($page!=$num_pages)
136.{
137. echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$next_page&txtKeyword=$strKeyword'>Next>></a> ";
138.}
139.sqlsrv_close($conn);
140.?>
141.</body>
142.</html>
