CODEWITHSUNDEEP
phpsqlwordpress
fairydragon
fairydragon

Reputation: 146

how display query results only if a row is more than 0

I am struggling with a query in worpdress.

I am using custom fields to display values. Unfortunately when the value is removed from the edit screen, I have a blank value left in the posteta table.

So when my query is run:

$month_results = $wpdb->get_results("SELECT meta_value AS monthprice FROM ch_postmeta 
WHERE meta_key = 'per_month' AND post_id = $productid");

if(!empty($month_results)) {
    foreach($month_results as $permonth) {
        echo $permonth->monthprice . 'per month';
    }
} else { 
echo "None found";}

and my table consists of the following:

meta_id     post_id     meta_key      meta_value
1398        126         per_month

per_month

I can see the "per month" on my page with an empty value next to it.

My question is, what is the best way to check within my query that there is something in the meta_value column and echo the value only if there is something there ?

I hope somebody can point me in the right direction.

Upvotes: 0

Views: 208

Answers (5)

Maha Dev
Maha Dev

Reputation: 3965

You can use IS NOT NULL inside your query like this :

SELECT meta_value AS monthprice FROM ch_postmeta WHERE 
 meta_value IS NOT NULL AND meta_key = 'per_month' AND post_id = $productid

This will remove records having meta_value empty.

Upvotes: 2

Igor Yavych
Igor Yavych

Reputation: 4228

Try the following code:

$month_results=$wpdb->get_results('SELECT `meta_value` AS `monthprice` FROM ' . $wpdb->prefix . 'postmeta 
WHERE `meta_key` = \'per_month\' AND `post_id` = \'$productid\' and (`meta_value` IS NOT NULL and `meta_value`!=\'\');');

if (!is_null($month_results) && count($month_results) > 0)
{
    foreach ($month_results as $permonth)
    {
        echo $permonth->monthprice . 'per month';
    }
}
else
{
    echo "None found";
}

Upvotes: 1

Chris Rowles
Chris Rowles

Reputation: 120

Modify your query like so to only return rows where meta_value has a value:

$month_results = $wpdb->get_results("SELECT meta_value AS monthprice FROM ch_postmeta 
        WHERE meta_value IS NOT NULL AND meta_value != '' AND meta_key = 'per_month' AND post_id = $productid");

        if(!empty($month_results)) {
            foreach($month_results as $permonth) {
                echo $permonth->monthprice . 'per month';
            }
        } else { 
        echo "None found";}

Upvotes: 1

Sardor Dushamov
Sardor Dushamov

Reputation: 1667

There are many way to display or not display:

in the query:

SELECT meta_value AS monthprice FROM ch_postmeta 
WHERE meta_key = 'per_month' AND post_id = $productid and (meta_value is not NULL or meta_value<>'')

with php:

if(!empty($month_results)) {
    foreach($month_results as $permonth) {
        if($permonth->monthprice)
             echo $permonth->monthprice . 'per month';
    }
} else { 
echo "None found";
}

Upvotes: 1

ale8oneboy
ale8oneboy

Reputation: 127

You could check to see if there is a value in $permonth->monthprice by using the following.

if($permonth->monthprice != '') echo $permonth->monthprice . 'per month';

Upvotes: 1

Related Questions