2009/12/14 Catherine Madsen <cmadsen@xxxxxxxxxxxx>:
> Hi Again,
>
> Following the suggestions I received from a earlier post, I've closed the
> statement
> while ($stmt->fetch(PDO::FETCH_BOUND))
> before $stmt = NULL;
> I don't get an error anymore, but still retrieve only one record.
>
> To check that the array was being filled correctly, I inserted in several
> places $ncand = count($_SESSION['numberCand']);
> $ndocid = count($_SESSION['DOCIDs']);
> and the count is always 0 for $ndocid and 1 for $ncand.
> Now I believe that the array is not being filled, and that's why only the
> last record gets processed. Can anybody see what I'm doing wrong in the for
> each statement? The updated code is below.
> Thank you for your help.
> Catherine
>
>
> Earlier message:
> I'm really in need of help. I'm not a PHP programmer, but I've been given
> the privilege of customizing a script written by somebody else and can't get
> it to work right. I have to query 2 different tables in 2 different Oracle
> 10G DBs, one's mine (my_schema.my_table), the other (otherdb.other_table)
> belongs to another department. In my_table, I have the doc_id and the
> app_id for a record. In other_table there's the doc_id and pdf to
> retrieve. The goal is to make a PDF file from each BLOB in other_table and
> store them in the right directory under hash(app_id). PDO has been
> installed and working, and I can connect to both DBs without a problem. If
> my query limits the retrieval to one record, the script works, but if I try
> to run it for all records, one pdf file is correctly created in the right
> directory then I get the following error: PHP Fatal error: Call to a
> member function fetch() on a non-object in /my_location/my_script.php on
> line 154. It the "while ($stmt->fetch(PDO::FETCH_BOUND))" line. I've
> pasted my script below. I thought my problem was that maybe I was in the
> wrong directory after creation of the first pdf, but several tries changing
> the directory didn't make a difference. Right now, I'm running the script
> at the command line. Soon I'm going to have a few hundred records to deal
> with and cannot do it one by one! Any help would be very much appreciated.
> Thank you!
>
> <?php
>
>
> /* First Oracle DB connection info removed */
>
> try
> {
> $dbh1 = new PDO("oci:dbname=".$tns1,$db1_username,$db1_password);
>
> }
>
> catch(PDOException $e)
> {
> echo ($e->getMessage());
> exit;
> }
>
>
> if (empty($_SESSION['docIDs']))
> {
> $_SESSION['DOCIDs'] = array();
> $_SESSION['msgs'] = array();
>
> $sql = "SELECT COUNT(*) all_rec FROM myschema.mytable
> where academic_year = 2010";
>
> $_SESSION['numberCand'] = 0; /* initialize ctr for stack popping */
> $_SESSION['PHPulled'] = 0; /* number new personal histories pulled */
>
> if ($res = $dbh1->query($sql))
>
> {
>
> /* Check the number of rows that match the SELECT statement */
> if ($res->fetchColumn() > 0)
> {
> /* Issue the real SELECT statement and work with the
> results */
> $sql = "select doc_id, app_id
> from myschema.mytable
> where academic_year = 2010";
>
> foreach ($dbh1->query($sql) as $row)
> {
>
> $cand = array();
> $cand['DOC_ID']= $row['DOC_ID'];
> $cand['APP_ID'] = $row['APP_ID'];
> $_SESSION['DOC_IDS'][] = $cand;
>
> }
> }
> $ncand = count($_SESSION['numberCand']);
> $ndocid = count($_SESSION['DOCIDs']);
> print "Cand Number: " . $ncand . "\n";
> print "DOCid Number: " . $ndocid . "\n";
> }
> }
>
> if (!empty($_SESSION['DOC_IDS']) and $_SESSION['numberCand'] <
> count($_SESSION['DOC_IDS']))
> {
> $ncand = count($_SESSION['numberCand']);
> $ndocid = count($_SESSION['DOCIDs']);
> print "Cand Number: " . $ncand . "\n";
> print "DOCid Number: " . $ndocid . "\n";
>
> /* if have doc_IDs, pick next one off array */
> $cand = $_SESSION['DOC_IDS'][$_SESSION['numberCand']];
>
> if ($cand['DOC_ID'] == 0)
> { /* redirect to self to get next candidate with updated numberCand */
> $_SESSION['numberCand'] = $_SESSION['numberCand'] + 1;
> exit;
> }
>
> $dirname = md5($cand['APP_ID']);
> $curdir = '/my_location/'.$dirname;
>
> print "App_id/Dir: " . $row['APP_ID'] . $curdir . "\n";
>
> if (!(is_dir($curdir)))
>
> {
> if (!mkdir($curdir,0775))
> print "error: " . $curdir . "\n";
> exit;
> }
>
> /* Second Oracle DB connection info removed */
>
> try
> {
> $dbh2 = new
> PDO("oci:dbname=".$tns2,$db2_username,$db2_password);
>
> }
>
> catch(PDOException $e)
> {
> echo ($e->getMessage());
> exit;
> }
>
>
> $stmt = $dbh2->prepare('select PERSONAL_HIST_PDF_CONTENT from
> otherdb.other_table
> where DOC_ID = :id');
>
> $stmt->bindParam(':id', $cand['DOC_ID'], PDO::PARAM_INT);
> $stmt->bindColumn(1, $PDF, PDO::PARAM_LOB);
>
> $stmt->execute();
>
> if (!(chdir($curdir)))
> {
> $_SESSION['msgs'][] = 'Could not change to '.$curdir;
> continue;
> }
>
> $cnt = 0;
>
> while ($stmt->fetch(PDO::FETCH_BOUND))
> {
> $filename = 'phs-'.$cnt.'.pdf';
>
> if (!file_exists($filename))
> { /* if file not already there, write file with BLOB
> contents */
> $fh = fopen($filename, 'w');
> fwrite($fh, stream_get_contents($PDF));
> fclose($fh);
>
> /* add to $_SESSION['PHPulled'] for each new
> file saved */
> $_SESSION['PHPulled'] = $_SESSION['PHPulled']
> + 1;
> }
> }
>
> /* increment stack counter */
> $_SESSION['numberCand'] = $_SESSION['numberCand'] + 1;
>
> $stmt = NULL; /* release the connection */
>
> /*if not done with stack, redirect to self to get next*/
> if (!empty($_SESSION['DOCIDs']) and
> $_SESSION['numberCand'] < count($_SESSION['DOCIDs']))
> {
> exit;
>
> }
>
> }
>
> /* once done, go back to display search page after clearing stack*/
> if(isset($_SESSION['DOCIDs']))
> unset($_SESSION['DOCIDs'] );
>
> $_SESSION['msgs'][] = 'For '.$_SESSION['numberCand'].' candidate(s), '.
> $_SESSION['PHPulled'].' personal histories were loaded.';
> exit;
>
> $res = null;
> $dbh1 = null;
>
> ?>
>
> --
> Catherine Madsen
>
> Programmer Analyst
> College of Chemistry
> Berkeley, CA 94720-1460
>
> TEL: 510-643-1706
> FAX: 510-643-6178
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
The PDOStatement->fetchAll() method will allow you to return all the
rows as an array.
http://docs.php.net/manual/en/pdostatement.fetchall.php
So, no need to iterate the result set.
Something along the lines of ...
<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
/* Fetch all of the remaining rows in the result set */
print("Fetch all of the remaining rows in the result set:\n");
$result = $sth->fetchAll();
print_r($result);
?>
for example.
--
-----
Richard Quadling
"Standing on the shoulders of some very clever giants!"
EE : http://www.experts-exchange.com/M_248814.html
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
ZOPA : http://uk.zopa.com/member/RQuadling
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
