Building an advanced search option using PHP & MSSQL and need some advice

Remove the following line if you want to allow keyword to be blank

&& strlen(trim($_POST['keyword'][$i])) !== 0

Hi, nearly there.

I thought I had it sorted again, but to only see that the pagination doesnt work properly, so I skipped through the other stuff we did, and noticed the code below:


 $sql = "SELECT COUNT(*) FROM Intranet";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet)
select * from LIMIT WHERE {limit clause} order by ID DESC";

The row_number im guessing controls the numbers for each page and that allows it to work properly.

Can you show me how I can integrate that into the below:


$query = "SELECT * FROM Intranet WHERE";
  for ($i = 0; $i < sizeof($_POST['field_names']); $i++)
  {
    if (strlen(trim($_POST['field_names'][$i])) !== 0
      && strlen(trim($_POST['operator'][$i])) !== 0)
      //&& strlen(trim($_POST['keyword'][$i])) !== 0)
	
	  {
	  if (($_POST['field_names'][$i])
	  && ($_POST['operator'][$i]))
      { 	
	
       $field_name = ($_POST['field_names'][$i]);
       $operator= ($_POST['operator'][$i]);
       $keyword= ($_POST['keyword'][$i]);
	
       if ($i === 0)
       {
	   	 if ($operator=="like"){
		 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
			} else {
         $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'";
		 }
       }
       else
       {
         if ($operator=="like"){
		 $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'";
			} else {
         $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
		 }
       }
    }
    else
    {
      $queryIsValid = false;
      echo "required information missing or is invalid - query was not executed";
      break;
    }
  }
}
  if ($queryIsValid)
{

Can you remind me what the pagination code looks like? What where clause we added to it?

I think I found it:

$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet)  
select * from LIMIT WHERE";
  for ($i = 0; $i < sizeof($_POST['field_names']); $i++) 
  { 
    if (strlen(trim($_POST['field_names'][$i])) !== 0 
      && strlen(trim($_POST['operator'][$i])) !== 0) 
      //&& strlen(trim($_POST['keyword'][$i])) !== 0)
	  
	  {
			if (($_POST['field_names'][$i])
			&& ($_POST['operator'][$i]))
				{ 	  
			
				 $field_name = ($_POST['field_names'][$i]); 
				 $operator= ($_POST['operator'][$i]); 
				 $keyword= ($_POST['keyword'][$i]);
			 
				 if ($i === 0) 
				 { 
				 if ($operator=="like"){
			 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
				} else {
					 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'";
				} 
				 } 
				 else 
				 { 
					if ($operator=="like"){
					 $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'";
					}else {
					 $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
					}  
				 } 
			} 
			else 
			{ 
				$queryIsValid = false; 
				echo "required information missing or is invalid - query was not executed"; 
				break; 
			} 
		} 
	}
  if ($queryIsValid) 
	{
		// read the $currentPage from the querystring...
		$offset = ($currentpage - 1) * $rowsperpage; 
		$limit=$offset + $rowsperpage;
		
		$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
	}

Hi,

I changed it over and echo’d it out and got this belo, when I select Contract_Number like DOM%:

WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE Contract_Number like ‘DOM%’ AND RowNumber BETWEEN 1 AND 50 order by ID DESC

Which as far as I can see is correct, but the pageing isnt working correctly, in that firstly its only meant to be 50 per page, but its bring out all the contracts and so displaying them all on the first page, and then the pageing seems to be offering more pages than contracts.

I have added it all below again sorry cpradio, but this last bit is now it I think (until Monday morning, when they find more problems)

Thanks again

