Solved

Wordpress Query Format

Posted on 2014-10-06
5
82 Views
Last Modified: 2014-10-29
I have a wordpress website and have a hard time remembering how to format a query.
The following Query is close but not correct:

$pdf_c = $pdo->prepare("SELECT meta_value
					FROM wp_woocommerce_order_itemmeta
					WHERE (meta_key='_subscription_start_date' AND order_item_id = :id) AS start
					WHERE (meta_key='_subscription_expiry_date' AND order_item_id = :id) AS ending");

Open in new window

I want to select the value of BOTH _subscription_start_date AND _subscription_expiry_date
0
Comment
Question by:rgranlund
  • 3
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Julian Hansen
ID: 40365718
Looks like you are trying to get the values out in one row - when they are in fact different rows.

You can't have AS in a WHERE clause - the AS is applied to field and table names in the SELECT part of the statement, the WHERE clause is for filtering results based on certain criteria.

i.e.
SELECT field as 'Fieldname' FROM table as t1;

It sounds like the two values you want are in different records which means you have to retrieve them as a JOIN or individually.

As a join you could try something like this
$pdf_c = $pdo->prepare("SELECT a.meta_value AS start, b.meta_value AS ending 
	FROM wp_woocommerce_order_itemmeta AS a LEFT JOIN wp_woocommerce_order_itemmeta AS b ON a.order_item_id = b.order_item_id
	WHERE a.order_item_id = :id AND a.meta_key='_subscription_start_date' AND b.meta_key='_subscription_expiry_date'");

Open in new window

The above query joins the wp_woocommerce_order_itemmeta table to itself on the order_id and then from that JOIN selects the row that has the _subscription_start_date and _subscription_expiry_Date meta_key values.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40378109
I get the following error using your script format:
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wp_woocommerce_order_itemmeta AS c ON a.order_item_id = c.order_item_id ' at line 4 ) 1

What do you think the issue is with my code?
	$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
										FROM wp_woocommerce_order_itemmeta AS a LEFT JOIN
										wp_woocommerce_order_itemmeta AS b ON a.order_item_id = b.order_item_id
										wp_woocommerce_order_itemmeta AS c ON a.order_item_id = c.order_item_id
										wp_woocommerce_order_itemmeta AS d ON a.order_item_id = d.order_item_id
										wp_woocommerce_order_itemmeta AS e ON a.order_item_id = e.order_item_id
										WHERE a.order_item_id = :id
										AND a.meta_key='_subscription_start_date'
										AND b.meta_key='_subscription_expiry_date'
										AND c.meta_key='_subscription_end_date'
										AND d.meta_key='_subscription_status'
										AND e.meta_key='_line_total'");
					
				try {
					$pdf_a->execute(array(':id' => $id)) or die(print_r($pdf_a -> errorInfo()));

Open in new window

0
 
LVL 53

Expert Comment

by:Julian Hansen
ID: 40378460
Are you getting an error or wrong results?

The query looks like a brute force transpose - is that what you are trying to achieve?
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40378536
I'm getting the following error:
Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wp_woocommerce_order_itemmeta AS c ON a.order_item_id = c.order_item_id ' at line 4 ) 1
I need to run a query and get certain meta_values from specific meta_keys.
0
 
LVL 53

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 40379216
You left a few LEFT JOIN's out. Try this
$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
  FROM wp_woocommerce_order_itemmeta AS a 
    LEFT JOIN wp_woocommerce_order_itemmeta AS b ON a.order_item_id = b.order_item_id
    LEFT JOIN wp_woocommerce_order_itemmeta AS c ON a.order_item_id = c.order_item_id
    LEFT JOIN wp_woocommerce_order_itemmeta AS d ON a.order_item_id = d.order_item_id
    LEFT JOIN wp_woocommerce_order_itemmeta AS e ON a.order_item_id = e.order_item_id
  WHERE a.order_item_id = :id
    AND a.meta_key='_subscription_start_date'
    AND b.meta_key='_subscription_expiry_date'
    AND c.meta_key='_subscription_end_date'
    AND d.meta_key='_subscription_status'
    AND e.meta_key='_line_total'");

try {
  $pdf_a->execute(array(':id' => $id)) or die(print_r($pdf_a -> errorInfo()));

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Wordpress Horizontal Drop-Down Menu In this tutorial I will show you had to add a WordPress horizontal navigation menu to your theme. I have searched and searched for a good tutorial on creating a WordPress nav menu without adding a plug-in or us…
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. …
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
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

20 Experts available now in Live!

Get 1:1 Help Now