get next record with current one in db loop 2

[Sleidia]

Hi again

Here is something I need to know :

While looping through mysql records, how do you get the next record at the same time as the current one?

You'll see what I mean by looking at this :

$sql_query[0] = "
SELECT 
my_record
FROM 
my_table 
ORDER BY 
ID
";

$sql_result[0] = mysql_query($sql_query[0], $GLOBALS["db_connect"]);

    while ($row[0] = mysql_fetch_array($sql_result[0])) {

    $current_record = $row[0]["my_record"];
    
    $next_record = ???
    
    }

 

[vacunita]

You would need to call mysql_fetch_array() again.

while ($row[0] = mysql_fetch_array($sql_result[0])) {

    $current_record = $row[0]["my_record"];

[red]$row[1] = mysql_fetch_array($sql_result[0]);[/red]    

[blue]    $next_record = $row[1]["my_record"];[/blue]

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Sleidia]

Thanks Vacunita

If I do that, will the next record be also available as $current_record in the next iteration?

 

[jpadie]

each call to mysql_fetch_* advances the internal pointer in the recordset by one row. so if you have 10 records in your set on the first iteration of your while loop you will have records 1 and 2 as $current and $next and the next iteration, records 3 and 4.

remember to test the result of the second mysql_fetch_* call for a return value of FALSE to signify that you are the end of the recordset.

the alternative to a while loop in these circs is a for loop

$numRows = ceil(mysql_num_rows($result)/2);
for ($i=0; $i<$numRows; $i++){
 $current_record = mysql_fetch_assoc($result);
 if (($next_record = mysql_fetch_assoc($result)) !== false){
 //do something
 }
}

 

[Sleidia]

Thanks for the explanation Jpadie

But, looking at your code, I miserably failed to see whether or not it avoids the record skipping mentioned in the first paragraph of your explanation.

Because I absolutely need to loop through all the records one by one.

 

[vacunita]

I think for that kind of manipulation you should use [blue]mysql_data_seek()[/blue], to place the fetch internal pointer in which ever location you want.

http://www.php.net/manual/en/function.mysql-data-seek.php

So you could have a while loop and move the pointer internally.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

aha, i understand now. you actually *want* to retain the record at the current pointer.

two ways:
1. read the whole recordset into an array

$result = mysql_query($sql);
while ($rows[] = mysql_fetch_assoc($result)){ }
$n = count($rows);
for($i=0; $i<$n; $i++){
 $current_record = $rows[$i];
 if ($i < ($n-1)) {$next_record=$rows[$i+1];}
}

or
2. using data_seek

$result = mysql_query($sql);
$cnt = 0;
while ($current_record = mysql_fetch_assoc($result)){
 $next_record = mysql_fetch_assoc($result); //should test for false
 //do stuff
 //increment the counter
 $cnt++;
 //return the pointer to the right record
 mysql_data_seek($result, $cnt);
}

i have a feeling that data_seek might be sequential, in which case you might actually have a performance benefit of soln 1 over 2.

if you just need a particular column of the next record then you could use this

$result = mysql_query($sql);
$cnt = 0;
while ($current_record = mysql_fetch_assoc($result)){
 $cnt++;
 $required_column = mysql_result($result,$cnt, FIELDNAME);
 //ideally you would use the col num rather than fieldname.  also this function is not fast.
}

 

[Sleidia]

Hmmmm sorry for the stupid question but wouldnt it be simpler to store the results in two identical arrays named differently and call the records this way? :

$result_array[0] = $result_array[1] = mysql_fetch_array($sql_result[0]);

for ($i=0; $i<sizeof($result_array[0]); $i++){

$current_record = $result_array[0][$i];
$next_record = $result_array[1][($i + 1)];

}

 

[jpadie]

you'd double your memory utilisation over the first solution i proposed.

 

[Sleidia]

Oh, I see

Well, my problem is that I always tend to be more gentle with my own brain power than with the server's brain power

 

[jpadie]

just seen you're based in france. anywhere nice? i'm in toulouse.

 

[Sleidia]

I'm located in the suburb of the always cloudy Paris
Well, I guess it's not that important since the only thing I can see around is my computer screen ;(

BTW, finally, I've used your first solution because mine couldn't possibly work.

Have a nice day

 

[jpadie]

yours would nearly have worked. you needed to do this

while ($result_array[0][] = $result_array[1][] = mysql_fetch_assoc($sql_result)) {//do nothing}

$c = count($result_array[0]);
for ($i=0; $i<$c); $i++){

 $current_record = $result_array[0][$i];
 $next_record = $result_array[1][($i + 1)];

}

but as i said, you didn't need the two arrays.