I have created a script that runs once a week and copies data from one
table (phpbb_users is the actual table name) in a database called
users
I have a table called users. There are two tables in this database.
The table names are:
phpbb_users
phpbb_users_backup
The two tables have identical structures. I made phpbb_users_backup a
duplicate structure of phpbb_users. Then I wrote a script that runs
once a week and loads that 2nd table phpbb_users_backup with the data
from phpbb_users. I just dump the data from phpbb_users and load it
into phpbb_users_backup. A few minutes after that happens on that day
once a week I run a script that does a compare of the two tables and
outputs it to a file. The output file displays about 23 columns out of
40 and shows me all data even nulls that are in the columns.
The issue I have:
There are 23 columns in each record. Each record that appears in that
output file has one or more columns that have been changed. What I
need to do is display only the username column whether it changes or
not and all other columns that were changed or different need to be
displayed as well. In other words I need to always see the username
column in the output file and only columns that were changed/different
from one table to the other. Example, if a column named
user_last_name changes in phpbb_users I want to see that
user_last_name and username column displayed in my output file only
and not the other 21 columns of data that did not change on that
record.
I have the sql I use to run the comparison. If anyone needs more info
please post it here or email me directly at rk******@onlineacc.com.
SELECT
t2.username, t1.user_last_name, t2.user_first_name, t2.user_email,
t2.user_company, t2.user_address, t2.user_city, t2.user_state,
t2.user_zip, t2.user_work_phone, t2.user_work_fax, t2.user_cellular,
t2.user_pager, t2.user_prop_loca_muni, t2.user_prop_loca_muni2,
t2.user_prop_loca_muni3, t2.user_prop_loca_muni4,
t2.user_prop_loca_muni5, t2.user_prop_loca_muni6,
t2.user_prop_loca_county, t2.user_prop_loca_county2,
t2.user_prop_loca_county3, t2.user_prop_loca_county4,
t2.user_prop_loca_county5, t2.user_prop_loca_county6, t2.user_website,
t2.user_from
FROM
phpbb_users_backup AS t1 RIGHT OUTER JOIN
phpbb_users AS t2 ON ((t1.user_id=t2.user_id) AND
(t1.username=t2.username) AND (t1.user_email=t2.user_email OR
(t1.user_email IS NULL AND t2.user_email IS NULL)) AND
(t1.user_company=t2.user_company OR (t1.user_company IS NULL AND
t2.user_company IS NULL)) AND (t1.user_city=t2.user_city OR
(t1.user_city IS NULL AND t2.user_city IS NULL)) AND
(t1.user_zip=t2.user_zip OR (t1.user_zip IS NULL AND t2.user_zip IS
NULL)) AND (t1.user_phone=t2.user_phone OR (t1.user_phone IS NULL AND
t2.user_phone IS NULL)) AND (t1.user_fax=t2.user_fax OR (t1.user_fax
IS NULL AND t2.user_fax IS NULL)) AND
(t1.user_prop_loca_muni=t2.user_prop_loca_muni OR
(t1.user_prop_loca_muni IS NULL AND t2.user_prop_loca_muni IS NULL))
AND (t1.user_prop_loca_muni2=t2.user_prop_loca_muni2 OR
(t1.user_prop_loca_muni2 IS NULL AND t2.user_prop_loca_muni2 IS NULL))
AND (t1.user_prop_loca_muni3=t2.user_prop_loca_muni3 OR
(t1.user_prop_loca_muni3 IS NULL AND t2.user_prop_loca_muni3 IS NULL))
AND (t1.user_prop_loca_muni4=t2.user_prop_loca_muni4 OR
(t1.user_prop_loca_muni4 IS NULL AND t2.user_prop_loca_muni4 IS NULL))
AND (t1.user_prop_loca_muni5=t2.user_prop_loca_muni5 OR
(t1.user_prop_loca_muni5 IS NULL AND t2.user_prop_loca_muni5 IS NULL))
AND (t1.user_prop_loca_muni6=t2.user_prop_loca_muni6 OR
(t1.user_prop_loca_muni6 IS NULL AND t2.user_prop_loca_muni6 IS NULL))
AND (t1.user_prop_loca_county=t2.user_prop_loca_county OR
(t1.user_prop_loca_county IS NULL AND t2.user_prop_loca_county IS
NULL)) AND (t1.user_prop_loca_county2=t2.user_prop_loca_count y2 OR
(t1.user_prop_loca_county2 IS NULL AND t2.user_prop_loca_county2 IS
NULL)) AND (t1.user_prop_loca_county3=t2.user_prop_loca_count y3 OR
(t1.user_prop_loca_county3 IS NULL AND t2.user_prop_loca_county3 IS
NULL)) AND (t1.user_prop_loca_county4=t2.user_prop_loca_count y4 OR
(t1.user_prop_loca_county4 IS NULL AND t2.user_prop_loca_county4 IS
NULL)) AND (t1.user_prop_loca_county5=t2.user_prop_loca_count y5 OR
(t1.user_prop_loca_county5 IS NULL AND t2.user_prop_loca_county5 IS
NULL)) AND (t1.user_prop_loca_county6=t2.user_prop_loca_count y6 OR
(t1.user_prop_loca_county6 IS NULL AND t2.user_prop_loca_county6 IS
NULL)) AND (t1.user_member_no=t2.user_member_no OR (t1.user_member_no
IS NULL AND t2.user_member_no IS NULL)) AND
(t1.user_website=t2.user_website OR (t1.user_website IS NULL AND
t2.user_website IS NULL)) AND (t1.user_gender=t2.user_gender) AND
(t1.user_work_phone=t2.user_work_phone OR (t1.user_work_phone IS NULL
AND t2.user_work_phone IS NULL)) AND
(t1.user_work_fax=t2.user_work_fax OR (t1.user_work_fax IS NULL AND
t2.user_work_fax IS NULL)) AND (t1.user_cellular=t2.user_cellular OR
(t1.user_cellular IS NULL AND t2.user_cellular IS NULL)) AND
(t1.user_pager=t2.user_pager OR (t1.user_pager IS NULL AND
t2.user_pager IS NULL)) AND (t1.user_address=t2.user_address OR
(t1.user_address IS NULL AND t2.user_address IS NULL)) AND
(t1.user_first_name=t2.user_first_name OR (t1.user_first_name IS NULL
AND t2.user_first_name IS NULL)) AND
(t1.user_last_name=t2.user_last_name OR (t1.user_last_name IS NULL AND
t2.user_last_name IS NULL)) AND (t1.user_state=t2.user_state OR
(t1.user_state IS NULL AND t2.user_state IS NULL)) AND
(t1.user_realname=t2.user_realname) AND
(t1.user_home_phone=t2.user_home_phone OR (t1.user_home_phone IS NULL
AND t2.user_home_phone IS NULL)) AND
(t1.user_home_fax=t2.user_home_fax OR (t1.user_home_fax IS NULL AND
t2.user_home_fax IS NULL)))
WHERE
t1.user_birthday IS NULL;
Another thing I would love to do is if the column is NULL for any of
the 22 columns (username is never null) I do not want to display the
word NULL. I would rather it be blank. In the event that a column is
changed from an actual last_name to nothing in that field then I do
not want to show NULL I would rather it be left blank.
Robert Knowles
rk******@onlineacc.com
