Home » Open Source » Programming Interfaces » Displaying Information based on entered value in PHP with Oracle Database 11g R2 (Oracle 11g R2 version(11.2.0.1), PHP)
icon5.gif  Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663238] Fri, 26 May 2017 08:01 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I have a simple form (main.php) which takes input as a Phone no. of the customer:
    <!DOCTYPE HTML>
   <html>
      <div style=margin:0 auto align=center >
       <form action = "options.php" method = "get" />
          <p> <h3>Enter Phone Number:</h3> <input type = "text" name = 
                                    "cust_phone" />
          <p> <input type = "submit" value = "Submit" />
       </form>
      </div>
   </html>
The no. entered is checked in the Oracle DB and if a customer is present with the no. then information regarding that customer is displayed else a new customer is added with the phone no. (options.php)
     <!DOCTYPE HTML>
    <html>
      <body> Details of:<?php echo htmlentities($_GET["cust_phone"])."<br>";

     $link = oci_connect('hd', 'hd', 'localhost/mydb');
     if(!$link)
        {       
            $e = oci_error();
            exit('Connection Error' . $e['message']);
        }
    $query = "select cust_id from customer where cust_phone = :ph_bv";
    $stid = oci_parse($link,$query);
    $ph = htmlentities($_GET["cust_phone"]);
    oci_bind_by_name($stid, ':ph_bv', $ph);
    oci_execute($stid);
    $row = oci_fetch_array($stid, OCI_ASSOC);
    if(!$row)
    {
        exit("Person Not Found");
    }
    $cust_id = $row["ID"];
    oci_free_statement($stid);
?>
    <table border = "black" />
        <tr> 
            <th> ADDRESS </th>
            <th> AREA </th>
        </tr>
    <?php
        $query1 = "select a.address, a.area from customer c 
              join customer_address ca on c.cust_id = ca.cust_id
              join address a on a.address_id = ca.address_id where cust_id = :id_bv";
        $stid1 = oci_parse($link, $query1);
        oci_bind_by_name($stid1, ":id_bv", $cust_id);
        oci_execute($stid1);
            while($row = oci_fetch_array($stid1))
            {
              echo "<tr><td>" . htmlentities($row["ADRESS"]) . "</td>";
              echo "<td>" . htmlentities($row["AREA"]) . "</td></tr>";
            }
             oci_free_statement($stid1);
             oci_close($link);
         ?>
         </table>
          </body>
        </html>
The first part of the code works fine and it displays the message "Person Not Found". However the second part gives error:
Details of: 9711210000

      ( ! ) Notice: Undefined index: ID in 
            E:\xampp\htdocs\myfiles\options.php on line 24
   Call Stack
   #    Time    Memory  Function    Location
     1  0.0013  137104  {main}( )   ...\options.php:0

     ADDRESS    AREA
     ( ! ) Warning: oci_execute(): ORA-00918: column ambiguously defined in 
   E:\xampp\htdocs\myfiles\options.php on line 38
  Call Stack
   #    Time    Memory  Function    Location
   1    0.0013  137104  {main}( )   ...\options.php:0
   2    0.0400  139336  oci_execute ( ) ...\options.php:38

   ( ! ) Warning: oci_fetch_array(): ORA-24374: define not done before 
   fetch or execute and fetch in E:\xampp\htdocs\myfiles\options.php on line 
   39
   Call Stack
   #    Time    Memory  Function    Location
   1    0.0013  137104  {main}( )   ...\options.php:0
   2    0.0418  139336  oci_fetch_array ( ) ...\options.php:39
I have two questions:
1. Instead of "person not found", I want to add a new customer in my DB?

2. How to resolve these errors? I am new to PHP and this is just my first code. Any help appreciated.
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663242 is a reply to message #663238] Fri, 26 May 2017 11:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>where cust_id = :id_bv";
line above needs table alias so Oracle knows from which table the column (cust_id) should taken.

BTW, you should always test every SQL statement using sqlplus to validate that it produces the desired result set.
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663254 is a reply to message #663242] Sat, 27 May 2017 02:27 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah this was the only one statement which I did not check. BTW I am still getting the "Undefined INDEX: ID" error.
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663256 is a reply to message #663254] Sat, 27 May 2017 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not tell, copy and paste what you do and get.
And number your lines so we can see which one/s is/are involved.

Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663258 is a reply to message #663256] Sat, 27 May 2017 02:59 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Its the same thing as I posted in the question

