Exporting table to a .csv file

[multichild]

I am working using php and mssql, and have a webpage that outputs contracts and how they are displayed is also controlled, so basically there are many ways in which to view the contracts.

The contracts are on display by building up a html table, so can I use that html table with all the data to export the data on show to a .csv file.

[Cups]

If you have your results as some kind of array prior to building your html table, then you might be able to use reuse that array and use fputcsv. Otherwise a google search of php array to csv throws up various solutions.

I know there is a mysql export as csv option - maybe there is such a thing in mssql too?

Is that the crux of your question, how to export a result set to csv as well as build a html table?

[wonshikee]

If the only data source is the HTML table, you will need to use a DOM viewer to pull it out manually.

As Cups stated, if you have access to the data source that created the HTML table, it's very easy to convert it to output in a CSV format

[multichild]

Yes ive access to all the data in the database, and the html table I have created on the page is dynamically generated from that data using php and mysql.

The data changes to what ever the user wants to see it like, such as view contracts from early to late, view contracts from high price to low and so, and so basically what the user has done to generate that data, I would like to have say an 'export' button, and then when clicked all that is seen in the html table is then saved as a .csv file.

I'm just in the thinking process at the moment, and need direction on how to do it, but will be back in the office on monday and can look and post code too if that would help me, but will spend the weekend researching it a bit now.

[wonshikee]

At the simplest level (though not the best), you can simply inject the code in between where you pull the data out of the database and where you output it as HTML.

Code to pull data out of database into an array.

if download_as_csv is true
set header for csv download
Loop through array and output as csv cells
exit 

loop through array and output array as html table.

A more reusable solution (but also more complex) is to use a function to do the formatting, because html table structure and a csv are similar (td = comma, tr = newline), so you could use a function and do it like this

Code to pull data out of database into an array.

if download_as_csv is true
set header for csv download
echo foobar($array, 'csv');
exit 

echo foobar($array, 'table');

Then the logic of the output goes in the function foobar, allowing yourself to reuse same code, which makes future changes/maintenance easier.

[multichild]

I have a few different ways of pulling the data out of the database as below, would somebody mind looking through what I got and strating me off with the first idea abovem which is the basic one.

<table style="table-layout:fixed">
<tr>
<td width="25" bgcolor="#A0B050">
<div class="tableTitle"></div></td>
<td width="25" bgcolor="#A0B050">
<div class="tableTitle"></div></td>
<td width="25" bgcolor="#A0B050">
<div class="tableTitle"></div></td>
<td width="70" bgcolor="#A0B050">
<div class="tableTitle">Contract Number</div></td>
<td width="70" bgcolor="#A0B050">
<div class="tableTitle">Contract Status</div></td>
<td width="55" bgcolor="#A0B050">
<div class="tableTitle">Annual Value of Contract</div></td>
<td width="80" bgcolor="#A0B050">
<div class="tableTitle">Original <br/>Date</div></td>
<td width="80" bgcolor="#A0B050">
<div class="tableTitle">Current <br/>Date</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Contract <br/>Length (Months)</div></td>
<td width="80" bgcolor="#A0B050">
<div class="tableTitle">Renewal <br/>Date</div></td>
<td width="110" bgcolor="#A0B050">
<div class="tableTitle">Site Name</div></td>
<td width="80" bgcolor="#A0B050">
<div class="tableTitle">Street</div></td>
<td width="80" bgcolor="#A0B050">
<div class="tableTitle">City</div></td>
<td width="100" bgcolor="#A0B050">
<div class="tableTitle">State or<br/>Province</div></td>
<td width="100" bgcolor="#A0B050">
<div class="tableTitle">Country</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Region</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Group <br/>Member</div></td>
<td width="100" bgcolor="#A0B050">
<div class="tableTitle">Group Name</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Rooms</div></td>
<td width="90" bgcolor="#A0B050">
<div class="tableTitle">Type of<br/>Establishment</div></td>
<td width="30" bgcolor="#A0B050">
<div class="tableTitle">CSF</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Food<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Pool<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Aqua<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Fire<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Crisis<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Eco<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Safety<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Room<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Quality<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Supply<br/>Check</div></td>
<td width="45" bgcolor="#A0B050">
<div class="tableTitle">Tour<br/>Check</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Number<br/>of Modules</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Currency<br/>of Invoice</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Number<br/>of Audits (Per Annum)</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Number<br/>of Visits (Per Annum)</div></td>
<td width="60" bgcolor="#A0B050">
<div class="tableTitle">Seasonal<br/>or Full Year</div></td>
<td width="65" bgcolor="#A0B050">
<div class="tableTitle">Month<br/>Open</div></td>
<td width="65" bgcolor="#A0B050">
<div class="tableTitle">Month<br/>Closed</div></td>
<td width="65" bgcolor="#A0B050">
<div class="tableTitle">Invoicing<br/>Profile</div></td>
</tr>

