[SOLVED] Download file populated with SQL results

[Asheeown]

I have an SQL query for a user search that I want them to be allowed to click the download link and make a file with the SQL results, how can I do this, I want to make temp files not just a set file that has to be overwritten each time

[cmgmyr]

why don't you just export the results to an excel spreadsheet and send that through a header? would that work? Then it would just be gone and you wouldn't have to worry about random files on your server, it would also give you organized output.

[HuggieBear]

I'd say the easiest way is to open a file and use fwrite() in a while loop over the results.

Regards
Huggie

[Asheeown]

An fwrite requires a set file already made and with permissions

[Asheeown]

Well I did find a nice tutorial on how to do it with fwrite however it only says $data for the imported data which I already knew how to do except I don't know how to setup the array for the file

[HuggieBear]

It does, but use fopen() before hand.  If you use fopen() with the file name as the first argument and "w" as the second argument then php will attempt to create the file if it doesn't exist.

Regards
Huggie

[Asheeown]

any pointers for the array?

[linuxdream]

Maybe something like:

[code]
<?php
$sql = "Your statement here";
$results = $db->query($sql);

foreach($results as $result){
    //format into whatever type file here....tab, comma, etc
    $data = .......
}

$file = "/tmp/".md5(time().rand(5, 100)) . ".txt"; //get unique filename...might be overkill
$fh = fopen($file, 'w');
if(fwrite($fh, $data)){
    echo "<a href='$file'>Download results</a>";
}
fclose($fh);
?>
[/code]

Then just clean out the /tmp directory whenever. Though be careful where you create the file. Be sure Apache can access it.

[linuxdream]

Sorry, posted that before you wrote last two questions. Do you want to know how to get from the $data results to readable text inside the file? You have to first decide how you want the data to be...tab delimited, comma, etc. Then just cycle through your results and build it.

foreach($results as $result){
    $data .= $result[0] . "\t" . $result[1] . "\t\n"...etc;
}

[HuggieBear]

Something like this should work...

[code]<?php
// Select info from database
$sql = "SELECT col1, col2 FROM table WHERE col3 = 'condition'";
$result = mysql_query($sql);

// Open the file for writing
$fh = fopen('/my/file/path/goes/here.tmp', "w");

// Create a header row in the file
fwrite($fh, "Column 1\t\t\tColumn 2\n");

// Loop through the results, writing to the file
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  fwrite($fh, $row['col1'] . "\t\t\t" . $row['col2'] . "\n");
}

// Close the file
fclose($fh);
?>[/code]

Regards
Huggie

[Asheeown]

Well say if i wanted $data to have a column name row and then the rows below it what the user searched in the query....and also the form I have for the user to select search filters can I put another form inside it to download, for example making it a download form and it would take the properties they wanted for the download file and query to the next page and did it there?

[Asheeown]

Thanks let me give it a few tries and i'll let you know how they go

[Asheeown]

[code]<?php
// Select info from database
$sql = "SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime BETWEEN '2006-02-02 01:00:00' AND '2007-02-02 01:00:00' LIMIT 0, 30";
$result = mysql_query($sql);

// Open the file for writing
$fh = fopen('temp/temp.txt', "w");

// Create a header row in the file
fwrite($fh, "Source\tID\t\n");

// Loop through the results, writing to the file
/*while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
print_r($row);
  fwrite($fh, $row['Originating_TG'] . "\t\t\t" . $row['ID'] . "\n");
}*/
foreach($result as $results){
    $data .= $results['Originating_TG'] . "\t" . $results['ID'] . "\t\n";
}
// Close the file
fclose($fh);



fwrite($fh, $data);
?>[/code]

It writes the file with the heading row but the other rows are not working...I even tried your suggestion bear for the while statement that didn't work, whats wrong about this, the query does go through

[corbin]

$result = mysql_query($sql);

Thats your problem... Change it to something like
$q = mysql_query($sql);
$result = mysql_fetch_assoc($q);

Then try it.

Do you absolutely have to save it to a temp file... you could just create the files and then send them to the client then when the php script finished the file would be gone...

[Asheeown]

The client will be downloading these files and never using the one on the site again...why should this be a set and done file?

[Asheeown]

// Select info from database
$sql = "SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime BETWEEN '2006-02-02 01:00:00' AND '2007-02-02 01:00:00' LIMIT 0, 30";
$q = mysql_query($sql);
$result = mysql_fetch_assoc($q);

// Open the file for writing
$fh = fopen('temp/temp.txt', "w");

// Create a header row in the file
fwrite($fh, "Source\tID\t\n");

// Loop through the results, writing to the file
/*while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
print_r($row);
  fwrite($fh, $row['Originating_TG'] . "\t\t\t" . $row['ID'] . "\n");
}*/
foreach($result as $results){
    $data .= $results['Originating_TG'] . "\t" . $results['ID'] . "\t\n";
}
// Close the file
fclose($fh);

fwrite($fh, $data);

[HuggieBear]

Fear, it probably doesn't like concatenation in fwrite().

Try this for the while() loop...

[code]<?php
// Loop through the results, writing to the file
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $data = $row['col1'] . "\t\t\t" . $row['col2'] . "\n";
  fwrite($fh, $data);
}
?>[/code]

Regards
Huggie

[Asheeown]

[code]<?php
// Select info from database
$sql = "SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime BETWEEN '2006-02-02 01:00:00' AND '2007-02-02 01:00:00' LIMIT 0, 30";
$result = mysql_query($sql);

// Open the file for writing
$fh = fopen('temp/temp.txt', "w");

// Create a header row in the file
fwrite($fh, "Source\tID\t\n");

// Loop through the results, writing to the file
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  fwrite($fh, $row['Originating_TG'] . "\t" . $row['ID'] . "\t\n");
}
// Close the file
fclose($fh);


?>[/code]
Not writing anything more than the heading

[HuggieBear]

You haven't made the change yet...  :D

Look at the while loop I just posted.  I concatenate the string before using it in fwrite().

Regards
Huggie

[Asheeown]

Oh thats my bad I did make the changes I just posted the wrong code...this is the current code:

[code]<?php
// Select info from database
$sql = "SELECT * FROM rated_cdrs WHERE Originating_TG IN (000500,000501) AND UTCTime BETWEEN '2006-02-02 01:00:00' AND '2007-02-02 01:00:00' LIMIT 0, 30";
$result = mysql_query($sql);

// Open the file for writing
$fh = fopen('temp/temp.txt', "w");

// Create a header row in the file
fwrite($fh, "Source\tID\t\n");

// Loop through the results, writing to the file
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $data = $row['Originating_TG'] . "\t" . $row['ID'] . "\n";
  fwrite($fh, $data);
}
// Close the file
fclose($fh);


?>[/code]

[Asheeown]

Wait wow I feel like an idiot I dont even know if I can explain to you guys the mistake I just made but somehow I mixed up servers so the SQL never went through

[Asheeown]

So before I go I want to add a form to my current filter form for the search...how can I get it to just post either the search form or the search and download form at the same time?