asked on

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

Joe Wu

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):' ').'</td>';
}
print '</tr>';
}
print '</table>';

oci_close($conn);
?>

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

freqout

ASKER

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

Joe Wu

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

freqout

ASKER

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

ASKER CERTIFIED SOLUTION

Joe Wu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

freqout

ASKER

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

freqout

ASKER

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