( ! ) Notice: Undefined index: ID in
E:\xampp\htdocs\myfiles\options.php on line 24
Call Stack
# Time Memory Function Location
1 0.0013 137104 {main}( ) ...\options.php:0
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663260 is a reply to message #663258] Sat, 27 May 2017 03:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know which line is line 24.
So post EVERYTHING as asked.

Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663261 is a reply to message #663260] Sat, 27 May 2017 03:52 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
1 <!DOCTYPE HTML>
 2    <html>
 3      <body> Details of:<?php echo htmlentities($_POST["cust_phone"])."<br>";
 4 
 5     $link = oci_connect('hd', 'hd', 'localhost/mydb');
 6     if(!$link)
 7        {       
 8            $e = oci_error();
 9            exit('Connection Error' . $e['message']);
 10       }
 11   $query = "select cust_id from customer where cust_phone = :ph_bv";
 12   $stid = oci_parse($link,$query);
 13   $ph = htmlentities($_POST["cust_phone"]);
 14   oci_bind_by_name($stid, ':ph_bv', $ph);
 15   oci_execute($stid);
 16   $row = oci_fetch_array($stid, OCI_ASSOC);
 17   if(!$row)
 18   {
 19       exit("Person Not Found");
 20   }
 21   $cust_id = $row["ID"];
 22   oci_free_statement($stid);
 23
 24   <table border = "black" />
 25       <tr> 
 26           <th> ADDRESS </th>
 27           <th> AREA </th>
 28       </tr>
 29   <?php
 30       $query1 = "select a.address, a.area from customer c 
 31             join customer_address ca on c.cust_id = ca.cust_id
 32             join address a on a.address_id = ca.address_id where c.cust_id = :id_bv";
 33       $stid1 = oci_parse($link, $query1);
 34       oci_bind_by_name($stid1, ":id_bv", $cust_id);
 35       oci_execute($stid1);
 36           while($row = oci_fetch_array($stid1))
 37           {
 38             echo "<tr><td>" . htmlentities($row["ADRESS"]) . "</td>";
 39             echo "<td>" . htmlentities($row["AREA"]) . "</td></tr>";
 40           }
 41            oci_free_statement($stid1);
 42            oci_close($link);
 43        ?>
 44        </table>
 45         </body>
 46       </html>

The error:
Details of:981115000

( ! ) Notice: Undefined index: ID in E:\xampp\htdocs\myfiles\options.php on line 21
Call Stack
# Time Memory Function Location
1 0.0009 138616 {main}( ) ...\options.php:0
ADDRESS AREA

[Updated on: Sat, 27 May 2017 03:58]

Report message to a moderator

Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663262 is a reply to message #663261] Sat, 27 May 2017 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sat, 27 May 2017 10:07

I don't know which line is line 24.
So post EVERYTHING as asked.
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663271 is a reply to message #663262] Sat, 27 May 2017 06:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>$cust_id = $row["ID"];
I suspect that error comes from line above
Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663279 is a reply to message #663271] Sun, 28 May 2017 02:16 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
How do I resolve it? I want to store the result of a query in a variable and use it to find the details of that cust_id. In this case ther will only be one value returned, but what if there are more than one value? How do I store the value in a variable? What is "ID" in $rows["ID"]? Is it a column name from my database?

[Updated on: Sun, 28 May 2017 04:29]

Report message to a moderator

Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663283 is a reply to message #663279] Sun, 28 May 2017 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Think a little bit, where does $row come from? How is it set?

Re: Displaying Information based on entered value in PHP with Oracle Database 11g R2 [message #663284 is a reply to message #663283] Sun, 28 May 2017 05:56 Go to previous message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I like you guys. I did it.
<!DOCTYPE HTML>
<html>
	<body> Details of: <?php echo htmlentities($_GET["cust_phone"]) . "<br>";
		$link = oci_connect('hd','hd', 'localhost/mydb');
		if(!$link) {
			$e = oci_error();
			exit('Connection error	' . $e['message']);
		}
		$ph = htmlentities($_GET["cust_phone"]);
		$q1 = "select CUST_ID from customer where CUST_PHONE = :bv_ph";
		$q1parse = oci_parse($link, $q1);
		oci_bind_by_name($q1parse, ':bv_ph', $ph);
		
		oci_execute($q1parse);
		oci_fetch($q1parse);
		$res = oci_result($q1parse, 'CUST_ID');
		if(!$res) {
			exit("Person Not Found");
		}
		?>
		<table border = "black">
			<tr> 
				<th> ADDRESS </th>
				<th> AREA </th>
			</tr>
		<?php
			
			$q2 = "select A.ADDRESS, A.AREA from customer c 
              join customer_address ca on C.CUST_ID = CA.CUST_ID
              join address a on A.ADDRESS_ID = CA.ADDRESS_ID where C.CUST_ID = :id_bv";
			$q2parse = oci_parse($link, $q2);
			oci_bind_by_name($q2parse, ':id_bv', $res);
			oci_execute($q2parse);
			while($row = oci_fetch_array($q2parse)) {
					echo "<tr><td>" . htmlentities($row["ADDRESS"]) . "</td>";
					echo "<td>" . htmlentities($row["AREA"]) . "</td></tr>";
            }
             oci_free_statement($q2parse);
             oci_close($link);
         ?>
         </table>
      </body>
</html>










Previous Topic: Unloading from Oracle in PHP
Next Topic: session variable not working in php
Goto Forum:
  


Current Time: Thu Mar 28 14:35:53 CDT 2024