How to retrieve the position of a row inside a table with garbagedata in all cloumns?

hello_world();

in fact.. how to goTo next/previous row of a table without using
incremental ids AND without reading the whole table?
i will explain:
I wanna do something like a paging toolbar blinded to a list of rows
(for example, a literally view of a db table).
The first difference from a normal paging system, is that i wanna show
only one row per page, and luckily i know the id of that first viewed
row; so, no problem until here. Im able to show the first row xD.
The second difference is that i don't know the position of the first
viewed row inside the whole original table, only the id of the row
(maybe someone is laughing while reading 'only').

I would like to know the position of that row to allow a easy goTo
next/prev method using LIMIT/OFFSET sql clauses.
But, since id's aren't incremental, and since i don't know the position
of that row, i cant find any way to know witch row is the next/prev.

I found a few solutions and it is poorly working now, reading the whole
table and thats all :o].
The front-end of the app, is not robust enough to fast-process this
amount of data (javascript), so this solution is a piece of ____.

I'm missing something?
How to retrieve the position of a row in a given table (always ordered
by ASC id) without reading more than one result per query using -only-
raw id's?

Thanks for your time.
Trullo :o]

P.S. Im using php526/mysql5051b but a generic answer is highly appreciated.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to retrieve the position of a row inside a tablewith garbage data in all cloumns?

Trullo wrote:
> hello_world();
>
> in fact.. how to goTo next/previous row of a table without using
> incremental ids AND without reading the whole table?
> i will explain:
> I wanna do something like a paging toolbar blinded to a list of rows
> (for example, a literally view of a db table).
> The first difference from a normal paging system, is that i wanna show
> only one row per page, and luckily i know the id of that first viewed
> row; so, no problem until here. Im able to show the first row xD.
> The second difference is that i don't know the position of the first
> viewed row inside the whole original table, only the id of the row
> (maybe someone is laughing while reading 'only').
>
> I would like to know the position of that row to allow a easy goTo
> next/prev method using LIMIT/OFFSET sql clauses.

Assuming the row id you have is a primary key, you could do:

select * from table where primary_key > $current_rowid order by
primary_key asc limit 1;


For previous you could do:

select * from table where primary_key < $current_rowid order by
primary_key desc limit 1;

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to retrieve the position of a row inside a tablewith garbage data in all cloumns?

En/na Chris ha escrit:
> Trullo wrote:
>> hello_world();
>>
>> in fact.. how to goTo next/previous row of a table without using
>> incremental ids AND without reading the whole table?
>> i will explain:
>> I wanna do something like a paging toolbar blinded to a list of rows
>> (for example, a literally view of a db table).
>> The first difference from a normal paging system, is that i wanna show
>> only one row per page, and luckily i know the id of that first viewed
>> row; so, no problem until here. Im able to show the first row xD.
>> The second difference is that i don't know the position of the first
>> viewed row inside the whole original table, only the id of the row
>> (maybe someone is laughing while reading 'only').
>>
>> I would like to know the position of that row to allow a easy goTo
>> next/prev method using LIMIT/OFFSET sql clauses.
>
> Assuming the row id you have is a primary key, you could do:
>
> select * from table where primary_key > $current_rowid order by
> primary_key asc limit 1;
>
>
> For previous you could do:
>
> select * from table where primary_key < $current_rowid order by
> primary_key desc limit 1;
>
Lot of thanks! its true that i dont care about the position of row. Your
solution will do the work sure :o]

As a curiosity... then.. the only way to know the position of a row, is
read the whole table?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to retrieve the position of a row inside a table with garbage data in all cloumns?

------=_Part_92933_19411759.1222145459425
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

As a caution,


I would never, ever use a query that relies on the data to be returned from
the table in a certain order. That is just wrong and goes against the
principals of a rdbms.

Just write your queries in such a way that the result is always returned the
way you want it regardless of the way it is ordered in the table.


Jack




2008/9/23 Trullo

> En/na Chris ha escrit:
> > Trullo wrote:
> >> hello_world();
> >>
> >> in fact.. how to goTo next/previous row of a table without using
> >> incremental ids AND without reading the whole table?
> >> i will explain:
> >> I wanna do something like a paging toolbar blinded to a list of rows
> >> (for example, a literally view of a db table).
> >> The first difference from a normal paging system, is that i wanna show
> >> only one row per page, and luckily i know the id of that first viewed
> >> row; so, no problem until here. Im able to show the first row xD.
> >> The second difference is that i don't know the position of the first
> >> viewed row inside the whole original table, only the id of the row
> >> (maybe someone is laughing while reading 'only').
> >>
> >> I would like to know the position of that row to allow a easy goTo
> >> next/prev method using LIMIT/OFFSET sql clauses.
> >
> > Assuming the row id you have is a primary key, you could do:
> >
> > select * from table where primary_key > $current_rowid order by
> > primary_key asc limit 1;
> >
> >
> > For previous you could do:
> >
> > select * from table where primary_key < $current_rowid order by
> > primary_key desc limit 1;
> >
> Lot of thanks! its true that i dont care about the position of row. Your
> solution will do the work sure :o]
>
> As a curiosity... then.. the only way to know the position of a row, is
> read the whole table?
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
J.A. van Zanen

