Solved

Wordpress Query Format

Posted on 2014-10-06
5
79 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 51

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 51

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 51

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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 properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…
This video teaches users how to migrate an existing Wordpress website to a new domain.

747 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

13 Experts available now in Live!

Get 1:1 Help Now