Hi,
If I have a lot of articles, all with a unique IDs.
First I would like to search for that ID, I could do
SELECT * from ARTICLE where ID = xx
But I also want to display the 5 articles before and after that article.
SELECT * from ARTICLE where ID > xx LIMIT 0, 10
and
SELECT * from ARTICLE where ID < xx LIMIT 0, 10
(I choose a limit of 10 in case the ID is one of the first one or one of the
last one, that way I will always have at least 10 articles).
Is it possible to do the above in one single query?
Should I even bother doing that? does it make the whole operation faster to
do it that way?
Many thanks.
Simon 10 1362
Providing that u know what xx is there two other needed variables are
the xx-5 and xx+5
so then
$start = $xx-5;
$stop = $xx+5;
SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
I think this is what you're looking for.
Rgds,
Gerard
Simon wrote: If I have a lot of articles, all with a unique IDs. First I would like to search for that ID, I could do SELECT * from ARTICLE where ID = xx But I also want to display the 5 articles before and after that article. SELECT * from ARTICLE where ID > xx LIMIT 0, 10 and SELECT * from ARTICLE where ID < xx LIMIT 0, 10 (I choose a limit of 10 in case the ID is one of the first one or one of the last one, that way I will always have at least 10 articles). Is it possible to do the above in one single query? Should I even bother doing that? does it make the whole operation faster to do it that way?
SELECT * FROM article WHERE ( x-5 < id and id < x+5 )
Or pick borders as you like.
/m
Gerard wrote: Providing that u know what xx is there two other needed variables are the xx-5 and xx+5
so then
$start = $xx-5; $stop = $xx+5;
SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
I think this is what you're looking for.
This assumes, of course, that there are no gaps in the sequence of
IDs...
--
Oli Providing that u know what xx is there two other needed variables are the xx-5 and xx+5
so then
$start = $xx-5; $stop = $xx+5;
SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
I think this is what you're looking for.
Rgds, Gerard
I know what XX is but unfortunately the unique ID are not in sequence.
So I could have articles with ID
1, 4, 5, 6, 10, 11, and so on...
I don't have all the article numbers, (in the case above I don't have ID 2,
3, 7, 8 and 9).
So doing your select would not work.
Thanks
Simon Gerard wrote: Providing that u know what xx is there two other needed variables are the xx-5 and xx+5
so then
$start = $xx-5; $stop = $xx+5;
SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
I think this is what you're looking for.
This assumes, of course, that there are no gaps in the sequence of IDs...
You are right, there are gaps in the sequence.
Simon
Simon wrote: Providing that u know what xx is there two other needed variables are the xx-5 and xx+5
so then
$start = $xx-5; $stop = $xx+5;
SELECT * FROM Articles WHERE id >= $start AND id <= $stop;
I think this is what you're looking for.
Rgds, Gerard
I know what XX is but unfortunately the unique ID are not in sequence.
So I could have articles with ID
1, 4, 5, 6, 10, 11, and so on...
I don't have all the article numbers, (in the case above I don't have ID 2, 3, 7, 8 and 9).
So doing your select would not work.
(SELECT * FROM Articles WHERE id >= $x ORDER BY id LIMIT 6)
UNION
(SELECT * FROM Articles WHERE id < $x ORDER BY id DESC LIMIT 5)
It's possible that the syntax isn't 100% correct, but you get the
idea...
--
Oli
Ask in a Mysql related newsgroup. For example, and preferably,
comp.databases.mysql.
--
E. Dronkert SELECT * FROM article WHERE ( x-5 < id and id < x+5 )
Or pick borders as you like.
I should have mentioned that the IDs have gaps. /m
Simon
Not quite what you asked for but
select num, abs(num - $reqd ) as srt from t1 order by srt limit 11
will give you your record plus the nearest 10 records to it
Just a thought.
Ian
"Simon" <sp********@example.com> wrote: Hi,
If I have a lot of articles, all with a unique IDs.
First I would like to search for that ID, I could do
SELECT * from ARTICLE where ID = xx
But I also want to display the 5 articles before and after that article.
SELECT * from ARTICLE where ID > xx LIMIT 0, 10 and SELECT * from ARTICLE where ID < xx LIMIT 0, 10
This is incorrect. SQL tables have no inherent ordering. Thus, the first
query will get 10 records with ID numbers larger than xx, but there is
absolutely no guarantee that they will be the records immediately above xx,
nor that they will be ordered in any way.
Given the limitations you described, there is no reliable way to do this
other than:
SELECT * FROM article WHERE ID < xx ORDER BY id DESC LIMIT 10
UNION
SELECT * FROM article WHERE ID > xx ORDER BY id LIMIT 10;
--
- Tim Roberts, ti**@probo.com
Providenza & Boekelheide, Inc. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: point |
last post by:
Hello there...
I'm a PHP programmer and starting to learn JS...
I have a following problem....
I have 3 select boxes! one is hotel one is destination and one is
country...
if someone...
|
by: serge |
last post by:
Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?
I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I...
|
by: Fan Ruo Xin |
last post by:
How to estimate the size of log space when I need to run "INSERT INTO
tgt_tbl SELECT * FROM src_tbl WHERE..." ?
What is the difference of before image between the above INSERT stmt and the...
|
by: GSteven |
last post by:
(as formerly posted to microsoft.public.access.forms with no result)
I've created a continuous form which is based on a straightforward table
(ex - customers - 100 records). On the form there is...
|
by: Matt Jensen |
last post by:
Howdy
Fairly simple question I think, I presume the answer is no it can't be
reused for 2 *SELECT* statements, but just hoping for clarification. Just
asking in the interests of trying to minimise...
|
by: Adam Tilghman |
last post by:
Hi all,
I have found that IE doesn't seem to respect the <SELECT> "multiple"
attribute when set using DOM methods, although the attribute/property
seems to exist and is updated properly. Those...
|
by: Peter |
last post by:
People are telling me it is bad to put
select * from <atable>
in a view. I better should list all fields of the table inside the
definition of the view.
I dont know exactly why but some...
|
by: Matt Ratliff |
last post by:
Hello,
I would appreciate any assistance you have with the following
problem:
I have (as an example) an array of values as follows:
arrayvalues=new Array("0001","0003","0005") where each is the...
|
by: adurth |
last post by:
Hi!
I wanna copy all childnodes of the current nodes except those with
basenames "name1" or "name2".
Something like
<xsl:copy-of select=" basename not equal ('name1' or 'name2') "/>
Can...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |