Select 20 records after criteria

[andrewsmd]

I need to run a select query in the manner of something like this
select * from table where column like 'text';

However, I need to get that record, plus the next 20 records. Any ideas on how I do that?

[rocknbil]

select (fields) from (table) where (condition) limit 0,20

Or do you mean the next records without the condition starting from that record? You'd probably have to do that in two selects or a complicated nested select, like

select (fields) from (table) where (condition) limit 1;

From (fields) you get the auto increment id, then

select (fields) from (table) where (id field)>(the id) limit 0,20

Or, if you want them ordered by some other field, like date, use the date field of that record.

select (fields) from (table) where (date field)>(the date) limit 0,20

[andrewsmd]

I meant the second one. I'll take a look at it thanks.

[andrewsmd]

I had thought about the auto increment thing, but I didn't design this db and it has no auto id. Is there any other way you can think of?

[rocknbil]

Well, that's a bad idea for several reasons, you can add one. In any case determine what your primary ordering field is and use that, see the date example.

[andrewsmd]

I know it's bad, like I said, I didn't design it. I have no control over this horribly designed database, I just have to work with it :). Do you have any other thoughts on how I can do this?

[rocknbil]

The date won't work?

Can you try this? It won't break anything and you can drop it if you want. As always, do a backup first.

alter table yourtable add my_id int(11) primary key auto_increment first;

What *should* happen is it will place a new column at the far left of the field list (left, by usage of "first") and add the id's in the order they were entered, giving you that field to order by. If it errors it means you already have a primary key, even if it's not an auto_increment.

If that doesn't work,

alter table yourtable drop my_id;

will drop the column.

[LifeinAsia]

If you're not allowed to alter the existing table, you could pull the data into a temp table. It's a bit more brute force and may not work well if you're doing it a lot on a huge table.

Have the temp table be the exact same structure as the existing table plus an auto_increment field. Select the records into the temp table. Then you should be able to use rocknbil's suggested solution on the temp table.

[andrewsmd]

That's one route, I was thinking of loading the whole thing into an array too. Which do you think would be more efficient.

[LifeinAsia]

In my experience, the DB engine is almost always faster and handling data than applications.

If in doubt, try both and compare.

[andrewsmd]

That's what I was probably going to end up doing. Just wanted someone else's thoughts. And I agree with you btw.