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:

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

Post by simonmlewis »

Code: Select all

<?php

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
$date = "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/{$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;
	?>
9 of both now, and $output is now $data, still outputs nothing.
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 $date instead of $data, but I also noticed you've removed the include that sets up the DB connection. Why not comment out the headers calls until you get the desired output on your screen?
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 »

It's only coz I didn't copy that bit in the "copy paste" for you - sorry.

I have commented the headers, and it's producing nothing.

Code: Select all

include "dbconn.php";

$result = mysql_query("SELECT * FROM products WHERE pause = 'off'");
$data = "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/{$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;
       	mysql_close($sqlconn);
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 »

Have you checked that $result actually contains a result set and isn't false? Is your error reporting on? It looks like it's failing silently somewhere. Even with no rows returned, $data is still being set, so should still produce some output on echo.
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 spotted it: $subcateg = "$row>subname";.

Should I see the results from the DB on a new line AFTER the rows at the top? Or "inline"....?
ie...
.... availability brand
.....in stock Special

Or...

....availability brand in stock special
?
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 »

\n isn't going to display in-browser. It will look like one huge mess of text. It will display correctly in a text file, though.
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 »

The first line of the txt file displays like this (with wordwrap on):
id title description price image link link condition availability brand 3 XL Top XL Top 29.99

Should it now show in TXT as:
id title description price image link link condition availability brand
3 XL Top XL Top 29.99

(tho perhaps spaced properly)
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 »

Are you on a Windows machine? \n is a UNIX linefeed. Try \r\n or see if your text editor can recognize LF instead of CRLF
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 »

bingo!!
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 »

Problem: I need to set all the text to be titlecase, as Google doesn't like CAPITALS.

I tried this but it just echoes with the tags I enter - how do I make it use those tags without actually rendering them on the page?

Code: Select all

$data .= "{$row->id}\t<font style='text-transform: titlecase'>{$row->title}</font>\t
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

$data .= "{$row->id}\t" . ucwords($row->title) . "\t";
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

$data .= "{$row->id}\t" . ucwords{$row->title} . "\t" . ucwords{$row->title} . "\t{$row->price}

Code: Select all

$data .= "{$row->id}\t" . ucwords($row->title) . "\t" . ucwords($row->title) . "\t{$row->price}
Try both of these options, as I did the top one by accident.
The top one doesn't make it titlecase.
The second one makes the page produce nothing at all.
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 »

I'm assuming that's not the entire line and that you've got the closing double quote and the semicolon at line end? What error(s) does it display?
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 was paraphrasing it:

Code: Select all

$data .= "{$row->id}\t" . ucwords($row->title) . "\t" . ucword($row->title) . "\t{$row->price}\thttp://www.site.co.uk/images/{$row->photoprimary}\thttp://www.site.co.uk/product/{$row->catid}/$categreplace/{$row->subid}/$subcategreplace/{$row->id}/$titlereplace\tNew\tinstock\t{$row->manufacturer}\t \r\n";
I am reading about ucwords, and it says that is sets the first character only to uppercase?

Sorry edit - this is the code with the (). Produces nothing, no errors.
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

$data .= "{$row->id}\t" . ucwords{$row->title} . "\t" . ucwords{$row->title} . "\t{$row->price}\thttp://www.site.co.uk/images/{$row->photoprimary}\thttp://www.site.co.uk/product/{$row->catid}/$categreplace/{$row->subid}/$subcategreplace/{$row->id}/$titlereplace\tNew\tinstock\t{$row->manufacturer}\t \r\n";
ucwords is a function; you need to use parentheses, not braces.

Code: Select all

ucwords($row->title)
simonmlewis wrote:I am reading about ucwords, and it says that is sets the first character only to uppercase?
It says no such thing. From the manual:
Returns a string with the first character of each word in str capitalized, if that character is alphabetic.
Last edited by Celauran on Thu Sep 26, 2013 7:18 am, edited 1 time in total.
Post Reply