$queryIsValid = true;
//$query = “SELECT * FROM Intranet WHERE”;
$query = “WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet)
select * from LIMIT WHERE”;
for ($i = 0; $i < sizeof($_POST[‘field_names’]); $i++)
{
if (strlen(trim($_POST[‘field_names’][$i])) !== 0
&& strlen(trim($_POST[‘operator’][$i])) !== 0)
//&& strlen(trim($_POST[‘keyword’][$i])) !== 0)

  {
  if (($_POST['field_names'][$i])
  && ($_POST['operator'][$i]))
  { 	

   $field_name = ($_POST['field_names'][$i]);
   $operator= ($_POST['operator'][$i]);
   $keyword= ($_POST['keyword'][$i]);

   if ($i === 0)
   {
   	 if ($operator=="like"){
	 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
		} else {
     $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'";
	 }
   }
   else
   {
     if ($operator=="like"){
	 $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'";
		} else {
     $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
	 }
   }
}
else
{
  $queryIsValid = false;
  echo "required information missing or is invalid - query was not executed";
  break;
}

}
}
if ($queryIsValid)
{

$result = sqlsrv_query($conn, $query);
$result2 = sqlsrv_query($conn, $query);
$r = sqlsrv_fetch_array($result2);
$numrows = $r[0];
// number of rows to show per page
if ($rows == “”){
$rowsperpage = 50;
} else {
$rowsperpage = $rows;// find out total pages
}

$totalpages = ceil($numrows / $rowsperpage);
// get the current page or set a default
if (isset($_GET[‘currentpage’]) && is_numeric($_GET[‘currentpage’])) {
// cast var as int
$currentpage = (int) $_GET[‘currentpage’];
} else {
// default page num
$currentpage = 1;
} // end if

// if current page is greater than total pages…
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page…
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end if

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset - 1) + $rowsperpage;
//$query = str_replace(‘{limit clause}’, “RowNumber BETWEEN $offset AND $limit”, $query);
$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";

echo $query;
$stmt = sqlsrv_query($conn, $query);
//var_dump($conn, $query);

/****** build the pagination links ******/// range of num links to show
$range = 30;
echo “<div style=position:relative;height:30px;line-height:30px;><span style=font-family:Arial;font-size:14px;color:#333333;margin-top:9px;margin-bottom:5px;height:20px;margin-left:2px;>”;
// if not on page 1, don’t show back links
if ($currentpage > 1) {
// show << link to go back to page 1
// was echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=1’><<</a> “;
echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=1&Search=$search&q=$searchKeyword&Rows=$rows’ style=‘color:#333333’><<</a>”;
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
// was echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$prevpage’><</a> ";
echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$prevpage&Search=$search&q=$searchKeyword&Rows=$rows’ style=‘color:#333333’><</a> ";
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
// if it’s a valid page number…
if (($x > 0) && ($x <= $totalpages)) {
// if we’re on current page…
if ($x == $currentpage) {
// ‘highlight’ it but don’t make a link
echo " [<b>$x</b>] ";
// if not current page…
} else {
// make it a link
// was echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$x’>$x</a> ";
echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$x&Search=$search&q=$searchKeyword&Rows=$rows’ style=‘color:#333333’>$x</a> ";
} // end else
} // end if
} // end for

// if not on last page, show forward and last page links
if ($currentpage != $totalpages) {
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
// was echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$nextpage’>></a> ";
echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$nextpage&Search=$search&q=$searchKeyword&Rows=$rows’ style=‘color:#333333’>></a> ";
// echo forward link for lastpage
// was echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$totalpages’>>></a> ";
echo " <a href=‘{$_SERVER[‘PHP_SELF’]}?currentpage=$totalpages&Search=$search&q=$searchKeyword&Rows=$rows’ style=‘color:#333333’>>></a> ";
} // end if
/****** end build pagination links ******/
echo “</span>”;
if ($rows==“1000000”){
echo “<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:143px;line-height:20px;top:-4px;left:30px;>Showing <strong>‘All’</strong>.  |  </span>”;
} else {
echo “<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:200px;line-height:20px;top:-4px;left:30px;>Showing <strong>‘$rowsperpage’</strong> per page  |  </span>”;
}

if ($search==“”){
} else {
echo “<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:auro;line-height:20px;top:-4px;left:7px;margin-right:10px;>Search: <strong>‘$search’</strong>  |  </span>”;
}