<?php 
// find out how many rows are in the table  
//  $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))   
//select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC";  

switch($search) 
{ 
    case "AllContractsOld-New"://complete             
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
          $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by ID ASC";
        break;
         
    case "AllContractsNew-Old"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'"; 
          $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by ID DESC";
        break;
         
    case "AllContractsOnHold"://complete but not tested with contracts
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold'))   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number";   
        break;
         
    case "AllContractsActive"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active'))   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number";   
        break;
         
    case "AllContractsTerminated"://complete but not tested with pageing
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')"; 
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated'))   
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number";   
        break; 
        
    case "ByCountryA-Z"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Country ASC";   
        break;
         
    case "ByCountryZ-A"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Country DESC";   
        break;
         
    case "ByRegionA-Z"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Region ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Region ASC";   
        break;
         
    case "ByRegionZ-A"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Region DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Region DESC";   
        break;
         
    case "ByNumberModulesO-X"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY No_of_Modules ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC";   
        break;
         
    case "ByNumberModulesX-O"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY No_of_Modules DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC";   
        break;
         
    case "ByContractValueLow-High"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC";  
        break;
                
    case "ByContractValueHigh-Low"://complete
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')   
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC";   
        break;
         
    case "ByFoodCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByPoolCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByAquaCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByFireCheckYes"://complete but not by pageing as not enough in database
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByCrisisCheckYes"://complete but not by pageing as not enough in database
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1'))   
select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC";   
        break;
         
    case "ByEcoCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1'))   
select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC";   
        break;
         
    case "BySafetyCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1'))   
select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC";   
        break;
         
    case "ByRoomCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByQualityCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC"; 
        break;
        
    case "BySupplyCheckYes"://complete but not by pageing as not enough in database 
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1'))   
select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC";   
        break;
         
    case "ByTourcheckYes"://complete but not by pageing as not enough in database  
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1'))   
select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC";   
        break;
        
    default:
        if ($searchKeyword == "") {
                $sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
        $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')  
select * from LIMIT WHERE {limit clause} order by ID DESC";
        break;
        } else {
    $sql = "SELECT COUNT(*) FROM Intranet WHERE (Contract_Number like '%{$trimmed}%' or Site_Name like '%{$trimmed}%' or Street like '%{$trimmed}%' or City like '%{$trimmed}%' or State like '%{$trimmed}%' or Country like '%{$trimmed}%' or Region like '%{$trimmed}%' or Group_Name like '%{$trimmed}%' or Type_of_Establishment like '%{$trimmed}%' or Currency_of_Invoice like '%{$trimmed}%')";
    $query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%{$trimmed}%' or Site_Name like '%{$trimmed}%' or Street like '%{$trimmed}%' or City like '%{$trimmed}%' or State like '%{$trimmed}%' or Country like '%{$trimmed}%' or Region like '%{$trimmed}%' or Group_Name like '%{$trimmed}%' or Type_of_Establishment like '%{$trimmed}%' or Currency_of_Invoice like '%{$trimmed}%'))  
select * from LIMIT WHERE {limit clause} order by ID DESC";
        break; 
        }
}

$result = sqlsrv_query($conn, $sql); 
$r = sqlsrv_fetch_array($result); 
$numrows = $r[0]; 

// number of rows to show per page
 if ($rows == ""){
 $rowsperpage = 1;
 } else {
$rowsperpage = $rows;// find out total pages
} 

 $totalpages = ceil($numrows / $rowsperpage); 
// get the current page or set a default 
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {    
// cast var as int    
$currentpage = (int) $_GET['currentpage']; 
} else {    
// default page num    
$currentpage = 1; 
} // end if 

