Addition within a loop

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Addition within a loop

Post 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?
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: Addition within a loop

Post 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);
}
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Addition within a loop

Post 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.
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Addition within a loop

Post 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);
                  }
?>
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: Addition within a loop

Post 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);
}
?>
IGGt
Forum Contributor
Posts: 173
Joined: Thu Nov 26, 2009 9:22 am

Re: Addition within a loop

Post by IGGt »

cheers for that, it does indeed look a lot neater, and it works great too.


cheers
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: Addition within a loop

Post by buckit »

Awesome! glad it worked out for you!
Post Reply