simcoweb Posted November 1, 2006 Share Posted November 1, 2006 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! Quote Link to comment Share on other sites More sharing options...
trq Posted November 1, 2006 Share Posted November 1, 2006 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] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted November 1, 2006 Author Share Posted November 1, 2006 :) 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? Quote Link to comment Share on other sites More sharing options...
simcoweb Posted November 2, 2006 Author Share Posted November 2, 2006 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). Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted November 2, 2006 Share Posted November 2, 2006 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 Quote Link to comment Share on other sites More sharing options...
simcoweb Posted November 2, 2006 Author Share Posted November 2, 2006 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? Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted November 2, 2006 Share Posted November 2, 2006 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 Quote Link to comment Share on other sites More sharing options...
simcoweb Posted November 3, 2006 Author Share Posted November 3, 2006 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? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.