MYSQL Select If Query – WP custom fields

4 Views

I am using a plugin that allows me to pull custom field values from the database to populate input fields. The plugin is a calculator form that adds up 3 custom fields that are each assigned to an input field using my original query.

Right now if I use this as my query it works fine —

Read More
SELECT meta_value AS value FROM tr_postmeta WHERE meta_key='pa_meta_debt' AND post_id=<%fieldname5%>

However if the custom field value is missing it throws off my calculations. How can do a select query to pull the value if present and if not give it a value of 0?

This is the query I was trying — 

if (exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'tr_postmeta' AND COLUMN_NAME = 'pa_meta_debt'))
begin
    SELECT meta_value AS value FROM tr_postmeta WHERE meta_key='pa_meta_debt' AND post_id=<%fieldname5%>
end

It’s not working though, how can I modify this?

Post Views: 4

Related posts

2 comments

  1. If the values are numeric (or can be converted into numeric) and only 1 row can be returned by the query at most, then use sum() aggregate function with ifnull() or coalesce(). If you use sum(), then your resultset will always have 1 row. If no matching rows are found, then sum() will return null, which has to be converted into 0 using ifnull() or coalesce().

    SELECT coalesce(sum(meta_value),0) AS value
FROM tr_postmeta WHERE meta_key='pa_meta_debt' AND post_id=<%fieldname5%>

    You also have the option of not changing your query and you either handle the no row returned status in the processing code, or you execute a

    SELECT count(*) AS noofrows
FROM tr_postmeta WHERE meta_key='pa_meta_debt' AND post_id=<%fieldname5%>

    query first, which will tell you if there are any matching records. I would use this latter approach if your query may return more than 1 rows.

  2. You can use this

    Select Coalesce((SELECT meta_value AS value FROM tr_postmeta WHERE meta_key='pa_meta_debt' AND post_id=<%fieldname5%> Limit 1),0)  As meta_value

Comments are closed.