Page 1 of 1

Addition within a loop

Posted: Fri Jul 16, 2010 9:25 am
by IGGt
Hi,

I have a script that checks the Master status from a set of MySQL databases. At prsent the script is quite long as I have coded a separate instance for each database, but I am thinking I should be able to put it into a function and loop it instead. However I can't find a way to increment it to the next database each time.

What I have so far is:

Code: Select all

function functionMasterQuery() {    		

	$mysqlU="root";	
	$mysqlP=""; 
	
	$connection01 = mysql_connect("localhost:3306", $mysqlU, $mysqlP); 
	$connection02 = mysql_connect("localhost:3307", $mysqlU, $mysqlP);
	$connection03 = mysql_connect("localhost:3308", $mysqlU, $mysqlP);
	$connection04 = mysql_connect("localhost:3309", $mysqlU, $mysqlP);
	$connection05 = mysql_connect("localhost:3310", $mysqlU, $mysqlP);
	
	$masterquery = "Show processlist";
	
	$db1 = "MySQL01";
	$db2 = "MySQL02";
	$db3 = "MySQL03";
	$db4 = "MySQL04";
	$db5 = "MySQL05";
	
	While($a<6) {
		connection = $connection01;
		$db = $db1;
	    		$result = mysql_query ($masterquery, $connection);
				while ($row = mysql_fetch_assoc($result)) {
					if($row['Command'] == "Binlog Dump"){
												echo "<tr><td>$db</td>";
												echo "<td>",$row["Host"],"</td>";
												echo "<td>",$row["State"],"</td></tr>";  
												} else {
												echo "";
												}
					                                                        }
			   }
			}
?>
<head></head>
<body>
<table><?php functionMasterQuery() ?></table>
</body>
But somehow I need a way to increment $connection01 to $connection02, and $db1 to $db2. Is there an easy way to do this?

Re: Addition within a loop

Posted: Fri Jul 16, 2010 12:03 pm
by liljester
try something like this:

Code: Select all

$username = "root";
$password = "";
$server = "localhost";
$port = 3306;

$query = "show processlist";

for($i = 1; $i <=5; $i++) {
	$connection = mysql_connect(("localhost:". (($port + $i) - 1)), $username, $password);
	mysql_select_db(("mysql0". $i), $connection);
	
	$result = mysql_query($query, $connection);
	while($row = mysql_fetch_assoc($result)) {
		// do your printing
	}
	mysql_close($connection);
}

Re: Addition within a loop

Posted: Mon Jul 19, 2010 5:52 am
by IGGt
Thanks for that, it is working quite well on my test site. However, as it stands I can't see how to make it work when live. The problem is that all my test servers are on localhost, so that part doesn't need to change, however when Live, they will be on different servers, so I need a way to change the $server variable from $server to $server2, $server3 etc. or to change the content of the $server variable each time.

Re: Addition within a loop

Posted: Mon Jul 19, 2010 8:27 am
by IGGt
OK, I think I have sussed it, although I don't know if it is necessarily the best way to go.

Code: Select all

<?php
function functionMasterQuery() {

$S1 = "123.123.123.1:";
$S2 = "123.123.123.2:";
$S3 = "123.123.123.3:";
$S4 = "125.56.25.25:";
$S5 = "96.35.63.56:";
$x = "S";
$y = "1";

$db1 = "MySQL01";
$db2 = "MySQL02";
$db3 = "MySQL03";
$db4 = "MySQL04";
$db5 = "MySQL05";
$xx = "db";
$yy = "1";

$U =  "root";
$P = "";
$port = 3306;
$query = "show processlist";

for($i = 1; $i <=5; $i++) {

	$Server = ${$x.$y};
	$db = ${$xx.$yy};  {
	
		$connection = mysql_connect(($Server . (($port + $i) - 1)), $U, $P);
       
			$result = mysql_query($query, $connection);
				while($row = mysql_fetch_assoc($result)) {
					if($row['Command'] == "Binlog Dump") {
	        				echo "<tr><td>$db</td>&nbsp";
                				echo "<td>",$row["Host"],"</td>&nbsp";
						echo "<td>",$row["State"],"</td>&nbsp</tr>";
  						} 	else 	{	
						echo "";
					        }	
					}
					$y++;
					$yy++;
				}
                            }	
        mysql_close($connection);
                  }
?>

Re: Addition within a loop

Posted: Mon Jul 19, 2010 8:50 am
by buckit
I personally would build an array with your servers, database, and username/password.

gives you more flexibility in my opinion... and keeps it a bit cleaner.

Code: Select all

<?php
function functionMasterQuery() {

$u = 'root';
$p = 'password';

$connections_array[] = array('server' => '123.123.123.1:3306',
					   'user' => $u,
					   'password' => $p,
					   'database' => 'MySQL01'
					   );
$connections_array[] = array('server' => '123.123.123.2:3307',
					   'user' => $u,
					   'password' => $p,
					   'database' => 'MySQL02'
					   );
$connections_array[] = array('server' => '123.123.123.3:3308',
					   'user' => $u,
					   'password' => $p,
					   'database' => 'MySQL03'
					   );
$connections_array[] = array('server' => '125.56.25.25:3309',
					   'user' => $u,
					   'password' => $p,
					   'database' => 'MySQL04'
					   );
$connections_array[] = array('server' => '96.35.63.56:3310',
					   'user' => $u,
					   'password' => $p,
					   'database' => 'MySQL05'
					   );


$query = "show processlist";

for($i = 0; $i <sizeof($connections_array); $i++) {

	$con = mysql_connect($connections_array[$i]['server'], $connections_array[$i]['username'], $connections_array[$i]['password']);
	mysql_select_db($connections_array[$i]['database'], $con);
	$result = mysql_query($query, $con);
			while($row = mysql_fetch_assoc($result)) {
					if($row['Command'] == "Binlog Dump") {
							echo "<tr><td>$db</td>&nbsp";
							echo "<td>",$row["Host"],"</td>&nbsp";
							echo "<td>",$row["State"],"</td>&nbsp</tr>";
							}       else    {       
							echo "";
							}       
					}
			} 
	mysql_close($con);
}
?>

Re: Addition within a loop

Posted: Mon Jul 19, 2010 9:09 am
by IGGt
cheers for that, it does indeed look a lot neater, and it works great too.


cheers

Re: Addition within a loop

Posted: Mon Jul 19, 2010 9:21 am
by buckit
Awesome! glad it worked out for you!