if(isset($_GET[‘q’]))
{
$searchKeyword = @$_GET[‘q’];
if ($searchKeyword==“”) {
} else {
$trimmed = $searchKeyword;
echo “<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:2px;top:-3px;font-size:13px;color:#555555;font-family:verdana;position:relative;>Search results for your keyword: <strong>'” .$trimmed. “'</strong></span></div>”;
}
}
// To change line colour
$color=“1”;
//echo $query;
while($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{

Okay, I forgot that the CTE needs to limit its’ results too

$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE{limit clause})  
select * from LIMIT WHERE{limit clause}"; // updated $query
  $limitClause = "";
  for ($i = 0; $i < sizeof($_POST['field_names']); $i++) 
  { 
    if (strlen(trim($_POST['field_names'][$i])) !== 0 
      && strlen(trim($_POST['operator'][$i])) !== 0) 
      //&& strlen(trim($_POST['keyword'][$i])) !== 0)
      
      {
            if (($_POST['field_names'][$i])
            && ($_POST['operator'][$i]))
                {       
            
                 $field_name = ($_POST['field_names'][$i]); 
                 $operator= ($_POST['operator'][$i]); 
                 $keyword= ($_POST['keyword'][$i]);
             
                 if ($i === 0) 
                 { 
                 if ($operator=="like"){
             $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                } else {
                     $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                } 
                 } 
                 else 
                 { 
                    if ($operator=="like"){
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                    }else {
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                    }  
                 } 
            } 
            else 
            { 
                $queryIsValid = false; 
                echo "required information missing or is invalid - query was not executed"; 
                break; 
            } 
        } 
    }
  if ($queryIsValid) 
    {
        // read the $currentPage from the querystring...
        $offset = ($currentpage - 1) * $rowsperpage; 
        $limit=$offset + $rowsperpage;
        
				$query = str_replace('{limit clause}', $limitClause, $query); // added this line
        $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
    }

I had white out for a little bit and went back through it and left some code out, but put it all back to how you have it, and the cms came back but with two errors.

This error:

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 832 WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE Contract_Number like ‘DOM%’ AND Row_Number BETWEEN 1 AND 50 order by ID DESC

relates to this line:


$result = sqlsrv_query($conn, $query);
$result2 = sqlsrv_query($conn, $query);
$r = sqlsrv_fetch_array($result2); //This line is 832
$numrows = $r[0];
// number of rows to show per page
 if ($rows == ""){
 $rowsperpage = 50;
 } else {
$rowsperpage = $rows;// find out total pages
}

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 941


while($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{

Thanks again

Change

$result2 = sqlsrv_query($conn, $query);

To

$result2 = sqlsrv_query($conn, $query);
if( $result2=== false ) {
     die( print_r( sqlsrv_errors(), true));
}

As a side note, why are you running the query twice?

$result = sqlsrv_query($conn, $query);
$result2 = sqlsrv_query($conn, $query);

This is the error:

Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 0

 => 0 [2] => [Microsoft][SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error [message] => [Microsoft][SQL Server Native Client 10.0]Syntax error, permission violation, or other nonspecific error ) )

In honesty I'm not 100% sure, I did it as it seemed before there was a conflict and when I did it got it working.

I was looking at it just now and thinking that it cant be right, and so I coded it out as below, and the same error came up:


//$result = sqlsrv_query($conn, $query);
//$result2 = sqlsrv_query($conn, $query);
$result = sqlsrv_query($conn, $query);
if( $result=== false ) {
die( print_r( sqlsrv_errors(), true));
}
$r = sqlsrv_fetch_array($result);
$numrows = $r[0];
// number of rows to show per page
if ($rows == “”){
$rowsperpage = 50;
} else {
$rowsperpage = $rows;// find out total pages
}



I suppose it was me using the old I fixed it, it seems to be working dont look back and move on situation, but they always came back to get you.

Got it!

Your CTE defines the ROW_NUMBER() value as ‘RowNumber’ and your SELECT against the CTE is using Row_Number, Change the following line

$query .= " AND Row_Number BETWEEN $offset AND $limit order by ID DESC"

To be

$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"
Edit:

Also make sure your CTE is on one line, and your SELECT * FROM LIMIT is on a separate line. Granted it shouldn’t make a difference

Ah, thought you had it then, but it still came back with the same error sorry.


$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset - 1) + $rowsperpage;
//$query = str_replace('{limit clause}', "RowNumber BETWEEN $offset AND $limit", $query);
$query = str_replace('{limit clause}', $limitClause, $query); // added this line
$query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC";
echo $query;
$stmt = sqlsrv_query($conn, $query);

I don’t see anything wrong with the Query syntax (as it runs perfectly fine when I run it), are you sure you connection to the database was successful?

Yes all seems fine as I could draw the contracts out.

I’ve been up and down it too and comparing to the previous code and it seems fine as you say.

I will go through it for an hour, and come back to it monday then.

have a nice weekend.

thanks again

Last thing to try and to make sure you echo the query right before calling sqlsrv_query, then run that query in SSMS or something to verify the way it was outputted is accepted by SQL Server.

Hi cpradio,

I hope you dont mind me keeping this going with you.

I worked backwards through our posts as at one point it was outputting but the pageing wasnt working, so have got it where it is working again, as in outputting the contracts.

And have found what update causes the error.

Basically, when the code below is as it is, the contracts output correctly, but they all come out on page 1 rather than the 50 allowed.


if ($i === 0)
                 {
                 if ($operator=="like"){
				 	 $query .= " " . $field_name . " " . $operator . " '" . $keyword . "%'";
                } else {
                     $query .= " " . $field_name . " " . $operator . " '" . $keyword . "'";
                }
                 }
                 else
                 {
                    if ($operator=="like"){
                     $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'";
                    }else {
                     $query .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'";
                    }
                 }
            }

