Question on generating csv file

No, it's not.

Let me ask you this: The reason I'm going this route is because this code, while it works, was not accommodating commas. Anytime one showed up, it resulted in an additional column being generated and that's not going to work.

Thing is, Gary, I think this is your code! How can I tweak it so it "escapes" those commas?

Here's what I've got:

<?php 
ini_set("memory_limit","500M");
error_reporting(E_ALL);

set_time_limit(600);
include ("carter.inc");
$cxn = mysqli_connect($host,$user,$password,$database)
or die ("couldn't connect to server");

$output="";

$michelle="select * from twitter_csv";
$michelle_query=mysqli_query($cxn, $michelle)
or die("Michelle didn't work");
$michelle_columns=mysqli_field_count($cxn);

//gets the field names from your table and sets them up as the first row in your csv file

$heading=mysqli_fetch_fields($michelle_query);

foreach ($heading as $val)
{
	$output .='"'.$val->name .'",';
}
$output .="\n";


while($michelle_row=mysqli_fetch_array($michelle_query))
{
	for($i=0; $i<$michelle_columns; $i++)
	{
		$output .=''.$michelle_row["$i"].',';
	}
$output .="\n";
}

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
?>

Select allOpen in new window

It's what I've been using and it works great, except for that comma dynamic.

To answer your question, the folder is not writeable. I may be able to change that, but I would prefer learning how to make the above code work rather than having to start over with a different approach.

If the folder isn't writable then you cannot change any file in it.

Does it need to be a csv, can you use a tab delimited file?

Your problem with the commas is because you didn't want the quote marks around the columns,

I thought we went over this in an earlier question?
http://iconoun.com/demo/temp_brucegust.php

The part you want is located at the end, around line 170.

The generated file is here:
http://iconoun.com/demo/storage/brucegust.csv

<?php // demo_temp_brucegust.php
error_reporting(E_ALL);

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28533249.html

// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
require_once('RAY_live_data.php');
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// THE DATA SET IS UTF-8
$mysqli->set_charset("utf8mb4");
mb_internal_encoding("UTF-8");

// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TEMPORARY TABLE `verizon`
( `id`                 INT           NOT NULL AUTO_INCREMENT PRIMARY KEY
, `twitter_id`         VARCHAR(150)  NOT NULL DEFAULT ''
, `actor_id`           VARCHAR(150)  NOT NULL DEFAULT ''
, `actor_display_name` VARCHAR(150)  NOT NULL DEFAULT ''
, `posted_time`        DATETIME      NOT NULL DEFAULT '0000-00-00 00:00:00'
, `display_name`       VARCHAR(150)  NOT NULL DEFAULT ''
, `geo_coords_lat`     DECIMAL(16,9) NOT NULL DEFAULT 0.0
, `geo_coords_lon`     DECIMAL(16,9) NOT NULL DEFAULT 0.0
, `location_name`      VARCHAR(150)  NOT NULL DEFAULT ''
, `posted_day`         DATE          NOT NULL DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// MAP OF COLUMN NAMES  // AND SAMPLE VALUES
$map = array
( 'id'                  //  "2"                      <-- AUTO_INCREMENT KEY
, 'twitter_id'          //  "364536294640451585"
, 'actor_id'            //  "322321678"
, 'actor_display_name'  //  "Mutt"
, 'posted_time'         //  "2013-08-06 00:00:00"
, 'display_name'        //  "Twitter for Android"
, 'geo_coords_lat'      //  "32.867479200"
, 'geo_coords_lon'      //  "-96.641121600"
, 'location_name'       //  "Garland"
, 'posted_day'          //  "2013-08-06"
)
;

// READ THE TEST DATA
$url = 'http://filedb.experts-exchange.com/incoming/2014/10_w41/876263/verizon-data.csv';
$utl = 'storage/brucegust_testdata.csv';
$fpr = fopen($url, 'rb');
if (!$fpr) trigger_error("Unable to open $url", E_USER_ERROR);

// LOAD THE DB TABLE
while ($csv = fgetcsv($fpr))
{
    $csv = array_combine($map, $csv);
    unset($csv['id']);
    foreach ($csv as $key => $val)
    {
        $csv[$key] = $mysqli->real_escape_string($val);
    }
    $cols = implode(',', array_keys($csv));
    $data = "'" . implode("','", $csv) . "'";
    $sql = "INSERT INTO verizon ($cols) VALUES ($data)";
    if (!$res = $mysqli->query($sql))
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }
}


