Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

PHPList Email Campaign Manager - Mysql Query to get detailed info

Avatar of azlumiere
azlumiereFlag for United States of America asked on
PHPMySQL Server
10 Comments1 Solution1014 ViewsLast Modified:
Hi Experts,

I'd love some help from a Mysql genius! I'm using PHPlist to send out newsletters. I have Usertracking set up to capture information about which users clicked on what URLs. I would like to generate a report that shows this information:

User ID, First Name, Phone, Email, URL, # of Clicks, Message ID

I AM able to get all of this information from PHPList, but it would take a lot of time since the information is on many different pages and would take some careful sorting. For example, one page shows all of the URLs and number of clicks, another page shows which user clicked on which URL, another page could give me the user's attribute info, like phone number etc.  I'm sure there has to be a faster way to query the database directly to get all of this information in a simple csv format.

I am NOT a Mysql genius in any way, which is why I could use some help. I used PHPMyAdmin to query the database for EACH of the fields that I'm looking to include. I was hoping if I shared that information, some evil genius could tell me how to COMBINE it all into one query. I'm not sure if this is enough information though, but I thought I would try. I always have great luck here on Experts Exchange.

The one thing that I think I am missing is the user attribute values like first name, phone number, etc. I think the query I have below just lists "what" attributes are available, I can't find a way to list all user attributes in a column. Because of this, at the very bottom of the code below, I pasted a small part of the "export.php" document that is used to export the list itself as a csv file. When you export the list, all of the attributes are listed, so I thought this might provide some more insight--but I'm not sure.

I found another thread here about someone trying to create a custom query, so I wasn't sure if any of the information provided here would be pertinent:  https://www.experts-exchange.com/Database/MySQL/Q_22653422.html?sfQueryTermInfo=1+10+30+mysql+phplist

The individual queries that I ran are listed below. Might anyone have an idea of how I can combine all of this to get the kind of report I'm looking for? I would really appreciate any thoughts!! If more information is necessary, I'd be happy to try and provide it IF I can find it.
To list URLs

SELECT COUNT( * ) AS `Rows` , `url`
FROM `phplist_linktrack`
GROUP BY `url`
ORDER BY `url`
LIMIT 0 , 30

To list User Attributes including First Name, Phone, etc.

SELECT COUNT( * ) AS `Rows` , `name`
FROM `phplist_user_attribute`
GROUP BY `name`
ORDER BY `name`
LIMIT 0 , 30

To list Emails

SELECT COUNT( * ) AS `Rows` , `email`
FROM `phplist_user_user`
GROUP BY `email`
ORDER BY `email`
LIMIT 0 , 30

To list # of clicks

SELECT COUNT( * ) AS `Rows` , `clicked`
FROM `phplist_linktrack`
GROUP BY `clicked`
ORDER BY `clicked`
LIMIT 0 , 30

To list Message ID

SELECT COUNT( * ) AS `Rows` , `messageid`
FROM `phplist_listmessage`
GROUP BY `messageid`
ORDER BY `messageid`
LIMIT 0 , 30


***------------- Snippet from export.php that exports user attributes like First Name, Last Name, Phone Number, etc. In case this is helpful -----------***

$attributes = array();
  if (is_array($_POST['attrs'])) {
    $res = Sql_Query("select id,name,type from {$tables['attribute']}");
    while ($row = Sql_fetch_array($res)) {
      if (in_array($row["id"],$_POST['attrs'])) {
        print trim(stripslashes($row["name"])) .$col_delim;
        array_push($attributes,array("id"=>$row["id"],"type"=>$row["type"]));
      }
    }
  }
ASKER CERTIFIED SOLUTION
Avatar of Andyc75
Andyc75Flag of Canada imageSoftware Architect
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers