Page 1 of 1

change colour of td based on other td

Posted: Mon Jul 26, 2010 11:13 am
by IGGt
Whilst, I'm sure this must be possible, I can't see how, any ideas appreciated...

I have a PHP script which runs a set of queries, against two MySQL databases, and presents them as a table. One is a master, the other a slave, the idea being to compare the number of entries in various tables.

Code: Select all

$u = "root";
$p = "xxx";
$p2 = "xxx"; 
$dbs = "dbname";
	
		$connections_array[] = array('server' => '123.1.2.3:3306',
                             'user' => $u,
                             'password' => $p,
                             'database' => 'db Master');                                 
		$connections_array[] = array('server' => '123.1.2.4:3306',
                             'user' => $u,
                             'password' => $p2,
                             'database' => 'db Slave' );
		
        
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab0");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab1");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab2");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab3");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab4");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab5");

?>

<!--<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">-->

<html>

    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
        <title>db replication v1.0</title>
        <link rel="stylesheet" type="text/css" href="db_Count.css" />
    </head>

    <body>
		<div align="center">    	
		<p class="heading">Results of db Replication</p>
<?php

		echo "<p>";
		$timezone = "Europe/London";
    	date_default_timezone_set ($timezone);
    	echo date('H:i:s');
    	echo "</p>";

echo "<table class=\"center\">";
        echo "<tr class=\"hcol\"><th>DB Name</th>";
        echo "<th>tab0</th>";
        echo "<th>tab1</th>";
        echo "<th>tab2</th>";
        echo "<th>tab3</th>";
        echo "<th>tab4</th>";
        echo "<th>tab5</th>"</tr>";
        	
for($i = 0; $i <sizeof($connections_array); $i++) {
        $con = mysql_connect($connections_array[$i]['server'], $connections_array[$i]['user'], $connections_array[$i]['password']); 
        mysql_select_db($dbs, $con);
        $dbName = $connections_array[$i]['database'];
        echo "<tr><td>$dbName</td>";
        
        for($q = 0; $q <sizeof($query_array); $q++) {
        	$result = mysql_query($query_array[$q]['query'], $con) or print("<p>ERROR:</p>".mysql_error());
        	if ( !$result ) { goto end;}        
        	
                        while($row = mysql_fetch_assoc($result)) {
							
									foreach ($row as $attribute)
									if ( is_null($attribute) )
												{ 	print "<td>&nbsp" ;
													print "\n";
													print "<br/></td>";
												} else {
													print "<td>{$attribute} " ;
													print "\n";
													print "<br/></td>";
												}
																
								} 	
								}
								     echo "</tr>";
							
        mysql_close($con); }
        end:
echo "</table> </br>";        
?>
		</div>	
What I was hoping to do is find a way to change the colour of a field if the slave doesn't match the Master. I've done this based on the content of the field, but can't see a way to do it by matching it against a different field.

Re: change colour of td based on other td

Posted: Mon Jul 26, 2010 12:47 pm
by websitesca
First, if you want to make a td background color for your website design, then you want to do

[text]<td style='background-color: red;'>[/text]

Since you're comparing your Master and Slave values, I'm assuming they have the same primary key values. If not, well then, how would you know they are the same object to compare in the first place, right?

So, you're going to have to put everything into 1 array. The array should have 2 dimensions. The first dimension will be your ID. The second dimension will be your actual records.

Here is some pseudo code:

Code: Select all

$array = array();
for each $db in databases
{
  for each $row in $db
  {
    if (! isset($array[$row[ID]])) $array[$row[ID]] = array();
    $array[$row[ID]][] = $row;
  }
}
Now, you should be able to do stuff as simple as:

Code: Select all

if ($array[5][0]['field'] != $array[5][1]['field'])
  print "red";
else
  print "green";
The 5 are your database IDs, the 0 & 1 are the different data in your databases and the 'field' is the column name that you want to compare.

The code is just rough - but hopefully you get the idea for your particular website.

Hope that helps!
Georges,
Websites.ca - Website Design Canada

Re: change colour of td based on other td

Posted: Tue Jul 27, 2010 2:38 am
by IGGt
cheers, I see what you're getting at. I'll have a play with that today, to see what I come up with.


cheers

Re: change colour of td based on other td

Posted: Tue Jul 27, 2010 9:41 am
by IGGt
I don't seem to be getting too far with this.

Am I right in thinking that I create my table headings, then I create the new array, then I run the rest of my script as before, but factoring in the comparison within the new array. Something like:

Code: Select all

//set the variables etc.
set connection_array
set query_array

