How to return the record above and the record below .. ?

[londres9b]

So I have this table:

------------------
Time --- Result
- - - - - -
11am - Yes
13pm - No
17pm - Yes
-------------------

I have a php script that when a user searches for 11am they get that line.
That's easy right?

But what If the user doesn't know the exact time? I want to create a php/mysql script that If a user searches for 12pm they get 11pm and 13pm, as those are the record above and the record bellow what the user searched.

Can this be done? How?

Thanks ;)

[code green]

There is no such thing as next record. last record or record above or below in a table.

You can simulate this with a query that has an ORDER BY, then above and below are the next/ last recordset.

so I suggest modifying the query.
You could use DATEDIFF(HOUR,-1(+1),timefield) to help you

[londres9b]

Oh, ok.

But let's forget that the first field is about date and time. Let's say it's just a normal text field.

Isn't there a way to do this:
If there is no record that corresponds to what the user searched, give back the next approximate record?
Like the user searched 5 but there is no 5 and so the script searches for 3 and 4, 2 and 5 ..

thanks :)

[Jerry Winston]

What kind of proximity are you looking for?

• Are you looking for an ASCII value range?
• Are you looking for a date range for date values stored as text?
• Are you looking for a "n row above, n row below" solution based on a sort?

We can't "just forget" the data type because a record is only above or below any other record because of a particular sort order based on a field or set of fields who's sort behavior is tied to data type.

[londres9b]

Well, first I want the script to go and see if there is a record that corresponds to what searched for.

And if not maybe check the 'value the user searched' + 1 and -1 ...

Can you explain the "n row above, n row below" please?

[Jerry Winston]

If you know how to get the n rows above you also know how to get the n rows below. It's just the UNION of two queries:

SELECT Field1 FROM
(Field1 FROM myTable WHERE Field1 < searchVar ORDER BY Field1 ASC) as SubQ1
LIMIT n
 
UNION
 
SELECT Field1 FROM
(Field1 FROM myTable WHERE Field1 > searchVar ORDER BY Field1 DESC) as SubQ2
LIMIT n
 

You'd be putting together the n records < searchVar with the n records > searchVar. My MySQL is quite rusty these days so the syntax may be off but that's the general logic.

I could be wrong, but I'm pretty sure you already knew how to get n rows below or above a value before you came here. It's not always fun but defining a troublesome data set using set notation first usually saves you time in the end as you already know the basic operations to bound the subsets of target data set.

[londres9b]

I'm sorry but I am a newbie to MySql...

SELECT Field1 FROM
(Field1 FROM myTable WHERE Field1 < searchVar ORDER BY Field1 ASC) as SubQ1
LIMIT n

I think I understand everything besides SubQ1.. ?

[Jerry Winston]

The

as SubQ1

creates an alias, just another name to refer to our

(SELECT Field1 FROM myTable WHERE Field1 < searchVar ORDER BY Field1 ASC)

sub query. The same goes for

as SubQ2

I just spotted an error in my code. My sub queries (SubQ1 and SubQ2) were missing their SELECT statements.

SELECT Field1 FROM
(SELECT Field1 FROM myTable WHERE Field1 < searchVar ORDER BY Field1 ASC) as SubQ1
LIMIT n
 
UNION
 
SELECT Field1 FROM
(SELECT Field1 FROM myTable WHERE Field1 > searchVar ORDER BY Field1 DESC) as SubQ2
LIMIT n
 

[londres9b]

It's not working.. I had the values 1, 2, 4, 5 and I made so that the searchVar was 3 and what I got was all the rows.. (using LIMIT 2 i got the 1st 2 rows) ..

[londres9b]

Sorry, my mistake.. I got it!