PHPList Email Campaign Manager - Mysql Query to get detailed info

azlumiere
azlumiere used Ask the Experts™
on
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:  http://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"]));
      }
    }
  }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Andyc75Software Architect
Top Expert 2010

Commented:
I think these tables can be joined into one select as long as the database was setup properly.

Does the user_id exists in each table ?

Can you post your database schema for each of these tables.

(in mysql just use the show create table command to get the structure)  ie

show create table phplist_linktrack
show create table phplist_user_attribute
show create table phplist_user_user
show create table phplist_listmessage

also if you could post  a few sample records for each table, that would be very helpful.

Author

Commented:
Hi Andyc75,

Thank you so much for your comment! Sorry to be so inexperienced: can I run the commands you recommended above from PHPmyAdmin?  I tried running the "show create table phplist_linktrack' but all I got was:

phplist_linktrack       CREATE TABLE `phplist_linktrack` (
 `linkid` int(...

I've attached a screenshot that I took from PHPmyAdmin showing the structure of linktrack. Please let me know if there is a better way to do this...

As for sample records. Here's some info (see below)--I hope this is what you mean by samples--but again, if NOT please let me know. I'll try to provide all the useful info that I can.

Upon further searching, I think the actual user data for user attributes is in: phplist_user_user_attribute (which I pasted below in the samples)

I tried running this:

SELECT `phplist_user_user` . `id` , `phplist_user_user` . `email` , `phplist_user_user_attribute` . `value` , `phplist_linktrack`.`url` , `phplist_linktrack` . `clicked` , `phplist_usermessage` . `messageid`
FROM phplist_linktrack, phplist_user_user, phplist_user_user_attribute, phplist_usermessage

But it took forever and seemed to time out, so I'm sure I did something wrong.

I hope some of this information is helpful, and I'm happy to provide more!

phplist_linktrack sample records:

linkid 	messageid 	userid 	url 											forward 											firstclick 	latestclick 			clicked
1 		3 			231 	http://domain.com/featured-articles/june-con... http://domain.com/featured-articles/june-con... 	NULL 		2010-06-25 18:12:41 	0
2 		3 			231 	http://domain.com/ 								http://domain.com/ 									NULL 		2010-06-25 18:12:42 	0
3 		3 			231 	http://domain.com/media-kit/AE-Media-Kit-201... http://domain.com/media-kit/AE-Media-Kit-201... 	NULL 		2010-06-25 18:12:42 	0
4 		3 			231 	http://domain.com/newsletter/?p=unsubscr... 	http://domain.com/newsletter/?p=unsubscr... 		NULL 		2010-06-25 18:12:42 	0
5 		3 			231 	http://domain.com/newsletter/?p=preferen... 	http://domain.com/newsletter/?p=preferen... 		NULL 		2010-06-25 18:12:42 	0

phplist_user_user_attribute sample records:
attributeid 	userid 	value
1 				1 		J.
2 				1 		Austin
4 				1 		Auto Pro Systems
5 				1 		13209
1 				2 		G.
2 				2 		Eagerton
4 				2 		AUL Corp.
5 				2 		94559
6 				1 	 
6 				2 		NULL
7 				1 	 
7 				2 		(555) 257-9700
8 				1 	 
8 				2 		NULL
1 				3 		Julie
2 				3 		_
4 				3 		Brian Tracy Intenational
6 				3 	 
7 				3 		(555) 436-7300
8 				3 	 
1 				4 		T.
2 				4 		Abela
4 				4 		JM&A Group
6 				4 	 
7 				4 		(555) 420-4677
8 				4 	 
1 				5 		S.
2 				5 		Aberlich
4 				5 		Open Dealer Exchange

phplist_user_user sample records:

id 	email 					confirmed 	blacklisted 	bouncecount 	entered 				modified 				uniqid 								htmlemail 	subscribepage 	rssfrequency 	password 	passwordchanged 	disabled 	extradata 	foreignkey
1 	email@biweeklydebit.com 0 			0 				0 				2010-05-18 08:10:30 	2010-05-18 08:10:30 	75372ca4b25636c61120ca7c8a5be36b 	1 			1 	  			NULL 			NULL 		0 					NULL 		NULL
2 	email@aulcorp.com 		1 			0 				0 				2010-06-07 13:45:07 	2010-06-07 13:47:29 	4c9e5fdc872d30f945c9ab1801460dc6 	1 			1 	  			NULL 			NULL 		0 					NULL 		NULL
3 	email@briantracy.com 	1 			0 				0 				2010-06-25 17:38:51 	2010-06-25 17:40:14 	d7270b1e7e536ace1d7a1fb565a2c169 	1 			NULL 			NULL 			NULL 		NULL 				0 			NULL 		NULL
4 	email@jmagroup.com 		1 			0 				0 				2010-06-25 17:38:51 	2010-06-25 17:40:14 	15924667f8e8eb9cc676e42f4e5f35e5 	1 			NULL 			NULL 			NULL

phplist_listmessage sample records:
id 	messageid 	listid 	entered 			modified
2 	8 			1 		010-06-30 18:08:09 	2010-06-30 18:08:09

Open in new window

linktrack-schema.jpg

Author

Commented:
Looks like I made a mess of the samples. Sorry about that. I tried to clean them up.
Andyc75Software Architect
Top Expert 2010

Commented:
Yes, you ran the show create table command correctly, it will output the text needed to re-create each table.   it just looks like it got cut off, can you sent the output to a file instead ?

Yes, running that statement is very bad :)

SELECT `phplist_user_user` . `id` , `phplist_user_user` . `email` , `phplist_user_user_attribute` . `value` , `phplist_linktrack`.`url` , `phplist_linktrack` . `clicked` , `phplist_usermessage` . `messageid`
FROM phplist_linktrack, phplist_user_user, phplist_user_user_attribute, phplist_usermessage

Without the proper table joins you will get exponential results and probably never end.
Andyc75Software Architect
Top Expert 2010

Commented:
Here is the first part which joins two of the tables together.

It will give you the User_ID, First Name, Phone and Email.

Give that a try to make sure its working and then we can build on that.

I assumed the Attrubuteid 1 is always the first name and
Attributeid 7 is always the phone

Can you explain the URL and message ID part a bit more.  Do you want to see one record per unique url ?  Perhaps a few more sample records from phplist_linktrack and phplist_listmessage sample.  Then show what you would like the output of the sample data to look like.


 
select  A.id as userid,
		coalesce(B.value,'') as fname, 
		A.email, 
		coalesce(C.value,'') as phone
from phplist_user_user A
   left outer join phplist_user_user_attribute B on A.id = B.userid and B.attributeid = 1
   left outer join phplist_user_user_attribute C on A.id = C.userid and C.attributeid = 7

Open in new window

Author

Commented:
Ah, you're awesome. Thanks for helping me!  I went back and tried to run the  show create table, and I realized I needed to show "Full Texts".  I'll paste the results below.

See, I can't be left alone with these things or I'll do stupid things like run that crazy statement! :)

