Page 1 of 1

mysql_data_seek error

Posted: Tue Nov 02, 2010 8:58 am
by IGGt
Hi Guys,

I am trying to do two things from one MySQL query. I believe I need to use 'mysql_data_seek' after the first one so that I can then use the second one, but I can't get it to work. My code so far is:

Code: Select all

//get list of servers to query, and choose them one at a time
for($a = 0; $a <sizeof($slaveRes_array); $a++) 	{
	$con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']); 
	mysql_select_db($dbs, $con);
//get list of MySQL Queries, and run them against the current server, one at a time      
	for($b = 0; $b <sizeof($query_array); $b++) {
		$slaveState = mysql_query($query_array[$b]['query1'], $con);
//1st Query
// Take the result of the query, and put it into an array
			while(($slave_array2[] = mysql_fetch_assoc($slaveState)) || array_pop($slave_array2));

// Reset using MySQL_Data_Seek
			mysql_data_seek($slaveState,0);

// Get the database 'name' for each server that returned a result			 
			while($row = mysql_fetch_assoc($slaveState)) {
				if ($row['Slave_IO_Running'] == "No")  {
					$slave_array[]['name'] = $slaveRes_array[$a]['database'];}	
				 else { print "nothing";};
				}			
				}
// Run Query2...Query3....etc.
                     		}
So what I end up with (for Query1) is an array that has the data for each server that returned a result, and a second array, containing the 'friendly name' for each server that returned a result, hopefully allowing me to tie them together later on.

Unfortunately when I run this I get:

[text]
Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 0 is invalid for MySQL result index 7 (or the query data is unbuffered) in C:\wamp\www\ts\slaveQuery.php on line 32

Warning: mysql_data_seek() [function.mysql-data-seek]: Offset 0 is invalid for MySQL result index 28 (or the query data is unbuffered) in C:\wamp\www\ts\slaveQuery.php on line 32
[/text]

line 32 is the line that says "mysql_data_seek($slaveState,0);"

what am I doing wrong?

Re: mysql_data_seek error

Posted: Tue Nov 02, 2010 10:27 am
by AbraCadaver
I am very confused when looking at this code, so if you code give a step-by-step explanation of what you want to do, the code should be fairly easy.

Re: mysql_data_seek error

Posted: Tue Nov 02, 2010 10:48 am
by IGGt
Sorry about the confusing code.

Basically I have a list of databases that I want to query which are in an array:

Code: Select all

$slaveRes_array[0] = array('server' => 'localhost:3306',
                             'user' => $u,
                             'password' => $p,
                             'database' => 'MySQL01',
                             'sendmail' => '0',
                             'repeat' => '0',
                             'dbID' => '1'
                                           );
Then I have a list of the queries that I want to run against each database:

Code: Select all

$query_array[0] = array(	'query1' => "SHOW SLAVE STATUS");
The Code I have connects to the first database, and then runs the first query.

Code: Select all

//get list of servers to query, and choose them one at a time
for($a = 0; $a <sizeof($slaveRes_array); $a++)  {
        $con = mysql_connect($slaveRes_array[$a]['server'], $slaveRes_array[$a]['user'], $slaveRes_array[$a]['password']);
        mysql_select_db($dbs, $con);
//get list of MySQL Queries, and run them against the current server, one at a time      
        for($b = 0; $b <sizeof($query_array); $b++) {
                $slaveState = mysql_query($query_array[$b]['query1'], $con);
if the query returns a resultset (and I know that sometimes they wont) it places the data for that resultset into an array:

Code: Select all

while(($slave_array2[] = mysql_fetch_assoc($slaveState)) || array_pop($slave_array2));
I also then want to know which databases returned a result, and put that data into another array:

Code: Select all

while($row = mysql_fetch_assoc($slaveState)) {
                                if ($row['Slave_IO_Running'] == "No")  {
                                        $slave_array[]['name'] = $slaveRes_array[$a]['database'];}     
                                 else { print "nothing";};
                                }                      
                                }
My understanding at this point is that because I am trying to query $slaveState twice, I need to reset the pointer to 0 after the first statement.

I hope this makes it a little clearer.

Re: mysql_data_seek error

Posted: Tue Nov 02, 2010 11:24 am
by AbraCadaver
foreach is your friend! Not sure if this will get exactly what you want, but it's a start (not tested in any way):

Code: Select all

foreach($slaveRes_array as $db) {
	$con = mysql_connect($db['server'], $db['user'], $db['password']);
	mysql_select_db($db['database'], $con);
	
	foreach($query_array as $query) {
		$res = mysql_query($query['query1']);
		
		while($row = mysql_fetch_assoc($res)) {
			$slaveState[] = $row;
		}
		if ($row['Slave_IO_Running'] == "No") {
			$slave_array[]['name'] = $db['database'];
		}
	}
}

Re: mysql_data_seek error

Posted: Tue Nov 02, 2010 11:56 am
by IGGt
Ah, that looks promising.

I have modified it to:

Code: Select all

foreach($slaveRes_array as $db) {
	$con = mysql_connect($db['server'], $db['user'], $db['password']);
		mysql_select_db($db['database'], $con);
       
        foreach($query_array as $query) {
        	$res = mysql_query($query['query1']);
               
                while($row = mysql_fetch_assoc($res)) {
                	$slaveState[] = $row;
                	$slave_array[]['name'] = $db['database'];
                }
                
        }
}
Which serves the purpose. If there is a resultset returned I get an entry in $slave_array telling me which database, and an entry in $slaveState with the data.

From there I can take the first item in each array and use it, the second item in each array and use it etc.

Re: mysql_data_seek error

Posted: Tue Nov 02, 2010 1:00 pm
by AbraCadaver
Looks good. I didn't really know what you were going for in the second array, but now it makes more sense. Depending on what you need this may be of use:

Code: Select all

while($row = mysql_fetch_assoc($res)) {
   $slaveState[$db['database']] = $row;
}
And then when you use the arry you can loop over it and get the key and data:

Code: Select all

foreach($slaveState as $name => $row) {

Re: mysql_data_seek error

Posted: Wed Nov 03, 2010 9:40 am
by IGGt
cheers for your help, that all works perfectly, and has reduced the size of my code quite significantly.

cheers