need a bit of help getting mysql data and writing to text/csv file

[simcoweb]

I need to retrieve data from the same mysql database to create two files in a text/csv format. Basically it's a product database. The first file needs to retrieve the first 5 rows only. The second one needs to retrieve all rows.

Here's my code for the 5 row setup:

[code]<?php
// run our query based upon 5 rows of results
$sql = "SELECT * FROM datafeed LIMIT 5 ORDER BY Product_ID";
$results = mysql_query($sql) or die(mysql_error());

while ($row = mysql_fetch_arry($results)) {
  $fp = fopen("datafeed-test.txt", 'r') or die("Could not open file.")
  if ( $fp = fopen("datafeed-test.txt", 'r'))
  {
  fwrite($fp, $URL, $Product_ID, $Product_Name, $Price, $Sale_Price, $Description, $Category, $Image, $Postage, $Brand, $Availability, $Thumbnail,
  $ThumbnailWidth, $ThumbnailHeight);
  echo "File successfully written.";
} else {
  echo "Could not write to file.";
  echo "<a href='datafeed-test.txt'>Click here</a> to open the file.<br>\n";
}
}
?>[/code]

I'm not 100% sure if my fwrite uses the array variables in order to write the data. Plus, the end result needs to be a csv file so it has to be separated by commas.

Need a bit of guidance on this. Thanks!

[trq]

Opening the file over and over and over within the while is a big no no. Also... where are all those variables coming from?

[code=php:0]
if ($fp = fopen("datafeed-test.txt", 'r'))
  while ($row = mysql_fetch_assoc($results)) {
    fwrite($fp, "{$row['URL']}, {$row['Product_ID']}, {$row['Product_Name']}, {$row['Price']}, {$row['Sale_Price']}, {$row['Description']}, {$row['Category']}, {$row['Image']}, {$row['Postage']}, {$row['Brand']}, {$row['Availability']}, {$row['Thumbnail']},
  {$row['ThumbnailWidth']}, {$row['ThumbnailHeight']}");
  }
  echo "File written";
} else {
  echo "Could not write to file.";
  echo "<a href='datafeed-test.txt'>Click here</a> to open the file.<br>\n";
}
[/code]

[simcoweb]

:) That's why I ask for guidance!

Ok, let me make these changes and report back.

Also, is there a way to create a .csv file that is downloadable instead of the way I have it now where it would simply open it?

[simcoweb]

Ok, i'm not getting any error messages but nothing is writing to the file either. I get a success notice but that doesn't necessarily mean there's data being written. Here's my modified code based upon your edits:

[code]<?php
// run our query based upon 5 rows of results
$sql = "SELECT * FROM Datafeed LIMIT 5";
$results = mysql_query($sql) or die(mysql_error());

$fp = fopen("datafeed-test.txt", 'r') or die("Could not open file.");
 
  if ($fp = fopen("datafeed-test.txt", 'r')) {
 
  while ($row = mysql_fetch_assoc($results)) {
   
    fwrite($fp, "{$row['URL']}, {$row['Product_ID']}, {$row['Product_Name']}, {$row['Price']}, {$row['Sale_Price']}, {$row['Description']}, {$row['Category']}, {$row['Image']}, {$row['Postage']}, {$row['Brand']}, {$row['Availability']}, {$row['Thumbnail']},
  {$row['ThumbnailWidth']}, {$row['ThumbnailHeight']}");
 
  }
  echo "File written";
  echo "<a href='datafeed-test.txt'>Click here</a> to open the file.<br>\n";
} else {
  echo "Could not write to file.";
  }
echo "The file is a simple text file that needs to be saved as Datafeed.csv and uploaded in your administration area at the data upload page. This file is intended to test the accuracy of the datafeed information before uploading the entire product database.";
?>[/code]

The 'datafeed-test.txt' file is present AND is writeable (chmod at 777).

[kenrbnsn]

In order to write into a file, you need to either open it for write ('w') or append('a'). You are currently opening it for read ('r').

Ken

[simcoweb]

Ok, cool. Made that change and now it writes. I knew that. Just didn't spot it as the problem.

Now, another quick question. Right now it's creating the file with just commas separating the fields. I need to surround the fields in " " 's as well. Is there a quick and easy way to do this?

[kenrbnsn]

Change
[code]<?php
  while ($row = mysql_fetch_assoc($results)) {
   
    fwrite($fp, "{$row['URL']}, {$row['Product_ID']}, {$row['Product_Name']}, {$row['Price']}, {$row['Sale_Price']}, {$row['Description']}, {$row['Category']}, {$row['Image']}, {$row['Postage']}, {$row['Brand']}, {$row['Availability']}, {$row['Thumbnail']},
  {$row['ThumbnailWidth']}, {$row['ThumbnailHeight']}");
 
  }
?>[/code]
to
[code]<?php
  while ($row = mysql_fetch_assoc($results)) {
    $tmp = array($row['URL'], $row['Product_ID'], $row['Product_Name'], $row['Price'], $row['Sale_Price'], $row['Description'], $row['Category'], $row['Image'], $row['Postage'], $row['Brand'], $row['Availability'], $row['Thumbnail'], $row['ThumbnailWidth'], $row['ThumbnailHeight']);
    fwrite($fp, '"' .implode('","',$tmp) . '"' . "\n"); 
  }
?>[/code]

Ken

[simcoweb]

Ken, thanks for that fix. Just a footnote, weird how the email I get regarding the post left out the \ in the \n at the end of the string. I copied from the email and pasted it into the script and, of course, everything was on one line. I added the \ and it worked fine...then came to the actual post here to respond and noticed the \ was in place. Just a word of caution to everyone...come to the actual post to get the correct code!

One more question if I may. Right now after the file has been written I have it set up where it opens in a browser and they'd have to copy and paste it into a text file. I'd prefer to have this saved as a CSV file they'd download. I've been doing some research on this and most of the stuff is on 'importing' a csv file. NOT creating one.

I need this to create a downloadable CSV file with the ',' separator and the fields in " which is what your code changes provide. Now it's the saving as .csv and the download. Ideas?