How do I show a row based on the value of another field in a separate row?
In the table below, the meta keys
show_logo are associated with each other based on the
post_id field. How do I get the value of
logo_ID for rows which have a
1 using only mysql?
post_id meta_key meta_value
------- ----------- ----------
262 logo_ID 263
262 show_logo 0
260 logo_ID 261
260 show_logo 1
258 logo_ID 259
258 show_logo 1
If it looks familiar it's because this is from the WP
postmeta table. I'm using
$wpdb to query the db so everything has to be in mysql (no WP functions anywhere).
pivot the rows by using
CASE statement based on the values of
meta_key so you can show the values of
post_id in a row, then wrap it in a subquery.
MAX(CASE WHEN meta_key = 'logo_ID' THEN meta_value ELSE NULL END) logo_ID,
MAX(CASE WHEN meta_key = 'show_logo' THEN meta_value ELSE NULL END) show_logo
GROUP BY post_ID
WHERE show_logo = 1
You can join the
wp_postmeta table to itself, specifying the value you want in the
SELECT pm2.post_id, pm2.meta_key
FROM wp_postmeta pm1
JOIN wp_postmeta pm2
ON pm2.post_id = pm1.post_id AND pm2.meta_key = 'logo_ID'
WHERE pm1.meta_key = 'show_logo' AND pm1.meta_value = 1
If you are dealing with a lot of data, this is fairly fast as it will use indexes on the
wp_postmeta table. You can
wp_postmeta more if you want to get other values as criteria as well, or the
wp_posts table to get the other post fields.
use mysql to replace one custom field value with another custom field value
I have two custom fields, and both have numeric values: tnid_01 tnid_01old Custom Field 'tnid_01' exists for all posts. Custom Field 'tnid_01old' only exists for some posts. I am trying to replace the value of 'tnid_01' with the value of 'tnid_01old', but only if 'tnid_01old' exists. This is what i have so far, but i get a mysql error: thx
If Custom Field Value exists display, if not display another Custom field value
I have a Wordpress managed web site at http://www.urbanvision.org.uk/ I have built it and everything works like I want it to and I'm happy with the outcome as it is my first fully built Wordpress web site. I've become stuck on a request I had at the start of the week. We have a Properties for Sale page (http://www.urbanvision.org.uk/services/proper...
Separate user bases or hide users of another role or connected to another minisite
I am creating a WordPress site in which I want to create mini-sites/sub-sites meaning each of them has their own separate user base. Alternatively, it's ok that all the users are in the same base, but that users that are not in the same role, group or those connected to a subsite are hidden. This could be done either with a plugin or that I create ...
Show subtotal excl. tax, add subtotal tax as separate row on Woocommerce checkout
Currently I have all my products set to include tax. Because of rules in my country I would like to have my subtotal without tax, then a line with the amount of tax being paid and then the total with all the taxes (which is already default) In my review-order.php the line <td><?php wc_cart_totals_subtotal_html(); ?></td> gets call...