PHP DB - record pointer

Author

record pointer

elk dolk

2007-07-05, 6:58 pm

Hi all,

My DB has the following columns: id, name ,cat. I want to select a cat i.e. cat=zzz when the query is finished I should see a specific record within this cat which is defined by id, like id=yyy
Any idea how to do this? Is there a function or something like record pointer in MySQL ?

cheers

---------------------------------
Shape Yahoo! in your own image. Join our Network Research Panel today!

elk dolk

2007-07-05, 6:58 pm

O.K. the id column is primary key and it is auto_incerment .I think my explanation was not clear enough :

this is my query : SELECT * FROM table WHERE ID=$ID
this will find the record with the specified ID and I will be able to see it, now I want to be able to scroll up and down to all the records that belong to the same cat(egory)!

Frank Flynn <frank@declan.com> wrote:
This is standard SQL:

SELECT id, name ,cat FROM catTable WHERE cat = 'zzz';

This would return something like
----------------------------
id name cat
----------------------------
1 fred zzz
5 fefe zzz
18 Mr. Puddles zzz
27 Moris zzz

and so on

Your column 'id' is the pointer you're looking for. You should define it as 'PRIMARY KEY' and perhaps AUTO_INCREMENT (this means MySQL will automatically assign a value to it).

Hi all,

My DB has the following columns: id, name ,cat. I want to select a cat i.e. cat=zzz when the query is finished I should see a specific record within this cat which is defined by id, like id=yyy
Any idea how to do this? Is there a function or something like record pointer in MySQL ?

cheers

---------------------------------
Get your own web address.
Have a HUGE year through Yahoo! Small Business.

elk dolk

2007-07-05, 6:58 pm

I am selecting by id

Dan Shirah <mrsquash2@gmail.com> wrote: Date: Thu, 5 Jul 2007 15:51:56 -0400
From: "Dan Shirah" <mrsquash2@gmail.com>
To: "elk dolk" <elkdolk@yahoo.com>
CC: php-db@lists.php.net
Subject: Re: [PHP-DB] Re: record pointer

Okay, in your first post you said you were selecting by category, now you're
saying you are selecting by ID and want all corresponding records that have
the same category as the selected ID.

Which way are you trying to do this??

On 7/5/07, elk dolk wrote:
>
> O.K. the id column is primary key and it is auto_incerment .I think my
> explanation was not clear enough :
>
> this is my query : SELECT * FROM table WHERE ID=$ID
> this will find the record with the specified ID and I will be able to see
> it, now I want to be able to scroll up and down to all the records that
> belong to the same cat(egory)!
>
>
>
> Frank Flynn wrote:
> This is standard SQL:
>
> SELECT id, name ,cat FROM catTable WHERE cat = 'zzz';
>
>
> This would return something like
> ----------------------------
> id name cat
> ----------------------------
> 1 fred zzz
> 5 fefe zzz
> 18 Mr. Puddles zzz
> 27 Moris zzz
>
>
> and so on
>
>
> Your column 'id' is the pointer you're looking for. You should define it
> as 'PRIMARY KEY' and perhaps AUTO_INCREMENT (this means MySQL will
> automatically assign a value to it).
>
>
>
>
>
>
>
>
>
> Hi all,
>
>
> My DB has the following columns: id, name ,cat. I want to select a cat
> i.e. cat=zzz when the query is finished I should see a specific record
> within this cat which is defined by id, like id=yyy
> Any idea how to do this? Is there a function or something like record
> pointer in MySQL ?
>
>
> cheers
>
>
>
>
>
>
> ---------------------------------
> Get your own web address.
> Have a HUGE year through Yahoo! Small Business.

---------------------------------
Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.

elk dolk

2007-07-06, 3:58 am

Thank you Niel
I am very close now have a look :

mysql> select id,cat from table where cat=(select cat from table where id=49);
+---------+------+
| id | cat |
+---------+------+
| 40 | FLK |
| 41 | FLK |
| 42 | FLK |
| 44 | FLK |
| 45 | FLK |
| 46 | FLK |
| 47 | FLK |
| 48 | FLK |
| 49 | FLK |
| 50 | FLK |
| 51 | FLK |
| 52 | FLK |
| 53 | FLK |
| 54 | FLK |
| 55 | FLK |
| 56 | FLK |
| 57 | FLK |
| 58 | FLK |
| 59 | FLK |
| 60 | FLK |
+---------+------+
20 rows in set (0.00 sec)

mysql> select id,cat from table where cat=(select cat from table where id=51);
+---------+------+
| id | cat |
+---------+------+
| 40 | FLK |
| 41 | FLK |
| 42 | FLK |
| 44 | FLK |
| 45 | FLK |
| 46 | FLK |
| 47 | FLK |
| 48 | FLK |
| 49 | FLK |
| 50 | FLK |
| 51 | FLK |
| 52 | FLK |
| 53 | FLK |
| 54 | FLK |
| 55 | FLK |
| 56 | FLK |
| 57 | FLK |
| 58 | FLK |
| 59 | FLK |
| 60 | FLK |
+---------+------+
20 rows in set (0.02 sec)

now I can select the whole cat when I enter an ID that is fine! but the query should
stop at id=$id or in other words in the end it should POINT to id=$id
in the above example the last record would be the record with id=51
do I need another SELECT here?

Niel Archer <spamfree@blueyonder.co.uk> wrote: Date: Thu, 05 Jul 2007 22:17:15 +0100
From: Niel Archer <spamfree@blueyonder.co.uk>
To: php-db@lists.php.net
Subject: Re: [PHP-DB] Re: record pointer

Hi

> O.K. the id column is primary key and it is auto_incerment .I think my explanation was not clear enough :
>
> this is my query : SELECT * FROM table WHERE ID=$ID
> this will find the record with the specified ID and I will be able to see it, now I want to be able to scroll up and down to all the records that belong to the same cat(egory)!

Assuming that is correct now, use a sub-query:

SELECT id, name FROM table
WHERE cat = (SELECT CAT FROM table WHERE id=$ID);

Niel

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

---------------------------------
Moody friends. Drama queens. Your life? Nope! - their life, your story.
Play Sims Stories at Yahoo! Games.

elk dolk

2007-07-06, 3:58 am

If I understand that correctly, you only need to add the extra condition
to the WHERE clause of the main query. So:

SELECT id, name, cat FROM table
WHERE cat = (SELECT cat FROM table WHERE id = $ID) AND id <= $ID;

This should display all rows, before and including the row with the same
'cat' as 'id' has. For your example of $ID = 51 it should display:

+---------+------+
| id | cat |
+---------+------+
| 40 | FLK |
| 41 | FLK |
| 42 | FLK |
| 44 | FLK |
| 45 | FLK |
| 46 | FLK |
| 47 | FLK |
| 48 | FLK |
| 49 | FLK |
| 50 | FLK |
| 51 | FLK |
+---------+------+

O.K. this is the problem! it should display all rows, before and including AND AFTER the row ,starting from the specified row with the same 'cat' as 'id' has.

---------------------------------
Shape Yahoo! in your own image. Join our Network Research Panel today!

elk dolk

2007-07-06, 3:58 am

Chris <dmagick@gmail.com> wrote:
Huh? You want before, after and including? So everything?

Maybe give us an example of what you want to get out of the query rather
than us guessing.

I keep the path to my photos in this DB so it is a photo Gallery , when I click on a thumbnail I want to see the image detail and then scroll to the next or previous photos with the same category.

---------------------------------
Luggage? GPS? Comic books?
Check out fitting gifts for grads at Yahoo! Search.

Sponsored Links