Query WordPress database by registered date and role

I can post a SQL query for you to run.

Hi respectyoda

That would be incredibly helpful of you.

It’ll be used in a PHP script and the idea is that the returned details then go into an array which can be looped through to subscribe them to a Sendy list.

Thanks

SELECT user_login, user_email, meta_key, meta_value FROM wp_usermeta, wp_users WHERE wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value = 0 AND wp_users.user_registered LIKE '2014-01-15%'

Hi,

So I have the complete code as:

<?
    mysql_connect("localhost", "user", "pass") or die(mysql_error());
	mysql_select_db("dbname") or die(mysql_error());

    $result = mysql_query("SELECT user_login, user_email, meta_key, meta_value FROM wp_usermeta, wp_users WHERE wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'wp_15_user_level' AND wp_usermeta.meta_value = 0 AND wp_users.user_registered LIKE '2014-01-15%'");

   while( $row = mysql_fetch_assoc( $result)){
    $new_array[ $row['id']] = $row;
}

print_r($new_array)

 ?>

It returns this:

Array ( [] => Array ( [user_login] => rabbadubba [user_email] => [email protected] [meta_key] => wp_15_user_level [meta_value] => 0 ) )

However, when I run the SQL query in my database, I get two rows returned. Any idea why I only get one with the PHP?

I’d recommend you to use this:

while ($row = mysql_fetch_assoc($result)) {
    print_r($row);
}

Sorry to be a pain. This is my full code. What I’m trying to do is extract each display name and each email from the result and put them through a loop:

<?
    mysql_connect("localhost", "DBUSER", "DBPASS") or die(mysql_error());
	mysql_select_db("DB") or die(mysql_error());

    $result = mysql_query("SELECT user_login, user_email, meta_key, meta_value FROM wp_usermeta, wp_users WHERE wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = 'wp_15_user_level' AND wp_usermeta.meta_value = 0 AND wp_users.user_registered LIKE '2014-01-15%'");

$row = mysql_fetch_row($result);

//-------------------------- You need to set these --------------------------//
	$sendy_installation_url = 'URL'; //Your Sendy installation (without the trailing slash)
	//---------------------------------------------------------------------------//

	foreach ($row as $individual)
	{
        //-------- Subscribe --------//
        $postdata = http_build_query(
            array(
            'name' => THE DISPLAYNAME FIELD
		    'email' => THE EMAIL FIELD
		    'list' => "xoYWaOYnRsu6KPliTC70Gg",
		    'boolean' => 'true'
            )
        );
        $opts = array('http' => array('method'  => 'POST', 'header'  => 'Content-type: application/x-www-form-urlencoded', 'content' => $postdata));
        $context  = stream_context_create($opts);
        $result = file_get_contents($sendy_installation_url.'/subscribe', false, $context);
        //-------- Subscribe --------//
	}

 ?>

My problem isn’t the rest of the code, it’s filling those variables. How do I extract individual items from the array?

Thanks again

You have to understand that this piece of code…

$row = mysql_fetch_row($result);

…only returns the number of rows. So, you’d have to access the values by using the index/name. Here’s an example.

while ($row = mysql_fetch_array($result))
{
    echo "Name :{$row['user_login']} <br>" .
         "Email : {$row['user_email']} <br>" .
         "User Level : {$row['meta_key']} <br><br>";
         "Value of User Level : {$row['meta_value']} <br><br>";
}

Thanks. I’m getting an error when i try and put my code in though.

while ($row = mysql_fetch_array($result))
{
    echo "Name :{$row['user_login']} <br>" .
         "Email : {$row['user_email']} <br>" .
         "User Level : {$row['meta_key']} <br><br>";
         "Value of User Level : {$row['meta_value']} <br><br>";
}

works fine.

But when I use

while ($row = mysql_fetch_array($result))
	{
        //-------- Subscribe --------//
        $postdata = http_build_query(
            array(
            'name' => $row['user_login'],
		    'email' => $row['user_email'],
		    'list' => "xoYWaOYnRsu6KPliTC70Gg",
		    'boolean' => 'true'
            )
        );
        $opts = array('http' => array('method'  => 'POST', 'header'  => 'Content-type: application/x-www-form-urlencoded', 'content' => $postdata));
        $context  = stream_context_create($opts);
        $result = file_get_contents($sendy_installation_url.'/subscribe', false, $context);
        //-------- Subscribe --------//
	}

I get the error

Warning: mysql_fetch_array() expects parameter 1 to be resource, string given in URL on line 19

Line 19 is while ($row = mysql_fetch_array($result))

I would suggest you to do something similar as this sample piece of generic code.

$data = array('foo'=>'bar',
              'baz'=>'boom',
              'cow'=>'milk',
              'php'=>'hypertext processor');

echo http_build_query($data) . "\n";
echo http_build_query($data, '', '&');

Sorry, I’m confused as to how that fixes the issue with the while ($row = mysql_fetch_array($result)) line

Check the return value of mysql_error().

Also, I would recommend you to use mysqli, not mysql.