<body>
// open the table, and create headings
echo "<table class=\"center\">";
        echo "<tr class=\"hcol\"><th>DB Name</th>";
        echo "<th>tab0</th>";
        echo "<th>tab1</th>";
        echo "<th>tab2</th>";
        echo "<th>tab3</th>";
        echo "<th>tab4</th>";
        echo "<th>tab5</th>"</tr>";

// create the new_array
$new_array = array();
for($i = 0; $i <sizeof($connections_array); $i++) {
        $con = mysql_connect($connections_array[$i]['server'], $connections_array[$i]['user'], $connections_array[$i]['password']); 
        mysql_select_db($dbs, $con);
        $new_array = array('ID' => $connections_array[$i]['ID']);
			{
  				for($q = 0; $q <sizeof($query_array); $q++) {
        		$result = mysql_query($query_array[$q]['query'], $con);
  					{
    					if (! isset($new_array[$row[ID]])) $new_array[$row[ID]] = array();
    					$array[$row[ID]][] = $row;
  }}
}}

//load data using the original variables
for($i = 0; $i <sizeof($connections_array); $i++) {
        $con = mysql_connect($connections_array[$i]['server'], $connections_array[$i]['user'], $connections_array[$i]['password']); 
        mysql_select_db($dbs, $con);
        $dbName = $connections_array[$i]['database'];
        echo "<tr><td>$dbName</td>";
        
        for($q = 0; $q <sizeof($query_array); $q++) {
        	$result = mysql_query($query_array[$q]['query'], $con) or print("<p>ERROR:</p>".mysql_error());
        	     
        	
                        while($row = mysql_fetch_assoc($result)) {
							
									
									foreach ($row as $attribute)
									if ( is_null($attribute) )
// somewhere in here add the comparison between the new_array
												{ 	print "<td>&nbsp" ;
													print "\n";
													print "<br/></td>";
												} else {
													print "<td>{$attribute} " ;
													print "\n";
													print "<br/></td>";
												}
																
								} 	
								}
								     echo "</tr>";
							
        mysql_close($con); }

echo "</table> </br>";
At present it seems to make sense except I can't figure out how to get the 'field' names. As each query would result in a heading called 'count(*)'.

Re: change colour of td based on other td

Posted: Thu Jul 29, 2010 7:52 am
by IGGt
OK, I got there in the end:

Code: Select all

//Set the two arrays to be queried later
                $connections_array[] = array('server' => '123.1.2.3:3306',
                             'user' => $u,
                             'password' => $p,
                             'database' => 'db Master');                                
                $connections_array[] = array('server' => '123.1.2.4:3306',
                             'user' => $u,
                             'password' => $p2,
                             'database' => 'db Slave' );
               
       
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab0",
                                          'name' => "tab0");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab1",
                                          'name' => "tab1");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab2",
                                          'name' => "tab2");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab3",
                                          'name' => "tab3");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab4",
                                          'name' => "tab4");
        $query_array[] = array('query' => "SELECT COUNT(*) FROM tab5",
                                          'name' => "tab5");

//number of entries in the $query_array
$inc = sizeof($query_array);

//Create $new_array for comparison purposes
$new_array = array();
for($a = 0; $a <sizeof($connections_array); $a++) {
        $con = mysql_connect($connections_array[$a]['server'], $connections_array[$a]['user'], $connections_array[$a]['password']); 
        mysql_select_db($dbs, $con);
        $dbName = $connections_array[$a]['database'];        
        for($b = 0; $b <sizeof($query_array); $b++) {
        	$result = mysql_query($query_array[$b]['query'], $con) or print("<p>ERROR:</p>".mysql_error());
        	if ( !$result ) { goto end;}        
                        while($row = mysql_fetch_assoc($result)) {
									foreach ($row as $attribute)
// This is the important line -->
									$new_array[] = array($query_array[$b]['name'] => $attribute);								
								} }
        mysql_close($con); }
        end:
This creates an array called $new_array, that looks like:

Array (
[0] => Array ( [tab0] => 188 )
[1] => Array ( [tab1] => 5605 )
[2] => Array ( [tab2] => 4245 )
[3] => Array ( [tab3] => 4 )
[4] => Array ( [tab4] => 68363 )
[5] => Array ( [tab5] => 26 )

This then allowed me to do the following :

Code: Select all

for($q = 0; $q <sizeof($query_array); $q++) {
	        if ($new_array[$q] == $new_array[$q+$inc] || $new_array[$q-$inc]) {
														$c = " style= \"background-color: #D9FFB3;\"";
													} else {
														$c = " style= \"background-color: #CC9999;\""; 
												}
and of course it is then a simple case of attaching the $c variable to the <td> tags.