record pointer

--0-1254788720-1183655391=:84341
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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!
--0-1254788720-1183655391=:84341--

Re: record pointer

------=_Part_151287_256963.1183663959811
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

If I understand what you're asking, just write a simple query.

$picked = ;
$sql = "SELECT * FROM WHERE cat = '$picked'";

$result = mssql_query($sql) or die ("My_Error_Message");
$row = mssql_fetch_assoc($result);

$id = $row['id'];
$name = $row['name'];
$cat = $row['cat'];

echo $id; // shows your record id on the screen.


On 7/5/07, elk dolk wrote:
>
> 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!

------=_Part_151287_256963.1183663959811--

Re: record pointer

--0-1983163515-1183664237=:86846
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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.
--0-1983163515-1183664237=:86846--

Re: Re: record pointer

------=_Part_151491_6667183.1183665116909
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

------=_Part_151491_6667183.1183665116909--

Re: record pointer

--0-435230949-1183665487=:35701
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I am selecting by id

Dan Shirah wrote: Date: Thu, 5 Jul 2007 15:51:56 -0400
From: "Dan Shirah"
To: "elk dolk"
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.
--0-435230949-1183665487=:35701--

Re: 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

Re: record pointer

--0-1383932215-1183699275=:20138
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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 wrote: Date: Thu, 05 Jul 2007 22:17:15 +0100
From: Niel Archer
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.
--0-1383932215-1183699275=:20138--

Re: Re: record pointer

Hi

> 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?

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 |
+---------+------+


Niel

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

Re: record pointer

--0-989295777-1183702879=:4033
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit



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!
--0-989295777-1183702879=:4033--

Re: Re: record pointer

elk dolk wrote:
>
> 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.

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.

--
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: record pointer

--0-1245059579-1183703881=:51950
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit



Chris 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.
--0-1245059579-1183703881=:51950--

Re: Re: record pointer

elk dolk wrote:
>
> Chris 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.

Not a great query but see if this returns what you want:

SELECT id, name, cat FROM table t2
WHERE cat = (SELECT cat FROM table t1 WHERE id = $ID) AND (t2.id =
t1.id - 1 OR t2.id = t1.id + 1);

I'm sure there's a much better way to do this however.

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

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