Member Database
Home
About CARC
Coming Events
Communicator
Presentations
Field Day
Photo Gallery
Club News
Exams
Links
North Peak
Officer/Staffs
Wants/Swaps
Net Control Roster
Net Checkin Script
Repeater Calendar
Repeater User Guide
Repeater Usage
Net Control User Guide
2014 Approved
  Bylaws and Amendments

This section establishes connection with the database carcmbrlst_20231017

$servername = mysql.pauatk5.dreamhosters.com
$username = carcuser
$password = its secret
$dbname = carcmbrlst_20231017
trying to connect
checking the connection
You have successfully connected to the carcmbrlst_20231017 database

This section shows edits made to each of the databases

USE carcmbrlst_20231017;
SELECT * FROM CARCMBRLST_202310177 LIMIT 100;
SELECT * FROM vu_people_detail LIMIT 100;
DROP TABLE IF EXISTS vuppl;
CREATE TABLE vuppl LIKE vu_people_detail;
INSERT INTO vuppl SELECT * FROM vu_people_detail;
ALTER TABLE vuppl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE vuppl ADD COLUMN member_fullName varchar(50) AFTER dues_cy;
UPDATE vuppl SET member_firstName = "Jacquie" where member_firstName LIKE "Jackie";
UPDATE vuppl SET member_firstName = "John R." where member_firstName = "John" and member_lastName = "Owens";
UPDATE vuppl SET member_firstName = "Michael S." where member_firstName = "Michael S";
UPDATE vuppl SET member_firstName = "Chris" where member_LastName = "Icide";
UPDATE vuppl SET member_fullName = concat_ws(" ", member_firstName, member_lastName);
DROP TABLE IF EXISTS carctb;
CREATE TABLE carctb LIKE CARCMBRLST_202310177;
INSERT INTO carctb SELECT * from CARCMBRLST_202310177;
ALTER TABLE carctb CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE carctb ADD COLUMN FullName varchar(50) AFTER CARCOfficer;
UPDATE carctb SET FirstName = "Jacquie" where FirstName LIKE "Jacqui";
UPDATE carctb SET FirstName = "Walt" where LastName LIKE "Long";
UPDATE carctb SET FirstName = "David G." where FirstName LIKE "David G";
UPDATE carctb SET LastName = "Hurlburt" where LastName LIKE "Hulburt";
UPDATE carctb SET FirstName = "Michael S." where FirstName LIKE "Michael S";
UPDATE carctb SET FullName = concat_ws(" ", FirstName, LastName);
DROP TABLE IF EXISTS merged;
CREATE TABLE merged AS SELECT ID, FullName, FirstName, LastName, Callsign, CARCMember, as_of_date, dues_cy, DatePaid FROM carctb JOIN vuppl ON member_fullname=FullName ORDER BY LastName, FirstName LIMIT 100;
ALTER TABLE merged ADD COLUMN Membership varchar(50) AFTER LastName;


SELECT * FROM merged LIMIT 100;
The merge table contains 72 rows
UPDATE merged SET Membership = 'Full' WHERE ID = 66
UPDATE merged SET Membership = 'Inactive' WHERE ID = 1
UPDATE merged SET Membership = 'Full' WHERE ID = 2
UPDATE merged SET Membership = 'Full' WHERE ID = 3
UPDATE merged SET Membership = 'Full' WHERE ID = 65
UPDATE merged SET Membership = 'Full' WHERE ID = 71
UPDATE merged SET Membership = 'Inactive' WHERE ID = 4
UPDATE merged SET Membership = 'Full' WHERE ID = 5
UPDATE merged SET Membership = 'Full' WHERE ID = 8
UPDATE merged SET Membership = 'Full' WHERE ID = 6
UPDATE merged SET Membership = 'Full' WHERE ID = 7
UPDATE merged SET Membership = 'Full' WHERE ID = 9
UPDATE merged SET Membership = 'Full' WHERE ID = 10
UPDATE merged SET Membership = 'Inactive' WHERE ID = 11
UPDATE merged SET Membership = 'Inactive' WHERE ID = 12
UPDATE merged SET Membership = 'Inactive' WHERE ID = 13
UPDATE merged SET Membership = 'Inactive' WHERE ID = 14
UPDATE merged SET Membership = 'Full' WHERE ID = 15
UPDATE merged SET Membership = 'Inactive' WHERE ID = 16
UPDATE merged SET Membership = 'Inactive' WHERE ID = 17
UPDATE merged SET Membership = 'Full' WHERE ID = 18
UPDATE merged SET Membership = 'Inactive' WHERE ID = 19
UPDATE merged SET Membership = 'Full' WHERE ID = 21
UPDATE merged SET Membership = 'Lifetime' WHERE ID = 20
UPDATE merged SET Membership = 'Full' WHERE ID = 22
UPDATE merged SET Membership = 'Inactive' WHERE ID = 23
UPDATE merged SET Membership = 'Full' WHERE ID = 24
UPDATE merged SET Membership = 'Inactive' WHERE ID = 25
UPDATE merged SET Membership = 'Inactive' WHERE ID = 26
UPDATE merged SET Membership = 'Full' WHERE ID = 27
UPDATE merged SET Membership = 'Inactive' WHERE ID = 28
UPDATE merged SET Membership = 'Full' WHERE ID = 30
UPDATE merged SET Membership = 'Full' WHERE ID = 29
UPDATE merged SET Membership = 'Full' WHERE ID = 31
UPDATE merged SET Membership = 'Full' WHERE ID = 32
UPDATE merged SET Membership = 'Full' WHERE ID = 33
UPDATE merged SET Membership = 'Inactive' WHERE ID = 34
UPDATE merged SET Membership = 'Full' WHERE ID = 67
UPDATE merged SET Membership = 'Full' WHERE ID = 56
UPDATE merged SET Membership = 'Full' WHERE ID = 35
UPDATE merged SET Membership = 'Full' WHERE ID = 36
UPDATE merged SET Membership = 'Associate' WHERE ID = 72
UPDATE merged SET Membership = 'Full' WHERE ID = 37
UPDATE merged SET Membership = 'Associate' WHERE ID = 68
UPDATE merged SET Membership = 'Inactive' WHERE ID = 38
UPDATE merged SET Membership = 'Full' WHERE ID = 39
UPDATE merged SET Membership = 'Silent Key' WHERE ID = 40
UPDATE merged SET Membership = 'Full' WHERE ID = 41
UPDATE merged SET Membership = 'Inactive' WHERE ID = 42
UPDATE merged SET Membership = 'Full' WHERE ID = 43
UPDATE merged SET Membership = 'Full' WHERE ID = 44
UPDATE merged SET Membership = 'Full' WHERE ID = 69
UPDATE merged SET Membership = 'Full' WHERE ID = 45
UPDATE merged SET Membership = 'Full' WHERE ID = 46
UPDATE merged SET Membership = 'Full' WHERE ID = 70
UPDATE merged SET Membership = 'Full' WHERE ID = 47
UPDATE merged SET Membership = 'Inactive' WHERE ID = 48
UPDATE merged SET Membership = 'Full' WHERE ID = 49
UPDATE merged SET Membership = 'Full' WHERE ID = 50
UPDATE merged SET Membership = 'Full' WHERE ID = 51
UPDATE merged SET Membership = 'Inactive' WHERE ID = 52
UPDATE merged SET Membership = 'Inactive' WHERE ID = 53
UPDATE merged SET Membership = 'Inactive' WHERE ID = 54
UPDATE merged SET Membership = 'Inactive' WHERE ID = 55
UPDATE merged SET Membership = 'Full' WHERE ID = 57
UPDATE merged SET Membership = 'Friend of Club' WHERE ID = 58
UPDATE merged SET Membership = 'Full' WHERE ID = 60
UPDATE merged SET Membership = 'Full' WHERE ID = 59
UPDATE merged SET Membership = 'Full' WHERE ID = 62
UPDATE merged SET Membership = 'Full' WHERE ID = 61
UPDATE merged SET Membership = 'Inactive' WHERE ID = 63
UPDATE merged SET Membership = 'Full' WHERE ID = 64

