row cursor

tlchung

Hi,
I'm new to sql so please help.
Say i'm at row 10 of the table, how to say to increment to row 11 or to row 20. same, to decremnet to row 9 or to row 5.
Thanks for examples.

frikikip

I think you mean mysql_data_seek()

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

With this function you can move the pointer within an mysql result. Your result however should have ALL the rows you want.

Greets,

Marvin

tlchung

Sincerely thank you Marvin for your help. If you don't mind, I would like to step some more for this obviously basic topic but for me it is something else.

I'm from Vfp Db world that Vfp provides single line functions a lot. In this case, as I've selected a record(row), then all I need to code is 'skip' to get next record(row) and -skip -3 for 3 steps back ...

I read the example you pointed to:

. . . .
$query = 'SELECT last_name, first_name FROM friends';
$result = mysql_query($query);
if (!$result) {
die('Query failed: ' . mysql_error());
}
/ fetch rows in reverse order /
for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--) {
if (!mysql_data_seek($result, $i)) {
echo "Cannot seek to row $i: " . mysql_error() . "\n";
continue;
}

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

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

mysql_free_result($result);
?>

I need help to understand:

(1) Since the current row is known by the first seek, then all I need to save row() to a global var somewhere, so I just row()+1 for next row and row()-1 for previous... for me it is not necessary to go thru a 'for' loop to fetch + or - the row... do you think it could work that way on php/mySql? if so, how?

(2) It seem [mysql_free_result($result);] is to empty $result or what?

Thanks again.

Best reguards,
Chin

frikikip

Hi Chin,

Lets get the easy stuff out of the way first:
2) mysql_free_result is to free memory taken by the mysql result. You only need to call this function if you do multiple large queries (which all take a fair amount of memory). If you do a query the PHP engine automatically frees the memory after your script is terminated.

OK now the more difficult stuff.

First of all, if you want to order your rows its best practice to let MySQL sort them. This saves you a lot of (programming) time.

Each time you call mysql_fetch_assoc, mysql_fetch_array, mysql_fetch_row, etc, you increase your pointer. So after each call to one of these functions, the pointer will point to the next row automatically.

So if you let mysql sort your data, this piece of code should work:

$query = 'SELECT last_name, first_name FROM friends ORDER BY last_name DESC';
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)) {
echo $row['last_name'] . ' ' . $row['first_name'] . "<br />\n";
}

As you can see, there is no for loop or data seeking being done.

Greets,

Marvin