By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,414 Members | 1,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,414 IT Pros & Developers. It's quick & easy.

PHP/Oracle - Pulling data into array

P: n/a
Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn.
I'm starting to hate it after using MySQL!!

--------------------------------------------------------------------------
1) Is there a similar statement using PHP/Oracle functions as below for
MySQL?
--------------------------------------------------------------------------

while (list ($key, $val) = each ($HTTP_POST_VARS)) {
print $key . " = " . $val . "<br>";
}

--------------------------------------------------------------------------
2) Should I be using something like this or is there easier way to pull data
into array?
--------------------------------------------------------------------------

// Start new Oracle cursor and query

$q = "select * from inventory";

$ora_cur = ora_do( $ora_conn, $q ) or die("Couldn't setup Oracle
Cursor");

if ( $ora_cur ) {

// Figure out how many columns

$numCols = ora_numcols( $ora_cur );

// Get the first fetched row and put it in to our array...

$row = array();

// Loop through columns

for( $i = 0; $i < $numCols; $i++ ){

array_push( $row, ora_getcolumn( $ora_cur, $i ) );
}
array_push( $results, $row );

// Fetch rows, one at a time, putting them in their own
// array. Each row should be appended to the array of
// results..

// Get each row

while ( ora_fetch( $ora_cur ) ){
$row = array();

// Loop through columns

for( $i = 0; $i < $numCols; $i++ ){
array_push( $row, ora_getcolumn( $ora_cur, $i ) );
}
array_push( $results, $row );
}
}
while (list ($results, $row) = each ($HTTP_GET_VARS)) {
print $results. " = " . $row . "<br>";
}

?>

--------------------------------------------------------------------------
3) For some reason my html below does not get displayed on the page. After
I submit the page is blank and just says database connected succesfully.
Why is it stopping here??
--------------------------------------------------------------------------

<html><head><title>etc...<body>etc...

Jul 17 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
pd*****@comcast.net says...
Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn.
I'm starting to hate it after using MySQL!!


Philip,

Suggest that you get ADODB http://php.weblogs.com/ADOdb/

This is a database abstraction layer package, which may be a softer
migration path for you than going from MySQL hard into the native OCI8
library.

If you do go that way, be sure to read up on how to query the DB using
bind variables (supported in ADODB for Oracle, MS-SQL) and make sure you
use them.

Geoff M
Jul 17 '05 #2

P: n/a
That link you provided does not work!

I can post to oracle no problem, but pulling data is a pain for me.

Here's an example that isn't working very well for me:

$stmt = OCIParse($conn, "select * from inventory");

OCIExecute($stmt);

OCIFetchInto ($stmt, $row, OCI_ASSOC);

while (list($key, $val) = each($row)) {

print $key . " = " . $val . "<br>";

}
"gmuldoon" <gm*************@scu.edu.au> wrote in message
news:MP************************@news.asgard.net.au ...
pd*****@comcast.net says...
Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn. I'm starting to hate it after using MySQL!!


Philip,

Suggest that you get ADODB http://php.weblogs.com/ADOdb/

This is a database abstraction layer package, which may be a softer
migration path for you than going from MySQL hard into the native OCI8
library.

If you do go that way, be sure to read up on how to query the DB using
bind variables (supported in ADODB for Oracle, MS-SQL) and make sure you
use them.

Geoff M

Jul 17 '05 #3

P: n/a
On Mon, 16 Feb 2004 16:32:32 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:
"gmuldoon" <gm*************@scu.edu.au> wrote in message
news:MP************************@news.asgard.net.a u...
pd*****@comcast.net says...
Ok, as some of you may know I'm an Oracle newbie w/ PHP. I'd rather use
MySQL but at the office here we use Oracle and boy do I have alot to learn.
I'm starting to hate it after using MySQL!!


Philip,

Suggest that you get ADODB http://php.weblogs.com/ADOdb/


That link you provided does not work!


Works from here. Check your ISP.
I can post to oracle no problem, but pulling data is a pain for me.

Here's an example that isn't working very well for me:

$stmt = OCIParse($conn, "select * from inventory");

OCIExecute($stmt);

