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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you so much for sending me in the right direction. I've re-written the first part of the script using PDO statements and it works great.

Catherine

Richard Quadling wrote:
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.

--
Catherine Madsen

Programmer Analyst
College of Chemistry
Berkeley, CA 94720-1460

TEL: 510-643-1706
FAX: 510-643-6178


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux