Extract domains+emails who has hosting but not domains administration

[cosmin]

Hi!

I'm searching for an ideea how to extract in a .csv/.txt a list of .tld domains name plus customer emails if they has no administration domains at me, have only the hosting.

I'm sure with php+mysql can be easy made, if you know php+mysql 😄

Also maybe with a phpmyadmin query is possible.

Many thanks!

Edited February 11, 2020 by cosmin

[brian!]

a starting point could be the SQL query in the thread below...

as is, it wouldn't give you client email addresses, so it would need a join to tblclients where tblclients.id = tbldomains.userid to get the email field.

if you also needed the specific TLD rather than the full domain, then that would require additional coding as the TLD isn't stored separately in the database tables, so it would need to be extracted from the domain value.

[cosmin]

Hi Brian and many thanks!

The .tld is not so important.
Can you tell me please exactly how can I exctract the customer's email for every domain? ☺️

Thanks again!

[brian!]

1 hour ago, cosmin said:

Can you tell me please exactly how can I extract the customer's email for every domain?

I assume you mean from the result of the query (as opposed to just ALL domains)...

SELECT DISTINCT tbldomains.domain, tblclients.email
FROM tbldomains
INNER JOIN tblclients ON tbldomains.userid = tblclients.id,
tblhosting
WHERE tbldomains.domain NOT IN ((SELECT domain from tblhosting))

the above should give you domain & email address of every domain that is in tbldomains that is not in tblhosting, e.g domains that do not have hosting.

if you wanted the opposite, e.g hosting domains that are not in tbldomains (e.g they're managed by the client or another company), then you could use...

SELECT DISTINCT tblhosting.domain, tblclients.email
FROM tblhosting
INNER JOIN tblclients ON tblhosting.userid = tblclients.id,
tbldomains
WHERE tblhosting.domain NOT IN ((SELECT domain from tbldomains))

my suspicion is that you might need to add more where statements to fine tune the results, because as it's currently written, it will find results of all statuses, including cancelled and terminated.

Edited February 11, 2020 by brian!

[cosmin]

I added  AND tblhosting.domainstatus='Active'; at the end and is perfect for me.

Many many thanks!