$curs1 = ocinewcursor($link);
$d_query = OCIParse($link, "BEGIN PKG_CMT_PROCS.GetAllCoDepartments($GOrgID,:po_refcur);END;");
OCIbindByName($d_query, "po_refcur", &$curs1, -1, OCI_B_CURSOR);
$d_result = ociexecute($d_query );
ociexecute($curs1);
           
$siNumberOfDepts = 0;
while (ocifetch($curs1))
{
$arDepartments[] = ociresult($curs1,2);
$siNumberOfDepts = $siNumberOfDepts + 1;
}

$curs = ocinewcursor($link);
$query = OCIParse($link, "BEGIN PKG_CMT_PROCS.GetAllCommissions($GOrgID,:po_refcur);END;");
OCIbindByName($query, "po_refcur", &$curs, -1, OCI_B_CURSOR);
$result = ociexecute($query);
ociexecute($curs);
           
?>
<form name="commission_form"
           method="POST"
           action="commission_change.php">
<table border="1">
<?
// -----------------------------Header Row
//            echo "<br>Header Row<br>";
$html = "<tr style='font-weight:bold;' align='center'>";
for ($siField = 2; $siField < ocinumcols($curs); $siField++)
{        
$html = $html."<td>".ocicolumnname($curs, $siField)."</td>";    
}
$html = $html."<td colspan='2'>Action</td></tr>";
echo $html;
// -----------------------------Output Data Row
//            echo "<br>Data Row<br>";
           $html = "";
           while (ocifetch($curs))    
           {
                 $html = $html."<tr>";
                 for ($siField = 2; $siField < ocinumcols($curs); $siField++)
                 {        
                       if ($siField == 4)
                       {            //list departments to choose from
                             $html = $html."<td align='center'>";
                             $html = $html."<select name='".ocicolumnname($curs, $siField).ociresult($curs,1)."'>";
                             if ($siNumberOfDepts == 0)
                             {
                                   $html = $html."<option value='Unknown'>Unknown</option>";
                             }
                             else
                             {
                                   for ($siDept = 0; $siDept < $siNumberOfDepts; $siDept++)
                                   {
                                         $html = $html."<option ";
                                         if ($arDepartments[$siDept] == ociresult($curs, $siField))
                                         {
                                               $html = $html."selected ";
                                         }
                                         $html = $html."value=\"".$arDepartments[$siDept]."\">".$arDepartments[$siDept]."</option>";
                                   }
                             }
                             $html = $html."</select></td>";    
                       }
                       else
                       {
                             $html = $html."<td><input type='text' name='".ocicolumnname($curs, $siField).ociresult($curs,1)."' value='".ociresult($curs, $siField)."'></td>";    
                       }
                 }
                 $html = $html."<td><input type='button' value='Change' onClick=\"Change(document.commission_form,'".ociresult($curs,1)."');\"></td>";
                 $html = $html."<td><input type='button' value='Remove' onClick=\"document.commission_form.cid.value='".ociresult($curs,1)."';document.commission_form.todo.value='d';document.commission_form.submit();\"></td>";
                 $html = $html."</tr>";      
           }
           echo $html;
//             -----------------------------Spacer row
//            echo "<br>Spacer Row<br>";
           $html = "";
           $html = $html."<tr><td colspan='".(ocinumcols($curs)+2)."' height='40'> </td></tr>";
           $html = $html."<tr>";
           echo $html;
           
                 
// -----------------------------Header Row Again 2nd One
     $html = "<tr style='font-weight:bold;' align='center'>";
       for ($siField = 2; $siField < ocinumcols($curs); $siField++)
           {        
                 $html = $html."<td>".ocicolumnname($curs, $siField)."</td>";    
           }
           $html = $html."<td colspan='2'>Action</td></tr>";
           echo $html;
           
// -----------------------------Row for Adding New Records 2nd time
//            echo "<br>Adder Row<br>";
           $html = "";
           for ($siField=2; $siField < ocinumcols($curs); $siField++)
           {        
                 if ($siField==4)
                 {            //list departments to choose from
                       $html = $html."<td align='center'>";
                       $html = $html."<select name='".ocicolumnname($curs, $siField)."0'>";
                       if ($siNumberOfDepts == 0)
                       {
                             $html = $html."<option value='Unknown'>Unknown</option>";
                       }
                       else
                       {
                             for ($siDept = 0; $siDept < $siNumberOfDepts; $siDept++)
                             {
                                   $html = $html."<option value=\"".$arDepartments[$siDept]."\">".$arDepartments[$siDept]."</option>";
                             }
                       }
                       $html = $html."</select></td>";    
                 }
                 else
                 {
                       $html = $html."<td><input type='text' name='".ocicolumnname($curs, $siField)."0' value=''></td>";    
                 }
           }
           $html = $html."<td colspan='2' align='center'><input type='button' value='Add New' onClick=\"Add(document.commission_form);\"></td>";
           $html = $html."</tr>";  
           echo $html;

?>


How to make it work for Header Row.
The first Header row is working but not the second one. Please help me.

Thanks
Pagal Reddy

If you can cut your example down to less than 20 lines of code, that would be a huge help .... as it is, it would take a long time for me to work my way into this.

From the FAQ:

Code:

1. Please provide short pieces of code to demonstrate your question.  It's great if the code is less that 10 lines long, and if it's over 30 lines long chances are it will be hard for us to help.

here is the short version of the code. Thanks for all the help.


ociexecute($curs);
 

// -----------------------------Header Row One

$html = "<tr style='font-weight:bold;' align='center'>";
  for ($siField = 2; $siField < ocinumcols($curs); $siField++)
 {    
  $html = $html."<td>".ocicolumnname($curs, $siField)."</td>";      
 }
 $html = $html."<td colspan='2'>Action</td></tr>";
 echo $html;  

// -----------------------------Output Data Row

 $html = "";
 while (ocifetch($curs))    
 {
  $html = $html."<tr>";
  for ($siField = 2; $siField < ocinumcols($curs); $siField++)
  {    
All the coding to display the Rows.
    echo $html;  
   

The First above 2 are working fine, But for some reason the second is not picking the Curs.
// -----------------------------Header Row Again 2nd One
$html = "<tr style='font-weight:bold;' align='center'>";
  for ($siField = 2; $siField < ocinumcols($curs); $siField++)
 {    
  $html = $html."<td>".ocicolumnname($curs, $siField)."</td>";      
 }
 $html = $html."<td colspan='2'>Action</td></tr>";
 echo $html;  
 
// -----------------------------Row for Adding New Records 2nd time
//  echo "<br>Adder Row<br>";
 $html = "";
 for ($siField=2; $siField < ocinumcols($curs); $siField++)
     
How to make it work for Header Row.
The first Header row is working but not the second one. Please help me.

Thanks
Pagal Reddy

A cursor is, I think, like an iterator or a tokenizer (or a railway train) - once it's gone, it is gone. You read from it using your ocifetch and it gives you the first row.   Then you call it again and get the second row, and so on.  Although each call is the same, you get different (always the next) results.

In order to end the process, you exit your while loop when ocifetch has hit the end and has nothing else to return - it's telling you that the last railway train of the day has gone and, tough, you can't go back and catch an earlier one.   Keep asking (by trying another loop) and it'll keep telling you that there are no more trains ....

Quick solution - repeat the ociexecute and you can start another day, iterating through all the trains, displaying headers, and so on.

Two issues with doing another ociexecute:
a) It may not be efficient (but if your data isn't big, that's not usually a problem)
b) The data may have changed by other user's queries between the two passes through, giving an inconsistent display.

A better approach would be to pass through the data just once, and as you do so to use the "." operator to generate two result strings you can echo or print later in your script.  That way, you're down to a single query.   This is also good coding practise as it starts to unmix the confusing morass of PHP and HTML and the separation of design and business logic.

Yes, If I execute Twice , It displaying the Coulmn names in the Second stage and also Its getting the Drop down box from Database.
But for some reason, Iam not able to add, Update, Delete Data. It keeps Saying an Error On Page. How to Overcome with it ?

on 12/01/05 at 15:24:58, pagalreddy wrote:

But for some reason, Iam not able to add, Update, Delete Data. It keeps Saying an Error On Page. How to Overcome with it ?

I don't see any add / update / delete code - at least in the shorter example.  Can you post a short demonstration piece that shows the problem, and also tell us exactly what the error message is and what the SQL command being performed is?

Here is the code to Update, Delete and Insert.
For Example, Its giving me an Error Near update and Delete for $cn = "CommissionName".$_POST['rcid'];
           $ce= "Email".$_POST['rcid'];
           $cd= "Department".$_POST['rcid'];
           $cmin= "Mins".$_POST['rcid'];
           $cmax= "Maxs".$_POST['rcid'];



<?
$Gbgcolor = $_SESSION['Gbgcolor'];
$Gfontcolor = $_SESSION['Gfontcolor'];
$GOrgID = $_SESSION['OrgID'];
?>
<body bgcolor = "#<?=$Gbgcolor?>" text="#<?=$Gfontcolor?>">
<!------------  Script By Anjani Reddy for WebMD   April, 2004     ---->
<center>
<h2>Commissions</h2>
<?
 // Check if if page comes from itself
     if (isset($_POST['todo']))
     {                        // if so, get the values for u & d
           $cn = "CommissionName".$_POST['rcid'];
           $ce= "Email".$_POST['rcid'];
           $cd= "Department".$_POST['rcid'];
           $cmin= "Mins".$_POST['rcid'];
           $cmax= "Maxs".$_POST['rcid'];
// -----------------------------Change/Update Action
           if ($_POST['todo']=="u")
           {      
                 $query =  OCIParse($link, "BEGIN PKG_CMT_PROCS_MAINT.UpdateCommission ($GOrgID , '".$_POST['rcid']."', '".$_POST[$cn]."', '".$_POST[$ce]."', '".$_POST[$cd]."', ".$_POST[$cmin].", ".$_POST[$cmax].");END;");
                 $message = "Updated";      
           }
// -----------------------------//Delete Action
           elseif($_POST['todo']=="d")
           {
                 $query =  OCIParse($link, "BEGIN PKG_CMT_PROCS_MAINT.DeleteCommission($GOrgID , ".$_POST['rcid'].");END;");
                 $message = "Deleted";
           }
// -----------------------------//Insert/Add Action
           elseif($_POST['todo']=="i")
           {
                 /*$cn = "CommissionName0"; //insertion values
                 $ce = "Email0";  //insertion values
                 $cd = "Department0";  //insertion values
                 $cmin = "Mins0";  //insertion values
                 $cmax = "Maxs0";  //insertion values*/
                 $query =  OCIParse($link, "BEGIN PKG_CMT_PROCS_MAINT.InsertCommission($GOrgID , '".$_POST[$cn]."', '".$_POST[$ce]."', '".$_POST[$cd]."', ".$_POST[$cmin].", ".$_POST[$cmax].");END;");
                 $message = "Added";
           }
// -----------------------------do the action    
           ociexecute($query);
           echo "Commission <b>".$_POST[$cn]."</b> was <i><u>".$message."</u></i> Successfully<br>";
     }
     if ($_SESSION['maintenance'] == "true")
     {
// -----------------------------Get department list (just once !)
           //$d_query = $CDatabaseOwner."GetAllCoDepartments $GOrgID ";
           
           $curs1 = ocinewcursor($link);
           $d_query = OCIParse($link, "BEGIN PKG_CMT_PROCS.GetAllCoDepartments($GOrgID,:po_refcur);END;");
             OCIbindByName($d_query, "po_refcur", &$curs1, -1, OCI_B_CURSOR);
     $d_result = ociexecute($d_query );
     ociexecute($curs1);
           
           $siNumberOfDepts = 0;
           while (ocifetch($curs1))
           {
                 $arDepartments[] = ociresult($curs1,2);
                 $siNumberOfDepts = $siNumberOfDepts + 1;
           }
// -----------------------------ok. Now the normal data presentation
           //$query = $CDatabaseOwner."GetAllCommissions $GOrgID";
           $curs = ocinewcursor($link);
           $query = OCIParse($link, "BEGIN PKG_CMT_PROCS.GetAllCommissions($GOrgID,:po_refcur);END;");
             OCIbindByName($query, "po_refcur", &$curs, -1, OCI_B_CURSOR);
     $result = ociexecute($query);
     ociexecute($curs);
           
?>
<form name="commission_form"
           method="POST"
           action="commission_change.php">
<table border="1">
<?
// -----------------------------Header Row
The Remainign Previous Code

on 11/30/05 at 18:26:40, Graham Ellis wrote:

If you can cut your example down to less than 20 lines of code, that would be a huge help .... as it is, it would take a long time for me to work my way into this. From the FAQ: Code: 1. Please provide short pieces of code to demonstrate your question.  It's great if the code is less that 10 lines long, and if it's over 30 lines long chances are it will be hard for us to help.

I find it really hard to debug large chunks of code ... your latest "cut down" post is over 80 lines long.  I would have a sporting chance (it would be great) if you could show me the problem in 10 lines ....

<!------------  Script By Anjani Reddy for WebMD   April, 2004     ---->
<center>
<h2>Commissions</h2>
<?
 // Check if if page comes from itself
     if (isset($_POST['todo']))
     {                        // if so, get the values for u & d
           $cn = "CommissionName".$_POST['rcid'];
           $ce= "Email".$_POST['rcid'];
           $cd= "Department".$_POST['rcid'];
           $cmin= "Mins".$_POST['rcid'];
           $cmax= "Maxs".$_POST['rcid'];
// -----------------------------Change/Update Action
           if ($_POST['todo']=="u")
           {      
                 $query =  OCIParse($link, "BEGIN PKG_CMT_PROCS_MAINT.UpdateCommission ($GOrgID , '".$_POST['rcid']."', '".$_POST[$cn]."', '".$_POST[$ce]."', '".$_POST[$cd]."', ".$_POST[$cmin].", ".$_POST[$cmax].");END;");
                 $message = "Updated";      
Delete----Code
Insert-----Code
     
           ociexecute($query);
                             

// -----------------------------Row for Adding New Records

$html = "";
           
           for ($siField=2; $siField < ocinumcols($curs);$siField++)
           {        
                 if ($siField==4)
                 {            //list departments to choose from
                       $html = $html."<td align='center'>";
                       $html = $html."<select name='".ocicolumnname($curs, $siField)."0'>";
                       if ($siNumberOfDepts == 0)
                       {
                             $html = $html."<option value='Unknown'>Unknown</option>";
                       }
                       else
                       {
                             for ($siDept = 0; $siDept < $siNumberOfDepts; $siDept++)
                             {
                                   $html = $html."<option value=\"".$arDepartments[$siDept]."\">".$arDepartments[$siDept]."</option>";
                             }
                       }
                       $html = $html."</select></td>";    
                 }
                 else
                 {
                       $html = $html."<td><input type='text' name='".ocicolumnname($curs, $siField)."0' value=''></td>";    
                 }
           }
   $html = $html."<td colspan='2' align='center'><input type='button' value='Add New' onClick=\"document.commission_form.todo.value='i';document.commission_form.submit();\"></td>";
           $html = $html."</tr>";  
           echo $html;
           


Here is the code
After Update, I have an delete and Insert but I cannot update nor Insert

on 12/02/05 at 16:33:28, Graham Ellis wrote:

I would have a sporting chance (it would be great) if you could show me the problem in 10 lines ....

... but I really have no chance at all if it's 60 lines long.

Our FAQ requests that problems be reduced to 30 lines or less unless there are exceptional circumstances.  Three good reasons for this:

1) As you cut down the problem into a shorter piece of code, you'll probably find what's wrong yourself  

2) We've only got limited time to provide the free support service here, and want to help as many people as possible.  This means we can't afford to spend a lot of time on a long piece of code such as the one reposted above

3) If you can't be bothered to trim your code down so that you can present it as a practical question, then I find it very hard to motivate myself .....

---