SitePoint Sponsor

User Tag List

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

    Saving continually to one excel file, rather than creating a fresh one every time.

    Hi all,

    This is an extension from a project I came for help with and one of the problems was on click of an export button the correct data was exported to an excel file, which we got working using the code below.

    Now i have a slightly different take on it for a different project.

    This time they want to data to be added to the same excel file that sits on the server, so building it up rather than creating a fresh one each time, and still having the option once the data has been saved of saving the file locally.

    PHP Code:

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

    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"=>$databaseName"UID"=>$username"PWD"=>$password);
    $conn sqlsrv_connect($hostName $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($resSQLSRV_FETCH_ASSOC)) { 
    $name $result["First_Name"];
    $security $result["Admin"];
    //echo $security;
        
    }
    $contractLike substr($username03);
    //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 sqlsrv_query($conn$query) or die('Query failed!');
      while(
    false !== ($row sqlsrv_fetch_array($resultSQLSRV_FETCH_ASSOC))) {

        if(!
    $flag) {
          
    // display field/column names as first row
          
    fputcsv($outarray_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(","","$row);
        
    $row str_replace(".""."$row);
        
    fputcsv($outarray_values($row), ',''"');
      }

      
    fclose($out);
      exit; 

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    $out = fopen("php://output", 'w');

    =>
    $out = fopen("some/local/file", 'a+');

    and then
    rewind($out);
    echo fread($out, filesize('some/local/file'));
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    never, seriosuly thats all it is?

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Had to tweak my post a bit, but yeah. Really. You've already got the formatting, you just need to tell the script to Append (a) the local file, rather than writing to the output buffer, and then be able to read (+) it back out using fread.

    EDIT: ACTUALLY. No. I'm wrong there. You need to tweak your query to only pull NEW lines, or you'll be duplicating your data. So you'll need to store some information about the last/newest/whatever piece of information the script pulled.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so the a+ is append is it?

    blimey that suprised me.

    and the only pull NEW lines out, could you help me a little bit more with that please.

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    a+ means "Read/Write, Create file If It Does Not Exist, Put File Pointer at end of file"
    (a means the same, but without Read)

    rewind puts the file pointer back at the beginning; this is necessary so that fread can read the document in it's entirity.

    As for the new lines; as i said, you'll need to store a piece of information somewhere that identifies your data as having been read; I cant quite follow your input/output, so i'll have to be a bit vague;
    If the data has an ID column, for example;

    Code:
    ID             DATA
    1              Moo
    2              Cow
    ID (probably an auto_increment) will always increase. If I pull the data now, my last record would be ID 2.

    If i then add some more data
    Code:
    ID             DATA
    1              Moo
    2              Cow
    3              Foo
    4              Bar
    5              Natural
    I dont want rows 1&2 again, because i've already written them to my file. So my query would have to implement WHERE ID > 2.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mm, yes I see thats a tricky one that isnt it, will have to think about that and see what could work.

    I may have to re loop again at the end and add a value to a field there that says this has been saved before, a simple 1,0 would work, but would have to get it working.

    Thanks a lot for this, that has taken a huge worry off my mind.

    I will crack on with this, and post back if I get any problems, so thanks again for all the help.

  8. #8
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    Good luck with it... I have a feeling you're trying to abstract this script to the point that it is causing you grief. (IE: Your query is too abstract - could be anything coming into that query)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  9. #9
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I agree, on this occasion though the query is going to be a lot easier, with that one above it had to be abstract due to the job, but this is far more straight forward, and thikning about it i dont have to loop around as when the last bit of the form is completed the user clicks submit and then after that the excel file is created.

    So at the point where the user clicks submit that indicator can go in then, and then the query can read it to create the new line to go into the excel file.

    I tihnk once I get going with it, and simplify it, I will see how it can come together.

    Thanks again though, will surely return, but not for a day or so.

  10. #10
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is going to sound like I dont know what I'm doing, but I cant seem to figure out what the path would be to a file lets say in My Documents (he shamefully asks!)

    How can I get that, or is there a standard path to that folder on a PC


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
  •