MySQL ODBC Connector - next, prev, records in MySql. Handler Function

Author

next, prev, records in MySql. Handler Function

Alvaro Cobo

2006-02-08, 11:23 am

Dear all:

I have been exploring about this issue quite a lot, and find no solution:

Platform: Debian, MySql 4.1.11, PHP 4.3.10-2, Apache.

Is there any way I can retrieve a set of values depending in a where clause:

For example:

from a set of values in one field: 1,2,5,8,9,11,13

I'd like to retrieve a record (8) and also the previous one (5) and the
next one (9) (so the record set would be: 5,8,9)

I have found the "Handler" function in the Manual, but it and keeps
giving me errors (I have also checked in the manual and it seems to work
with MySql 4.1.x)

/* --Start example
---------------------
HANDLER tbl_sm04_indicador READ PK_indicador_id { FIRST | NEXT | PREV |
LAST }

WHERE PK_indicador_id=8

LIMIT 0, 3
----------------------
--End example (I know, I am completely lost)*/

Does anybody has tried this function before?.
Is it useful for the result I would like to accomplish?
Could anybody could share an example of how to use this function?

Thanks and best regards.

Alvaro Cobo

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

sheeri kritzer

2006-02-15, 8:23 pm

Hi there,

What did your MySQL error say?

It looks like you didn't open the table. my example, which worked:

CREATE TABLE `foo` (
`id` int(11) NOT NULL auto_increment,
`bar` char(3) default NULL,
PRIMARY KEY (`id`),
KEY `idx_bar` (`bar`)
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |

insert into foo (bar) VALUES
('abc'),('def'),('qw
e'),('ert'),('wer'),
('tyu'),('asd'),('sd
f'),('dfg'),('z=
xc'),('xcc');

and then:

mysql> HANDLER foo open;
Query OK, 0 rows affected (0.00 sec)

mysql> HANDLER foo READ idx_bar FIRST where bar=3D'wer';
+----+------+
| id | bar |
+----+------+
| 5 | wer |
+----+------+
1 row in set (0.00 sec)

mysql> HANDLER foo READ idx_bar NEXT;
+----+------+
| id | bar |
+----+------+
| 11 | xcc |
+----+------+
1 row in set (0.00 sec)

mysql> HANDLER foo READ idx_bar PREV;
+----+------+
| id | bar |
+----+------+
| 5 | wer |
+----+------+
1 row in set (0.00 sec)

mysql> HANDLER foo READ idx_bar PREV;
+----+------+
| id | bar |
+----+------+
| 6 | tyu |
+----+------+
1 row in set (0.00 sec)

Granted, that's using "previous" and "next" in an alphabetical sense.=20
I found that using the "id" index didn't work, and I had to create
another non-primary index on key for it to work.

-Sheeri

On 2/8/06, Alvaro Cobo <coboalvaro@gmail.com> wrote:
> Dear all:
>
> I have been exploring about this issue quite a lot, and find no solution:
>
> Platform: Debian, MySql 4.1.11, PHP 4.3.10-2, Apache.
>
> Is there any way I can retrieve a set of values depending in a where clau=
se:
>
> For example:
>
> from a set of values in one field: 1,2,5,8,9,11,13
>
> I'd like to retrieve a record (8) and also the previous one (5) and the
> next one (9) (so the record set would be: 5,8,9)
>
> I have found the "Handler" function in the Manual, but it and keeps
> giving me errors (I have also checked in the manual and it seems to work
> with MySql 4.1.x)
>
> /* --Start example
> ---------------------
> HANDLER tbl_sm04_indicador READ PK_indicador_id { FIRST | NEXT | PREV |
> LAST }
>
> WHERE PK_indicador_id=3D8
>
> LIMIT 0, 3
> ----------------------
> --End example (I know, I am completely lost)*/
>
> Does anybody has tried this function before?.
> Is it useful for the result I would like to accomplish?
> Could anybody could share an example of how to use this function?
>
> Thanks and best regards.
>
> Alvaro Cobo
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...mail
.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Alvaro Cobo

2006-02-25, 9:43 am

-------------- enigBAF15C1A53FFE3AE
56F447FB
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks Sheeri:

Yes, you where right. It was because I was not opening the table first.
Actually, I was using this statements from a MySQL GUI (MySQL Query
Browser) so it doesn't work in this program (it doesn't keeps the table
opened).

But when I use your example in the shell mode it works perfect.

Have you ever tried to use this kind of statements using PHP?. It is
because I would like to use it in a PHP script (I was exploring this
function to make a navigator which sends me to the next record each time
I click on a link generated from a MySQL query).

Thanks a lot and very grateful with you,

Alvaro.

sheeri kritzer escribi=F3:
> Hi there,
>=20
> What did your MySQL error say?
>=20
> It looks like you didn't open the table. my example, which worked:
>=20
> CREATE TABLE `foo` (
> `id` int(11) NOT NULL auto_increment,
> `bar` char(3) default NULL,
> PRIMARY KEY (`id`),
> KEY `idx_bar` (`bar`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
>=20
> insert into foo (bar) VALUES
> ('abc'),('def'),('qw
e'),('ert'),('wer'),
('tyu'),('asd'),('sd
f'),('dfg')=
,('zxc'),('xcc');[co
lor=darkred]
>=20
> and then:
>=20
> mysql> HANDLER foo open;
> Query OK, 0 rows affected (0.00 sec)
>=20
> mysql> HANDLER foo READ idx_bar FIRST where bar=3D'wer';
> +----+------+
> | id | bar |
> +----+------+
> | 5 | wer |
> +----+------+
> 1 row in set (0.00 sec)
>=20
> mysql> HANDLER foo READ idx_bar NEXT;
> +----+------+
> | id | bar |
> +----+------+
> | 11 | xcc |
> +----+------+
> 1 row in set (0.00 sec)
>=20
> mysql> HANDLER foo READ idx_bar PREV;
> +----+------+
> | id | bar |
> +----+------+
> | 5 | wer |
> +----+------+
> 1 row in set (0.00 sec)
>=20
> mysql> HANDLER foo READ idx_bar PREV;
> +----+------+
> | id | bar |
> +----+------+
> | 6 | tyu |
> +----+------+
> 1 row in set (0.00 sec)
>=20
> Granted, that's using "previous" and "next" in an alphabetical sense.=20
> I found that using the "id" index didn't work, and I had to create
> another non-primary index on key for it to work.
>=20
> -Sheeri
>=20
>=20
> On 2/8/06, Alvaro Cobo <coboalvaro@gmail.com> wrote:
on:
lause:[color=darkred
]
e[color=darkred]
rk[color=darkred]
|[color=darkred]
om[color=darkred]
>=20
>=20

-------------- enigBAF15C1A53FFE3AE
56F447FB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFD/ ADxQNkfDBAXrLURAvUMA
J0Sk5ktIjlKOtlryfy/jfqdpJgoKwCgjIQc
IV7nh9oBwmO7eX69x76f
9LQ=
=pJt0
-----END PGP SIGNATURE-----

-------------- enigBAF15C1A53FFE3AE
56F447FB--

Sponsored Links