使用oracle 10g和php在单个数据库连接中执行多个查询

Question

I want to run multiple sql queries in a single database connection using oracle 10g and php. Here for every sql query queries I have to create database connection. Is there any way to run multiple sql queries in a single database connection or we have to fetch data this way only? Because when we have to run 50 queries, we have to write 50 times like below.

<?php
include("mydb.php");
// run query

$sql6 = "select * from dat where to_char(WD,'dd/mm')='19/08'";
$stid6=oci_parse($conn, $sql6);
// set array
$arr6 = array();
if(!$stid6){
$e=oci_error($conn);
trigger_error(htmlentities($e[message],ENT_QUOTES),E_USER_ERROR);
}

$r6=oci_execute($stid6);

if(!$r6){
$e=oci_error($stid6);
trigger_error(htmlentities($e[message],ENT_QUOTES),E_USER_ERROR);
}

// look through query
while($row = oci_fetch_array($stid6,OCI_ASSOC)){

  // add each row returned into an array
  $arr6[] = array(($row['WD']) , (float)$row['DATA']);

}

oci_free_statement($stid6);
oci_close($conn);
?>

<?php
include("mydb.php");
// run query

$sql7 = "select * from dat where to_char(WD,'dd/mm')='11/03'";
$stid7 = oci_parse($conn, $sql7);
// set array
$arr7 = array();
if(!$stid7){
$e=oci_error($conn);
trigger_error(htmlentities($e[message],ENT_QUOTES),E_USER_ERROR);
}

$r7=oci_execute($stid7);

if(!$r7){
$e=oci_error($stid7);
trigger_error(htmlentities($e[message],ENT_QUOTES),E_USER_ERROR);
}

// look through query
while($row = oci_fetch_array($stid7,OCI_ASSOC)){

  // add each row returned into an array
  $arr7[] = array(($row['WD'])) , (float)$row['DATA']);

}


oci_free_statement($stid7);
oci_close($conn);
?>
................
................

*Pardon me, I forgot to mention that we have store day-wise data in different array. I mean to say that 11/03's data will store in arr1 and 19/08's data will be stored in arr2. Not in same array.

Answer 1

(this should be a comment, but its a bit long)

I don't want to be disparaging here, but your question is alarming naive - so much so that it should be closed as off-topic.

Your code exhibits a lack of understanding about modular programming and variable scope. These should be covered by day 2 of a programming-from-scratch course. but oddly includes some more sophisticated PHP specific programming, but apalling SQL - it looks like someone else wrote the code as a quick hack and now you are trying to extend its capabilities.

That you are using an Oracle database, raises all sorts of questions about why are attempting this (Oracle is expensive; how can someone afford that but not afford to provision you with the skills you need?)

The solution to the problem as you have described it is to re-implement the script as a function that takes the OCI connection and SQL statement as parameters, then simply....

<?php
include("mydb.php");

$queries=array(
    "select * from dat where to_char(WD,'dd/mm')='11/03'",
    "select * from dat where to_char(WD,'dd/mm')='19/08'"
);
foreach ($queries as $sql) {
   run_qry($sql, $conn);
}
oci_close($conn);
exit;

function run_query($sql, $conn)
{
   $stid=oci_parse($conn, $sql);
   // set array
   $arr = array();
   if(!$stid){
   $e=oci_error($conn);
      trigger_error(htmlentities($e[message],ENT_QUOTES),E_USER_ERROR);
   }
   ...
   oci_free_statement($stid);
   return $arr;
}

However since the 2 example queries have exactly the same structure, there are other ways to the results of what are multiple queries - merging the SQL states into a single select using OR or UNION, using parameterized queries. Since the code you've shown us simply throws away the results its hard to say how you should approach the task.