Solved

WordPress Query Syntax

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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

710 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