1

I am trying to write a page in php with the goal of counting the specified data from the database and showing that data. It seems like a simple enough task, but I cannot get this to work correctly and I'm at the limits of my very limited PHP/SQL knowledge. This is my code for the whole page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Counter Test</title>
</head>

<body>

<div id="counter">

<?php
$advance = "(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xx.xx.xxx)(PORT = 1521)))
    (CONNECT_DATA =
      (SERVICE_NAME = domain.domain)))";

$conn = oci_connect('xxx', 'xxx', xxx);
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
    }

$stid = oci_parse($conn, 'SELECT count(distinct(b.id_number)) as total FROM bio b WHERE (b.alum_memb_type) is not null AND b.record_status_code NOT IN ('D', 'X', 'R')' );
oci_execute($stid);

echo "<table border='1'>\n";
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "    <td>" . ($item !== null ? htmlentities($item, ENT_QUOTES) : "&nbsp;") . "</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";
?>

</div>
</body>
</html>
user3179376
  • 11
  • 2
  • 1
    At first glance, the `'` at the SQL query's `IN` statement are not escaped. – Adam Wenger Jan 09 '14 at 21:14
  • You've got a very basic PHP syntax error in your `oci_parse()` line - your SQL data is using `'`, the same quotes that your PHP is using to delimit the query. – Marc B Jan 09 '14 at 21:18
  • thank you all for the comments so far. I have removed the `'` now and that removes the syntax error that I was getting. but when I open the page in browser, it doesn't load the php at all. the code stops when it gets to the php. I have in on a server with php installed. – user3179376 Jan 09 '14 at 21:39

1 Answers1

0

You can expect one result back, so don't have to fetch in a loop.

oci_execute($stid);

$row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS);

$count = $row['total'];
crafter
  • 6,246
  • 1
  • 34
  • 46
  • ok i made that change to the code. It's still not running the php and this is the error that I get on it: Fatal error: Call to undefined function oci_connect() in /Applications/MAMP/htdocs/olemissalumni-test/wp-content/themes/news/counter-test-template.php on line 25 Again, I'm not much of a troubleshooter for php so i'm not sure what this means – user3179376 Jan 09 '14 at 21:58
  • Then you should have taken the time to answer @John Conde's original comment : 'What doesn't work?' – crafter Jan 09 '14 at 22:03
  • yes i'm sorry. i didn't see that at the time. and i just added in the code for the php error log. – user3179376 Jan 09 '14 at 22:04
  • See if this helps : http://stackoverflow.com/questions/8635881/fatal-error-call-to-undefined-function-oci-connect Run a phpinfo() as well to verify the oracle libaries are loaded. – crafter Jan 09 '14 at 22:06
  • ok i moved this over to my web server and now my oracle server seems to be connecting this the error message i'm getting now: Warning: oci_execute(): ORA-00904: "R": invalid identifier in C:\inetpub\wwwroot\olemissalumni\wp-content\themes\news\counter-test-template.php on line 32 Warning: oci_fetch_array(): ORA-24374: define not done before fetch or execute and fetch in C:\inetpub\wwwroot\olemissalumni\wp-content\themes\news\counter-test-template.php on line 35 – user3179376 Jan 09 '14 at 22:21