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?
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
5:46 pm on Jan 25, 2011 (gmt 0)
I meant the second one. I'll take a look at it thanks.
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?
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.
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?
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.
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.
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.
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.
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.