This section shows edits manually made to the 'merged' TABLE showing derived membership status

Although code exists to establish membership status based on callsign and membership status from TABLE carcmbrlst_20231017 it was found difficult to enter this derived status back into the 'merged' TABLE. In lieu of using results of the calculation, membership status was entered manually at this point.

Snippets from the 'merged' TABLE showing membership data

The table below shows all of the people that are contained in the 'merged' TABLE. This shows that there are some members haveing 'FULL' membership status not being shown as paid for 2023. I have noted these with an asterisk (*). Also, there is one person, KK6PCC (ID=39), that is shown as inactive but paid.



SELECT * FROM merged LIMIT 100;
ID Name CallSign Membership dues_cy CARC Member Date Paid
66 Jillian Aldersen KN6PIV Full 2023 True 2022-12-14 00:00:00
1 William J. Anderson KM6HYK Inactive False
2 Fernel Andong KM6UYM Full 2023 True 2022-08-10 00:00:00
3 Paul Atkins AI6BB Full 2023 True 2023-01-31 00:00:00
65 Steve Austin KN6ORM Full 2023 True 2022-12-14 00:00:00
71 Bharat Bailur W2OKB Full 2023 True 2023-04-12 00:00:00
4 Robert Barbitta W6LOG Inactive True
5 Gary Barnes KI6HIG Full 2023:2024:2025 True 2022-12-14 00:00:00
8 Anna Bernstein KJ6FHQ Full 2023 True 2023-01-23 00:00:00
6 Dan Bernstein N6ZEN Full 2023 True 2023-01-23 00:00:00
7 Emily Bernstein KK6FOI Full 2023 True 2023-01-23 00:00:00
9 Michael G. Bevington AA6XL Full 2023 True 2022-12-14 00:00:00
10 Miles E. Bond AB7ZX Full 2023 True 2023-01-23 00:00:00
11 Roy Brixen KE6MNJ Inactive False
12 Andy Calman K6ZP Inactive False
13 Ben Calvert KN6FUT Inactive False
14 David Chamberlin AE6DC Inactive False
15 David Conroy K6NIA Full 2023 True 2023-01-31 00:00:00
16 Don R. Corral KK6JJX Inactive True
17 Derek Dang AF6BT Inactive True
18 Tricia Dell KC6LCA Full 2023 True 2023-01-23 00:00:00
19 Alan W. Dye W6AWD Inactive True
21 Barbara Erbacher K6IIP Full 2023 True 2023-01-31 00:00:00
20 Frank C. Erbacher N6FG Lifetime 2023 True 2023-01-31 00:00:00
22 George Fenisey N6GYR Full 2022:2023:2024:2025:2026 True 2022-02-14 00:00:00
23 Carmel Gallagher KJ6ERS Inactive True 2022-03-01 00:00:00
24 Ariel Gallega K6RYL Full 2023 True 2023-01-31 00:00:00
25 Rudy Gaoay N6DOZ Inactive False
26 Scott C. Gillette KN6GQZ Inactive False
27 Georgia Grant KE6KRT Full 2023 True 2023-01-31 00:00:00
28 Paul Grigorieff N1HEL Inactive True
30 Kris Herbert N6QGZ Full 2023 False 2023-02-28 00:00:00
29 Michael S. Herbert WB6JKV Full 2023 True 2023-02-28 00:00:00
31 Breck Hitz W6CBH Full 2023 True
32 David G. Hurlburt WB6FEK Full 2023 True 2023-02-28 00:00:00
33 Jane Hurlburt KF6ACH Full 2023 True 2023-02-28 00:00:00
34 Chris Icide WY7W Inactive False
67 Dennis Kafka KN6QER Full 2023 True 2023-01-10 00:00:00
56 Zoe Kersteen-Tucker KI6DRM Full 2023 True 2023-01-31 00:00:00
35 Ralph Kugler KC6YDH Full 2023 True 2022-12-14 00:00:00
36 Jonathan Lancelle N6SJF Full 2023 True 2023-01-26 00:00:00
72 Julie Lancelle Associate 2023 True 2023-01-26 00:00:00
37 David L. Lawrence KF6TWW Full 2023 True 2023-03-13 00:00:00
68 Jacquie Lawrence Associate 2023 True 2023-03-13 00:00:00
38 William A. Lillie N6BCT Inactive True
39 Richard T. Lira KK6PCC Full 2023 False 2023-02-28 00:00:00
40 Walt Long KG6EDY SK Silent Key 2023 True 2022-12-14 00:00:00
41 Richard D. Markel KB6VT Full 2023 False
42 Victor M. Munoz KB6LGZ Inactive False
43 Ted Niemira W6SY Full 2023:2024:2025 True 2023-01-31 00:00:00
44 Tom Oliver KJ6OGL Full 2023 True 2022-12-14 00:00:00
69 John R. Owens NN6U Full 2023 True 2023-01-31 00:00:00
45 Steve Paganelli K6YUA Full 2023:2024 True 2023-03-13 00:00:00
46 Vernon Patterson W6VJJ Full 2023 True 2023-03-13 00:00:00
70 Ellen Price KN6UNF Full 2023 True 2022-06-03 00:00:00
47 Ron Purser WB9EGG Full 2023 True 2022-12-14 00:00:00
48 Michael S. Ransom KM6HZR Inactive False
49 Dan Reid N6DBR Full 2023 True 2023-03-13 00:00:00
50 David M. Rinck K6DMR Full 2023 True 2023-01-16 00:00:00
51 George Salet KJ6TSX Full 2023 True 2023-01-31 00:00:00
52 Linda C. Sanders KB6OIZ Inactive False
53 Mary Ellen Scherer AJ6J Inactive False
54 R. Scott Sutor KM6SCD Inactive False
55 Charles Tillman KC6CTT Inactive False
57 George H. Tucker W6HAF Full 2023 True 2023-01-31 00:00:00
58 Kelly Tuttle WB6AAJ Friend of Club False
60 Audrey J. Villyard WA2KPS Full 2023 True 2022-09-14 00:00:00
59 Casey Villyard N6TZE Full 2023 True 2022-09-14 00:00:00
62 Joshua Villyard N6TZF Full 2023 True 2022-09-14 00:00:00
61 Nikki Villyard KI6VRA Full 2023 True 2022-09-14 00:00:00
63 Pete Wanger WA6ECH Inactive False
64 Harry Ysselstein N6BHS Full 2023 True 2023-01-16 00:00:00


Membership Class Count
Full Members: 45
Associate Members: 2
Lifetime Members: 1
Silent Key Members: 1
Inactive: 22
Friend of Club: 1
re-checking the connection
You are still successfully connected to the carcmbrlst_20231017 database

USE carcmbrlst_20231017;
DROP TABLE IF EXISTS in_vuppl_not_carctb;
CREATE TABLE in_vuppl_not_carctb SELECT member_fullName, member_firstName, member_lastName FROM vuppl WHERE NOT EXISTS (SELECT FullName from carctb where carctb.FullName = vuppl.member_fullName) ORDER BY member_LastName, member_firstName LIMIT 100;;


SELECT * FROM in_vuppl_not_carctb LIMIT 100;
The in_vuppl_not_carctb table contains 9 rows
FullName
Dishaan Ahuja
Chris Bell
Anthony Dowler
Lucas Ford
Henry Lancelle
Stephen Martin
James Sanders
Jenni Williams
Victor Zarich
Last modified: October 26, 2023 10:39:20
For questions or comments about this website: Contact webmaster at: info@CoastsideARC.ORG
Copyright © 2000-2023, Coastside Amateur Radio Club, All Rights Reserved