export appropriate records to .csv file based on querystring values??

[Kaylech]

Hello mates,

My third post here and hopefully, third time is a charm.

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

In other words, out of 8 search params, a user could search by either one or more params by simply passing a dynamic WHERE clause called $searchType.

This works great.

The last phase is to pass the value of $searchType via querystring to another page called exports.php and then dump the contents of the records associated with $searchType into a .csv file.

This is where I am having issues.

First the url with params:

 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>";

Three params are passed from this url to exports.php.

Here is the entire code for exports.php:

  <?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;
?>

Right now, I am having issues with $strTypes.

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource boolean given in exportsw.php on line 35

Obviously, I am relatively new to php.

Any ideas how to work this out?

Thanks in advance

 

[jpadie]

that means your query is failing. check what error is being shown and that should help you fix it.

 

[Kaylech]

Nope, it is not the query.

The query runs perfectly if I remove this WHERE predicate:

 where b.BidDate = (strpos($strTypes, 'bidDate')

As stated in my first post, it has to do with $strTypes

 

[jpadie]

Sorry. No.
The query is returning boolean false. That means it is an error so far as your database engine believes.

Simply get the error and we can help further.

It may be that the error derives from within that code segment you posted. Probably is in fact as you cannot include php functions within quotes like that and i dont recall sql server having a strpos function. (Isnt it called charindex?).

In php within double quotes variables expand functions dont.

 

[Kaylech]

Just to clarify a few things, strops has nothing to do with sql server.

This one works perfectly because WHERE clause is static.

    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
  	";

I don't know what you mean by get the error.

I posted the error that I am getting and it *has* to do with the $strTypes variable.

I am not using it correctly and that's where I need help on.

 

[Kaylech]

UPDATE:

I changed the WHERE clause a little bit.
So, instead of this:

where b.BidDate = (strpos($strTypes, 'bidDate')

It becomes this:

where b.BidDate = '$strTypes'

When put an echo on this, this is what I got below

where b.BidDate = [b]'bidDate=03/03/2015'[/b]

How can I get rid of bidDate= so it becomes

where b.BidDate = '03/03/2015'

?

 

[jpadie]

you've posted the php error for the fetch. not the database error for the query.

if(FALSE !== $x = strpos($strTypes, '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 = %s
    ) AS c
WHERE       c.RowID > %s 
            AND c.RowID <= %s 
ORDER BY c.RowID ASC
";
    $sql = sprintf($sql, $x, $start, $end);
endif;
$select_c = sqlsrv_query( $conn, $sql);
if( $select_c === false ):
     die( print_r( sqlsrv_errors(), true));
endif;
echo $csv_output;
while($result = sqlsrv_fetch_array($select_c, SQLSRV_FETCH_ASSOC)):
   echo implode(",", $result)."\n";
endwhile;
exit;

note that the above may not produce well formed csv. it would be more normal to enquote and escape fields.

 

[Kaylech]

hi jpadie,

I think we are seriously miscommunicating about errors.

There is NO database error.

When I run my code in SSMS, it works great.

Also, as stated above, if I remove the WHERE predicate, the code runs fine and dumps contents of the results into csv file except that it is not the correct results because I removed the where clause.

The only error is the fetch error I posted.

Also, just looking at your code, it will produce no results because b.BidDate is getting assigned the value of 0 which is wrong.

There is no such value for date field.

I posted my most current result where b.BidDate = 'bidDate=03/03/2015'

If we could use some sort of replace function to remove bidDate= (just those two) leaving b.BidDate='03/03/2015', I thing we will have our solution.

I am researching that now.

My background is sql server, reporting services and asp.net.

So, php is new but then I still have a good understanding of it.

Thanks for your help.

 

[Kaylech]

Ok, I got this worked out.

For those who might run into similar situation, I discovered explode()

$strTypes = $_REQUEST['stypes'];
$Types = explode("=", $strTypes);

Then

SELECT * FROM myTable WHERE b.BidDate = $Types[1];

That solved it for me.

 

[jpadie]

i guarantee there was an error in the query.
you must test for that. your code did not do so.
whether the error derived from your use of strpos or not is irrelevant. the error was in the query. that is the only way that php will return boolean false rather than a resource.

 

[Kaylech]

For some reason, I don't think YOU are reading *everything* I have been posting including the codes.

Of course the query is failing but the *reason* the query is failing is because of the $strTypes variable.

When I run this code - (example for simplicity):

SELECT * FROM myTable WHERE b.BidDate = '$strTypes';

When debugged, produces the following query:

SELECT * FROM myTable WHERE b.BidDate = 'bidDate=03/03/2015'

Of course that is considered query and of course that will fail *BUT* it is failing because of the $strTypes.

Got it now?????

If you remove the WHERE clause, there is NOTHING wrong with my query.

It is working great now because of the solution I just posted.

Now, create a simple table, insert dummy data into the table, use the code I was having problem with, run it and you will reproduce exact same problem I was having.

Now, go back and use the solution I provided and it will be SOLVED.

 

[jpadie]

the point here is to show you how to debug in php.

your first post made clear that your error was in the query. so i told you this and suggested you check the error back from the database. you could have checked the manual for how to do so.

then i showed you the code that would tell you the error.

if( $select_c === false ):
     die( print_r( sqlsrv_errors(), true));
endif;

of course the obvious place for the error to exist was in the segment where you tried to use php functions inside quotes. i explained to you twice that this would not work. you could have checked the manual to work this out. perhaps in the end you did.

i then tried to rewrite your query for you, showing you the normal methods of string substitution, which are useful for building queries. at the time I did not know what output you wanted from strpos. i had assumed that you had read the manual and understood that strpos would provide either boolean false or an integer result and that this was what you wished to test bidDate against. Without the schema we know no better.

your post of 17h34 crossed with me being offline rewriting your query for you. had I seen it i would have built an alternative into the query. probably using parse_str.

the point of these forums is not just to help you, but to show others how to help themselves using the information in the posts. here this post exemplifies how to check the errors in sql server, and how to do string substitution.

you are still missing, in your query and routines, protection from sql injection and programmatic enquoting of variables to be used in sql (if necessary for sql server). I was fully expecting the query still to fail so that this could be exemplified with you; and thus you (and others) would see then how to construct and use prepared statements.

I'm glad you got it working the way you want, in any event. self-help is often best. and if you choose not to protect your queries for whatever reason (the classic (stupid) reason being 'it's only going to be used on an intranet...') then I sincerely hope that you'll not be a victim to sql injection attacks.