Comparing 2 MySQL table with Server Connect

[updates]

Hi everybody,
I m so sorry these week I din t follow properly the forum but I had to work (sometimes happens)

I got a problem that I think I can solve in wappler but because I’m not so good in Conditions in Server Connect I m here to challenge you (translation: help me pleaseeeeee )

The problem is the following:

1. I have two tables in MySQL with long list of emails.
2. The second table contains a subset of the first email.

Like a full list of mailing list and just the list who opened the email.
I would love to make a repeat in ServerConnect to compare the list and when an email is found in the “opened” table, it is deleted in the big list…

I tried to do it… but at the last I got message from Wappler:
“Roberto, switch off and go outside walking, it is better!!”

Any suggestion?

[Hyperbytes]

Is this a one off process or does it need to be available via a web page, much easier to do as a SQL query directly from a database manager.

[updates]

can you give me an example ?

[Hyperbytes]

Obviously i cant be precise as i don’t have the structure but the query would be similar

DELETE * FROM table1 WHERE table1.email in select email from [table2]

Alternatively you could create a new table using a union which will merge the two lists
something like:
select email from main union select email from list
(EDIT. After re-reading this second option is probably not what you want)
Sadly neither are supported directly in Wappler but could be run through an SQL manager

[s.alpaslan]

You can do this via server connect repeat . Repeat table one if results match with other table after then insert them into new table

[updates]

that’s I would love to… can you explain me better please @s.alpaslan
then I will make my client happy

[s.alpaslan]

Of course but I am not at office or home now… I will write you detailed explantation to night … no problem

I created many documentation about queries I will share with you

[updates]

Thank you very much !
take your time no problem

[s.alpaslan]

You are welcome

[s.alpaslan]

Hello @updates ,

I have questions ;

1. do you want to see all emails and subsets ?
2. do you want to see just 1 email and own all subsets ?

I need to see your table fields . Can you share tables and table fields ?

[updates]

OK I will as I will back to office thank you …

[updates]

Hi @s.alpaslan
The tables are: email_total
and email_brokers

email_total:

et_id (Int)
et_emailaddress

email_brokers:

eb_id
eb_emailaddress

emaul_brokers is a subset of email_total

then the target is to delete in email_total the emails addresses in email_brokers

Because email_brokers is changing then sometimes I would run this routine

[Hyperbytes]

I never got the answer to how often this query has to be run and if it needs to be inside a webpage but if it is a one off or an occasional task then running the following from a query manager will remove all entries from email_total which are in email_brokers

delete from email_total where et_emailaddress in (select eb_emailaddress as et_emailaddress from email_brokers)

(or i could give you a few lines of PHP to put in the page to do this, not the wappler way but it will work efficiently)

[updates]

thank you very much @Hyperbytes.
I m curios any way to know if there is a way as @s.alpaslan said so I could give like a tool in the dashboard

[Hyperbytes]

Just on smartphone now but will send you some code in morning to add it to a webpage.

[s.alpaslan]

hello @updates ,

really busy for me sorry for my late reply ..

you can do this via server connect repeating

second way

wappler validation

clean all duplicate emails then use this way

Validating Input Value Against Database

Check if a Database Record Already Exists Before Inserting a New Entry

Check the data in your Database before inserting possible duplicate data Using server-side data validation from server connect you can check if a record already exists in the database. This is useful when new users register to your site so you can check if the email is already used and if it is display an error message, while at the same time stopping the server insert action from inserting duplicate data.   Step 1: Select your Insert Action(1) and right click the step before the insert recor…

[updates]

Thank you very much @s.alpaslan the first solution is exactly what I wanted to achieve…

I wish to thank you that you reply to my also in a very busy day.

One think I would like to ask you… please
if you could explain me all the steps (when you will have time) because I do not understand the Set Value and how you did the condition.

Thank you very much in advance…
Roberto

[s.alpaslan]

you are welcome @updates, No problem

Step 1
set value just I want to show you … you can use it any post or get variables . it will work

Step 2
We need to check mails if exists or not exits via query email_total

Step 3
This is condition step . Expression has to be like this {{email_total}} …

if expression email_total return true you can create new action like delete/update/select or insert
if expression email_total return false then you can create new action like delete/update/select or insert

PS : you can use javascript operators in wappler ( https://www.w3schools.com/js/js_comparisons.asp )
and check all dmxzone videos and wappler documents …

[vowukur]

you can use javascript operators in wappler
https://www.welookups.com/js/js_comparisons.html