[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1713
  • Last Modified:

Need to create php oracle select query

I am trying to query an oracle table from php, there are three rows from the five row table I want to get, and it will most likely return multiple rows, I want to display each column in it's own column but display each row on it's own row, I dont know how to loop through and display row.

SQL> desc messages
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 MESSAGEID                                          NUMBER(38)
 MSGFROM                                            VARCHAR2(50)
 MSGTO                                              VARCHAR2(50)
 SENT                                               DATE
 MESSAGE                                            LONG
0
freqout
Asked:
freqout
  • 4
  • 3
1 Solution
 
nizsmoDeveloperCommented:
Most conventional way of doing this:


<?php

  $conn = oci_connect('hr', 'hr', 'orcl'); // replace with your own parameters!
  if (!$conn) {
    $e = oci_error();
    print htmlentities($e['message']);
    exit;
  }

  $query = 'SELECT MESSAGEID,MSGFROM,MSGTO FROM messages'; // replace with what query you want here...

  $stid = oci_parse($conn, $query);
  if (!$stid) {
    $e = oci_error($conn);
    print htmlentities($e['message']);
    exit;
  }

  $r = oci_execute($stid, OCI_DEFAULT);
  if (!$r) {
    $e = oci_error($stid);
    echo htmlentities($e['message']);
    exit;
  }

  print '<table border="1">';
  while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) { // this is the bit you want, looping through and displaying rows...
    print '<tr>';
       foreach ($row as $item) {
         print '<td>'.($item?htmlentities($item):'&nbsp;').'</td>';
       }
       print '</tr>';
  }
  print '</table>';

  oci_close($conn);
?>

Hope this helps, you can customize it to the way you like it.
0
 
freqoutAuthor Commented:
I just tried the last solution, but it shows each value twice rather than just once.
0
 
nizsmoDeveloperCommented:
What does the output look like? Are you able to copy and paste?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
freqoutAuthor Commented:
100      100      fakeuser      fakeuser      freqout      freqout
100      100      fakeuser      fakeuser      freqout2      freqout2
messageid is first two rows
msgfrom is second two rows
msgto is third row
0
 
nizsmoDeveloperCommented:
Script there is a bit messy, how about trying this one?


$connection = oci_connect($username, $password, $db); // change your user, pass & db here!
$stmt = oci_parse($connection, "SELECT MESSAGEID,MSGFROM,MSGTO FROM messages");

$r = oci_execute($stmt);

$nrows = oci_fetch_all($stmt, $results);

if($nrows>0){
echo "<table border><tr><th>Column 1</th><th>Column 2</th><th>Column 3</th>";

for ($i = 0; $i < $nrows; $i++) {
   echo "<tr>\n";
   foreach ($results as $data) {
      echo "<td>$data[$i]</td>\n";
   }
   echo "</tr>\n";
}

echo "</table>";

}
0
 
freqoutAuthor Commented:
Returned 2 rows:
Column 1      Column 2      Column 3
100       fakeuser       freqout
100       fakeuser       freqout2

but there are three rows in the table? I really apperciate all your help so far, it appears the count is just off one
0
 
freqoutAuthor Commented:
nevermind, I didnt do a commit for my last transaction on sqlplus, my fault
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now