Hi cpradio, I would normally email the hosts, so if I just copy that into an email and ask them if this will be ok, thats what we need is it.
Update:
OK I have emailed them and explained, so hopefully they wont be too long getting back to me.
Hi cpradio,
I just had a chat with our techie guy here and he doesn’t see that there will be any problems, I’m guessing your in the know more than he is, but as far as we can see who ever has use of the config.php file has access to the database, and the permissions on the server have been fine when using it as we have done.
We awaiting a response back from the hosts, but I think we should be OK.
Well if your not (or the hosts refuses to allow it to work this way), your only other option is to create the CSV output in PHP after getting the result set back from SQL Server (so not impossible, just not ideal).
Hi cpradio,
I finally heared back from the hosts and they didnt think there would be any problems, as all the permissions are set to allow us to do that line of code.
So i think we can go down that path you suggested.
Happy independance day too.
Great! and Thank You! If you run into problems the first question I would as the host is what file path to use so your web server will have access to the outputted file created by SQL Server. Once you know that, the query is plug and play with the queries that already exist in your php file.
Enjoy.
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>
ame 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>\
ame of file.csv" -T -c -t","'
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/showthread.php?860276-Paging-using-PHP-amp-MSSQL/page3
So you will need to pass the appropriate variables to the download page and use them to build your $sqlQueryToExport
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 
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.
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.
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.
Well you have two options there
- Try to pull out the data you need from that query.
- 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);
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);
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"
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.
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
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.
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
LOL, ahhhhh.
Nope its not budging…
$query = str_replace(') ) select * from LIMIT WHERE {limit clause}', '', $query);
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!