Welcome to WebmasterWorld Guest from 54.226.2.31

Forum Moderators: open

Message Too Old, No Replies

Select 20 records after criteria

     

andrewsmd

4:54 pm on Jan 25, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

5:30 pm on Jan 25, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

5:46 pm on Jan 25, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

andrewsmd

6:45 pm on Jan 25, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

5:16 pm on Jan 26, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

5:30 pm on Jan 26, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

5:44 pm on Jan 27, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

6:26 pm on Jan 27, 2011 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

2:26 pm on Jan 28, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

4:22 pm on Jan 28, 2011 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

If in doubt, try both and compare.

andrewsmd

4:28 pm on Jan 28, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

Featured Threads

Hot Threads This Week

Hot Threads This Month