• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Php script to query db and save results to a text file for the user

Good morning.  I'm just learning PHP, so forgive the basic question...

I have a user that needs to get information from our system and into a flat file so he can then import it over to another DB (Going from Oracle here to Access there so he can manipulate it)

I have created a view in Oracle, then done a basic select in PHP and inserting a pipe(|) character as a separator before and after each item so he can use that as a separator on the import..

Currently this does work and process to the screen (I put a limiter in on my select just for a test) - I dont need it to come back to the screen, ideally it would prompt the user to download the file and let him select a name/location as usual and then he can do whatever he likes with it...  How can I get it from going to the regular output on the screen like it is currently to having it ask him how/where to save it and rendering it as a text file?

Thanks.
<?php
require("dbconn.php");

$sql = "select * from myView where rownum < 1000";


$result =$myConn->query($sql);
if(DB::isError($result))
die ("Select Failed!: " .$result->getMessage(). "\n");
echo "There are: <i> " . $result->numRows(). " </i>number of active files in the query.<br>";

while($row =& $result->fetchRow(DB_FETCHMODE_ASSOC))
{



echo "|".$row['firstname']."|"; 
echo "|".$row['lastname']."|";
echo "|".$row['dob']."|";
echo "|".$row['ssno']."|";
echo "<br>";

}
?>

Open in new window

0
hej613
Asked:
hej613
  • 4
  • 2
1 Solution
 
darren-w-Commented:
You need to write it to a temporarily cache and open it as a file, this example:

<?php
function utf8_fopen_read($fileName) {
    $fc = iconv('windows-1250', 'utf-8', file_get_contents($fileName));
    $handle=fopen("php://memory", "rw");
    fwrite($handle, $fc);
    fseek($handle, 0);
    return $handle;
}
?
on this page:

http://php.net/manual/en/function.fopen.php

is a good starter, you can substitute you generated content into this line:  'file_get_contents($fileName);'


0
 
hej613Author Commented:
So- I can replace the file_get_contents with the result set of the query?
0
 
darren-w-Commented:
try this:
<?php

require("dbconn.php");

$sql = "select * from myView where rownum < 1000";


$result = $myConn->query($sql);
if (DB::isError($result))
    die("Select Failed!: " . $result->getMessage() . "\n");
echo "There are: <i> " . $result->numRows() . " </i>number of active files in the query.<br>";
$buffer = fopen('php://temp', 'r+');
while ($row = & $result->fetchRow(DB_FETCHMODE_ASSOC)) {


    fputcsv($fp, array($row['firstname'], $row['lastname'], $row['dob'], $row['ssno']), "|");
}
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=afile.csv');
$fp = fopen($buffer, 'w');
?>

Open in new window


not tested
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
darren-w-Commented:
slight amendment to line 16:

    fputcsv($buffer, array($row['firstname'], $row['lastname'], $row['dob'], $row['ssno']), "|");

Open in new window

0
 
hej613Author Commented:
error trying to open fopen.
Warning: fopen(php://temp): failed to open stream: Success in /path/extract_f.php on line 12

Fatal error: Call to undefined function: fputcsv() in /path/extract_f.php on line 15

Open in new window

0
 
darren-w-Commented:
Sorry,

here is a example with a simple array:

<?php
$rows = array(array('firstname' => "tom", "lastname" => "dick", "dob" => "21/05/16", "ssno" => 4455), array('firstname' => "mike", "lastname" => "jones", "dob" => "21/05/16", "ssno" => 4455));
$filename = 'test.csv';
header('Content-type: application/csv');
header("Content-Disposition: attachment; filename=".$filename);
$output = fopen('php://output', 'w') or die("Cannot open php://output");
foreach ($rows as $row) {
  fputcsv($output, $row, '|');
}
fclose($output);
?>

so you just need to substituent my array for you db query output.

Open in new window

0
 
Ray PaseurCommented:
Whoa... Something is seriously wrong if you get a fatal error on fputcsv().  See the man page:
http://php.net/manual/en/function.fputcsv.php

That would seem to indicate that the PHP installation is very, very old and probably unsupported.  Suggest you follow the guidance on php.net which says, "All PHP users should note that the PHP 5.2 series is NOT supported anymore. All users are strongly encouraged to upgrade to PHP 5.3.8."
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now