We then changed that too:


if ($i === 0)
                 {
                 if ($operator=="like"){
             $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                } else {
                     $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                }
                 }
                 else
                 {
                    if ($operator=="like"){
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                    }else {
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                    }
                 }
            }

And we get the following error:

sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\advanced_Search.php on line 834 WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet) select * from LIMIT WHERE AND RowNumber BETWEEN ‘1’ AND ‘50’ order by ID DESC

So its around there, the problem lies, but there nothing much there to look at, as in change.

I will keep trying things, and if you can help that be great if not no worries.

Make sure you updated the $query variable to (per my prior post)

$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE{limit clause})  
select * from LIMIT WHERE{limit clause}"; // updated $query

And (per my prior post)

                $query = str_replace('{limit clause}', $limitClause, $query); // added this line
        $query .= " AND RowNumber BETWEEN $offset AND $limit order by ID DESC"

Yep got those in;


$query = "WITH LIMIT AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE {limit clause})
select COUNT(*) from LIMIT WHERE {limit clause}";
$limitClause = "";
  for ($i = 0; $i < sizeof($_POST['field_names']); $i++)
  {
    if (strlen(trim($_POST['field_names'][$i])) !== 0
      && strlen(trim($_POST['operator'][$i])) !== 0)
      //&& strlen(trim($_POST['keyword'][$i])) !== 0)

      {
            if (($_POST['field_names'][$i])
            && ($_POST['operator'][$i]))
                {

                 $field_name = ($_POST['field_names'][$i]);
                 $operator= ($_POST['operator'][$i]);
                 $keyword= ($_POST['keyword'][$i]);

                 if ($i === 0)
                 {
                 if ($operator=="like"){
             		 $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                } else {
                     $limitClause .= " " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                }
                 }
                 else
                 {
                    if ($operator=="like"){
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "%'"; // updated
                    }else {
                     $limitClause .= " AND " . $field_name . " " . $operator . " '" . $keyword . "'"; // updated
                    }
                 }
            }
            else
            {
                $queryIsValid = false;
                echo "required information missing or is invalid - query was not executed";
                break;
            }
        }
    }

  if ($queryIsValid)
{

//$result = sqlsrv_query($conn, $query);
$result2 = sqlsrv_query($conn, $query);
$result = sqlsrv_query($conn, $query);
//if( $result=== false ) {
     //die( print_r( sqlsrv_errors(), true));
//}
$r = sqlsrv_fetch_array($result);
$numrows = $r[0];
// number of rows to show per page
 if ($rows == ""){
 $rowsperpage = 50;
 } else {
$rowsperpage = $rows;// find out total pages
}

 $totalpages = ceil($numrows / $rowsperpage);
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$currentpage = (int) $_GET['currentpage'];
} else {
// default page num
$currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end if

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit = ($offset - 1) + $rowsperpage;
//$query = str_replace('{limit clause}', "RowNumber BETWEEN $offset AND $limit", $query);
$query = str_replace('{limit clause}', $limitClause, $query); // added this line
$query .= " AND RowNumber BETWEEN '$offset' AND '$limit' order by ID DESC";
echo $query;
$stmt = sqlsrv_query($conn, $query);

i agree this is very strange, as nothing now stands out

Ah, your are trying to execute the query before you replace the {limit clause} placeholder…

Put this line

$query = str_replace('{limit clause}', $limitClause, $query); // added this line

So it is right after

  if ($queryIsValid) 
{

Ah yes progress…

The error has now gone but no contracts in view but the pageing is working in that the correct number of pages are available for the pageing, but nothing on show and when page 2 of the pageing is clicked all the pageing data goes away, as in the number of pages.

Thanks cpradio

I would need to see your entire script to see why that is.