How do I find a single record and the next record after it only.

[jasonc]

I wish to query for an entry using `Pid` and the one that follows after it, using ORDER BY `Pid`

 

The `Pid` is not always nubmers.  There is no "index" due to this.

 

 

[trq]

You will need to define what you meen by 'the one that follows after it'. Keep in mind that databases don't store data in a particular order.

[jasonc]

not sure if this will show correctly, but here goes....

 

My table has `Pid` and `Item` I have separated with underscores as spaces get condensed to a single one on this site.

 

Pid__Item

a1__text6

v7__text1

g6__text8

s5__text5

l7__text7

f6__text2

n5__text4

f8__text9

w9__text10

s2__text3

 

 

Looking to have a query that will search for a `Pid` (which will exist as the previous page get the `Pid` that needs to be found) and then have this record in the results but also include the next record, based on the ORDER BY `Pid`

 

So if I was to search for `Pid` being 's5' the results would have 's5' and also 'v7' as this would be the next record.

 

Basically the next record in strick alpha-numeric order after the inital one being looked for.

[Muddy_Funster]

Why would you not index a text field if you are searching against it?  what do you get just now if you use "ORDER BY Pid LIMIT 2"?

[jasonc]

"SELECT * FROM `products` WHERE `Pid` = '" . $itemCode . "'"

 

This would return the first item I need but not the next record.

 

All the products code are completely random codes set by the manufacture.  So I am unable to 'add 1' to the item code to find the next one on the database. (I wish)

 

Each `Pid` is unique.

[jasonc]

Actually I have just found out that the `Pid` is not a unique code.

 

But anyhow I still do not understand how having a unique primary key will give the next record as the Pid being sorted using ORDER BY will reorder the primary keys meaning that I would get the next record that may be much higher or lower than the one I am referencing.

 

in my original post i stated that i needed both results, I really only need the next record in the database. As long as it is the next in the strick order of Pid

[Buddski]

Have you tried Muddy_Funster's suggestion, it seems perfectly logical from what you have described.

[Muddy_Funster]

you can't refference the Pid field directly in the WHERE clause if you you are looking to get multiple different records from it without a common factor in the search.  You will need to search against another field (prefferably a primary key int index) using a subquery.  Something along the lines of:

SELECT Pid, Item 
FROM table 
WHERE id IN (
SELECT id from table where id > 
((SELECT id from from table WHERE Pid = '$variable')-1)
ORDER BY id LIMIT 2
)

[floridaflatlander]

The `Pid` is not always nubmers.  There is no "index" due to this.

 

How big is your table, can you make a column fill it then rename it an auto increment column or build another table?

 

Also I have a small site but my picture info db has auto increment and then I use three queries to retrieve the display pic, the next pic and the previous pic. I'm sure there are better ways but like I said it's a small site.

[jasonc]

The `Pid` is not always nubmers.  There is no "index" due to this.

 

How big is your table, can you make a column fill it then rename it an auto increment column or build another table?

 

Also I have a small site but my picture info db has auto increment and then I use three queries to retrieve the display pic, the next pic and the previous pic. I'm sure there are better ways but like I said it's a small site.

 

Because Pid is not unique and not in order and that I add items in between, i can not use a primary key as adding an item in the middle later on will cause that item to have a higher primary key id.

[Muddy_Funster]

The `Pid` is not always nubmers.  There is no "index" due to this.

 

How big is your table, can you make a column fill it then rename it an auto increment column or build another table?

 

Also I have a small site but my picture info db has auto increment and then I use three queries to retrieve the display pic, the next pic and the previous pic. I'm sure there are better ways but like I said it's a small site.

SELECT pic FROM table WHERE id IN(SELECT id, id+1, id-1 FROM table where id = $id)

would be one way of doing it.

SELECT pic FROM table WHERE ((id = $id) OR (id = $id+1) OR (id = $id-1))

is another.

The `Pid` is not always nubmers.  There is no "index" due to this.

 

How big is your table, can you make a column fill it then rename it an auto increment column or build another table?

 

Also I have a small site but my picture info db has auto increment and then I use three queries to retrieve the display pic, the next pic and the previous pic. I'm sure there are better ways but like I said it's a small site.

 

Because Pid is not unique and not in order and that I add items in between, i can not use a primary key as adding an item in the middle later on will cause that item to have a higher primary key id.

OK, you can still index the column regardless of the duplicate values.  what do you meen by "add items in the middle"? what exactly are you using this table for?

[jasonc]

it is used for a shopping cart and every product has a code that could be like the following...

 

123abc

abc123

abc123abc

123abc123

123

abc

 

there is no numbering like 0001, 0002, 0003, 0004  .... 0100

 

as the existing codes are completely random codes there are times that i may add an item in the above list say...

 

1234abc

 

and adding this would mean that the index number for this record would be say 12345.  or some other number that is next in line to be used as an index.  so the index would be not in the strick order of Pid.

 

so

[Muddy_Funster]

erm...I can't see any logic to that at all. You are storing shopping cart items - items you are going to be billing people for - with no definitive id structure?  Also, if all the numbers are random, and in random locations, why bother trying to pull two consecutive records when they could have absoloutly no relevance to each other, why not just select your single item and then any other random record?

 

This just makes no sense to me at all.  I'm either going to need you to explain in detail what you are doing here, and exactly why you think it's a good idea, or I'm affraid I'll be of little help at all.

[jasonc]

i am actually only after a single item and not both.

 

not sure if this might be a better example...

 

order the list by `Pid`, search for an item by its `Pid` then get the next record that is in the database.

 

[Buddski]

Theoretically, this should work..

SELECT * FROM `products` WHERE `Pid` > '123abc123' ORDER BY `Pid` LIMIT 1

Where 123abc123 is your entered variable.

[fenway]

I have no idea how you know which one should be next.