Solved

Wordpress Query Format

Posted on 2014-10-06
5
86 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 56

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 56

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 56

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

726 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