asked on

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.

Brian Tao

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.

sammySeltzer

ASKER

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?

Brian Tao

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.

sammySeltzer

ASKER

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.

ASKER CERTIFIED SOLUTION

Brian Tao

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Tao

And yes I was right about the ORDER BY clause in my comment https://www.experts-exchange.com/questions/28650766/Attempting-to-navigate-to-NEXT-or-PREVIOUS-page-gives-an-error-Last-Issue.html?anchorAnswerId=40709329#a40709329
I just checked and confirmed that SQL Server would not accept such clause in a SELECT COUNT(*) statement.

sammySeltzer

ASKER

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.

sammySeltzer

ASKER

Thank you Brian

Brian Tao

You're welcome. Glad that helped. Thanks for the points.