// ASSIGN VARIABLES FOR OUR QUERY
$latitude_1  = 32;
$latitude_2  = 36;
$longitude_1 = -94;
$longitude_2 = -86;
$start_date  = '2013-07-04';
$end_date    = '2013-08-06';

// CREATE A SELECT QUERY
$sql
=
"
SELECT
  actor_id
, actor_display_name
, posted_time
, display_name
, geo_coords_lat
, geo_coords_lon
, location_name
, posted_day
FROM verizon
WHERE
( geo_coords_lat >= '$latitude_1' AND geo_coords_lat <= '$latitude_2')
AND
( geo_coords_lon >= '$longitude_1' AND geo_coords_lon <= '$longitude_2')
AND
( posted_day >= '$start_date' AND posted_day <= '$end_date')
LIMIT 400000
"
;

// RUN THE QUERY OR FAIL ON ERROR
$res = $mysqli->query($sql);
if (!$res = $mysqli->query($sql))
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}

// HOW MANY ROWS WERE RETURNED?
$cnt = $res->num_rows;
if($cnt == 0)
{
    echo PHP_EOL . "NO ROWS MATCHED QUERY: $sql";
}
else
{
    // WRITE A CSV FILE HERE
    echo PHP_EOL . "$cnt ROWS MATCHED QUERY: $sql<br>";
    $out = 'storage/brucegust.csv';
    $fpw = fopen($out, 'wb');
    if (!$fpw) trigger_error("Unable to Open $out", E_USER_ERROR);

    // GET COLUMN NAMES
    $fld = $res->fetch_fields();
    $csv = array();
    foreach ($fld as $obj)
    {
        $csv[] = $obj->name;
    }
    fputcsv($fpw, $csv);

    // GET DATA VALUES
    while ($row = $res->fetch_assoc())
    {
        fputcsv($fpw, $row);
    }
    fclose($fpw);

    // PRESENT A LINK FOR THE CLIENT
    echo PHP_EOL . 'CSV: <a target="_blank" href="' . $out . '">' . $out . '</a>';
}

Select allOpen in new window

Gary - I need a csv file.

Steve - I got this when I ran your code:

Catchable fatal error: Object of class stdClass could not be converted to string in /opt/lampp/htdocs/twit/good_twitter_csv_test.php on line 17

JulianH - I still had the same problem, although I was totally digging your logic.

Ray - Yes, you've seen this before, but I went in a different direction when it became evident that I was going to need write permission to the directory. That's why, although your input is all over this project, I didn't use that particular piece of "Ray Cake..."

But guys, we're closer...

Here's what I wound up doing and it worked:

while($michelle_row=mysqli_fetch_array($michelle_query))
{
      for($i=0; $i<$michelle_columns; $i++)
      {
            $output .=''.str_replace(",", "&#44;", $michelle_row["$i"]).',';
      }
$output .="\n";
}

I just did a "replace" so in the event one of the users documented their name with a comma, I wasn't going to have to worry about a new column.

But we're not out of the woods, yet...

Attached is a screenshot of the new and improved csv file. While the previous quandary was a result of a renegade comma, I don't know what is causing this hiccup here.

You can see the dilemma with "Will Brooks." For no apparent reason, his row is a complete mess. Any ideas as to why his particular row chooses to go south?

What do you think?
what-the-smoke.docx

Looks like there is a line-feed character in Will Brooks data.  This is normal and expected in CSV files.

I really think you might want to get write permissions so you can do this the right way, with fputcsv().  If you use the standard methods, you can get correct results very quickly and easily.  But trying to create your own CSV files would seem to require writing a state engine that replicates the functionality of fputcsv().  That's an advanced programming task and it's completely unnecessary -- all you need to do is get the directory write permissions for a temporary directory!

So, Ray, let me explain this back to you: The nature of the CSV "beast" is that you're bound to encounter characters within your data that will be processed as "code." When you say "line feed," in this case that could be a carriage return or a line break which is enough to wreak havoc on a CSV export process.

That said, I'm doing well to get the necessary permissions and use the fputcsv() dynamic if for no other reason in that it anticipates those kinds of things and prevents them from clogging the pipes.

Yes?

Got it!

Thank you!

What are you importing this into, you can normally set whatever delimiter you want in most programs