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_bac kup
The two tables have identical structures. I made phpbb_users_bac kup a
duplicate structure of phpbb_users. Then I wrote a script that runs
once a week and loads that 2nd table phpbb_users_bac kup with the data
from phpbb_users. I just dump the data from phpbb_users and load it
into phpbb_users_bac kup. 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******@online acc.com.
SELECT
t2.username, t1.user_last_na me, t2.user_first_n ame, t2.user_email,
t2.user_company , t2.user_address , t2.user_city, t2.user_state,
t2.user_zip, t2.user_work_ph one, t2.user_work_fa x, t2.user_cellula r,
t2.user_pager, t2.user_prop_lo ca_muni, t2.user_prop_lo ca_muni2,
t2.user_prop_lo ca_muni3, t2.user_prop_lo ca_muni4,
t2.user_prop_lo ca_muni5, t2.user_prop_lo ca_muni6,
t2.user_prop_lo ca_county, t2.user_prop_lo ca_county2,
t2.user_prop_lo ca_county3, t2.user_prop_lo ca_county4,
t2.user_prop_lo ca_county5, t2.user_prop_lo ca_county6, t2.user_website ,
t2.user_from
FROM
phpbb_users_bac kup 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_compan y=t2.user_compa ny OR (t1.user_compan y IS NULL AND
t2.user_company IS NULL)) AND (t1.user_city=t 2.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_l oca_muni=t2.use r_prop_loca_mun i OR
(t1.user_prop_l oca_muni IS NULL AND t2.user_prop_lo ca_muni IS NULL))
AND (t1.user_prop_l oca_muni2=t2.us er_prop_loca_mu ni2 OR
(t1.user_prop_l oca_muni2 IS NULL AND t2.user_prop_lo ca_muni2 IS NULL))
AND (t1.user_prop_l oca_muni3=t2.us er_prop_loca_mu ni3 OR
(t1.user_prop_l oca_muni3 IS NULL AND t2.user_prop_lo ca_muni3 IS NULL))
AND (t1.user_prop_l oca_muni4=t2.us er_prop_loca_mu ni4 OR
(t1.user_prop_l oca_muni4 IS NULL AND t2.user_prop_lo ca_muni4 IS NULL))
AND (t1.user_prop_l oca_muni5=t2.us er_prop_loca_mu ni5 OR
(t1.user_prop_l oca_muni5 IS NULL AND t2.user_prop_lo ca_muni5 IS NULL))
AND (t1.user_prop_l oca_muni6=t2.us er_prop_loca_mu ni6 OR
(t1.user_prop_l oca_muni6 IS NULL AND t2.user_prop_lo ca_muni6 IS NULL))
AND (t1.user_prop_l oca_county=t2.u ser_prop_loca_c ounty OR
(t1.user_prop_l oca_county IS NULL AND t2.user_prop_lo ca_county IS
NULL)) AND (t1.user_prop_l oca_county2=t2. user_prop_loca_ county2 OR
(t1.user_prop_l oca_county2 IS NULL AND t2.user_prop_lo ca_county2 IS
NULL)) AND (t1.user_prop_l oca_county3=t2. user_prop_loca_ county3 OR
(t1.user_prop_l oca_county3 IS NULL AND t2.user_prop_lo ca_county3 IS
NULL)) AND (t1.user_prop_l oca_county4=t2. user_prop_loca_ county4 OR
(t1.user_prop_l oca_county4 IS NULL AND t2.user_prop_lo ca_county4 IS
NULL)) AND (t1.user_prop_l oca_county5=t2. user_prop_loca_ county5 OR
(t1.user_prop_l oca_county5 IS NULL AND t2.user_prop_lo ca_county5 IS
NULL)) AND (t1.user_prop_l oca_county6=t2. user_prop_loca_ county6 OR
(t1.user_prop_l oca_county6 IS NULL AND t2.user_prop_lo ca_county6 IS
NULL)) AND (t1.user_member _no=t2.user_mem ber_no OR (t1.user_member _no
IS NULL AND t2.user_member_ no IS NULL)) AND
(t1.user_websit e=t2.user_websi te OR (t1.user_websit e IS NULL AND
t2.user_website IS NULL)) AND (t1.user_gender =t2.user_gender ) AND
(t1.user_work_p hone=t2.user_wo rk_phone OR (t1.user_work_p hone IS NULL
AND t2.user_work_ph one IS NULL)) AND
(t1.user_work_f ax=t2.user_work _fax OR (t1.user_work_f ax IS NULL AND
t2.user_work_fa x IS NULL)) AND (t1.user_cellul ar=t2.user_cell ular OR
(t1.user_cellul ar IS NULL AND t2.user_cellula r IS NULL)) AND
(t1.user_pager= t2.user_pager OR (t1.user_pager IS NULL AND
t2.user_pager IS NULL)) AND (t1.user_addres s=t2.user_addre ss OR
(t1.user_addres s IS NULL AND t2.user_address IS NULL)) AND
(t1.user_first_ name=t2.user_fi rst_name OR (t1.user_first_ name IS NULL
AND t2.user_first_n ame IS NULL)) AND
(t1.user_last_n ame=t2.user_las t_name OR (t1.user_last_n ame IS NULL AND
t2.user_last_na me IS NULL)) AND (t1.user_state= t2.user_state OR
(t1.user_state IS NULL AND t2.user_state IS NULL)) AND
(t1.user_realna me=t2.user_real name) AND
(t1.user_home_p hone=t2.user_ho me_phone OR (t1.user_home_p hone IS NULL
AND t2.user_home_ph one IS NULL)) AND
(t1.user_home_f ax=t2.user_home _fax OR (t1.user_home_f ax IS NULL AND
t2.user_home_fa x IS NULL)))
WHERE
t1.user_birthda y 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******@online acc.com
