Solved

WordPress Query Syntax

Posted on 2014-10-13
4
207 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

707 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

11 Experts available now in Live!

Get 1:1 Help Now