?
Solved

Wordpress Query Format

Posted on 2014-10-06
5
Medium Priority
?
93 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 61

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 61

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 61

Accepted Solution

by:
Julian Hansen earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. 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 : Open Page or Post…
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…
Suggested Courses

621 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