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
IGGt
Forum Contributor
Posts: 173 Joined: Thu Nov 26, 2009 9:22 am
Post
by IGGt » Fri Jul 16, 2010 9:25 am
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?
liljester
Forum Contributor
Posts: 400 Joined: Tue May 20, 2003 4:49 pm
Post
by liljester » Fri Jul 16, 2010 12:03 pm
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
Post
by IGGt » Mon Jul 19, 2010 5:52 am
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
Post
by IGGt » Mon Jul 19, 2010 8:27 am
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> ";
echo "<td>",$row["Host"],"</td> ";
echo "<td>",$row["State"],"</td> </tr>";
} else {
echo "";
}
}
$y++;
$yy++;
}
}
mysql_close($connection);
}
?>
buckit
Forum Contributor
Posts: 169 Joined: Fri Jan 01, 2010 10:21 am
Post
by buckit » Mon Jul 19, 2010 8:50 am
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> ";
echo "<td>",$row["Host"],"</td> ";
echo "<td>",$row["State"],"</td> </tr>";
} else {
echo "";
}
}
}
mysql_close($con);
}
?>
IGGt
Forum Contributor
Posts: 173 Joined: Thu Nov 26, 2009 9:22 am
Post
by IGGt » Mon Jul 19, 2010 9:09 am
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
Post
by buckit » Mon Jul 19, 2010 9:21 am
Awesome! glad it worked out for you!