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.
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'
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:
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.
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.
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