How do you export as TXT Tab Delimited from MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you export as TXT Tab Delimited from MySQL

Post 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).
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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;
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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...
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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"');
	?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

You have a bunch of unmatched braces and you aren't echoing. Also, why do you have $csv_output, $output, and $data?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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;
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

Except replace $output with $data so you get your column headers included in the output
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

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

Post by Celauran »

I also noticed you have more columns inside your loop than you have column headers defined.
Post Reply