Comparing 2 MySQL table with Server Connect

#1

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 :sweat_smile:)

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? :wink:

#2

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.

2 Likes
#3

can you give me an example ?

#4

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

#5

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

#6

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

#7

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

#8

Thank you very much !
take your time no problem :slight_smile:

#9

You are welcome :slight_smile:

#10

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 ?

#11

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

#12

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

#13

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)

#14

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

#15

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

#16

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

#17

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

#18

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 …

#19

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