Jump to content



Photo

Mysql error help

php mysql

  • Please log in to reply
8 replies to this topic

#1 +hedleigh

hedleigh

    Neowinian

  • Joined: 07-June 02
  • Location: Geelong

Posted 22 February 2013 - 06:33

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 "&nbsp;&nbsp;";
					$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);
	 }
}

Any help on this would be appreciated.


#2 +virtorio

virtorio

    4089 III

  • Tech Issues Solved: 12
  • Joined: 28-April 03
  • Location: New Zealand
  • OS: OSX 10.9, Windows 8.1
  • Phone: Samsung Galaxy SIII

Posted 22 February 2013 - 06:51

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);


#3 OP +hedleigh

hedleigh

    Neowinian

  • Joined: 07-June 02
  • Location: Geelong

Posted 22 February 2013 - 07:01

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. :)

#4 +theblazingangel

theblazingangel

    Software Engineer

  • Tech Issues Solved: 6
  • Joined: 25-March 04
  • Location: England, UK

Posted 22 February 2013 - 07:08

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 "&nbsp;&nbsp;";
				$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++;
	}
}


#5 OP +hedleigh

hedleigh

    Neowinian

  • Joined: 07-June 02
  • Location: Geelong

Posted 22 February 2013 - 09:07

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.

#6 Mike

Mike

    Neowinian Senior

  • Joined: 11-August 02

Posted 22 February 2013 - 09:17

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


no no no no

#7 OP +hedleigh

hedleigh

    Neowinian

  • Joined: 07-June 02
  • Location: Geelong

Posted 22 February 2013 - 09:26

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.

#8 +virtorio

virtorio

    4089 III

  • Tech Issues Solved: 12
  • Joined: 28-April 03
  • Location: New Zealand
  • OS: OSX 10.9, Windows 8.1
  • Phone: Samsung Galaxy SIII

Posted 22 February 2013 - 14:13

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.

#9 OP +hedleigh

hedleigh

    Neowinian

  • Joined: 07-June 02
  • Location: Geelong

Posted 23 February 2013 - 00:23

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.