CODEWITHSUNDEEP
mysqllimit
Username_null
Username_null

Reputation: 1315

MYSQL LIMIT. Is it possible to skip certain rows?

Sorry if this question is confusing.

I have inherited a site that is already built, so I can't really do anything too drastic.

The MYSQL query on a certain page uses LIMIT to only show the relevant entries like this:

comtitlesub.idcts = %s LIMIT 1,3

Skipping the first record and displaying the following three records. I have been asked to add a new record, which is fine, but this is record number 7. Records 5 and 6 are not supposed to display on this page so changing the query to:

comtitlesub.idcts = %s LIMIT 1,6

displays all 6 records as you would expect.

One confusing thing is that I have altered the ID's for each of the records so that my new one is ID 4, and yet this did not make a difference.

Is there a simple way to 'skip' the unwanted records or am I approaching this from the wrong direction?

Upvotes: 1

Views: 1383

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270637

I believe the easiest course would be to modify your WHERE clause to exclude the rows you want excluded. For example: 

WHERE comtitlesub.idcts = %s AND someothercol NOT IN ('cat','frog','kazoo')

Ideally for maintainability, you would want someothercol to hold stable data rather than a numeric ID which might change as your application data changes.

Upvotes: 1

Anthony Accioly
Anthony Accioly

Reputation: 22471

Andrew, LIMIT will delimit according to a specific order, in your case, coincidentally the default order was the same than ID order, now that you've changed it, you will need to order by ID:

ORDER BY comtitlesub.idcts

Upvotes: 1

roselan
roselan

Reputation: 3775

add "order by comtitlesub.idcts" at the end of you query, but before the limit clause.

... comtitlesub.idcts = %s ORDER BY comtitlesub.idcts LIMIT 1,6

basically, changing the id doesn't reorder them, rows are stored in order they have been created, and retrieved that way by default.

Upvotes: 2

Related Questions