Solved

Wordpress Query Format

Posted on 2014-10-06
5
84 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 55

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 55

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 55

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
SQL Help 27 57
T-SQL: Do I need CLUSTERED here? 13 59
SQL Syntax: How to force case sensitive query? 2 44
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…
So you have coded your own WordPress plugin and now you want to allow users to upload images to a folder in the plugin folder rather than the default media location? Follow along and this article will show you how to do just that!
This video teaches users how to migrate an existing Wordpress website to a new domain.
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. …

860 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