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.