selecting record from oracle

T开发者_JAVA百科o select last record, I was using this when my database was MySQL:

$result = mysql_query("SELECT Id 
                         FROM test 
                     ORDER BY LENGTH(Id), Id ASC");

$count = mysql_numrows($result);

if($count != 0) {    
  $lastid = mysql_result($result,$count-1,"Id");    
}

...and it works fine.

But now my table is in an Oracle database - I wrote:

$stid = oci_parse($conn, "SELECT Id 
                            FROM test 
                        ORDER BY LENGTH(Id), Id ASC");

oci_execute($stid);

$p = oci_parse($conn, "SELECT COUNT(ID) 
                         FROM test");

oci_execute($p);

$count = oci_fetch_array($p);

if($count[0] != 0) {    
  $lastid = oci_result($stid, $count[0]-1);
}

It doesn't work - I am not getting the last record.

---

And what are you using this for? If it is to determine the next Id, then don't do this, but use a sequence instead. If not then you can use select max(id) from testid to get the maximum value of the column.

edit

First create a sequence to store the sequential number :

create sequence testid_seq;

Then when you insert a record, use

insert into testid(id, ...) values ("D111-" || testid_seq.nextval, ... );

And just fill in the dots with the info you want to insert.

---

If you read the documentation for oci_result, you'll find you're using the second parameter incorrectly.

The second parameter is what you use to retrieve the column - it can be the column name, or the ordinal value. Ordinal value is a number, starting at one (1) based on the columns specified in the SELECT clause. Ordinals aren't a recommended practice, because if the query changes -- if you forget to update the ordinal reference your retrieval of values is screwed. Effectively, the subsequent COUNT query returns a value higher than the number of columns in the first queries SELECT clause.

$stid = oci_parse($conn, "SELECT Id 
                            FROM test 
                        ORDER BY LENGTH(Id), Id ASC");

oci_execute($stid);

$lastid = oci_result($stid, 1);

oci_result lacks the third parameter that mysql_result provides.

To make things work in Oracle, use:

$stid = oci_parse($conn, "SELECT x.id
                            FROM (SELECT Id 
                                    FROM test 
                                ORDER BY LENGTH(Id) DESC, Id DESC) x
                           WHERE ROWNUM = 1");

oci_execute($stid);

$lastid = oci_result($stid, 1);

The updated query will return one row, the latest based on reversing the ORDER BY you were using previously.