// if current page is greater than total pages... 
if ($currentpage > $totalpages) {    
// set current page to last page    
$currentpage = $totalpages; 
} // end if 
// if current page is less than first page... 
if ($currentpage < 1) {    
// set current page to first page    
$currentpage = 1; 
} // end if 

// the offset of the list, based on current page   
$offset = ($currentpage - 1) * $rowsperpage + 1; 
$limit=($offset - 1) + $rowsperpage;  
$query = str_replace('{limit clause}', "RowNumber BETWEEN $offset AND $limit", $query);

//echo $query; 
$stmt = sqlsrv_query($conn, $query);   

/******  build the pagination links ******/// range of num links to show 
$range = 30; 
echo "<div style=position:relative;height:30px;line-height:30px;><span style=font-family:Arial;font-size:14px;color:#333333;margin-top:9px;margin-bottom:5px;height:20px;margin-left:2px;>"; 
// if not on page 1, don't show back links 
if ($currentpage > 1) {    
// show << link to go back to page 1  
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Search=$search&q=$searchKeyword&Rows=$rows' style='color:#333333'><<</a>";   
// get previous page num    
$prevpage = $currentpage - 1;    
// show < link to go back to 1 page    
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> "; 
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Search=$search&q=$searchKeyword&Rows=$rows' style='color:#333333'><</a> ";
} // end if 

// loop to show links to range of pages around current page 
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {    
// if it's a valid page number...    
if (($x > 0) && ($x <= $totalpages)) {       
// if we're on current page...       
if ($x == $currentpage) {          
// 'highlight' it but don't make a link          
echo " [<b>$x</b>] ";       
// if not current page...       
} else {          
// make it a link          
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Search=$search&q=$searchKeyword&Rows=$rows' style='color:#333333'>$x</a> ";       
} // end else    
} // end if  
} // end for 

// if not on last page, show forward and last page links         
if ($currentpage != $totalpages) {    
// get next page    
$nextpage = $currentpage + 1;     
// echo forward link for next page     
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> "; 
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Search=$search&q=$searchKeyword&Rows=$rows' style='color:#333333'>></a> ";   
// echo forward link for lastpage    
// was echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Search=$search&q=$searchKeyword&Rows=$rows' style='color:#333333'>>></a> ";
} // end if 
/****** end build pagination links ******/ 
echo "</span>";
echo "<span style=font-size:13px;color:#555555;font-family:verdana;position:relative;width:250px;line-height:20px;top:-4px;left:30px;>Showing '$rowsperpage' per page.</span>";

if(isset($_GET['q']))
{
  $searchKeyword = @$_GET['q'];
  if ($searchKeyword=="") {
  } else {
  $trimmed = $searchKeyword;
  echo "<span style=position:relative;margin=0px;margin-bottom:5px;margin-left:20px;top:-3px;>Search results for your keyword: <strong>'" .$trimmed. "'</strong></span></div>";
  }
}
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))           
{ 

echo "<tr>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&Admin=Edit'><img src='images/icon_edit.gif' alt='Edit Item' height='13'></a></td>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='read_contract.php?ID=".$data["ID"]."'><img src='images/icon_view.gif' alt='View Item &amp; Print'></td>";
echo "<td width='25' bgcolor='#ffffff' class='tableData'><a href='upload_contract.php?ID=".$data["ID"]."&AdminDelete'><img src='images/delete_Icon.jpg' alt='Delete Item'></a></td>";
echo "<td width='70' class='tableData'>".$data["Contract_Number"]."</td>";
echo "<td width='70' class='tableData'>".$data["Contract_Status"]."</td>";
echo "<td width='55' class='tableData'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80' class='tableData'>Contract Start</td>";
echo "<td width='80' class='tableData'>Contract Current</td>";
echo "<td width='60' class='tableData'>".$data["Contract_Length"]."</td>";
echo "<td width='80' class='tableData'>Renewal Date</td>";
echo "<td width='110' class='tableData'>".$data["Site_Name"]."</td>";
echo "<td width='80' class='tableData'>".$data["Street"]."</td>";
echo "<td width='80' class='tableData'>".$data["City"]."</td>";
echo "<td width='100' class='tableData'>".$data["State"]."</td>";
echo "<td width='100' class='tableData'>".$data["Country"]."</td>";
echo "<td width='60' class='tableData'>".$data["Region"]."</td>";
echo "<td width='60' class='tableData'>";
if ($data["Group_Member"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='100' class='tableData'>".$data["Group_Name"]."</td>";
echo "<td width='45' class='tableData'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90' class='tableData'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60' class='tableData'>";
if ($data["CSF"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Food_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Swimming_Pool_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Legionella_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Fire_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Hurricane"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Green"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Guest"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Room_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Quality_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Supply_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>";
if ($data["Tourcheck"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60' class='tableData'>".$data["No_of_Modules"]."</td>";
echo "<td width='60' class='tableData'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60' class='tableData'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60' class='tableData'>".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='45' class='tableData'>".$data["Seasonal_or_Full"]."</td>";
echo "<td width='65' class='tableData'>".$data["Month_Opens"]."</td>";
echo "<td width='65' class='tableData'>".$data["Month_Closes"]."</td>";
echo "<td width='65' class='tableData'>".$data["Invoicing_Profile"]."</td>";
echo "</tr>";
}                
?>
</table>

[multichild]

Can somebody start me off with this one, as I think I understand that you can use the fact that I am already creating a html tabel with all the data from the database, but I'm not sure where to start the code off so that when then 'Export' button is clicked in the nav bar, it creates the excel file according to the data that is on show on the page.

[cpradio]

@multichild,

What version of SQL Server are you using? If uncertain you can run SELECT @@version

[multichild]

I just looked in server studio and found SQL Server 10.500.2500

is that it cpradio.

Thanks again too

[multichild]

Hi cpradio, just ran SELECT and got a few errors stopping me, mostly firewall

but im guessing that we up to date with SQL server, but cant be exactly sure

[cpradio]

Are you using a hosting company or do you have the web server and the sql server in house? The only reason I ask, is you can tell SQL Server to create the CSV file for you, but it requires knowledge of the setup of both the sql server and the web server.

In short, you need to be able to have SQL Server write a file to the web server.
Here is the command you would need to run

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

[multichild]

Would the same process apply if the user clicks 'export' and has the option to save that file to their desktop, rather than automatically saving it to the server.

[cpradio]

Well you would save it to the server and then send that file down to the user.

The code to send the CSV file to the user can be found at http://www.techportal.co.za/php/192-...link-using-php

[multichild]

to answer your question too yes we are using a host service, i will see if i can find out about the server and report back.
Think this will roll over again to Monday, but will do what I can today

[multichild]

Hi cpradio, I followed that link and got it allset up and yes thats exactly what I was thinking.

They click the 'export' button in the nav, and they get to save a .csv file

[cpradio]

So you got it working?
Funny enough, there is a CSV snippet on a RSS Feed I follow that showed up over lunch today (personally, I prefer the SQL Server approach, let it do the majority of the work).
http://phpmaster.com/5-inspiring-and...-php-snippets/

[multichild]

I only got the link to work mind, I still have to fill that excel file up with the data that's on display on the page.

Unfortunately once again the second I get my teeth into something liek this the weekend comes around again.

Would you mind if I bump this up on Monday, and we pick it up from there.

Thanks again cpradio, you must have a fantastic job there in Ohio, you know a lot fair play.

Take care

[cpradio]

I do love my job here in Ohio. I actually changed jobs in December of last year and love the new one much more than the old job. A lot more relaxing. If you run into any issues getting your SQL Server questions answered by your host, feel free to ask them here.

If they ultimately tell you, you can't do that approach, then we'll have to work on a PHP only approach.

[multichild]

Hi cpradio,

OK the techie is in now so can ask the questions we need to find out about the server.

So would you mind reminding me what we need to find out, as the way it currently works is perfect in that you click the 'export' button and they get the option to save the .csv file where they prefer.

The job that I got now, is to try and get the contracts on display into that excel file.

Cheers

[cpradio]

Okay, to get the contacts to be in the downloaded csv file, you need to ask if you can run the following command on SQL Server:

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

Items to note:
* Your query will by dynamic, meaning it will change based on what the user is currently viewing.
* You need to know what to provide for the path (ideally it can write to a directory in your web folder via a network share)

[multichild]

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.

[multichild]

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.

[cpradio]

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).

[multichild]

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.

[cpradio]

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.