Exporting table to a .csv file

[multichild]

Hi cpradio,

Got so much going on at the mo, thought i best come back to this one.

I understand I need to use:

ec xp_cmdshell 'bcp "your query goes here" queryout "<path to web server>\name of file.csv" -T -c -t","'

and will have to work out how, but do I included it in the download.php file that the user goes to when they click the 'Export' button.

As below, or do I use it on its own in download.php

header('Content-Type: application/csv'); //Outputting the file as a csv file 
header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '  
header('Pragma: no-cache'); 
echo readfile('contractsCSF.csv'); //Reading the contents of the file
ec xp_cmdshell 'bcp "your query goes here" queryout "<path to web server>\name of file.csv" -T -c -t","' 

[cpradio]

Yes, you would.

However, you have a few lines in the wrong order.

header('Content-Type: application/csv'); //Outputting the file as a csv file  
header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '   
header('Pragma: no-cache');  
// connect to your SQL Server
// Build the Query you want to Export to CSV (may need to send the 
// Run a Query using 'exec xp_cmdshell \'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\contractsCSF.csv" -T -c -t","\'' as your SQL Query
// Close SQL Server Connection
echo readfile('contractsCSF.csv'); //Reading the contents of the file 

Same way we build the query in http://www.sitepoint.com/forums/show...mp-MSSQL/page3
So you will need to pass the appropriate variables to the download page and use them to build your $sqlQueryToExport

[multichild]

Right, this is going to be another step up by the looks, but thanks again cpradio, probably keep me busy for a year or two!

Cheers

[multichild]

Hi,

OK Im back on this now, as didnt properly finish off the other stuff as you know.

I am starting off by trying to work out how to pass the query over to download.php.

This is where I'm at at this point, but am workign on things:

<?
header('Content-Type: application/csv'); //Outputting the file as a csv file  
header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '   
header('Pragma: no-cache');  

// connect to your SQL Server
session_start();

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);


// Build the Query you want to Export to CSV
 
// Run a Query using 'exec xp_cmdshell \'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\contractsCSF.csv" -T -c -t","\'' as your SQL Query
// Close SQL Server Connection
echo readfile('contractsCSF.csv'); //Reading the contents of the file  

?>

It seems I'm having trouble at the start when I click the Export button. It all kicks in so to speak and an IE pop up error box appears and isnt allowing me to continue.

Internet Explorer cannt download download.php.

[cpradio]

I would write the query to a session variable such as $_SESSION['currentQuery'] = $query; Then in your download page read the query using $_SESSION['currentQuery']

And so you don't make a common mistake, make sure session_start(); is at the top of all scripts involved with assigning and reading your $_SESSION variables.

[multichild]

Ah ye thank you. I echoed it out and realised that there are bits in there that are not needed to create the .csv file:

WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC

Such as WITH LIMIT AS( and RowNumber and all, So should I create another query just for export minus the add ons, so in effect trimming it down.

No just thinking I cant do that, will have to think this through.

[cpradio]

Well you have two options there
1) Try to pull out the data you need from that query.
2) Create a 3rd variable for each query you have and store that in the $_SESSION variable

The biggest challenge you really have is removing ROW_NUMBER and the WHERE {limit clause} with your existing query.

So doing the following "might" work

