?
Solved

WordPress Query Syntax

Posted on 2014-10-13
4
Medium Priority
?
220 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

Accepted Solution

by:
PortletPaul earned 2000 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

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 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