Page 1 of 1

problem returning array in correct format

Posted: Tue Nov 02, 2010 10:31 am
by IGGt
I have a script that runs a query against a MySQL database, then, if it returns a resultset, it takes the 'freindly name from an array containg the database connections, then adds the data for the resultset.
Unfortunately at the minute, it doesn't quite work the way that I want.

So far I have:

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		
			while($row = mysql_fetch_assoc($SlaveState)) {
				for($c = 0; $c <mysql_num_rows($SlaveState); $c++)	{
					$slave_array[]['name'] = $slaveRes_array[$a]['database'];
						for($d = 0; $d <mysql_num_fields($SlaveState); $d++)	{
							$slave_array[][mysql_field_name($SlaveState,$d)] = mysql_result($SlaveState,$c,mysql_field_name($SlaveState,$d));	 
						}
				}
				}
// Run Query2...Query3....etc.				
        		}

The problem is that at the minute it puts each field into a separate part of the array e.g.

[text]Array (
[0] => Array ( [name] => MySQL02_5083 )
[1] => Array ( [Slave_IO_State] => Waiting for master to send event )
[2] => Array ( [Master_Host] => localhost )
[3] => Array ( [Master_User] => root )[/text]

Whereas what I am trying to achieve is more like:
[text]Array (
[0]=> Array ( [name] => MySQL02 )
[Slave_IO_State] => Waiting for master to send event )
[Master_Host] => localhost )
[Master_User] => root )...
[1] => Array ( [name] => MySQL03[/text]
etc. etc.

But I can't see how to achieve this?

Re: problem returning array in correct format

Posted: Tue Nov 02, 2010 10:41 am
by AbraCadaver
That code is whack. Post your queries / table structures and what you want to achieve.

Re: problem returning array in correct format

Posted: Tue Nov 02, 2010 11:09 am
by IGGt
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[] = array(	'query1' => "SHOW SLAVE STATUS");
For the moment there is only the one query "SHOW SLAVE STATUS", but I will be adding to it over time. (n.b. all queries will only ever return one line of data).

I then connect to the first database, and run the first query. What I want to do is get the result of that query into an array, and have the first element in that array the name of the database.
So what I end up with is something like:

Code: Select all

print_r ($slave_array);
[text]
Array (
//First Database that returns a resultest
[0] => Array (
[name] => MySQL02 //This item is from the $slaveRes_array that contains info about the database
[Slave_IO_State] => Waiting for master to send event //This is the first field in the resultset
[Master_Host] => localhost //This is the second field in the resultset
. . .next item from the resultset. . .
// Second Database that returns a resultset
[1] => Array (
[name] => MySQL03
[Slave_IO_State] => Waiting for master to send event
. . .next item from the resultset. . .
[/text]


I hope this makes things a little clearer.


The reason for doing this is becuase I figure if I have 10 databases and 10 queries, it is better to open a connection the database once, and run all 10 queries in one go, rather than open and close a new connection for each query, as the script will be set to run every 60 seconds or so.

Re: problem returning array in correct format

Posted: Tue Nov 02, 2010 11:26 am
by AbraCadaver
Let's stay on the other thread: viewtopic.php?f=1&t=123442

Re: problem returning array in correct format

Posted: Tue Nov 02, 2010 11:37 am
by IGGt
fair enough. I raised two threads as they referred to different problems (although I used the same data for testing).