How to get the next row from the Database table?

Question

How to get the next row from the Database table? Such that the row might be incremented row that is if structure has a field called "id" then the row can be id++ or very next incremented row, but it may also b any other id containing row (NOT VERY NEXT) , (because the ids could be deleted from the table).

I am using Mysql Database below is my code..

mysql_select_db('pranav_test');
$seletaudit = mysql_query("SELECT * FROM jos_audittrail WHERE live = 0");
while($row2 = mysql_fetch_array($seletaudit))
{
    $audit[] =$row2;
}
$trackid = array();
$tracktable = array();
$i = 0;
foreach($audit as $val)
{
    $trackid[$i] = $val['trackid'];
    $tracktable[$i] = $val['table_name'];   

    if($i!=0)
    {
        $prev_id = $trackid[$i-1];
        $prev_valtab = $tracktable[$i-1];
    }
    if($val['operation'] == 'INSERT')
    {
        if($tracktable[$i]!=$prev_valtab)
        {
            $inserttable = "INSERT INTO '".$tracktable[$i]."' (";
        }


        if($tracktable[$i]==$prev_valtab)
        {
            $insertfield .= "'".$val['field']."', ";
        }
        if($tracktable[$i]==$prev_valtab)
        {
            $insertfield .= "]";
            $insertfield = str_replace(", ]",")",$insertfield);

        }
    }
}

here above I need t know whats going to be my next rows's "table_name" field contain... how can i do that....? Please help for fetching the next row in the for loop to get it checked.

Answer 1

UPDATE:

Ok, now that you posted some code, here a more specific answer:

If you want to have access to the next row in the array, I suggest to use a normal for loop to have access to the index:

for($i = 0; $i<count($audit); $i++) {
     // $audit[$i] is current row
     // $audit[$i+1] is next row
}

But be careful when you reach the end of the array, as $i+1 will be out of bounds.

Or if you just wonder why the $i in your current code is not behaving correctly: You have to increment it at the end of your foreach loop:

$i++;

UPDATE2:
A more complete example to make it clear (I adapt your approach and check for the previous row)

for($i=0; $i<count($audit);$i++)
{
    $curr = $audit[$i];

    if($i>0) {
        $prev = $audit[$i-1];

        if($curr['operation'] == 'INSERT')
        {
            if($curr['table_name']!=$prev['table_name'])
            {
                $inserttable = "INSERT INTO '".$curr['table_name]."' (";
            }
            else {
                // whereever $insertfield comes from
                $insertfield .= "'".$curr['field']."', ";
                $insertfield .= "]";
                $insertfield = str_replace(", ]",")",$insertfield);

            }
        }
    }
}

---

As you have not specified at all how you connect to the database, here is a generic answer:

Specify your query to order the result set by ID:

$query = "SELECT <columns here> FROM <table here> ORDER BY id";

Then get the result set (we have a fictive database class):

$result = $db->query($query);

Then iterate over the resultset:

while($row = $result->next()) {
    // do something with row
}