Attempting to navigate to NEXT or PREVIOUS page gives an error -- Last Issue

Greetings again,

I am confident this is the last known issue with this php project.

I have worked on this on and off while concentrating on my last big issue.

This is the code we use for the dynamic WhERE clause.

$fields = array(
    'projectTitle' => array('field' => 'b.BidTitle', 'searchType' => 'like'),
    'BidType' => array('field' => 'b.BidType', 'searchType' => 'equal'),
    'BidStatus' => array('field' => 'b.BidStatus', 'searchType' => 'equal'),
    'department' => array('field' => 'b.AliasID', 'searchType' => 'equal'),
    'bidId' => array('field' => 'b.BidID', 'searchType' => 'equal'),
    'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')

);

$where = array();
foreach($fields as $fieldPost => $field) {
    if(isset($_POST[$fieldPost]) && strlen($_POST[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
    }
}

Open in new window


The sort and ordering code:

// select array
$Columns = array('BidDate','DueDate', 'AwardDate','LastUpdate');


// define sortable query ASC DESC
$sort =  isset($_GET['sort']) && in_array($_GET['sort'], $Columns) ? $_GET['sort'] : $sortDefault;
$order = (isset($_GET['order']) && strcasecmp($_GET['order'], 'ASC') == 0) ? 'ASC' : 'DESC';

Open in new window


Then the code snip:
   $sql = "Select COUNT(*) As totalRecords
          FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID inner join Dept d on da.DeptCode =d.DeptCode inner join status s on b.BidStatus=s.StatusId " . ( count($where) > 0 ? " WHERE " . implode(' AND ', $where) : " ORDER By da.DeptAlias ASC" );
//echo $sql;
	//$params = array();
//	$options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
	$query = sqlsrv_query( $conn, $sql );

   $num_rows = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)["totalRecords"];

Open in new window


Finally, the navigation code:
Total <?php echo $num_rows;?> Records : <?php echo $num_pages;?> Page(s) :
<?php
if($prev_page)
{
	echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$prev_page&where=$searchType'><< Back</a> ";
}

for($i=1; $i<=$num_pages; $i++){
	if($i != $page)
	{
		echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&where=$searchType&sort=$sort&order=$order'>$i</a> ]";
	}
	else
	{
		echo "<b> $i </b>";
	}
}
if($page!=$num_pages)
{
	echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$next_page&where=$searchType'>Next>></a> ";
}

Open in new window


The code now loads correctly, THANKS in large part to Brian.

However, the only issue that I am stuck on is when a user clicks a number, say 2 or when a user clicks NEXT to go the next page or PREVIOUS to go to previous page, it gives the following error:

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in PagedSearch.php on line 239

This error is on this line:

$num_rows = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)["totalRecords"];

Open in new window


I don't have this problem with other pages simply because this one is using dynamic WHERE clause.

Any assistance, as always, is greatly appreciated.
LVL 29
sammySeltzerAsked:
Who is Participating?
 
Brian TaoSenior Business Solutions ConsultantCommented:
Try the following:
Change the dynamic WHERE part to (by adding the $_GET part and composing the $searchType):
$fields = array(
    'projectTitle' => array('field' => 'b.BidTitle', 'searchType' => 'like'),
    'BidType' => array('field' => 'b.BidType', 'searchType' => 'equal'),
    'BidStatus' => array('field' => 'b.BidStatus', 'searchType' => 'equal'),
    'department' => array('field' => 'b.AliasID', 'searchType' => 'equal'),
    'bidId' => array('field' => 'b.BidID', 'searchType' => 'equal'),
    'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')

);

$where = array();
$searchType = "";
foreach($fields as $fieldPost => $field) {
    if(isset($_POST[$fieldPost]) && strlen($_POST[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_POST[$fieldPost];
    }
    if(isset($_GET[$fieldPost]) && strlen($_GET[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_GET[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_GET[$fieldPost]) . "'";
        }
        $searchType .= (empty($searchType) ? "" : "&") . $fieldPost . "=" . $_GET[$fieldPost];
    }
}

Open in new window

And then change the NAVIGATION code to (by removing the "where="s):
Total <?php echo $num_rows;?> Records : <?php echo $num_pages;?> Page(s) :
<?php
if($prev_page)
{
	echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$prev_page&$searchType'><< Back</a> ";
}

for($i=1; $i<=$num_pages; $i++){
	if($i != $page)
	{
		echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&$searchType&sort=$sort&order=$order'>$i</a> ]";
	}
	else
	{
		echo "<b> $i </b>";
	}
}
if($page!=$num_pages)
{
	echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$next_page&$searchType'>Next>></a> ";
}

Open in new window


Let me know if it works.
0
 
Brian TaoSenior Business Solutions ConsultantCommented:
The problem here is that the search fields (those $_POST[$fieldPost] in your code) are not being posted again, so it goes to the " ORDER By da.DeptAlias ASC" part and that's where it went wrong.

So change the $sql in your "code snip" to:
   $sql = "Select COUNT(*) As totalRecords
          FROM bids b inner join DeptALIAS da on b.AliasID = da.AliasID inner join Dept d on da.DeptCode =d.DeptCode inner join status s on b.BidStatus=s.StatusId " . ( count($where) > 0 ? " WHERE " . implode(' AND ', $where) : " " );

Open in new window

and it should work.

But please note, that even if you get rid of the error, the search result is no longer filtered for the same reason - the search fields (those $_POST[$fieldPost] in your code) are not being posted again.
0
 
sammySeltzerAuthor Commented:
Brian,

Thanks very much.

Your help is very much appreciated.

I just need some clarification.

First, you are suggesting that ORDER BY da.DeptAlias was the reason for the error?

You are  also suggesting that by removing ORDER by da.DeptAlias, code is no longer order based on that ORDER BY?

I am ok with that if that's what you mean.

Finally, I didn't quite understand what you mean by:
the search fields (those $_POST[$fieldPost] in your code) are not being posted again

Open in new window


Can you please explain?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Brian TaoSenior Business Solutions ConsultantCommented:
1) Sorry, I might have been wrong about the SQL statement.
One of the problems is that you're trying to "squeeze" things into shorter code, which is not necessary in PHP. Can you please try making the single line
$num_rows = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)["totalRecords"];

Open in new window

into 2 lines
$tmp_res_array = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC);
$num_rows = $tmp_res_array["totalRecords"];

Open in new window


2) for the part
the search fields (those $_POST[$fieldPost] in your code) are not being posted again
It means that there's no longer any WHERE applied when linked from the page navigation, because there's nothing being "POSTED".  All arguments are from the querystring in the link and are only available through $_GET,  On top of that I don't see you adding any of those search fields to the page navigation links.

3) in the page navigation links, you put "where=$searchType", but I don't see you referencing any "where" in your code and I don't see how you compose the $searchType.  It means you only have the page number (accessed using $_GET["Page"]) and nothing else on your page 2, page 3, etc.
0
 
sammySeltzerAuthor Commented:
Well, you just touched on my biggest issue.

Recall that I said in the original post above that I have several other pages using the navigation that work perfectly?

The reason I am having issues with this page is because of the dynamic WHERE.

So, the where=$searchType is my attempt to guess what the WHERE could be.

That's why I am hoping that perhaps, with your php experience you could look at the code I am using for the dynamic WHERE and tell me if possible what I could put in the navigation links so that it doesn't matter whether AliasID is passed as value of where or name is passed as value of where, the navigation links will get the correct WHERE value.

If you don't know, then that's fine.
0
 
Brian TaoSenior Business Solutions ConsultantCommented:
And yes I was right about the ORDER BY clause in my comment http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28650766.html#a40709329
I just checked and confirmed that SQL Server would not accept such clause in a SELECT COUNT(*) statement.
0
 
sammySeltzerAuthor Commented:
You are a php genius man.

Ordering and navigation are working fine now.

Only problem is the sorting is no longer working fine.

If you click the header, it is only sorting current page, not entire pages.

It may have to do with that ROWID again.
Thanks for all your help Brian.

Almost there.
0
 
sammySeltzerAuthor Commented:
Thank you Brian
0
 
Brian TaoSenior Business Solutions ConsultantCommented:
You're welcome.  Glad that helped.  Thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.