[SOLVED] DB table to txt

[dmschenk]

I want to send the contents of a db table with three fields to a text file but I haven't got a clue where to start.

the format would be something like this basically writing one field per line.

 

Data for 'row 1 field 1'

Data for 'row 1 field 2'

Data for 'row 1 field 3'

Data for 'row 2 field 1'

Data for 'row 2 field 2'

Data for 'row 2 field 3'

 

Can someone please give me some direction?

 

Thanks

Dave

[steve448]

You don't say what stage your at so I'm assuming you know how to get the data from the database?

 

If so you want to put the contents into a string, like:

 

$contents = ''; // initialize a variable

foreach($sql as $sql) {   // loop through your database query results

$contents .= $sql->field1 . "\n";
$contents .= $sql->field2 . "\n";
$contents .= $sql->field3 . "\n";
  
   }

$file = 'test.txt'; //the filename you want to write to

if($fp = fopen($file, w)) {  // open the file ready for writing

fputs($fp, $content);  // write $contents to the file

fclose($fp);  // close the file
  } 

If you've no idea how to connect to your database and get the data then you probably need to read a tutorial and start from the beginning.

 

Hope this helps

[dmschenk]

I don't seem to be able to get it to work. I get this error:

Warning: Invalid argument supplied for foreach() in ../test.php on line 11

 

I also know that it does connect to the database because I can run an echo to the fields and they come back.

Could you please show me what I've done wrong in the code below. 

 

 

<?php require_once('Connection link'); ?>
<?php
mysql_select_db($database_bsACKM, $bsACKM);
$query = "SELECT ProductName, ProductDetails, ProductPrice FROM store";
$bsProduct = mysql_query($query, $bsACKM) or die(mysql_error());
$row_bsProduct = mysql_fetch_assoc($bsProduct);
$totalRows_bsProduct = mysql_num_rows($bsProduct);

$contents = 'ProductName, ProductDetails, ProductPrice'; // initialize a variable

foreach($sql as $sql) {
$contents .= $sql->ProductName . "\n";
$contents .= $sql->ProductDetails . "\n";
$contents .= $sql->ProductPrice . "\n";
  
   }

$file = 'test.txt'; //the filename you want to write to

if($fp = fopen($file, w)) {  // open the file ready for writing

fputs($fp, $content);  // write $contents to the file

fclose($fp);  // close the file
  } 

mysql_free_result($bsProduct);
?>

 

Thanks

Dave

 

[steve448]

Change this:

 

foreach($sql as $sql)

 

 

For:

 

foreach($row_bsProduct as $sql)

 

I only called it $sql because I didn't know the name of the array you've got.

 

 

Also, leave:

 

$contents = 'ProductName, ProductDetails, ProductPrice';

 

as just:

 

$contents = '';

 

unless you want it displayed in the text file

 

Let me know if this works

[steve448]

Sorry what I posted before isn't going to work at all, I've spent too much time working with my own class which gets rid of most of these steps

 

The code you want is:

 

<?php require_once('Connection link'); ?>
<?php
mysql_select_db($database_bsACKM, $bsACKM);
$query = "SELECT ProductName, ProductDetails, ProductPrice FROM store";
$bsProduct = mysql_query($query, $bsACKM) or die(mysql_error());

$contents = ''; // leave this empty

//use while so we can loop through the results
while($sql = mysql_fetch_assoc($bsProduct)) {

foreach($sql as $sql) {
$contents .= $sql . "\n";
$contents .= $sql . "\n";
$contents .= $sql . "\n";
   }
  }

$file = 'test.txt'; //the filename you want to write to

if($fp = fopen($file, w)) {  // open the file ready for writing

fputs($fp, $content);  // write $contents to the file

fclose($fp);  // close the file
  } 

mysql_free_result($bsProduct);
?>

 

Hopefully

[dmschenk]

Well..... it's getting closer... I don't get any errors but it doesn't output to the test.txt file either

I set the chmod properties to 777 on test.txt just to be sure that wasn't the problem.

 

I also tried manually adding text to the file and running the script and the result is that it deletes all the text in the file ... so I know it is doing something

 

Dave

[steve448]

Can you echo $contents to the screen, to make sure it contains the data

[roopurt18]

Why not use

SELECT ... INTO OUTFILE ...

 

http://dev.mysql.com/doc/refman/4.1/en/select.html

[steve448]

Ok,

 

This:

 

fputs($fp, $content);

 

Should be:

 

fputs($fp, $contents);

 

And this:

 

foreach($sql as $sql) {

$contents .= $sql . "\n";

$contents .= $sql . "\n";

$contents .= $sql . "\n";

  }

 

Should just be:

 

foreach($sql as $sql) {

$contents .= $sql . "\n";

  }

 

Sorry about this, think I've been in front of this computer too long today and my brain is frazzled, but this should work

[roopurt18]

You realize you made a mistake with?

foreach($sql as $sql) {

[steve448]

Why not use

SELECT ... INTO OUTFILE ...

 

http://dev.mysql.com/doc/refman/4.1/en/select.html

 

Well that looks a bit easier, where were you an hour ago 

[dmschenk]

I figured it out, it works fine now.  Thank you so much for your help!!

 

 

..... there was a typo in this line :

 

fputs($fp, $content); 

 

 

 

should have been:

fputs($fp, $contents);

 

This is how the code finally ended up, also note that the foreach() loop only needed one statement.

 

<?php
mysql_select_db($database_bsACKM, $bsACKM);
$query = "SELECT ProductName, ProductDetails, ProductPrice FROM store";
$bsProduct = mysql_query($query, $bsACKM) or die(mysql_error());
$row_bsProduct = mysql_fetch_assoc($bsProduct);
$totalRows_bsProduct = mysql_num_rows($bsProduct);


$contents = ''; // leave this empty

//use while so we can loop through the results
while($sql = mysql_fetch_assoc($bsProduct)) {

foreach($sql as $sql) {
$contents .= $sql . "\n";
   }
  }

echo ($contents);

$file = 'test.txt'; //the filename you want to write to

if($fp = fopen($file, w)) {  // open the file ready for writing

fputs($fp, $contents);  // write $contents to the file

fclose($fp);  // close the file
  } 

mysql_free_result($bsProduct);
?>

 

Again, Thank you very much for your help!

 

Dave