SQL Queries for TAE Admins

To find duplicate IPs
SELECT ip, id, name, userid, contact_email, pword, joindate, lastpost
FROM members
WHERE IP IN (SELECT IP
  FROM members
  GROUP BY  IP
  HAVING COUNT(*) > 1)
To replace all instances of a URL like http://av.id.ly/ and chanege it
update table SET url = REPLACE(url,  ‘http://av.id.ly/’,  ‘http://grabcashtoday.com/’ ) WHERE userid = ‘richardmoyer’ AND url LIKE  ‘%http://av.id.ly/%’
Send back banner ads and premium ads with targeturl from vitalviralpro
update `banners`
set status=0, added=0
where targeturl like ‘%vitalviralpro%’;
update `premiumads`
set approved=0, added=0
where url like ‘%vitalviralpro%’;
\
Find contact_email that is not gmail.com
SELECT * FROM `members` WHERE not (contact_email like ‘%gmail.com%’)
Delete where contact_email is not gmail (actually mark deleted but can be restored)
update members set deleted=time() WHERE not (contact_email like ‘%gmail.com%’)
Find Deleted Members (most recent first)
select * from members where not (deleted=”) order by deleted desc
Restore a list of members (list consists of userid from previous listings)
update members set deleted=” where userid in ( richardmoyer, thomasdavid6156, joeschmoe64 )
Find Pro members who have not logged in since 1/1/2016
SELECT id, name, lastname, contact_email, memtype, lastlogin FROM `members` WHERE lastlogin<‘2016-01-01’
and memtype=’PRO’ and status=’1′