AW: AW: AW: AW: [PHP-DB] php and OCI
MAYBE THERES ANOTHER WAY; but before selecting or fertching all you wont
know
Are you saying that, in php, every query has to be perfomed twice, if you
want
to know the number of rows in selection (before the fetch loop)?
Matthias Kopolt wrote:
> OK:
>
> imagine you want to find all rows where COLOR = 'foo' :
>
> $st1 = ociparse ($conn, "SELECT count(*) FROM myTable WHERE COLOR =
> 'foo'");
> ociexecute($st1,OCI_DEFAULT);
> ocifetch ($st1);
> $number_of_rows = ociresult ($st1, 1); //e.g. 372
>
> $st2 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR =
> 'foo'");
> ociexecute($st2,OCI_DEFAULT);
> while (ocifetch($st2)) {
> echo " ID : ".ociresult ($st2, 1);
> echo " NAME : ".ociresult ($st2, 2);
> } //this will return about 372 datarows
>
> $st3 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR =
'foo'
> AND ROWNUM <= 10");
> ociexecute($st1,OCI_DEFAULT);
> while (ocifetch($st3)) {
> echo " ID : ".ociresult ($st3, 1);
> echo " NAME : ".ociresult ($st3, 2);
> } //this will return max 10 datarows
>
> //remember to ocifreestatement($st..) every ressource when duing continuos
> queries
>
> /*
> rownum is a metacolumn that is a signed during retrieving data from
> database, but before sorting it.
> so " where rownum < 11 order by points desc " will not give you the top
ten
> but, the first 10 entries in the list, ortderd by points.
>
> to overcom that do subselects :
> "select * from (select * from table where foocondition order by foo)
where
> rownum < 10 "
>
> mk
--
==
Valerio FerrucciTabasoft Sas
[EMAIL PROTECTED] http://tabasoft.ancitel.it
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: AW: AW: AW: [PHP-DB] php and OCI
Are you saying that, in php, every query has to be perfomed twice, if you want
to know the number of rows in selection (before the fetch loop)?
Matthias Kopolt wrote:
> OK:
>
> imagine you want to find all rows where COLOR = 'foo' :
>
> $st1 = ociparse ($conn, "SELECT count(*) FROM myTable WHERE COLOR =
> 'foo'");
> ociexecute($st1,OCI_DEFAULT);
> ocifetch ($st1);
> $number_of_rows = ociresult ($st1, 1); //e.g. 372
>
> $st2 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR =
> 'foo'");
> ociexecute($st2,OCI_DEFAULT);
> while (ocifetch($st2)) {
> echo " ID : ".ociresult ($st2, 1);
> echo " NAME : ".ociresult ($st2, 2);
> } //this will return about 372 datarows
>
> $st3 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR = 'foo'
> AND ROWNUM <= 10");
> ociexecute($st1,OCI_DEFAULT);
> while (ocifetch($st3)) {
> echo " ID : ".ociresult ($st3, 1);
> echo " NAME : ".ociresult ($st3, 2);
> } //this will return max 10 datarows
>
> //remember to ocifreestatement($st..) every ressource when duing continuos
> queries
>
> /*
> rownum is a metacolumn that is a signed during retrieving data from
> database, but before sorting it.
> so " where rownum < 11 order by points desc " will not give you the top ten
> but, the first 10 entries in the list, ortderd by points.
>
> to overcom that do subselects :
> "select * from (select * from table where foocondition order by foo) where
> rownum < 10 "
>
> mk
--
==
Valerio FerrucciTabasoft Sas
[EMAIL PROTECTED] http://tabasoft.ancitel.it
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
AW: AW: AW: AW: [PHP-DB] php and OCI
>and to get rows, say, from 20 to 40? >Is it ok to write: >"select * from (select * from table where foocondition order by foo) where >rownum >= 20 AND rownum <= 40" its not that easy, as oracle start counting with the first retrieved row, so rownum > x will wait for ever, as oracle will never start counting because no rows are retrieved. make a trick and select the rownum in a subquery, than you can treat it as a fixed row: select * from ( select rownum "rn", * from (select * from table where foocondition order by foo) ) where rn >= 20 AND rn <= 40 this should do it (although there are a lot of subqueries) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
AW: AW: AW: [PHP-DB] php and OCI
OK:
imagine you want to find all rows where COLOR = 'foo' :
$st1 = ociparse ($conn, "SELECT count(*) FROM myTable WHERE COLOR =
'foo'");
ociexecute($st1,OCI_DEFAULT);
ocifetch ($st1);
$number_of_rows = ociresult ($st1, 1); //e.g. 372
$st2 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR =
'foo'");
ociexecute($st2,OCI_DEFAULT);
while (ocifetch($st2)) {
echo " ID : ".ociresult ($st2, 1);
echo " NAME : ".ociresult ($st2, 2);
} //this will return about 372 datarows
$st3 = ociparse ($conn, "SELECT ID, NAME FROM myTable WHERE COLOR = 'foo'
AND ROWNUM <= 10");
ociexecute($st1,OCI_DEFAULT);
while (ocifetch($st3)) {
echo " ID : ".ociresult ($st3, 1);
echo " NAME : ".ociresult ($st3, 2);
} //this will return max 10 datarows
//remember to ocifreestatement($st..) every ressource when duing continuos
queries
/*
rownum is a metacolumn that is a signed during retrieving data from
database, but before sorting it.
so " where rownum < 11 order by points desc " will not give you the top ten
but, the first 10 entries in the list, ortderd by points.
to overcom that do subselects :
"select * from (select * from table where foocondition order by foo) where
rownum < 10 "
mk
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
AW: [PHP-DB] php and OCI
you can use the meta column rownum
e.g. where rownum<20
note: you need to do subselects and (where rownum > 1) will never return a
line !!!
you could use something like
select * from
select rownum "rownumber", mydata from mytable order by
mycol
) where rownumber > 10 and rownumber < 20;
should work ...
2. i know there is a phpfunction for that ocifetchedrows or something like
that
mk
-Ursprungliche Nachricht-
Von: Valerio Ferrucci [mailto:[EMAIL PROTECTED]]
Gesendet: Dienstag, 13. Februar 2001 12:54
An: [EMAIL PROTECTED]
Betreff: [PHP-DB] php and OCI
Hi,
I'm using php with oracle to select data in a db.
1)
My code is:
putenv("ORACLE_HOME=/xx");
putenv("ORACLE_SID=xx");
$conn = ociLogon("xx","xx","xx");
$st = ociparse($conn,"select * from myTable order by MYFIELD");
$res = ociexecute($st,OCI_DEFAULT);
while(ocifetch($st))
{
...
}
This is ok, BUT:
I want to show records in pages with 20 records each one. How can I
obtain this?
I see that php asks all thousands records to (remote) oracle server,
slowing time. Is there something like:
ociexecute($st,OCI_DEFAULT, fromRec, toRec);
2)
How can I know the total records in current selection?
Thanks
==
Valerio FerrucciTabasoft Sas
[EMAIL PROTECTED] http://tabasoft.ancitel.it
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
