So this question may be a little hard to follow, but here i go anyway. I am trying to create a table in mysql that is comprised of only the email addresses that show up multiple times in my origianl table. More specifically, the original table has a record for every time someone opened an email - it records the email address (even if the same email address already exists in the table it records it again). I want to make a table that records only the email addresses that show up multiple times in the original table.
my current approach isn't working: In my phpmyadmin i can do a select statement (shown below) that shows how many times each email address shows up, but i cant figure out how to store it as a variable especially as a variable in php. If i could store it as a php variable then i could do some kind of if-then statement to insert only emails that have more than 1 instance within the table, but i can't figure out how to store this variable (cnt)
here is the mysql statement that counts the number of instances of any single email - "select email, count(email) as cnt from tempclick group by email order by cnt desc"
I don't know if this is an approriate approach, but i cant seem to figure out anything better to do. Anyone have any ideas?
If I am following your question you should be able to do this in your query easily. I would use a GroupBy and a count greater than 1. I am not a MySQL guy but here is the SQL in Access. It should at least point you in the right direction.
SELECT Table1.EmailAddress, Count(Table1.EmailAddress) AS CountOfEmailAddress
FROM Table1
GROUP BY Table1.EmailAddress
HAVING (((Count(Table1.EmailAddress))>1))
alright, thanks. Now when i only select the ones i want, i can just store them in an array and do a while loop to insert all email addresses in the array into my new table, thanks xpartmgr
No problem. Glad I could help.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.