PHP MySql Compare 2 Columns in Different Tables, Perform a Calculation & Output Result

379

I've got 2 tables with exactly the same structure called table_new & table_old respectively. The table structure is below:

CREATE TABLE IF NOT EXISTS `table_new` (
  `Club_Number` int(11) default NULL,
  `Club_Name` varchar(60) character set utf8 default NULL,
  `Active_Members` int(11) default NULL,
  `Goals_Met` int(11) default NULL,
  `Last_Updated` date NOT NULL,
  `id` int(6) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Here's what I want to do:

Step 1. Compare table_new against table_old where table_new.Active_Members is greater than table_old.Active_Members OR table_new.Goals_Met is greater than table_old.Goals_Met WHERE table_new.Club_Number equals table_old.Club_Number

Step 2. If something positive is found (the results are greater than zero), calculate the difference between:

table_new.Active_Members and table_old.Active_Members & echo the results
table_new.Goals_Met and table_old.Goals_Met & echo the results

How do I achieve this using PHP & MySQL? Thanks a lot for your help.

760

Answer

Solution:

This query will give you required output

SELECT table_new.Active_Members, table_old.Active_Members, table_new.Goals_Met, table_old.Goals_Met FROM table_new 
INNER JOIN table_old ON table_new.id = table_old.id
WHERE (table_new.Active_Members > table_old.Active_Members OR table_new.Goals_Met > table_old.Goals_Met) 
AND table_new.Club_Number = table_old.Club_Number

398

Answer

Solution:

This query will be helpful to you

select convert(t_n.Active_Members - t_o.Active_Members, SIGNED) as 'Active Members',
convert(t_n.Goals_Met - t_o.Goals_Met, SIGNED) as 'Goals Met'
from table_new as t_n inner join 
table_old as t_o 
on t_n.Club_Number = t_o.Club_Number
where t_n.Active_Members > t_o.Active_Members OR 
t_n.Goals_Met > t_o.Goals_Met;

942

Answer

Solution:

PHP script display difference.

$result=$db->query('SELECT tabn.`Active_Members` as active_new, tabo.`Active_Members` as active_old, tabn.`Goals_Met` as goals_new, tabo.`Goals_Met` as goals_old FROM `table_new` as tabn LEFT JOIN `table_old` as tabo ON tabn.`Club_Number`=tabo.`Club_Number` WHERE (tabn.`Active_Members` > tabo.`Active_Members`) OR (tabn.`Goals_Met` > tabo.`Goals_Met`)');

if(count($result) > 0)
while($row = $result->fetch_assoc()){

echo 'Difference in Active_Members is: '.$row['active_new'] - $row['active_old']."\n";
echo 'Difference in Goals_Met is: '.$row['goals_new'] - $row['goals_old']."\n";

}

People are also looking for solutions to the problem: php - Forward any PayPal payment in EUR currency using openexchangerates.org

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question