Database query WordPress
- SOLVED
I sent a newsletter with wp mymail, now i want to get a list of recipients that clicked.
if link is clicked can be found in table post_meta
— —Table `wp_postmeta`
(58519, <strong>36326</strong>, ‘mymail-campaigns’, ‘a:1:{i:44465;a:7:{s:4:”sent”;b:1;s:9:”timestamp”;i:1373360443;s:4:”open”;i:1373363894;s:10:”firstclick”;i:1373363933;s:11:”totalclicks”;i:1;s:6:”clicks”;a:1:{s:72:”http://thisistheclickedlink.com/aaa.pdf”;i:1;}s:17:”totaluniqueclicks”;i:1;}}’),
The connected recipient can be found in wp_post
— —Table `wp_posts`
(<strong>36326</strong>, 1, ‘2013-07-08 15:17:24’, ‘2013-07-08 15:17:24’, ’’, ‘[email protected]’, 1, 1, 0, NULL, NULL, NULL, 0, NULL, ’’, ‘subscribed’, ‘closed’, ‘closed’, ’’, ‘b2454a0e745b9011c507cb71d5bf94af’, ’’, ’’, ‘2013-07-08 15:17:24’, ‘2013-07-08 15:17:24’, ’’, 0, ’’, 0, ‘subscriber’, ’’, 0),
Can somebody help me with a SQL-Query to get a list of all recipients that clicked?
i don´t need it for frontend-ouput, i just need a list of the filtered adresses.
Thank You, Patric
Answers (2)
Luis Abarca answers:
The post meta information is serialized, it needs to be unserialized first, i think we cant do it with pure SQL, but we can combine with PHP to get the information.
<?php
$args = array(
'post_type' => 'subscriber',
'posts_per_page' => -1,
'meta_query' => array(
array(
'meta_key' => 'mymail-campaigns',
'meta_value' => '',
'meta_compare' => '!=',
)
)
);
$myquery = new WP_Query( $args );
while ( $myquery->have_posts() ) {
$myquery->the_post();
$data = get_post_meta(get_the_ID(), 'mymail-campaigns');
if ( intval($data['totalclicks']) < 1 ) {
continue;
}
the_title();
}
Giri answers:
I hope email id used as title. In that case here is the code.
$args = array(
'posts_per_page' => '100',
'post_type' => 'subscriber',
'meta_key' => 'mymail-campaigns',
'meta_value' => '',
'meta_compare' => '!=',
'order' => 'DESC'
);
query_posts( $args );
// The Loop
echo '<ol>';
while ( have_posts() ) : the_post();
echo '<li>';
the_title();
echo '</li>';
endwhile;
echo '</ol>';
// Reset Query
wp_reset_query();
Patric Schwarz comments:
this one works, but it displays ALL emails.
inserted
'meta_value' => '{s:72:”http://thisistheclickedlink.com/aaa.pdf”;i:1;}',
so i should only get mails that clicked on this, but still get full list of mails.
Giri comments:
Then try this one
$args = array(
'posts_per_page' => '100',
'post_type' => 'subscriber',
'meta_key' => 'mymail-campaigns',
'meta_value' => 'http://thisistheclickedlink.com/aaa.pdf',
'meta_compare' => 'LIKE',
'order' => 'DESC'
);
query_posts( $args );
// The Loop
echo '<ol>';
while ( have_posts() ) : the_post();
echo '<li>';
the_title();
echo '</li>';
endwhile;
echo '</ol>';
// Reset Query
wp_reset_query();
Giri comments:
Right now it displays 100 results per page. If you want to list everything on the same page then use the value "-1"
Giri comments:
If my above code not work then try this one.. I guess it will work for sure..
$args = array(
'posts_per_page' => '-1',
'post_type' => 'subscriber',
'meta_key' => 'mymail-campaigns',
'meta_value' => '{s:72:”http://thisistheclickedlink.com/aaa.pdf”;i:1;}',
'meta_compare' => 'LIKE',
'order' => 'DESC'
);
query_posts( $args );
// The Loop
echo '<ol>';
while ( have_posts() ) : the_post();
echo '<li>';
the_title();
echo '</li>';
endwhile;
echo '</ol>';
// Reset Query
wp_reset_query();
Giri comments:
Please note: the quotes looks different when you copy and paste i this line
s:72:”http://thisistheclickedlink.com/aaa.pdf”;i:1;}
So replace that with normal quotes when you opy and paste
Giri comments:
Hello patrick, Have you tried my last answer?
Patric Schwarz comments:
it works!