------=_Part_92933_19411759.1222145459425--

Re: How to retrieve the position of a row inside a tablewith garbage data in all cloumns?

Jack van Zanen wrote:
> As a caution,
>
>
> I would never, ever use a query that relies on the data to be returned
> from the table in a certain order. That is just wrong and goes against
> the principals of a rdbms.

I don't know that it goes against the principle, it's more that a
database will never guarantee the order of results unless you tell it
the order to use.

ie unless you specify an order by, the db will return the rows as soon
as it finds them (which may appear random but it's not really, it's to
do with where the row will be in the data/index file).

To the OP: why does it matter where the row appears in the list?

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to retrieve the position of a row inside a table with garbage data in all cloumns?

On Tue, Sep 23, 2008 at 6:58 AM, Chris wrote:
> To the OP: why does it matter where the row appears in the list?
>

In the case i'm facing it doesn't matter, but it could be useful to
display a scroll bar related to the size of the table and the row
viewed at the moment, or any simple [begin---youAreHere---end]
representation of the table.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to retrieve the position of a row inside a tablewith garbage data in all cloumns?

Trullo wrote:
> On Tue, Sep 23, 2008 at 6:58 AM, Chris wrote:
>> To the OP: why does it matter where the row appears in the list?
>>
>
> In the case i'm facing it doesn't matter, but it could be useful to
> display a scroll bar related to the size of the table and the row
> viewed at the moment, or any simple [begin---youAreHere---end]
> representation of the table.

Combine the prev & next queries to work out where you are.

$pages_before = select count(primary_key) from table where primary_key <
$current_id;

$pages_after = select count(primary_key) from table where primary_key >
$current_id;

Of course it will be an approximation only because as you are editing
record #X someone else could be deleting records a,b,c...

--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

how is this line a security risk?

I get an error stating that this line in my code is a security risk when I
code it.

require_once($PLUGINS_DIRECTORY."forum/forum.php");

here is what the explanation is:

include() or analogous is used with variable argument this can be dangerous
since variables are in many cases controlled by remote users.

the recommended soloution is to write it this way

define('SCRIPT_PATH',"/htdocs");
include ('sSCRIPT_PATH."/Foo.inc");

my question is why is the other way safer? im kinda confused..




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: how is this line a security risk?

read up on register_globals. It is a security risk because if i do
http://domain.com/file.php?plugins_directory=/directory/

it can reset your variable.

the best way to do that is to make PLUGINS_DIR a constant

define('PLUGINS_DIR', '/directory/');

Joseph Crawford

On Sep 23, 2008, at 12:58 PM, michael wrote:

> I get an error stating that this line in my code is a security risk
> when I code it.
>
> require_once($PLUGINS_DIRECTORY."forum/forum.php");
>
> here is what the explanation is:
>
> include() or analogous is used with variable argument this can be
> dangerous since variables are in many cases controlled by remote
> users.
>
> the recommended soloution is to write it this way
>
> define('SCRIPT_PATH',"/htdocs");
> include ('sSCRIPT_PATH."/Foo.inc");
>
> my question is why is the other way safer? im kinda confused..
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: how is this line a security risk?

Only is register_globals is on can that reset a variable. You are
correct though, defining directory paths is safer.

Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com



Joseph Crawford wrote:
> read up on register_globals. It is a security risk because if i do
> http://domain.com/file.php?plugins_directory=/directory/
>
> it can reset your variable.
>
> the best way to do that is to make PLUGINS_DIR a constant
>
> define('PLUGINS_DIR', '/directory/');
>
> Joseph Crawford
>
> On Sep 23, 2008, at 12:58 PM, michael wrote:
>
>> I get an error stating that this line in my code is a security risk
>> when I code it.
>>
>> require_once($PLUGINS_DIRECTORY."forum/forum.php");
>>
>> here is what the explanation is:
>>
>> include() or analogous is used with variable argument this can be
>> dangerous since variables are in many cases controlled by remote users.
>>
>> the recommended soloution is to write it this way
>>
>> define('SCRIPT_PATH',"/htdocs");
>> include ('sSCRIPT_PATH."/Foo.inc");
>>
>> my question is why is the other way safer? im kinda confused..
>>
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php