Solved

WordPress Query Syntax

Posted on 2014-10-13
4
212 Views
Last Modified: 2014-11-09
I have the following the Wordpress Query.  Can anyone explain to me a better way to write it?  If not can you explain what I am doing wrong?

$pdf_a = $pdo->prepare("SELECT a.meta_value AS start, b.meta_value AS ending, c.meta_value AS end_date, d.meta_value AS status, e.meta_value AS total_policy, f.meta_value AS last_name
										FROM 
										wp_woocommerce_order_itemmeta AS a LEFT JOIN
										wp_woocommerce_order_itemmeta AS b LEFT JOIN 
										wp_woocommerce_order_itemmeta AS c LEFT JOIN
										wp_woocommerce_order_itemmeta AS d LEFT JOIN
										wp_woocommerce_order_itemmeta AS e LEFT JOIN
										wp_woocommerce_order_itemmeta AS f
										ON a.order_item_id = b.order_item_id = c.order_item_id = d.order_item_id = e.order_item_id = f.order_item_id
										WHERE a.order_item_id = :id
										OR a.meta_key = '_subscription_start_date'
										OR b.meta_key = '_subscription_expiry_date'
										OR c.meta_key = '_subscription_end_date'
										OR d.meta_key = '_subscription_status'
										OR e.meta_key = '_line_total'
										OR f.meta_key = 'Bicycle Info - Primary Operator Last Name'");
										
									
                                          
					
				try {
					$pdf_a->execute(array(':id' => $id)) or die(print_r($pdf_a -> errorInfo()));
				

Open in new window

0
Comment
Question by:rgranlund
4 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40378812
Hard to make sense of what you are trying to do.
What is the point of this
wp_woocommerce_order_itemmeta AS a LEFT JOIN
wp_woocommerce_order_itemmeta AS b LEFT JOIN
wp_woocommerce_order_itemmeta AS c LEFT JOIN
wp_woocommerce_order_itemmeta AS d LEFT JOIN
wp_woocommerce_order_itemmeta AS e LEFT JOIN
wp_woocommerce_order_itemmeta AS f

You join a table once on a common column, not multiple times.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40379128
You need to move the filters you have in the where clause to the relevant join conditions.

It should look more like this:
SELECT
      *
FROM wp_woocommerce_order_itemmeta AS a
      LEFT JOIN wp_woocommerce_order_itemmeta b ON a.order_item_id = b.order_item_id
                                               AND b.meta_key = '_subscription_expiry_date'
      LEFT JOIN wp_woocommerce_order_itemmeta c ON a.order_item_id = c.order_item_id
                                               AND c.meta_key = '_subscription_end_date'
      LEFT JOIN wp_woocommerce_order_itemmeta d ON a.order_item_id = d.order_item_id
                                               AND d.meta_key = '_subscription_status'
      LEFT JOIN wp_woocommerce_order_itemmeta e ON a.order_item_id = e.order_item_id
                                               AND e.meta_key = '_line_total'
      LEFT JOIN wp_woocommerce_order_itemmeta f ON a.order_item_id = f.order_item_id
                                               AND f.meta_key = 'Bicycle Info - Primary Operator Last Name'
WHERE a.meta_key = '_subscription_start_date'

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Creating and Managing Databases with phpMyAdmin in cPanel.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now