SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exporting query to .csv file

    This is a new project but I'm trying to use code that has worked well before, the difference being that the output needed is far less and its using mysql this time and sql last time.

    I have a detail page, and the relevant code on that page is below:

    Code:
    <?
    $a=mysql_query("select * from hazzard where UniqueIdentifier = ".$report_ID."");
    $_SESSION['currentQuery'] = $a;
    $data=mysql_fetch_assoc($a);
    ?>
    I have a button on this page as below:

    Code:
    <a href="download_2.php">Export This Report (Excel/.csv)</a>
    Then on the download page I have the following, I have left the commented code in there as I dont think I need it, but I wasnt 100% sure to delete it.

    Code:
    <?
    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");
    
    require_once('../config.php');
    $connectionInfo = array("Database"=>$db, "UID"=>$user, "PWD"=>$pass);
    $conn = mysql_connect($host, $connectionInfo);
    
    //$username=$_SESSION['user1'];
    //if ($_SESSION['flaglog']!=1)
      //{	
    	//header("location:index.php");
      //}
    //else
    //{
    //$query = "SELECT * FROM Users WHERE Username='$username'";
    //$res = sqlsrv_query($conn, $query); 
    //while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) { 
    //$name = $result["First_Name"];
    //$security = $result["Admin"];
    ////echo $security;
    	//}
    //$contractLike = substr($username, 0, 3);
    //echo $contractLike;
    //}
    
    $query = $_SESSION['currentQuery'];
    //var_dump($query);
    
    //$query = str_replace('WITH LIMIT AS( ', '', $query); 
    //$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
    //$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
    //$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
    //$query = str_replace('))', ')', $query);
    //$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
    //$query = str_replace("%')", "%'", $query);
    ////var_dump($query);
    
    function cleanData(&$str)
      {
        if($str == 't') $str = 'TRUE';
        if($str == 'f') $str = 'FALSE';
        if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
          $str = "'$str";
        }
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
        $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
      }
    
      // filename for download
      $filename = "website_data_" . date('Ymd') . ".csv";
    
      header("Content-Disposition: attachment; filename=\"$filename\"");
      header("Content-Type: text/csv; charset=UTF-16LE");
    
      $out = fopen("php://output", 'w');
    
      $flag = false;
        
      $result = mysql_query($conn, $query) or die('Query failed!');
      while(false !== ($row = mysql_fetch_array($result, mysql_fetch_assoc))) {
    
        if(!$flag) {
          // display field/column names as first row
          fputcsv($out, array_keys($row), ',', '"');
          $flag = true;
        }
        //array_walk($row, 'cleanData');
    	//start
    	foreach ($row as $key => $value)
        	{ 
        if ($value instanceof DateTime) 
        	{ 
        $row[$key] = date_format($value, 'd/m/y'); 
        	}
        }
    	//end
    	$row = str_replace("&#44;", ",", $row);
    	$row = str_replace("&#46;", ".", $row);
        fputcsv($out, array_values($row), ',', '"');
      }
    
      fclose($out);
      exit;
    
    ?>
    I get the option to save as the .csv file that bit seems to be working, but when I open the csv file up I get the following errors.

    <br />
    <b>Warning</b>: mysql_connect() expects parameter 2 to be string
    array given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>15</b><br />

    <br />

    <b>Notice</b>: Undefined index: currentQuery in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>35</b><br />

    <br />

    null given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>68</b><br />

    Query failed!


    Here is the link to the project im working on:

    http://www.whhazardreport.co.uk/mana..._Ref.php?ID=75

    Is it that the session hasnt got the info it requires?

  2. #2
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed the code below to that and I got some output, but there still some errors and the columns arent lined up correctly as below:

    Code:
    $a=mysql_query("select * from hazzard where UniqueIdentifier = ".$report_ID."");
    $data=mysql_fetch_assoc($a);
    $_SESSION['currentQuery'] = $data;
    This is copy and pasted directly from the excel file
    Code:
    <br />	
    <b>Warning</b>:  mysql_connect() expects parameter 2 to be string	 array given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>15</b><br />
    array(25) {	
      ["UniqueIdentifier"]=>	
      string(2) "75"	
      ["Name"]=>	
      string(13) "Bruce Preston"	
      ["Employerofperson"]=>	
      string(3) "LOR"	
      ["Contactphone"]=>	
      string(11) "07919014767"	
      ["Location"]=>	
      string(44) "Queen's Building/Welcome Centre Level 1 Room"	
      ["DateOccured"]=>	
      string(10) "2013-07-01"	
      ["ProductionZone"]=>	
      string(3) "N/A"
      ["NearMissDetails"]=>
      string(53) "Canteen staff not wearing hair nets when serving food"
      ["Anyimmediateactions"]=>
      string(44) "Reported to catering manager-nets to be worn"
      ["HazardorNearMiss"]=>
      string(6) "Hazard"
      ["Primarycause"]=>
      string(30) "Exposure to Harmful substances"
      ["Rottcause"]=>
      string(19) "Inadequate training"
      ["Behaviours"]=>
      string(21) "Communication failure"
      ["PotentialOutcome"]=>
      string(1) "1"
      ["Likelihood"]=>
      string(1) "1"
      ["Comments"]=>
      string(0) ""
      ["PersonsMostLikelyInjured"]=>
      string(0) ""
      ["CloseDate"]=>
      string(10) "0000-00-00"
      ["HighPotentialIncident"]=>
      string(0) ""
      ["HPIInvestigatedBy"]=>
      string(0) ""
      ["BUHSELeader"]=>
      string(0) ""
      ["BUSectorLeader"]=>
      string(0) ""
      ["HPIInvestigationConclusion"]=>
      string(0) ""	
      ["HPIBriefingNoteRef"]=>	
      string(0) ""	
      ["HPIInvestigationCloseOutDate"]=>	
      string(10) "0000-00-00"	
    }	
    <br />	
    <b>Warning</b>:  mysql_query() expects parameter 2 to be resource	 array given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>68</b><br />
    Query failed!

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry the var dump($query) gave the output above, so I'm still getting the original errors.

    Code:
    <br />	
    <b>Warning</b>:  mysql_connect() expects parameter 2 to be string	 array given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>15</b><br />
    <br />	
    <b>Warning</b>:  mysql_query() expects parameter 2 to be resource	 array given in <b>/home/linweb22/w/whhazardreport.co.uk/user/htdocs/manager_Admin/download_2.php</b> on line <b>68</b><br />
    Query failed!

  4. #4
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I THINK i have resolved one issue as I THINK it was something to do with a double connection to the database, as there was already this in the config file:

    $link=mysql_pconnect($host,$user,$pass);

    So I changed $conn for $link and commented out:

    //$connectionInfo = array("Database"=>$db, "UID"=>$user, "PWD"=>$pass);
    //$conn = mysql_connect($host, $connectionInfo);

    And the first error went.

    There is a second similar error now left:

    <b>Warning</b>: mysql_query() expects parameter 1 to be string

    Which I think is in relation to where I changed $conn for $link.

    Code:
      $result = mysql_query($link, $query) or die('Query failed!');
      while(false !== ($row = mysql_fetch_array($result, mysql_fetch_assoc))) {
    But I'm not sure if I'm making progress here

  5. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I think I know what the problem is, but I dont know how to fix it.

    Basically I'm calling two instances of mysql_query and I need to have only 1.

    So on the detail page where the session is created I have

    Code:
    $a=mysql_query("select * from hazzard where UniqueIdentifier = ".$report_ID."");
    $_SESSION['currentQuery'] = $a;
    $data=mysql_fetch_assoc($a);
    And then its doing it again on the download_2.php page.

  6. #6
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I got it sorted but there seems to be a problem in that the data is doubling up but instead of the column name its putting a number to it, like a count.

    It can be best seen if you try it yourself.

    http://www.whhazardreport.co.uk/mana..._Ref.php?ID=75

    Click the export button

    Here is the full code for the download page

    Code:
    <?
    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");
    
    require_once('../config.php');
    //$connectionInfo = array("Database"=>$db, "UID"=>$user, "PWD"=>$pass);
    //$conn = mysql_connect($host, $connectionInfo);
    
    //$username=$_SESSION['user1'];
    //if ($_SESSION['flaglog']!=1)
      //{	
    	//header("location:index.php");
      //}
    //else
    //{
    //$query = "SELECT * FROM Users WHERE Username='$username'";
    //$res = sqlsrv_query($conn, $query); 
    //while ($result = sqlsrv_fetch_array($res, SQLSRV_FETCH_ASSOC)) { 
    //$name = $result["First_Name"];
    //$security = $result["Admin"];
    ////echo $security;
    	//}
    //$contractLike = substr($username, 0, 3);
    //echo $contractLike;
    //}
    
    $query = $_SESSION['currentQuery'];
    //var_dump($query);
    
    //$query = str_replace('WITH LIMIT AS( ', '', $query); 
    //$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
    //$query = str_replace(", ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber'", '', $query);
    //$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
    //$query = str_replace('))', ')', $query);
    //$query = str_replace('SELECT * FROM Intranet)', 'SELECT * FROM Intranet', $query);
    //$query = str_replace("%')", "%'", $query);
    ////var_dump($query);
    
    function cleanData(&$str)
      {
        if($str == 't') $str = 'TRUE';
        if($str == 'f') $str = 'FALSE';
        if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
          $str = "'$str";
        }
        if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
        $str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
      }
    
      // filename for download
      $filename = "hazzard_data_" . date('Ymd') . ".csv";
    
      header("Content-Disposition: attachment; filename=\"$filename\"");
      header("Content-Type: text/csv; charset=UTF-16LE");
    
      $out = fopen("php://output", 'w');
    
      $flag = false;
        
      $result = mysql_query($query);
      while($row = mysql_fetch_array($result)) {
    
        if(!$flag) {
          // display field/column names as first row
          fputcsv($out, array_keys($row), ',', '"');
          $flag = true;
        }
        //array_walk($row, 'cleanData');
    	//start
    	foreach ($row as $key => $value)
        	{ 
        if ($value instanceof DateTime) 
        	{ 
        $row[$key] = date_format($value, 'd/m/y'); 
        	}
        }
    	//end
    	$row = str_replace("&#44;", ",", $row);
    	$row = str_replace("&#46;", ".", $row);
        fputcsv($out, array_values($row), ',', '"');
      }
    
      fclose($out);
      exit;
    
    ?>

  7. #7
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK sorry i got it, changed array to assoc and it seems to be fine.

    Code:
    while($row = mysql_fetch_assoc($result)) {
    I doubt im out of the woods yet though

  8. #8
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I have a strange problem where the zero on a mobile number phone is missing when outputting to the csv file.

    I have looked into it and it seems I have to wrap that value in double quotes, but I'm not sure where I can do this, so attempted below, and that didnt work

    Code:
    $query = $_SESSION['currentQuery'];
    //var_dump($query);
    
    $query = str_replace("$Contactphone", "'$Contactphone'", $query);
    Can anyone advise me on this bit

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    906
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I thought that maybe this would work, but it brings up a boolean error:

    Code:
    $b="select UniqueIdentifier, Name, Employerofperson, CONCAT('\'', Contactphone , '\''), Location, DateOccured, ProductionZone, NearMissDetails, Anyimmediateactions, HazardorNearMiss, Primarycause, Rottcause, Behaviours, PotentialOutcome, Likelihood, Comments, PersonsMostLikelyInjured, CloseDate, HighPotentialIncident, HPIInvestigatedBy, BUHSELeader, BUSectorLeader, HPIInvestigationConclusion, HPIBriefingNoteRef, HPPnvestigationCloseOutDate from hazzard where UniqueIdentifier = ".$report_ID."";
    I'm not sure whether to do it before i pass the session over, or change it on the download page.

    This is in place of select * on the detail page


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •