?
Solved

PHP/MySQL: How to get adjacent records?

Posted on 2007-07-29
7
Medium Priority
?
926 Views
Last Modified: 2013-12-12
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
Comment
Question by:jtcy
5 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 19588507
SELECT column FROM table
LIMIT 5 OFFSET 2

This would give you 5 rows (LIMIT 5) starting at row 2 (OFFSET 2)
0
 
LVL 19

Expert Comment

by:Michael701
ID: 19589830
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
 

Author Comment

by:jtcy
ID: 19589981
Michael, the thing is...how do u know which row number it is to get?
0
 
LVL 19

Accepted Solution

by:
Michael701 earned 1000 total points
ID: 19590158
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
 
LVL 2

Assisted Solution

by:bahadirkocaoglu
bahadirkocaoglu earned 1000 total points
ID: 19592434
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question