records come one by one very slowly and incomplete

Question

I have an issue with my code This script writes the variable to a csv file. I m getting the parameter trough via HTTP GET, the problem is each records comes one by one very slowly. It should be able to take a batch of thousands of records. I also noticed it's incomplete because it's missing about half the record when comparing to the full report downloaded from my vendor. Here is the script:

<?php

error_reporting(E_ALL ^ E_NOTICE);

// setting the default timezone to use.
date_default_timezone_set('America/New_York');

//setting a the CSV File
$fileDate = date("m_d_Y");
$filename = "./csv_archive/" . $fileDate . "_SmsReport.csv";

//Creating handle
$handle = fopen($filename, "a");
//$handle = fopen($directory.$filename, 'a')
//These are the main data field
$item1 = $_REQUEST['item1'];
$item2 = $_REQUEST['item2'];
$item3 = $_REQUEST['item3'];

$mydate = date("Y-m-d H:i:s");

$csvRow = $item2 . "," . $item1 . "," . $item3 . "," . $mydate . "\n";
//writing to csv file
// just making sure the function could wrtite to it
if (!$handle = fopen($filename, 'a')) {
    echo "Cannot open file ($filename)";
    exit;
}
//writing the data 
if (fwrite($handle, $csvRow) === FALSE) {
    echo "Cannot write to file ($filename)";
    exit;
}
fclose($handle);
?>

I rewrote it twice but the issue still persist. This goes beyond the scope of my knowledge so I am hoping someone tell me a better approach. My boss blaming PHP, help me prove him wrong!

How often will the requests come in? If multiple requests will arrive at (almost) the same time, they both will try to access the same output-file and blocking the other request access. Also you may consider using fputcsv() in stead of manually placing the commas as fputcsv() will properly escape the values (e.g. if item1 contains a comma) http://php.net/manual/en/function.fputcsv.php. For many simultaneous requests, a database may be a better option — thaJeztah, Feb 07 '13 at 23:48
at Duotrigesimal its using GET @thaJeztah request come by bacth it could be thousands of records at once — , Feb 08 '13 at 00:03
possible duplicate of [PHP: Missing records when writing to file](http://stackoverflow.com/questions/14592677/php-missing-records-when-writing-to-file) — Omn, Apr 03 '14 at 21:58

score 0 · Answer 1 · answered Feb 08 '13 at 00:01

I think there's a better way of doing this. Try putting all your data into an array first, with each row in the CSV file being an array in itself, and then outputting it. Here's an example of some code I wrote a while back:

class CSV_Output {

    public $data = array();
    public $deliminator;

    function __construct($data, $deliminator=",") {
        if (!is_array($data)) {
            throw new Exception('CSV_Output only accepts data as arrays');
        }
        $this->data = $data;
        $this->deliminator = $deliminator;
    }

    public function output() {
        foreach ($this->data as $row) {
            $quoted_data = array_map(array('CSV_Output', 'add_quotes'), $row);
            echo sprintf("%s\n", implode($this->deliminator, $quoted_data));
        }
    }

    public function headers($name) {
        header('Content-Type: application/csv');
        header("Content-disposition: attachment; filename={$name}.csv");
    }

    private function add_quotes($data) {
        $data = preg_replace('/"(.+)"/', '""$1""', $data);
        return sprintf('"%s"', $data);
    }

}   

// CONSTRUCT OUTPUT ARRAY
$CSV_Data = array(array(
    "Item 1",
    "Item 2",
    "ITem 3",
    "Date"
));

// Needs to loop through all your data..
for($i = 1; $i < (ARGUMENT_TO_STOP_LOOP) ; $i++) {

    $CSV_Data[] = array($_REQUEST['item1'], $_REQUEST['item2'], $_REQUEST['item3'], $_REQUEST['itemdate']);

}

$b = new CSV_Output($CSV_Data);
$b->output();
$b->headers("NAME_YOUR_FILE_HERE");

thanks for the advice, Im trying your script , it does not create the CSV, also what am I replacing "ARGUMENT_TO_STOP_LOOP" with ? — , Feb 08 '13 at 00:34

score 0 · Answer 2 · answered Feb 08 '13 at 01:05

0

As requests come in to your server from BulkSMS, each request is trying to open and write to the same file.

These requests are not queued, and do not wait for the previous one to finish before starting another, meaning many will fail as the server finds the file is already in use by the previous request.

For this application, you'd be much better off storing the data from each request in a database such as SQLite and writing a separate script to generate the CSV file on demand.

I'm not particularly familiar with SQLite, but I understand it's fairly easy to implement and seems to be well documented.

answered Feb 08 '13 at 01:05

noevidenz

150
1
6

thanks for your advice , is it a good idea to stored them all directly into SQL server? – Feb 08 '13 at 01:18
@user200900 I've found other database technologies easier to use with PHP (eg. MySQL and SQLite) than MS SQL Server. [This article (tutsplus.com)](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) seems like a pretty good starting point. It would be advantageous to learn something like PDO up front, as it provides a common interface to safely interact with many different database types. – noevidenz Feb 08 '13 at 02:20

score 0 · Answer 3 · edited May 23 '17 at 12:06

Because multiple requests will arrive at the same time, concurrent requests will try to access the same output-file and blocking the other request access.

As I pointed out in my comment, you should be using a decent database. PostgreSQL or MySQL are open-source databases and have good support for PHP.

In my experience, PostgreSQL is a more solid database and performs better with many simultaneous users (especially when 'writing' to the database), although harder to learn (its more 'strict').

MySQL is easier to learn and may be sufficient, depending on the total number of request/traffic.

PostgreSQL: http://www.postgresql.org

MySQL: http://www.mysql.com

Do not use SQLite as a database for this because SQLite is a file-based database designed as a single-user database, not for client/server purposes. Trying to use it for multiple requests at the same time will give you the same kind of problems you're currently having

http://www.sqlite.org/whentouse.html How Scalable is SQLite?

records come one by one very slowly and incomplete

3 Answers3