The first part you provided worked PERFECT! You are right. Attribute 1 is always first name and 7 is the phone. As I'm thinking about this though, I should also include the last name. Could you help me add in attribute 2 also? Sorry about that. I should have thought about that before.

I can definitely clarify URL and message ID.  URL would give me a list of the distinct URLs that users clicked on in the email I sent. And message ID is the ID of the email sent. This is useful since I've sent a couple of emails out. Message ID will tell me which email the URL was in. Here's how I'm thinking this would work (I don't really think I need user_id--I just thought I would have to have that info to get the right data):

fname     lname     phone          email                   URL                              # of Clicks     Message ID

John       Smith      555-5555   j@email.com       domain.com/link1.html    2                     8
John       Smith      555-5555   j@email.com       domain.com/link2.html    1                     8
John       Smith      555-5555   j@email.com       domain.com/link3.html    3                     8
John       Smith      555-5555   j@email.com       domain.com/link4.html    1                     8
Jane       Jones     555-1111    jj@email.com      domain.com/link1.html    1                     8
Pete       Miller       555-2222   pm@email.com   domain.com/link3.html    2                     8

etc. etc.

So, I'm thinking users will be repeated IF they click on multiple URLs. So in response to your question: Yes!  I would like to see one record per unique URL. You are right.

I pasted two more samples below for linktrack and listmessage.

Thanks again for all of your help with this!!!!

phplist_linktrack 	CREATE TABLE `phplist_linktrack` (
 `linkid` int(11) NOT NULL auto_increment,
 `messageid` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `url` varchar(255) collate latin1_general_ci default NULL,
 `forward` text collate latin1_general_ci,
 `firstclick` datetime default NULL,
 `latestclick` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `clicked` int(11) default '0',
 PRIMARY KEY  (`linkid`),
 UNIQUE KEY `messageid` (`messageid`,`userid`,`url`),
 KEY `midindex` (`messageid`),
 KEY `uidindex` (`userid`),
 KEY `urlindex` (`url`),
 KEY `miduidindex` (`messageid`,`userid`),
 KEY `miduidurlindex` (`messageid`,`userid`,`url`)
) ENGINE=MyISAM AUTO_INCREMENT=53337 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci


phplist_user_user_attribute 	CREATE TABLE `phplist_user_user_attribute` (
 `attributeid` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `value` varchar(255) collate latin1_general_ci default NULL,
 PRIMARY KEY  (`attributeid`,`userid`),
 KEY `userindex` (`userid`),
 KEY `attindex` (`attributeid`),
 KEY `userattid` (`attributeid`,`userid`),
 KEY `attuserid` (`userid`,`attributeid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

phplist_user_user 	CREATE TABLE `phplist_user_user` (
 `id` int(11) NOT NULL auto_increment,
 `email` varchar(255) collate latin1_general_ci NOT NULL,
 `confirmed` tinyint(4) default '0',
 `blacklisted` tinyint(4) default '0',
 `bouncecount` int(11) default '0',
 `entered` datetime default NULL,
 `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 `uniqid` varchar(255) collate latin1_general_ci default NULL,
 `htmlemail` tinyint(4) default '0',
 `subscribepage` int(11) default NULL,
 `rssfrequency` varchar(100) collate latin1_general_ci default NULL,
 `password` varchar(255) collate latin1_general_ci default NULL,
 `passwordchanged` date default NULL,
 `disabled` tinyint(4) default '0',
 `extradata` text collate latin1_general_ci,
 `foreignkey` varchar(100) collate latin1_general_ci default NULL,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `email` (`email`),
 KEY `foreignkey` (`foreignkey`),
 KEY `idx_phplist_user_user_uniqid` (`uniqid`),
 KEY `emailidx` (`email`),
 KEY `enteredindex` (`entered`)
) ENGINE=MyISAM AUTO_INCREMENT=1578 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

phplist_listmessage 	CREATE TABLE `phplist_listmessage` (
 `id` int(11) NOT NULL auto_increment,
 `messageid` int(11) NOT NULL,
 `listid` int(11) NOT NULL,
 `entered` datetime default NULL,
 `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
 PRIMARY KEY  (`id`),
 UNIQUE KEY `messageid` (`messageid`,`listid`),
 KEY `listmessageidx` (`listid`,`messageid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci



---------------------

Another phplist_linktrack sample:

linkid 	messageid 	userid 	url 	forward 	firstclick 	latestclick 	clicked
69 	6 	232 	http://domain.com/newsletter/?p=forward&...  	http://domain.com/newsletter/?p=forward&... 	2010-06-28 13:12:52 	2010-06-28 13:12:52 	1
70 	6 	232 	http://tincan.co.uk/powered 	http://tincan.co.uk/powered 	NULL 	2010-06-28 11:14:21 	0
71 	6 	233 	http://domain.com/featured-articles/june-con... 	http://domain.com/featured-articles/june-con... 	2010-06-28 13:56:01 	2010-06-28 13:56:01 	1
72 	6 	233 	http://domain.com/ 	http://domain.com/ 	2010-06-28 13:57:01 	2010-06-28 13:57:01 	1
73 	6 	233 	http://domain.com/media-kit/AE-Media-Kit-201... 	http://domain.com/media-kit/AE-Media-Kit-201... 	2010-06-28 13:57:21 	2010-06-28 13:57:21 	1


Another phplist_listmessage sample:

id 	messageid 	listid 	entered 			modified
1 	6 			1 		010-06-30 18:08:09 	2010-06-30 18:08:09

Open in new window

Andyc75Software Architect
Top Expert 2010

Commented:
ok azlumiere, give this one a try, I've added in the last name and joined in the other tables to grab the url, clicked an messageid.

I also assumed that if someone clicks on the same record twice it increments the clicked column in the phplist_linktrack table.




select  A.id as userid,
		coalesce(B.value,'') as first_name,
		coalesce(D.value,'') as last_name, 		
		A.email, 
		coalesce(C.value,'') as phone,
		E.url,
		E.clicked,
		E.messageid
from phplist_user_user A
   left outer join phplist_user_user_attribute B on A.id = B.userid and B.attributeid = 1
   left outer join phplist_user_user_attribute C on A.id = C.userid and C.attributeid = 7
   left outer join phplist_user_user_attribute D on A.id = D.userid and D.attributeid = 2
   inner join phplist_linktrack E on A.id = E.userid

Open in new window

Author

Commented:
Andyc75:

You are so awesome!  The script works great! And you assumed right about the clicks incrementing.  I wonder if I can ask one more question before I close the question and award you the points (I increased the points by-the-way). Is there a way to include the URL field only IF clicks are greater than or = to 1? Something like: WHERE clicked <= 1

This script will definitely do the trick for me, but it is pulling all URLs even if they haven't been clicked (which is probably EXACTLY what we told it to do). So, I could essentially sort the results in Excel--but I thought I would see if adding the conditional statement for the clicks would be fairly simple. If not, I'm happy to use what you've supplied! You've already done a lot for me!!
Software Architect
Top Expert 2010
Commented:
Sure no problem azlumiere,

This will only show URL'S where the clicked count is >= 1

Your welcome, glad I could help you out :)
select  A.id as userid,
		coalesce(B.value,'') as first_name,
		coalesce(D.value,'') as last_name, 		
		A.email, 
		coalesce(C.value,'') as phone,
		E.url,
		E.clicked,
		E.messageid
from phplist_user_user A
   left outer join phplist_user_user_attribute B on A.id = B.userid and B.attributeid = 1
   left outer join phplist_user_user_attribute C on A.id = C.userid and C.attributeid = 7
   left outer join phplist_user_user_attribute D on A.id = D.userid and D.attributeid = 2
   inner join phplist_linktrack E on A.id = E.userid and E.clicked >= 1

Open in new window

Author

Commented:
Ha haa!!!  Andyc75 you are a genius!  The query works perfect! I truly can't thank you enough. That is something I would never have figured out on my own. Thanks so much for all of your help--it is MUCH appreciated! You rock. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial