php Mysql error help

[+hedleigh Subscriber²]

G'day All,

First off, if this is in the wrong forum, could a mod move it to the correct one.

I was bored and thought I'd have a go at some php. I have no real idea what I'm doing but thought I'd start learning from a script I already had.

In this function, the line mysql_data_seek($sql,$row) throws up the following error.

Warning: mysql_data_seek(): Offset 2 is invalid for MySQL result index 8 (or the query data is unbuffered)

function traverse($root, $depth, $sql)
{
	 $row=0;
	 while ($acat = mysql_fetch_array($sql))
	 {
		  if ($acat['parentcat'] == $root)
		  {
			   echo "<option value='" . $acat['catID'] . "'>";
			   $j=0;
			   while ($j<$depth)
			   {
					 echo "  ";
					$j++;
			   }
			   if($depth>0)
			   {
				 echo "-";
			   }
			   echo $acat['catname'] . "</option>";

			   mysql_data_seek($sql,0);
			   traverse($acat['catID'], $depth+1,$sql);
		  }
		  $row++;
		  mysql_data_seek($sql,$row);
	 }
}
[/CODE]

Any help on this would be appreciated.

[+virtorio MVC]

I've never used mysql_data_seek before (and neither should you because it's depreciated (as are all mysql_xxx functions) and will disappear in a future version of PHP, instead use mysqli or better yet PDO), but at a guess I'd say you're receiving this warning because you're trying to seek to a row that doesn't exist in the result.

You may not have seen that warning before if the code has been used in a production server because warnings are usually turned off so users don't see them.

You could use mysql_num_rows to get the number of rows in the result and only call mysql_data_seek if it's not outside the number of rows available.

function traverse($root, $depth, $sql)
{
  $num_rows = mysql_num_rows($sql);
  ...
  if ($row < $num_rows)
	mysql_data_seek($sql,$row);

You could also call mysql_data_seek with at @ infront of it to suppress the error message.

$row++;
@mysql_data_seek($sql,$row);

[+hedleigh Subscriber²]

I've never used mysql_data_seek before (and neither should you because it's depreciated (as are all mysql_xxx functions) and will disappear in a future version of PHP, instead use mysqli or better yet PDO), but at a guess I'd say you're receiving this warning because you're trying to seek to a row that doesn't exist in the result.

You may not have seen that warning before if the code has been used in a production server because warnings are usually turned off so users don't see them.

You could use mysql_num_rows to get the number of rows in the result and only call mysql_data_seek if it's not outside the number of rows available.

You're right Virtorio, the script is from about 2006 I think. :D

As I said, I have no real idea of what I'm doing and after a few searches came to the conclusion that the error is generated for the reason you stated.

I did try and use mysql_num_rows, but couldn't work out how to put it in the correct place.

Where would be a good tutorial to rewrite the function in the correct manner?

Thank you for the quick reply as well. :)

[+theblazingangel MVC]

So, this is looping over a set of records that correspond to a hierarchical data structure stored using the Adjacency Model, printing them out in an indented fashion.

All records are gone through, looking for direct children of a given node, which for each one identified is printed and the function recalled to print its own direct children and so on. Each printing of a nodes children must loop through the entire result set from scratch looking for them, hence why the pointer is set back to 0 prior to calling the function to get its children. After getting its children printed, it needs to reset the pointer to the next record it is supposed to be moving on to. Here is where there's a flaw.

It's incrementing the variable that keeps track of where the pointer should be pointing to, so that it should be pointing to the next record, and then setting the pointer to it. However, upon having processed the last record in the set, the pointer is incremented to one greater than the index of the last item (itself) and therefore trying to set the pointer to it fails. A simple off by one bug.

All you should need to do is switch the order of the last two statements. Set the pointer to the current record, then increment $row. If mysql_fetch_array works how I think it works (I've never messed with setting the index pointer myself), the call to it will itself increment its pointer and fetch the next row, if there is one.

Furthermore, I'd set default values on the root and depth parameters if I were you, it'll make it simpler to use, by just calling traverse($sql);

function traverse($sql, $root = null, $depth = 0)
{
	$row = 0;
	while ($acat = mysql_fetch_array($sql))
	{
		if ($acat['parentcat'] == $root)
		{
			echo "<option value='" . $acat['catID'] . "'>";
			$j = 0;
			while ($j < $depth) {
				echo "  ";
				$j++;
			}
			 if ($depth > 0) {
				echo "-";
			}
			echo $acat['catname'] . "</option>";

			mysql_data_seek($sql, 0);
			traverse($sql, $acat['catID'], $depth + 1);
		}
		mysql_data_seek($sql, $row);
		$row++;
	}
}

[+hedleigh Subscriber²]

Thank you to both Virtorio and +theblazingangel.

Tried what you suggested +theblazingangel, got rid of that error but threw up a different one. :)

Did what you suggested Virtorio and that worked a treat.

Thank you both very much.

[Mike]

You could also call mysql_data_seek with at @ infront of it to suppress the error message.

no no no no

[+hedleigh Subscriber²]

no no no no

lmao.

No Mike, you are right there. I used the first suggestion.

I knew that I could suppress the error, not a good solution though as you said.

[+virtorio MVC]

no no no no

I simply stated it as a way of getting the code to "work" without having to rewrite it; it was far from a recommendation.

[+hedleigh Subscriber²]

I simply stated it as a way of getting the code to "work" without having to rewrite it; it was far from a recommendation.

Which was exactly how I took it.