homepage Welcome to WebmasterWorld Guest from 54.242.18.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Select 20 records after criteria
andrewsmd




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

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




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

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




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

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

andrewsmd




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

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




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

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




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

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




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

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




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

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




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

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




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

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

If in doubt, try both and compare.

andrewsmd




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved