Solved

MySQL - get next record id from results

Posted on 2010-11-22
3
644 Views
Last Modified: 2012-05-10
Hi,
I have the following query which returns a result set and loops through to display the different records. Is there a way to get the id of the record coming next, perhaps using mysql_data_seek?


mysql_select_db($database_conn_data, $conn_data);
$query_view_items = "SELECT * FROM items ORDER BY sortid";
$view_items = mysql_query($query_view_items, $conn_data) or die(mysql_error());
$row_view_items = mysql_fetch_assoc($view_items);



do {


//get current record id
$id = $row_view_items['id'];

//code here to push pointer forward by one

$next_id =  $row_view_items['id'];

//code here to move pointer back by one
......
...

} while ($row_view_items = mysql_fetch_assoc($view_items));

Thanks in Advance for your feedback.

Cheers,

Andrew
0
Comment
Question by:sabecs
  • 2
3 Comments
 
LVL 17

Expert Comment

by:sweetfa2
Comment Utility
$numrows = mysql_num_rows($result);
for ($i = 0; $i < $numrows; $i++) {
    if (!($row = mysql_fetch_assoc($result))) {
        continue;
    }
    if (!mysql_data_seek($result, $i + 1)) {
        echo "Cannot seek to row $i: " . mysql_error() . "\n";
        continue;
    }

    if (!($nextrow = mysql_fetch_assoc($result))) {
        continue;
    }

    echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";
}

Open in new window

0
 
LVL 17

Accepted Solution

by:
sweetfa2 earned 500 total points
Comment Utility
Will try again - forgot a step.

numrows = mysql_num_rows($result);
for ($i = 0; $i < $numrows; $i++) {
    if (!mysql_data_seek($result, $i)) {
        echo "Cannot seek to row $i: " . mysql_error() . "\n";
        continue;
    }

    if (!($row = mysql_fetch_assoc($result))) {
        continue;
    }
    if (!mysql_data_seek($result, $i + 1)) {
        echo "Cannot seek to row $i: " . mysql_error() . "\n";
        continue;
    }

    if (!($nextrow = mysql_fetch_assoc($result))) {
        continue;
    }

}

Open in new window

0
 

Author Closing Comment

by:sabecs
Comment Utility
Thanks for your help.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now