Re: can't retrieve more than 1 record at the time still

2009/12/14 Catherine Madsen <cmadsen@berkeley.edu>: > 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