OCIFetchInto ($stmt, $row, OCI_ASSOC);

while (list($key, $val) = each($row)) {

print $key . " = " . $val . "<br>";

}


Other than the lack of error checking and assumption that it returns a single
row, what about that isn't 'working very well' for you? Presumably you want to
put the OCIFetchInto in a while loop. Like in the manual.

<http://uk2.php.net/manual/en/function.ocifetchinto.php>

But since you haven't said what's wrong, it's hard to suggest how to fix it.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Jul 17 '05 #4

P: n/a
pd*****@comcast.net says...
That link you provided does not work!
An example why not to top-post. Put you responses in-line.

Capitalisation problem, try:
http://php.weblogs.com/ADODB/
I can post to oracle no problem, but pulling data is a pain for me.

Here's an example that isn't working very well for me:

$stmt = OCIParse($conn, "select * from inventory");

OCIExecute($stmt);

OCIFetchInto ($stmt, $row, OCI_ASSOC);

while (list($key, $val) = each($row)) {

print $key . " = " . $val . "<br>";

}

Some very rough sample snippets:

OCI8 example of the way I code:

$sql1="select item_id, item_desc from inventory";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);

ADODB example of the way I code:

$type='some_type';
$sql="select item_id, item_desc from inventory where item_type=:type";
$stmt=$conn->Prepare($sql);
$conn->Parameter($stmt, $type, 'type'); // USE BIND VARIABLES
$rs=$oraconn->Execute($stmt);
if ($rs===false) die('Sorry, unable to retrieve items);
while (!$rs->EOF ) {
echo $rs->fields(item_id).' - '$rs->fields(item_desc).'<br>';
$rs->moveNext();
}

Cheers,

Geoff M

Jul 17 '05 #5

P: n/a
On Mon, 16 Feb 2004 22:45:08 GMT, gmuldoon <gm*************@scu.edu.au> wrote:
Suggest that you get ADODB http://php.weblogs.com/ADOdb/


That link you provided does not work!


Capitalisation problem, try:
http://php.weblogs.com/ADODB/


They're running Windows so your original URL was (also) fine.

<http://uptime.netcraft.com/up/graph/?host=php.weblogs.com>

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Jul 17 '05 #6

P: n/a
Thansk for the examples GMuldoon, and folks...!

---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate a
form...
---------------------------------------------------

"select * from inventory where item_id = '$item_id'";

Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.

$sql1="select * from inventory where item_id = '$item_id'";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);

---------------------------------------------------
2) What are the benefits to using ADODB??
---------------------------------------------------


That link you provided does not work!
An example why not to top-post. Put you responses in-line.

Capitalisation problem, try:
http://php.weblogs.com/ADODB/
I can post to oracle no problem, but pulling data is a pain for me.

Here's an example that isn't working very well for me:

$stmt = OCIParse($conn, "select * from inventory");

OCIExecute($stmt);

OCIFetchInto ($stmt, $row, OCI_ASSOC);

while (list($key, $val) = each($row)) {

print $key . " = " . $val . "<br>";

}

Some very rough sample snippets:

OCI8 example of the way I code:

$sql1="select item_id, item_desc from inventory";
$stmt1=OCIParse($conn, $sql1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
OCILogoff($conn);

ADODB example of the way I code:

$type='some_type';
$sql="select item_id, item_desc from inventory where item_type=:type";
$stmt=$conn->Prepare($sql);
$conn->Parameter($stmt, $type, 'type'); // USE BIND VARIABLES
$rs=$oraconn->Execute($stmt);
if ($rs===false) die('Sorry, unable to retrieve items);
while (!$rs->EOF ) {
echo $rs->fields(item_id).' - '$rs->fields(item_desc).'<br>';
$rs->moveNext();
}

Cheers,

Geoff M
Jul 17 '05 #7

P: n/a
On Tue, 17 Feb 2004 17:52:31 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:
Thansk for the examples GMuldoon, and folks...!

---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate a
form...
---------------------------------------------------

"select * from inventory where item_id = '$item_id'";
No, completely wrong.
Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.
OCIFetchInto is acceptable, and acts similarly to mysql_fetch_*. Have you read
the documentation?

OCIDefineByName more closely matches the way you're _supposed_ to use the OCI
interface natively from C, and so is likely to be more efficient (untested).
$sql1="select * from inventory where item_id = '$item_id'";
*bash over head - this is some thing you must never do*

MySQL has the flaw that you have to stuff values into SQL statements, mixing
DATA with SQL. This is Bad. Sensible databases use BIND VARIABLES,
alternatively named PLACEHOLDERS.

A properly rewritten version of above would look like:

select * from inventory where item_id = :item_id

Or:

select * from inventory where item_id = ?

Depending on what interface and database you're using.

(Actually you'd probably also want to lose the '*' and replace it with a
proper column list to insulate you from schema changes).

Stuffing data into literal SQL causes several problems:

(1) SQL injection attacks. You forget to escape quotes, and suddenly data
becomes executed SQL. This is a severe security problem.

(2) Oracle, and other databases, cache the execution plans of SQL. The first
time it comes across a new statement, it works out the best way to execute it.
Since it has a decent optimiser, this is a relatively expensive operation, as
it does a fair bit of work to find out what's really the best way to do it from
the many access paths it has available. But it caches this in memory, so when
you execute it again later (you rarely only run something *once*) it doesn't
have to go through the parsing and optimisation steps, it already knows HOW to
execute it, it just has to plug in the values.

But if you put literal values in an SQL statement, the text of the statement
is different every time you execute it. So it can't use the cached plan, and
has to re-parse it, and do all the optimisation again. This causes excessive
"hard-parsing" and cripples your database.

If you have bind variables/placeholders, then the execution plan is the same
(since the SQL is the same) but you plug in the _data_ later.

$sql = 'select * from inventory where item_id = :item_id';
$stmt1 = OCIParse($conn, $sql1);

if (!$stmt1) {
// handle the error, with reference to OCIError()
}

OCIBindByName($stmt, ':item_id', &$item_id, -1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision.
OCILogoff($conn);

---------------------------------------------------
2) What are the benefits to using ADODB??
---------------------------------------------------


ADODB has a manual. The very first chapter of the manual gives reasons why you
might want to use ADODB.

Geoff already gave you a reason why; it hides you from some of the OCI8
interface, which is quite low-level, and gives you a higher-level uniform
interface that works for many different types of database.
That link you provided does not work!


An example why not to top-post. Put you responses in-line.


You (Philip) didn't write this. But because you're still top-posting (look it
up on Google) and further you're not quoting correctly, Geoff's reply is
attributed to you in your reply.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Jul 17 '05 #8

P: n/a
"You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision."

From your poor explanation, I would conclude that I three choices not two.
So which is it?

OCIDefineByName, OCIBindByName, or using OCIFetchInto

Perhaps you could be a little clearer.

Phil

"Andy Hassall" <an**@andyh.co.uk> wrote in message
news:q8********************************@4ax.com...
On Tue, 17 Feb 2004 17:52:31 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:
Thansk for the examples GMuldoon, and folks...!

---------------------------------------------------
1) How would I query an item_id and return all the key/values to populate a
form...
---------------------------------------------------

"select * from inventory where item_id = '$item_id'";
No, completely wrong.
Then what?? Must I list every variable in OCIDefineByName?? I would hope
not...I should be able to pull key/val like I do in mySQL which right now
looks much easier then oracle's pain in the ass way.


OCIFetchInto is acceptable, and acts similarly to mysql_fetch_*. Have you

read the documentation?

OCIDefineByName more closely matches the way you're _supposed_ to use the OCI interface natively from C, and so is likely to be more efficient (untested).
$sql1="select * from inventory where item_id = '$item_id'";
*bash over head - this is some thing you must never do*

MySQL has the flaw that you have to stuff values into SQL statements,

mixing DATA with SQL. This is Bad. Sensible databases use BIND VARIABLES,
alternatively named PLACEHOLDERS.

A properly rewritten version of above would look like:

select * from inventory where item_id = :item_id

Or:

select * from inventory where item_id = ?

Depending on what interface and database you're using.

(Actually you'd probably also want to lose the '*' and replace it with a
proper column list to insulate you from schema changes).

Stuffing data into literal SQL causes several problems:

(1) SQL injection attacks. You forget to escape quotes, and suddenly data
becomes executed SQL. This is a severe security problem.

(2) Oracle, and other databases, cache the execution plans of SQL. The first time it comes across a new statement, it works out the best way to execute it. Since it has a decent optimiser, this is a relatively expensive operation, as it does a fair bit of work to find out what's really the best way to do it from the many access paths it has available. But it caches this in memory, so when you execute it again later (you rarely only run something *once*) it doesn't have to go through the parsing and optimisation steps, it already knows HOW to execute it, it just has to plug in the values.

But if you put literal values in an SQL statement, the text of the statement is different every time you execute it. So it can't use the cached plan, and has to re-parse it, and do all the optimisation again. This causes excessive "hard-parsing" and cripples your database.

If you have bind variables/placeholders, then the execution plan is the same (since the SQL is the same) but you plug in the _data_ later.

$sql = 'select * from inventory where item_id = :item_id';
$stmt1 = OCIParse($conn, $sql1);

if (!$stmt1) {
// handle the error, with reference to OCIError()
}

OCIBindByName($stmt, ':item_id', &$item_id, -1);
OCIDefineByName($stmt1, "item_id", &$id);
OCIDefineByName($stmt1, "item_desc", &$desc);
OCIExecute($stmt1);
while(OCIFetch($stmt1)){
echo $id.' - '.$desc.'<br>';
}
You have a choice between using defines, or using OCIFetchInto which

wraps this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision.
OCILogoff($conn);

---------------------------------------------------
2) What are the benefits to using ADODB??
---------------------------------------------------
ADODB has a manual. The very first chapter of the manual gives reasons

why you might want to use ADODB.

Geoff already gave you a reason why; it hides you from some of the OCI8
interface, which is quite low-level, and gives you a higher-level uniform
interface that works for many different types of database.
That link you provided does not work!
An example why not to top-post. Put you responses in-line.


You (Philip) didn't write this. But because you're still top-posting

(look it up on Google) and further you're not quoting correctly, Geoff's reply is
attributed to you in your reply.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>

Jul 17 '05 #9

P: n/a
On Wed, 18 Feb 2004 12:50:34 -0500, "Philip D Heady" <pd*****@comcast.net>
wrote:
"You have a choice between using defines, or using OCIFetchInto which wraps
this up for you and gives you PHP arrays. It's up to you. Read the
documentation, make your own decision."

From your poor explanation, I would conclude that I three choices not two.
So which is it?

OCIDefineByName, OCIBindByName, or using OCIFetchInto

Perhaps you could be a little clearer.


You have (at least) four choices for fetching data, but OCIBindByName is not
one.

OCIDefineByName is for binding PHP variables to columns to get data out of a
result set; each time you OCIFetch a row, the variables get set to the
corresponding columns of the result set.

Or, OCIResult fetches the value of one column in the 'current' row.

Or, OCIFetchInto fetches a single row as an array.

Or, OCIFetchStatement, which fetches an array (one entry per row) of arrays
(one entry per column of that row) - i.e. the entire result set in one call.
OCIBindByName is for binding data IN to placeholders, not for fetching data.

For a given SQL statement, you follow this cycle:

Prepare: OCIParse - you do this once.

Loop: (for each different set of values you want to query with):
Bind: OCIBindByName - for each bind variable/placeholder in the query.
[ Define: Optionally bind PHP variables to result set columns. ]
Execute: OCIExecute - begin execution of the statement.

Fetch: Either:
Loop: (for each row in the result set)
Then either:
OCIFetch (values go into previously OCIDefineByName'd variables
or use OCIResult)
or OCIFetchInto (PHP fetches the row into a PHP array)
End loop (when no more rows)
Or: OCIFetchStatement: Fetch all rows in one go

End loop (when you're finished with the cursor)

So for 'select a, b, c from inventory where d = :d' you would have:

* One call to OCIBindByName to bind a value to the :d placeholder.
* [Optionally] Three calls to OCIDefineByName to bind PHP variables to receive
values for a, b and c when fetching.

Any clearer?

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Jul 17 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.