Combine 2 MySql Queries

Hi,
These are 2 queries which work fine individually.

[code]SELECT * FROM wp_usermeta WHERE meta_key = ‘rcp_subscription_level’
AND meta_value > ‘0’

SELECT * FROM wp_usermeta WHERE meta_key = ‘rcp_status’ AND meta_value = ‘active’ [/code]
I want to find all instances where ‘rcp_status’ is ‘active’ and ‘rcp_subscription_level’ is greater than zero.

How do I achieve this?

Thanks.

Is ‘rcp_subscription_level’ a varchar or integer? If it’s integer, I don’t think 0 should be in quotes.

SELECT * /* Never use *, if you can help it. */
FROM `wp_usermeta` 
WHERE `meta_key` = `rcp_subscription_level`
   AND `meta_value` > 0
   AND `rcp_status` = 'active'

At least, as I understand your question.

If I misunderstand, please advise.

V/r,

:slight_smile:

Thanks for your reply Wolf. In this case, rcp_status is not a column. It is actually a value in meta_key (which is a column). My table structure is as follows:

CREATE TABLE IF NOT EXISTS `wp_usermeta` ( `umeta_id` bigint(20) unsigned NOT NULL auto_increment, `user_id` bigint(20) unsigned NOT NULL default '0', `meta_key` varchar(255) default NULL, `meta_value` longtext, PRIMARY KEY (`umeta_id`), KEY `user_id` (`user_id`), KEY `meta_key` (`meta_key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

Okay, now I’m really confused.

Are you trying to check that the LENGTH of meta_value is greater than 0? If that isn’t the case, I don’t understand how text in a string is supposed to be “greater than” zero.

V/r,

:slight_smile:

Sorry for the confusion. I’m confused myself :smile:

I want to identify which user_id is active in the meta_value table. This state of activeness must correspond with rcp_status in the meta_key table. After obtaining the results from this query, I want to use this query results to run the 2nd query. In the 2nd query, I want to use the user_id obtained in the 1st query to identify which user_id has got a meta_value which is greater than zero. This meta_value which is greater than zero must correspond with rcp_subscription_level in the meta_key table.

I’ve written a php code to achieve to explain my logic.

<?php
$query1=("SELECT * FROM  `wp_usermeta` WHERE  `meta_key` =  'rcp_status' AND `meta_value` =  'active'"); 

$result1=mysql_query($query1); 
$num1=mysql_num_rows($result1);

while ($row1 = mysql_fetch_array($result1))
{ 
$user_id = $row1["user_id"];

$query2= "SELECT * FROM `wp_usermeta` WHERE (`user_id` = '{$row1['user_id']}' AND `meta_key` = 'rcp_subscription_level' AND `meta_value` > 0"; 
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);

while ($row2 = mysql_fetch_array($result2)) 
	{
echo $row2["user_id"];
	}
}
echo $query1;
echo '<br>';
echo $query2;
?>

In the case of the php code above, I’m a bit confused with $query2 because it’s only selecting 1 record from the database. It should be selecting more than 1 record.

I hope I didn’t confuse you further…

You are running a second query inside a loop of the first query, but then you expect to just display the second query outside of the loop.

Where are $row1 and $row2 being defined?

:slight_smile:

Okay, I’ve fine tuned by php code to get me my desired result. So now my question is, is there a simpler way to write the query to achieve the same result without so much php coding?

<?php

$query1=("SELECT * FROM  `wp_usermeta` WHERE  `meta_key` =  'rcp_status' AND `meta_value` =  'active'"); 

$result1=mysql_query($query1); 
$num1=mysql_num_rows($result1);

while ($row1 = mysql_fetch_array($result1))
{ 
$user_id = $row1["user_id"];

$query2= "SELECT * FROM `wp_usermeta` WHERE `user_id` = '{$row1['user_id']}' AND `meta_key` = 'rcp_subscription_level' AND `meta_value` > 0"; 
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);


while ($row2 = mysql_fetch_array($result2)) 
	{
$user_id2 = $row2["user_id"];
echo $user_id2;
echo '<br>';
	}
}

?>

SELECT something , anything , just_not_the_dreaded_evil_select_star FROM wp_usermeta WHERE user_id IN ( SELECT user_id FROM wp_usermeta WHERE meta_key = 'rcp_status' AND meta_value = 'active' ) AND meta_key = 'rcp_subscription_level' AND meta_value > 0it can also be done with a join

1 Like

Beautiful job, r937. This was exactly what I was looking for. Thank you very much for your expertise.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.