$query = $_SESSION['currentQuery'];
$query = str_replace(array(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", 'WHERE {limit clause}'), '', $query);
var_dump($query); 

[multichild]

Ye that did it, well nearly:

Before:

WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%24%' or Contract_Status like '%24%' or Contract_Start like '%24%' or Contract_End like '%24%' or Contract_Length like '%24%' or Renewal_Date like '%24%' or Site_Name like '%24%' or Street like '%24%' or City like '%24%' or Postcode like '%24%' or Country like '%24%' or Region like '%24%' or Group_Member like '%24%' or Group_Name like '%24%' or No_of_Rooms like '%24%' or Type_of_Establishment like '%24%' or CSF like '%24%' or Food_Safety like '%24%' or Swimming_Pool_Safety like '%24%' or Legionella_Safety like '%24%' or Fire_Safety like '%24%' or Hurricane like '%24%' or Green like '%24%' or Guest like '%24%' or Room_Safety like '%24%' or Quality_Safety like '%24%' or Supply_Safety like '%24%' or Tourcheck like '%24%' or No_of_Modules like '%24%' or Currency_of_Invoice like '%24%' or Annual_Contract_Value like '%24%' or No_of_audits_per_annum like '%24%' or No_of_visits_per_annum like '%24%' or Seasonal_or_Full like '%24%' or Labs like '%24%' or Month_Opens like '%24%' or Month_Closes like '%24%' or Invoicing_Profile like '%24%') ) select * from LIMIT WHERE {limit clause} order by ID DESC 

After:

SELECT * FROM Intranet WHERE (Contract_Number like '%24%' or Contract_Status like '%24%' or Contract_Start like '%24%' or Contract_End like '%24%' or Contract_Length like '%24%' or Renewal_Date like '%24%' or Site_Name like '%24%' or Street like '%24%' or City like '%24%' or Postcode like '%24%' or Country like '%24%' or Region like '%24%' or Group_Member like '%24%' or Group_Name like '%24%' or No_of_Rooms like '%24%' or Type_of_Establishment like '%24%' or CSF like '%24%' or Food_Safety like '%24%' or Swimming_Pool_Safety like '%24%' or Legionella_Safety like '%24%' or Fire_Safety like '%24%' or Hurricane like '%24%' or Green like '%24%' or Guest like '%24%' or Room_Safety like '%24%' or Quality_Safety like '%24%' or Supply_Safety like '%24%' or Tourcheck like '%24%' or No_of_Modules like '%24%' or Currency_of_Invoice like '%24%' or Annual_Contract_Value like '%24%' or No_of_audits_per_annum like '%24%' or No_of_visits_per_annum like '%24%' or Seasonal_or_Full like '%24%' or Labs like '%24%' or Month_Opens like '%24%' or Month_Closes like '%24%' or Invoicing_Profile like '%24%') ) order by ID DESC 

Using:

$query = str_replace(array("select * from LIMIT order by ID DESC", ", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", "WHERE {limit clause}", "WITH LIMIT AS(","select * from LIMIT"), '', $query); 

If you notice at the end of the query you have a double )), the last ')' I've tried to remove by adding it as below and it wont allow it, and makes the rest of it appear.

$query = str_replace(array("select * from LIMIT order by ID DESC", ", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", "WHERE {limit clause}", "WITH LIMIT AS(",") select * from LIMIT"), '', $query); 

[cpradio]

Hmm... I didn't end up with a double ) ) problem... I ended up with

string(1304) "WITH LIMIT AS( SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT  order by ID DESC"

Using

<?php
    session_start();
    //$query = $_SESSION['currentQuery'];
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC";
    $query = str_replace(array(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", 'WHERE {limit clause}'), '', $query);
    var_dump($query);
?>

Yes, you could argume it is using a CTE unnecessarily, but I think it would work. Otherwise, you could use:

<?php
    session_start();
    //$query = $_SESSION['currentQuery'];
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC";
    $query = str_replace(array('WITH LIMIT AS( ', ", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", ') ) select * from LIMIT WHERE {limit clause}'), '', $query);
    var_dump($query);
?>

Which gives you:

string(1265) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' order by ID DESC"

[multichild]

That is typical, that I'm getting different from you.

This is the query:

string(1310) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' )) select * from LIMIT WHERE {limit clause} order by ID DESC"

Using this:

$query = str_replace(array('WITH LIMIT AS( ', ", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", ')) select * from LIMIT WHERE {limit clause}'), '', $query);
var_dump($query); 

I get this:

string(1310) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' )) select * from LIMIT WHERE {limit clause} order by ID DESC"

It wont let ')) select * from LIMIT WHERE {limit clause}' be removed.

[cpradio]

What version of PHP are you running? Maybe it has something to do with that.

Does doing this provide the same end result:

$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace(')) select * from LIMIT WHERE {limit clause}', '', $query); 
var_dump($query); 

Edit:

fixed typo in code

[multichild]

Yes by the look of it it does.

I took the )) out and it allowed the rest of the code behind to go, its the double )) thats causing the problem.

[cpradio]

Ah, I found the issue, it seems the formatting is removing the space between ) and ) in my last str_replace, make sure there is a space between the parenthesizes

[multichild]

LOL, ahhhhh.

Nope its not budging...

$query = str_replace(') ) select * from LIMIT WHERE {limit clause}', '', $query); 

[multichild]

I changed it to this and it seems to have worked.

$query = str_replace('WITH LIMIT AS( ', '', $query); 
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query); 

string(1269) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' order by ID DESC"

There no theory behind it, just tried it!

[cpradio]

Yeah, that would work too Though, keep in mind if you have ORs and AND conditions properly separated with (), your last replacement will cause issues.

[multichild]

The next stage on from here is it to create the table from the data as below:

<?
session_start();
//header('Content-Type: application/csv'); //Outputting the file as a csv file  
//header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '   
//header('Pragma: no-cache');  

// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query); 
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);  
var_dump($query);

$stmt = sqlsrv_query($conn, $query);
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))         
{
echo "<table>";
echo "<tr>";
echo "<td width='40'>".$data["ID"]."</td>";
echo "<td width='70'>".$data["Contract_Number"]."</td>";
echo "<td width='70'>".$data["Contract_Status"]."</td>";
echo "<td width='55'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80'>".date_format($data['Contract_Start'], 'd/m/y')."</td>";
echo "<td width='80'>".date_format($data['Contract_End'], 'd/m/y')."</td>";
echo "<td width='60'>".$data["Contract_Length"]."</td>";
echo "<td width='80'>".date_format($data['Renewal_Date'], 'd/m/y')."</td>";
echo "<td width='110'>".$data["Site_Name"]."</td>";
echo "<td width='80'>".$data["Street"]."</td>";
echo "<td width='80'>".$data["City"]."</td>";
echo "<td width='100'>".$data["State"]."</td>";
echo "<td width='100'>".$data["Country"]."</td>";
echo "<td width='60'>".$data["Region"]."</td>";
echo "<td width='60'>";
if ($data["Group_Member"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='100'>".$data["Group_Name"]."</td>";
echo "<td width='45'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60'>";
if ($data["CSF"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Food_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Swimming_Pool_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Legionella_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Fire_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Hurricane"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Green"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Guest"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Room_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Quality_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Supply_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Tourcheck"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>".$data["No_of_Modules"]."</td>";
echo "<td width='60'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60'>";
if ($data["No_of_visits_per_annum"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Labs"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
//echo "<td width='60' >".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='65'>";
if ($data["Seasonal_or_Full"]==1){
echo "Seasonal";
} else {
echo "Full Year";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Opens"]."</td>";
echo "<td width='60'>";
if ($data["Month_Opens"]=="A. January"){
  echo "January";
}
if ($data["Month_Opens"]=="B. February"){
  echo "February";
}
if ($data["Month_Opens"]=="C. March"){
  echo "March";
}
if ($data["Month_Opens"]=="D. April"){
  echo "April";
}
if ($data["Month_Opens"]=="E. May"){
  echo "May";
}
if ($data["Month_Opens"]=="F. June"){
  echo "June";
}
if ($data["Month_Opens"]=="G. July"){
  echo "July";
}
if ($data["Month_Opens"]=="H. August"){
  echo "August";
}
if ($data["Month_Opens"]=="I. September"){
  echo "September";
}
if ($data["Month_Opens"]=="J. October"){
  echo "October";
}
if ($data["Month_Opens"]=="K. November"){
  echo "November";
}
if ($data["Month_Opens"]=="L. December"){
  echo "December";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Closes"]."</td>";
echo "<td width='60'>";
if ($data["Month_Closes"]=="A. January"){
  echo "January";
}
if ($data["Month_Closes"]=="B. February"){
  echo "February";
}
if ($data["Month_Closes"]=="C. March"){
  echo "March";
}
if ($data["Month_Closes"]=="D. April"){
  echo "April";
}
if ($data["Month_Closes"]=="E. May"){
  echo "May";
}
if ($data["Month_Closes"]=="F. June"){
  echo "June";
}
if ($data["Month_Closes"]=="G. July"){
  echo "July";
}
if ($data["Month_Closes"]=="H. August"){
  echo "August";
}
if ($data["Month_Closes"]=="I. September"){
  echo "September";
}
if ($data["Month_Closes"]=="J. October"){
  echo "October";
}
if ($data["Month_Closes"]=="K. November"){
  echo "November";
}
if ($data["Month_Closes"]=="L. December"){
  echo "December";
}
echo "</td>";
echo "<td width='65'>".$data["Invoicing_Profile"]."</td>";
echo "<td width='80'>".date_format($data['date_Created'], 'd/m/Y')."</td>";
echo "<td width='80'>".date_format($data['date_Modified'], 'd/m/Y')."</td>";
echo "</tr>";
echo "</table>";
}

// Build the Query you want to Export to CSV 
// Run a Query using 'exec xp_cmdshell \'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\contractsCSF.csv" -T -c -t","\'' as your SQL Query
// Close SQL Server Connection

//echo readfile('contractsCSF.csv'); //Reading the contents of the file  

?>

If not then will scrap it, but if so then I cant get it to appear at the moment, but working on it.

[multichild]

OK it does work with another query, so thats good.

"SELECT * FROM Intranet order by ID DESC"

Thats fine, but not when using search, so at least I know it works....

Is it the next thing to do though, or am I going down the wrong path here

[multichild]

Morning cpradio,

Do I need to output the contract details from the query to an actual table as below:

$stmt = sqlsrv_query($conn, $query);
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))         
{
echo "<table>";
echo "<tr>";
echo "<td width='40'>".$data["ID"]."</td>";
echo "<td width='70'>".$data["Contract_Number"]."</td>";
echo "<td width='70'>".$data["Contract_Status"]."</td>";
echo "<td width='55'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80'>".date_format($data['Contract_Start'], 'd/m/y')."</td>";
echo "<td width='80'>".date_format($data['Contract_End'], 'd/m/y')."</td>";
echo "<td width='60'>".$data["Contract_Length"]."</td>";
echo "<td width='80'>".date_format($data['Renewal_Date'], 'd/m/y')."</td>";
echo "<td width='110'>".$data["Site_Name"]."</td>";
echo "<td width='80'>".$data["Street"]."</td>";
echo "<td width='80'>".$data["City"]."</td>";
echo "<td width='100'>".$data["State"]."</td>";
echo "<td width='100'>".$data["Country"]."</td>";
echo "<td width='60'>".$data["Region"]."</td>";
echo "<td width='60'>";
if ($data["Group_Member"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='100'>".$data["Group_Name"]."</td>";
echo "<td width='45'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60'>";
if ($data["CSF"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Food_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Swimming_Pool_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Legionella_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Fire_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Hurricane"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Green"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Guest"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Room_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Quality_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Supply_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Tourcheck"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>".$data["No_of_Modules"]."</td>";
echo "<td width='60'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60'>";
if ($data["No_of_visits_per_annum"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Labs"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
//echo "<td width='60' >".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='65'>";
if ($data["Seasonal_or_Full"]==1){
echo "Seasonal";
} else {
echo "Full Year";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Opens"]."</td>";
echo "<td width='60'>";
if ($data["Month_Opens"]=="A. January"){
  echo "January";
}
if ($data["Month_Opens"]=="B. February"){
  echo "February";
}
if ($data["Month_Opens"]=="C. March"){
  echo "March";
}
if ($data["Month_Opens"]=="D. April"){
  echo "April";
}
if ($data["Month_Opens"]=="E. May"){
  echo "May";
}
if ($data["Month_Opens"]=="F. June"){
  echo "June";
}
if ($data["Month_Opens"]=="G. July"){
  echo "July";
}
if ($data["Month_Opens"]=="H. August"){
  echo "August";
}
if ($data["Month_Opens"]=="I. September"){
  echo "September";
}
if ($data["Month_Opens"]=="J. October"){
  echo "October";
}
if ($data["Month_Opens"]=="K. November"){
  echo "November";
}
if ($data["Month_Opens"]=="L. December"){
  echo "December";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Closes"]."</td>";
echo "<td width='60'>";
if ($data["Month_Closes"]=="A. January"){
  echo "January";
}
if ($data["Month_Closes"]=="B. February"){
  echo "February";
}
if ($data["Month_Closes"]=="C. March"){
  echo "March";
}
if ($data["Month_Closes"]=="D. April"){
  echo "April";
}
if ($data["Month_Closes"]=="E. May"){
  echo "May";
}
if ($data["Month_Closes"]=="F. June"){
  echo "June";
}
if ($data["Month_Closes"]=="G. July"){
  echo "July";
}
if ($data["Month_Closes"]=="H. August"){
  echo "August";
}
if ($data["Month_Closes"]=="I. September"){
  echo "September";
}
if ($data["Month_Closes"]=="J. October"){
  echo "October";
}
if ($data["Month_Closes"]=="K. November"){
  echo "November";
}
if ($data["Month_Closes"]=="L. December"){
  echo "December";
}
echo "</td>";
echo "<td width='65'>".$data["Invoicing_Profile"]."</td>";
echo "<td width='80'>".date_format($data['date_Created'], 'd/m/Y')."</td>";
echo "<td width='80'>".date_format($data['date_Modified'], 'd/m/Y')."</td>";
echo "</tr>";
echo "</table>";
} 

Or do I bypass that, and I'm looking at this line:

exec xp_cmdshell \'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\contractsCSF.csv" -T -c -t","\' 

As I see it I need to change $sqlQueryToExport to $query and then find the path to the server.

I have had to comment out some lines of code that you gave me to, otherwise download.php which is this page doesnt load, as I get a error pop up and have to close the connection down.

//header('Content-Type: application/csv'); //Outputting the file as a csv file  
//header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '   
//header('Pragma: no-cache'); 

I understand your probably busy cpradio, so I am reading through and trying ot understand how to exprt this lot to a .csv file now.

[multichild]

Ye I can see it now, the bcp $query bit to putput the query is what outputs it.

So have taken away the table and have left myself with:

<?
error_reporting(E_ALL);
ini_set('display_errors','OFF');

session_start();
header('Content-Type: application/csv'); //Outputting the file as a csv file  
header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '   
header('Pragma: no-cache');  

// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query); 
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);  
//var_dump($query);

exec xp_cmdshell \'bcp "' . $query . '" queryout "\contractsCSF.csv" -T -c -t","\''
echo readfile('contractsCSF.csv');
echo readfile('contractsCSF.csv'); //Reading the contents of the file

But one problem I have is when i click the export button from the cms it blocks me as it says it cant find the file, but it is there.

Then when I comment them lines out and go to the page, and refresh it doesnt do anything.

Will keep looking, I'm getting there I think.

[multichild]

Right, not getting there error now when I click export, but just a Internal 500 error page.

so possibly some script error somewhere.

Ah, found the error:

'exec xp_cmdshell \'bcp "' . $query . '" queryout "contractsCSF.csv" -T -c -t","\''; 

Now when I click export, it looks as though its beginnning to download download.php by exporting file, but its stopped in its tracks by a IE opo up telling me that IE cannot download download.php, its either unavailable or cannot be found.

[multichild]

Seems like a IE problem that above as tried it in firefox and the option to save as example.csv worked, so got excited and then an error appeared in excel with the following;

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 25

So do I need to create an empty contractsCSF.csv file at the root level.

[multichild]

Ah,

Got around the IE problem, by using the lines below:

header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");
header("Content-Disposition: attachment; filename=contractsCSF.csv" );

But not sure if its right...

It allows me to save a csv file to the desktop say, which I do, but the file doesnt contain any of the query data only the line below:

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 30

This is what I got so far:

error_reporting(E_ALL);
ini_set('display_errors','On');

session_start();
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");
header("Content-Disposition: attachment; filename=contractsCSF.csv" ); 


// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query); 
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);  
//var_dump($query);

'exec xp_cmdshell \'bcp "' . $query . '" queryout "contractsCSF.csv" -T -c -t","\'';
echo readfile('contractsCSF.csv'); 

But then open it up to get the error:

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 34


So have gone to the hosts now to try and help me with the path to the server.

[multichild]

Have got in touch with the hosts and the path they gave is below:

'exec xp_cmdshell \'bcp "' . $query . '" queryout "CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv" -T -c -t","\'';
echo readfile('CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv');

So then tried it again as its all sorted and still had the same error, so then they got back to me about that and said:

Your users won't be able to access that folder once they've downloaded it because it's only available locally to the webserver. You'll need to get the webserver script to extract the data, put it into the file and then supply the file with the data already inside it.

But isnt that what we are doing. Building the query which is being transfered to that csv file, then its going to the server, and bouncing back for the user to see and save.

Thats what I'm thinking anyway.

[cpradio]

Have got in touch with the hosts and the path they gave is below:

'exec xp_cmdshell \'bcp "' . $query . '" queryout "CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv" -T -c -t","\'';
echo readfile('CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv');

So then tried it again as its all sorted and still had the same error, so then they got back to me about that and said:



But isnt that what we are doing. Building the query which is being transfered to that csv file, then its going to the server, and bouncing back for the user to see and save.

Thats what I'm thinking anyway.

This is where it gets interesting because SQL Server is going to want to store the file locally, and you need to have it store it on a different server.

I am assuming this is your web directory (that contains your scripts)

\\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\

With that thought, the first thing I would try is

'exec xp_cmdshell \'bcp "' . $query . '" queryout "\\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv" -T -c -t","\'';
echo readfile('\\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv');

If that still fails, check your permissions on your export folder and make sure you give the user the CSV export is running as has permission to write to that folder.