• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

PHP/MySQL: How to get adjacent records?

Can someone tell me how to select adjacent records from a table?

For example, the table has rows:
---------------------------------------------
row1
row2
row3
row4
row5
row6
row7

and i wanna get row4 plus 2 adjacent records, that means i wanna get row4 + row2, row3 and row5, row6.

0
jtcy
Asked:
jtcy
2 Solutions
 
gamebitsCommented:
SELECT column FROM table
LIMIT 5 OFFSET 2

This would give you 5 rows (LIMIT 5) starting at row 2 (OFFSET 2)
0
 
Michael701Commented:
I'd say select all the records and then use

bool mysql_data_seek ( resource result_identifier, int row_number)

to jump around the results and fetch the records you need

Michael
0
 
jtcyAuthor Commented:
Michael, the thing is...how do u know which row number it is to get?
0
 
Michael701Commented:
oh, first quick read and i thought he was going to display all records in maybe 2 columns, possible alpha

A M
B N
C O
- -
L Z

guess you'd still have to count the fetches till you get to the desired row, then use seek to back up 3 before displaying 5 records.
0
 
bahadirkocaogluCommented:
A helpful note about the 'resource' data type.

Since the 'resource' variable is pointing to a row in a result set at any given time, you can think of it as being passed to this function by reference every time you pass it or assign it to a variable.

<?

$sql = "SELECT * from <table>";
$result = mysql_query($sql);
$temp_result = $result;

while ($row = mysql_fetch_assoc($temp_result)) {
    // do stuff with $row
}

while ($row = mysql_fetch_assoc($result)) {
    // This code will never run because the 'resource' variable is pointing past the end of the result set,
    // even though it was *not* assigned by reference to $result2.
}

?>

Therefore, the following snipits are functionally identical:

<?

// Start snipit 1

$sql = "SELECT * from <table>";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
    // do stuff with $row
}

mysql_data_seek($result, 0);

while ($row = mysql_fetch_assoc($result)) {
    // do other stuff with $row
}

// Start snipit 2

$sql = "SELECT * from <table>";
$result = mysql_query($sql);
$temp_result = $result;

while ($row = mysql_fetch_assoc($temp_result)) {
    // do stuff with $row
}

mysql_data_seek($result, 0);

while ($row = mysql_fetch_assoc($temp_result)) {
    // do other stuff with $row
}

?>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now