Manipulating the data…

OK, last refinement: can we use PHP to actually alter the data, not just retrieve it.

The answer is that it’s trivially easy to tweak what we’ve just done to make that happen. Let us say that the filter.php form will now be used to specify the percentage salary increase to be applied to all records. If I type in ’10′ now, for example, I don’t mean ‘show me records in department 10′, but ‘increase all salaries by 10 percent’. This means the filter.php form doesn’t need to be altered at all… the only change is in the oracle.php form, like so:

<?php
$percent = $_POST['dnum'];
$myupdate = "update scott.emp set sal=sal+sal*".$percent/100;
$oraconn = oci_connect('system', 'password', '//192.168.221.145/lindb');
$doquery = OCIparse($oraconn, $myupdate) or die("Couldn't parse statement.");
OCIexecute($doquery) or die("Couldn't execute statement.");
OCIcommit($oraconn);

$myselect = "select * from scott.emp";
$doquery = OCIparse($oraconn, $myselect) or die("Couldn't parse statement.");
OCIexecute($doquery) or die("Couldn't execute statement.");

print 'Staff Salaries.'<hr />';
print '<table border=1>';
while (OCIfetch($doquery))
 {$surname = OCIresult($doquery, ENAME);
  $salval = OCIresult($doquery, SAL);
  print '<tr></tr><td>'.$surname.'</td><td align="right">'.$salval.'</td></tr>';
 }
print '</tr></table>'; 

?>

In Line 1, I’ve renamed the variable returned by the filter form. It’s still passed as “dnum”, but I now reference it from this point as “$percent”.

In a similar fashion, I’ve altered line 2: the variable is not called $myselect any more, because it’s not actually a select statement! So I’ve called it $myupdate instead. The names are really immaterial as far as functionality is concerned, but if it’s not selecting data, I feel the variable name shouldn’t imply that it is! Obviously, the rest of line 2 constructs a syntactically-correct update statement that uses the value of the $percent to alter the SAL column values.

The next three lines are unchanged, but the line after them is new: it’s the way you get PHP to commit an insert, update or delete statement. If it wasn’t there, the query we’re about to do wouldn’t display the updated salaries. You’d have to refresh the browser for that. With a commit in place, however, the query will see the updated values.

The rest of the code should be familiar to you: it sets up a new $myselect variable as being a select of all names and salaries from EMP. It then submits that query and displays the results as before. The line giving the title of the report has been modified slightly (because the report once again shows all salaries, not just one department’s), but otherwise it’s all the same as before. So when you now call filter.php and submit a value of 10, you should end up seeing this:

…which indeed shows a 10% increase on previous salaries. Keep hitting your browser’s refresh button (which means the 10% increase is repeatedly applied) and you’ll keep seeing salaries go up.