Need to create php oracle select query

Posted on 2007-10-20
Last Modified: 2013-12-19
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
Question by:freqout
    LVL 21

    Expert Comment

    Most conventional way of doing this:


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

      $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']);

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

      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>';


    Hope this helps, you can customize it to the way you like it.

    Author Comment

    I just tried the last solution, but it shows each value twice rather than just once.
    LVL 21

    Expert Comment

    What does the output look like? Are you able to copy and paste?

    Author Comment

    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
    LVL 21

    Accepted Solution

    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);

    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>";


    Author Comment

    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

    Author Comment

    nevermind, I didnt do a commit for my last transaction on sqlplus, my fault

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now