[RESOLVED] next record query

[cbesh2]

I am trying to create previous/next links that will select the next product in my database; however, I have gaps in my ID number for my products that is causing me problems. For example:

The product IDs for shirts are 1000-1014
The product IDs for shoes are 2000-2023
The product IDs for jeans are 3000-3008
etc.

So when it displays the shirt with product ID 1014 and you click the next link, it wants to display product ID 1015 which has no entry. What do I do? I don't mind if it goes to product ID 2000. I just don't want it to display pages without entries in the database. Thanks for you help in advance.

[jdorsch]

Don't use IDs for this, use a LIMIT clause.

Also, using IDs as categorization is not a good idea. This is going to cause you problems.

[cbesh2]

Yes, I can see how I am already running into problems. Thanks for the input. So you are saying I should create a field that describes the item (example: shirts) and search for a match (type=shirts) instead of using ID?

[jdorsch]

No, create an external table named categories, then add a categoryID field (foreign key) to your products table. All your primary keys should be indexed unique autonumber integer fields, and foreign keys matching integers.

So if the categoryID for shirts is 10, then select all products where categoryID = 10. This way if you decide to change shirts to 'Fashionable Shirts', you only change it in one spot instead of 1000, and searching on numbers instead of text is a lot more efficient.

For the paging issue, if you are browsing shirts you would select * from products where categoryID = 10 LIMIT startrecord, numrecords.

If you were on the 12th record and only wanted to display one, it would be

LIMIT 12,1

[cbesh2]

Foreign Key -- Gotcha! So say I'm on record 12 and I wanted to show the next shirt item by creating a next link. Could I use the following since I won't be able to tell it which record I'm starting with every time (category 10 could be "shirts")?

SELECT * from products WHERE categoryID = 10 LIMIT 1;

Would that select the next shirt item?

[jdorsch]

No, if you are on record 12, you want to show record 13, so you would do something like

index.php?rec=13 <-- show the next record
index.php?rec=11 <-- show the previous record

SELECT * FROM products WHERE categoryID = 10 LIMIT {$_GET['rec']}, 1

The SQL you have is what you would want for the very first record. Also, the example above is simplified, you need to make sure and validate or escape $_GET['rec'] before putting it in the query.

[cbesh2]

Oh, I think I understand now. Thank you very much!