Solved

WordPress Query Syntax

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

827 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