...

View Full Version : records not looping properly in export to csv



harlequin2k5
02-14-2008, 02:38 AM
first I want to thank missing-score for his fine tutorial in the php faq on exporting to csv - I wouldn't have gotten this far without it!

there are actually 2 things that I'd like to do with this export...first and foremost would be that it at least displays the correct records; the second would be rather than have it display them at all have the csv prompt for a download to save a step of copying and pasting

but on to what I have so far...when I use the following code it will echo the correct # of records...when it moves on to the actual .csv only 1 record is displaying - this code is the closest I've come even if it only displays one record on the .csv :)


<?
$query="SELECT name, value, maincategory FROM domains WHERE maincategory='" . $_GET['valuebycategory'] . "' ORDER BY name ASC";
$result=mysql_query($query) or die(mysql_error());
$row=mysql_fetch_array($result);
$numrows=mysql_num_rows($result);
echo $numrows . ' records have been found.';

$fp=fopen('newappraisals.csv','w');

while($row=mysql_fetch_array($result))
{
$string='"' . $row['name'] . '","' . $row['value'] . '"' . "\n";
}

flock($fp,LOCK_EX);
fputs($fp,$string);
flock($fp,LOCK_UN);
fclose($fp);
?>


as for bypassing viewing the .csv and having it prompt for a download instead - I had very little luck in finding the appropriate header() to use...if someone could point me in the right direction it would be much appreciated

thanks for the help!

oesxyl
02-14-2008, 03:36 AM
use fputcsv to format lines:

http://www.php.net/manual/en/function.fputcsv.php

but I think your problem is in query not in how you build csv.

use mysql_num_rows to see how many rows the query returns and echo/print or print_r each row to see results

I don't understand the part with header, give more details.

best regards

Fumigator
02-14-2008, 03:48 AM
Your fputs() command must be inside your "while" loop. As oesxyl mentioned, you should use fputscsv to properly format the fields with quotes and commas.

You don't seem to understand how your data is retrieved from your database. You immediately fetch one row right after you build the resultset, but then your "while" loop fetches again before you've done anything with the first row you fetched.

1. Build the text of the query and assign to variable (as you've done properly)

2. Open the query for fetching using mysql_query() (as you've done properly)

3. Use a looping mechanism (while, for, etc) to fetch all rows into an array, or process each row as you fetch it (as you've not done properly)

harlequin2k5
02-14-2008, 04:17 AM
thank you fumigator - that helps a great deal

about the echo before the while loop - I was just looking - the echo wasn't really there to server any real purpose and I should have removed it before posting here

I will take a run at correcting my current while statement

thanks!

harlequin2k5
02-14-2008, 05:01 AM
I did not use the fputcsv but this provided exactly what I wanted...


<?
$query="SELECT name, value, maincategory FROM domains WHERE maincategory='" . $_GET['valuebycategory'] . "' ORDER BY name ASC";
$result=mysql_query($query) or die(mysql_error());

$fp=fopen('newappraisals.csv','w');

while($row=mysql_fetch_array($result))
{
$string='"' . $row['name'] . '","' . $row['value'] . '"' . "\n";

flock($fp,LOCK_EX);
fputs($fp,$string);
flock($fp,LOCK_UN);
}
fclose($fp);
?>

to revisit the 2nd thing I'd like to do with this csv is rather than have it update the file on the server (the user will not have access to the server files) it would be preferable that after the file is updated the user then either is prompted to download the file or have the file download from a link...this way they can have the file local on their own machine

aedrin
02-14-2008, 03:41 PM
as for bypassing viewing the .csv and having it prompt for a download instead - I had very little luck in finding the appropriate header() to use...if someone could point me in the right direction it would be much appreciated

The manual page on header() (http://us3.php.net/header) has all the information you need if you read the comments.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum