Having issues exporting appropriate querystring values to another page

[sonnieboy]

<?php
  $filename ="bids";
  header("Content-type: application/csv");
  header("Content-Disposition: attachment; filename=$filename.csv");
  header("Pragma: no-cache");
  header("Expires: 0");
 
  ini_set('display_errors',1);
  $private=1;
  error_reporting(E_ALL ^ E_NOTICE);
 
  // Connect to SQL Server database
  include("connections/Connect.php");
 
  $start = $_REQUEST["start"];
  $end = $_REQUEST["end"];
  $strTypes = $_REQUEST['stypes'];
 // echo $strTypes;
 
 $csv_output = 'Row,"Bid Date","Due Date","Due Time","Project Title","ID","Department","Type","Award Date","Last Update","Status"'."\r\n";
 
     if(strpos($strTypes, 'bidDate') !== false){
       	$sql = " SELECT c.* FROM (
   			SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime,  b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status
             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 where b.BidDate = (strpos($strTypes, 'bidDate')
   			) AS c
   		WHERE c.RowID > $start AND c.RowID <= $end ORDER BY c.RowID ASC
   	";
       }
 
 // echo $sql;
  	$select_c = sqlsrv_query( $conn, $sql);
   echo $csv_output;
 
  while($result = sqlsrv_fetch_array($select_c, SQLSRV_FETCH_ASSOC))
  {
   echo implode(",", $result)."\n";
  }
      exit;
?> 

echo "<br /><br /><a class='btn btn-default' href='exportsw.php?start=".urlencode($row_start)."&end=".urlencode($num_rows)."&stypes=".urlencode($searchType)."'>Export to CSV</a>"; 

Dear experts,

 

We  have a query that displays searched results based on dynamic WHERE clause.

 

In other words, we have a total of 8 parameters that users search records by.

 

A user can search by one or more parameter(s) and get results displayed based on search types.

 

This works a treat.

 

Next step is to export those results to another page called exports.php.

 

This is where I am having issues.

 

Three parameters are passed as url params to exports.php.

 

Here is that line of code:

 

Then here is the entire code for exports.php:

 

Without $strTypes, the code works and results are exported into .csv file.

 

The issue is that wrong results are exported to .csv.

 

When you add the where clause, where b.bidDate = (strops($strType, 'bidDate')), I am getting

 

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource Boolean given in exports.php line 35.

 

Any ideas how to resolve this?

Thanks a lot in advance.

 

[IThinkMyBrainHurts]

I'd hazzard a guess that early on when you use:

     if(strpos($strTypes, 'bidDate') !== false){

that its coming back as false, so the query string is not made, you then don't check for an empty query string, hence:

$select_c = sqlsrv_query( $conn, $sql);

returns false since no query string, then:

sqlsrv_fetch_array(...

throws an error since $select_c is not a resource but a boolean false...
 
As the manual for sqlsrv_query() states:
Returns a statement resource on success and FALSE if an error occurred.

Which sounds like your reported error!?

[sonnieboy]

    if(strpos($strTypes, 'Current') !== false){
      	$sql = " SELECT c.* FROM (
  			SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime,  b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status
            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 where b.BidStatus = 1
  			) AS c
  		WHERE c.RowID > $start AND c.RowID <= $end ORDER BY c.RowID ASC
  	";

My own guess is that I am not using $strTypes correctly and that's where I need help.

 

For instance, this query below, works perfectly and I am sure that's because the WHERE clause is static, where b.BidStatus = 1

 

If I can grab the value of strTypes and parse it correctly in that WHERE clause, the error goes away.

[IThinkMyBrainHurts]

But what is $strTypes??? It's a passed variable... something you're passing...

 

&stypes=".urlencode($searchType)

 

So... what is in $searchType?

[sonnieboy]

SELECT c.* FROM ( SELECT ROW_NUMBER() OVER(ORDER BY b.ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime, b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status 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 where b.BidDate = 'bidDate=03/03/2015' ) AS c WHERE c.RowID > 0 AND c.RowID <= 3 ORDER BY c.RowID ASC

Ok, I see the problem -- *AND* I was right!

 

It is the $strTypes!!

 

I changed to query slightly to this:

WHERE b.BidDate = '$strTypes' and below is the echo result.

 

For some reason, when I did the echo before, the results would not display. Here it is:

 

Notice that I am getting the correct date values.

 

*HOWEVER*, it is appending the variable bidDate.

 

See where it says, WHERE b.BidDate = 'bidDate=bidDate=03/03/2015'

 

How do I remove bidDate so that it shows:

WHERE b.BidDate = '03/03/2015'???

[QuickOldCar]

A user can search by one or more parameter(s) and get results displayed based on search types.

 

Show this part of the code that creates $searchType

 

Try building the href urls with http_build_query instead of manually

 

You shouldn't have to be looking for a parameter and it should just hold the value of each

if(strpos($strTypes, 'bidDate') !== false){

 

Instead it should be something like $_REQUEST['bidDate'] or if passed them as a multi in the form would be $_REQUEST['stypes']['bidDate']

 

So the solution to this is to gather each selected GET parameter from your form and use http_build_query

Then you check which GET parameters exist and use them for your query

 

 

Let's say your form is using GET as the method

echo "<br /><br /><a class='btn btn-default' href='exportsw.php?".http_build_query($_GET, '', '&')."'>Export to CSV</a>";

Then

if(isset($_REQUEST['bidDate']) && trim($_REQUEST['bidDate']) !=''){
//are going to want to escape and sanitize these, pdo, prepared statements or parameterized queries
$bidDate = trim($_REQUEST['bidDate']);

        $sql = " SELECT c.* FROM (
      SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,CONVERT(VARCHAR(11), b.BidDate, 106) BidDate,CONVERT(VARCHAR(11), b.DueDate, 106) DueDate, b.DueTime,  b.BidTitle, b.BidID, da.DeptAlias, b.BidType, CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe CONVERT(VARCHAR(11), b.AwardDate, 106) END AS AwardDate, CASE WHEN b.LastUpdate='01/01/1900' THEN NULL ELSE CONVERT(VARCHAR(11), b.LastUpdate, 106) END AS LastUpdate, s.Status
             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 where b.BidDate = $bidDate
      ) AS c
     WHERE c.RowID > $start AND c.RowID <= $end ORDER BY c.RowID ASC
    ";
       }