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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.