Ask your WordPress questions! Pay money and get answers fast! (more info)

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)

2013-07-09

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();
}

2013-07-09

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!