[Resolved] Need to create a tab delimited from a DB and FTP it

[sgtpepper]

Hello all,

I hope everyone is staying healthy...I am battling the flu so I may be a little cloudy.

In any event....here is what I would like to be able to do...and need some help figuring it out.

I have a table in a mysql database and I need a way to do the following with a script.

1) Get all data from the table and place it in a ASCII file, tab delimited between fields, new-line terminator between records (carriage-return or carriage-return + new line).

2) Than I would like the script to FTP this file to a different server.

I dont have the slightest idea on how to do this...so if someone could help me out by either sharing some code or...point me to a few tutorials that could help I would greatly appreciate it.

Thank you.

Scott

[Shrike]

A quick and dirty example to give you an idea... :

$mysql_result = mysql_query("SELECT * FROM table");
while($data = mysql_fetch_array($result, MYSQL_NUMERIC)){
  $rows = array();
  foreach($data as $row){
    $rows[] = $row;
  }
  $tabbed_rows[] = implode("\t", $row)."\n";
}
mysql_close();

$fp = fopen("file.txt", "w+");
$str = implode($tabbed_rows);
fputs($fp, $str, strlen($str));
@fclose($fp);

$ftp = ftp_connect($server);
ftp_login($ftp, $user, $pass);
$put = ftp_put($ftp, "file.txt", "file.txt", FTP_ASCII);
if(!$put){
  echo "FTP failed";
}else{
  echo "FTP success";
}
ftp_close($ftp);

[sgtpepper]

Thanks for the start....but I am having some problems.

I decided to take your code one step at a time...so I broke it into two section

Section 1 gets the data and creates the tab

<?
require('connect.php');
$mysql_result = mysql_query("SELECT * FROM listings");
while($data = mysql_fetch_array($result, MYSQL_NUMERIC)){
  $rows = array();
  foreach($data as $row){
    $rows[] = $row;
  }
  $tabbed_rows[] = implode("\t", $row)."\n";
}
mysql_close();
?>

But this is running into a problem with this line:
while($data = mysql_fetch_array($result, MYSQL_NUMERIC)){

The error says:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

I assume it may be the MYSQL_NUMERIC? I am not sure what this is...any idea on what is wrong?

[Shrike]

Yes sorry it should be MYSQL_NUM.

Also the following line should be

$tabbed_rows[] = implode("\t", $rows)."\n";  // $rows not $row

[sgtpepper]

hmmm....still and issue...

Now the code is:

<?
require('connect.php');
$mysql_result = mysql_query("SELECT * FROM listings");
while($data = mysql_fetch_array($result, MYSQL_NUM)){
  $rows = array();
  foreach($data as $row){
    $rows[] = $row;
  }
  $tabbed_rows[] = implode("\t", $rows)."\n";
}
mysql_close();
?>

and error is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

It still is unhappy with this line
while($data = mysql_fetch_array($result, MYSQL_NUM)){

[Shrike]

Ack, another typo:

while($data = mysql_fetch_array($mysql_result, MYSQL_NUM)){

Sorry, it was pre-caffeine early when I wrote that 😉

[sgtpepper]

Caffeine is an important tool !

Your last posted fixed my last problem, so I decided to put everything together and test

<?
require('connect.php');
$mysql_result = mysql_query("SELECT * FROM listings");
while($data = mysql_fetch_array($mysql_result, MYSQL_NUM)){
  $rows = array();
  foreach($data as $row){
    $rows[] = $row;
  }
  $tabbed_rows[] = implode("\t", $rows)."\n";
}
mysql_close();

$fp = fopen("file.txt", "w+");
$str = implode($tabbed_rows);
fputs($fp, $str, strlen($str));
@fclose($fp);

$ftp = ftp_connect('SERVER_IP_NUMBER_HERE');
ftp_login($ftp, USERNAME, PASSWORD);
$put = ftp_put($ftp, "file.txt", "file.txt", FTP_ASCII);
if(!$put){
  echo "FTP failed";
}else{
  echo "FTP success";
}
ftp_close($ftp);
?>

I created a file called file.txt and chmod 777. The error message I get is : Warning: Wrong parameter count for implode()

Its unhappy with this line:
$str = implode($tabbed_rows);

Thanks for all your help!

[sgtpepper]

I changed

$str = implode($tabbed_rows);

to

$str = implode('', $tabbed_rows);

And all seemed to work. Was this the correct thing to add?

[weevil]

The first value is whatever you want to add in between each value from your array. It looks like you are adding nothing, so you are just displaying all of the values in the array as a string. Is that what you want?

[Shrike]

Yep that is correct, it's just a method of creating a valid delimited string with a \n at the end.

I should stop trying to write code at 8am 😉

[sgtpepper]

Yes, because I am looking for a straight tab delimited file as the output. This now appears to be what I am getting.

[sgtpepper]

I wanted to thank you again. This script is doing what I needed. One quick question....

Would it be possible to alter this code to also, FTP's the contents of a directory? If so how?