Page 1 of 3

How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 4:46 am
by simonmlewis
Apologies of this is meant for the database part, but you need to know your PHP to do this - and I'm stuck.

I have the following script to do a CSV export, but its use is to upload to Google Products and I don't think that system likes CSV, only .txt.

I can Save As the CSV as a txt file, but be better if I could export it as .txt.

Here's the code:

Code: Select all

<?php
$cookietype = $_COOKIE['type'];
$todaydate = date('Y-m-d');

if ($cookietype == "admin") {
include "dbconn.php";
$csv_output = '"id","title","description","price","image link","link","condition","availability","brand"';
$csv_output .= "\015\012";

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
while($row = mysql_fetch_array($result)) 
{      
 
  $title = "$row[title]"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 
 
 $categ = "$row[catname]"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$row[subname]"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
 
  $csv_output .= '"'.$row[id].'","'.$row[title].'","'.$row[title].'","'.$row[price].'","http://www.site.co.uk/images/'.$row[photoprimary].'","http://www.site.co.uk/product/'.$row[catid].'/'.$categreplace.'/'.$row[subid].'/'.$subcategreplace.'/'.$row[id].'/'.$titlereplace.'","New","In Stock","'.$row[manufacturer].'"';
    $csv_output .= "\015\012";
}
  //You cannot have the breaks in the same feed as the content. 
  header("Content-type: application/vnd.ms-excel");
  header("Content-disposition: csv; filename=Google_" . date("Y-m-d") .".csv");
  print $csv_output;
  exit;
       	mysql_close($sqlconn);
  echo "Extract in progress - close page when completed.";
	}

	else 
	{
	echo "<meta http-equiv='Refresh' content='0 ;URL=/'>";
	}
	?>
Is it simple to alter this to convert to TXT (txt doesn't have the " in the code).

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 5:21 am
by simonmlewis
I have tried this but it does nothing at all, even though I've created a test.txt file in the root, with full permissions:

Code: Select all

include "dbconn.php";
$sql = "SELECT * INTO OUTFILE "/test.txt"
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
FROM products;";
	mysql_close($sqlconn);

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 5:40 am
by simonmlewis

Code: Select all

include "dbconn.php";
$fh = fopen('data.txt', 'w');
$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
while ($row = mysql_fetch_array($result)) {
    $last = end($row);
    foreach ($row as $item) {
        fwrite($fh, $item);
        if ($item != $last)
            fwrite($fh, "\t");
    }
    fwrite($fh, "\n");
}
fclose($fh);
	mysql_close($sqlconn);
I have tried this, and it works - it saves the file, BUT, is there something i can add to the script that automatically pops up a 'Save as'.

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 6:32 am
by Celauran

Code: Select all

<?php

$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');

$query = "SELECT id, email, password FROM users";
$result = $pdo->query($query);

$data = '';

while ($row = $result->fetch(PDO::FETCH_OBJ)) {
	$data .= "{$row->id}\t{$row->email}\t{$row->password}\n";
}

header('Content-Type: application/octet-stream');
header('Content-Transfer-Encoding: binary');
header('Content-disposition: attachment; filename="foo.txt"');
echo $data;

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 7:15 am
by simonmlewis
Sorry I meant to add something here - how do I add my own column names? Google insists on certain words. This won't get be in PDO, but will be soon.

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 7:36 am
by Celauran
Make the first row column names? Just define it outside the loop.

Code: Select all

$output = "column 1\tcolumn 2\tcolumn 3\n";
while ($row = $result->fetch()) {
etc...

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 7:48 am
by simonmlewis
This isn't producing anything - I assume because I am trying to put static test among the variables - done it wrong somehow.

I need to show the full URL of an image. ie
http://www.site.co.uk/images//{$row->image}

Code: Select all

<?php
$csv_output = '"id","title","description","price","image link","link","condition","availability","brand"';
$csv_output .= "\015\012";

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
$output = "id\ttitle\tdescription\tprice\timage link\tlink\tcondition\tavailability\tbrand\n";
while($row = mysql_fetch_array($result)) 
{      
 
  $title = "$row->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 
 
 $categ = "$row->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$row>subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
 
  $data .= "{$row->id}\t{$row->title}\t{$row->title\t{$row->price\thttp://www.site.co.uk/images//\t{$row->image\tURLHERE\t\t{$row->New\tinstock\t{$row->manufacturer}\t\n";
  }
  header('Content-Type: application/octet-stream');
header('Content-Transfer-Encoding: binary');
header('Content-disposition: attachment; filename="data.txt"');
	?>

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:19 am
by Celauran
You have a bunch of unmatched braces and you aren't echoing. Also, why do you have $csv_output, $output, and $data?

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:25 am
by simonmlewis
My bunch'a'bads!

Code: Select all

include "dbconn.php";

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
$output = "id\ttitle\tdescription\tprice\timage link\tlink\tcondition\tavailability\tbrand\n";
while($row = mysql_fetch_array($result)) 
{      
 
  $title = "$row->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 
 
 $categ = "$row->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$row>subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
 
  $data .= "{$row->id}\t{$row->title}\t{$row->title}\t{$row->price}\thttp://www.site.co.uk/images/\t{$row->image}\tURLHERE\tNew\tinstock\t{$row->manufacturer}\t\n";
  }
  header('Content-Type: application/octet-stream');
header('Content-Transfer-Encoding: binary');
header('Content-disposition: attachment; filename="data.txt"');

       	mysql_close($sqlconn);
  echo "Extract in progress - close page when completed.";
This outputs and empty data.txt file.

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:26 am
by Celauran
You're fetching an array (mysql_fetch_array) and treating it as though it were an object ($row->foo)
Oh, and you need to echo $data

EDIT: I still don't understand the purpose of $output

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:34 am
by simonmlewis
Outputs nothing. I have changed it to mysql_fetch_object, so it uses $row->
Also, the purpose of $output is to add column names, as I requested earlier.

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:38 am
by Celauran
Can you post your updated code?

My point about $output was that you're assigning a value to it but subsequently doing nothing with it. The code I posted initially works perfectly.

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:41 am
by simonmlewis
Yeah I understand.
I did think my CSV version could be altered, this looks like that is what you are trying to do.

Code: Select all

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
$output = "id\ttitle\tdescription\tprice\timage link\tlink\tcondition\tavailability\tbrand\n";
while($row = mysql_fetch_object($result)) 
{      
 
  $title = "$row->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 
 
 $categ = "$row->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$row>subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
 
  $data .= "{$row->id}\t{$row->title}\t{$row->title}\t{$row->price}\thttp://www.site.co.uk/images/\t{$row->image}\tURLHERE\tNew\tinstock\t{$row->manufacturer}\t\n";
  }
  header('Content-Type: application/octet-stream');
header('Content-Transfer-Encoding: binary');
header('Content-disposition: attachment; filename="data.txt"');
  echo $data;
  exit;

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:43 am
by Celauran
Except replace $output with $data so you get your column headers included in the output

Re: How do you export as TXT Tab Delimited from MySQL

Posted: Wed Sep 18, 2013 8:45 am
by Celauran
I also noticed you have more columns inside your loop than you have column headers defined.