?
Solved

PHP Query Syntax

Posted on 2014-07-21
3
Medium Priority
?
178 Views
Last Modified: 2014-07-29
Can anyone shed some light on my error message?  The following is the query.  Everything work, but the line that causes the error and I'm not sure what is wrong with it:
	$check = "SELECT DISTINCT
				 p1.post_id AS post_id,
				 oi1.order_item_id AS order_item_id,
				 p3.meta_value AS address_2,
				 om1.meta_value AS first_name,
				 om2.meta_value AS last_name,
				 om3.meta_value AS serial_number,
				 om4.meta_value AS total_amount,
				 om5.meta_value AS starting_date,
				 om10.meta_value AS status
				 FROM wp_postmeta p1, wp_woocommerce_order_itemmeta oi1
					JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
				 JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
				 JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Policy Holder Last Name - Policy Holder Last Name')
				 JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = 'Bicycle Information - Frame Serial Number')
				 JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_line_total')
				 JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_start_date')	
			JOIN wp_woocommerce_order_itemmeta om10 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
				 WHERE p1.meta_value = :user_id AND om10.meta_value = 'active'";
				 

Open in new window

ERROR Message:   Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'p1.post_id' in 'on clause' ) 1
0
Comment
Question by:rgranlund
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40210387
that field (post_id) does not exist in that table (p1)  (p1 is an alias for wp_postmeta)

it is complaining about this join:

     JOIN wp_postmeta p3
                 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40210395
(by omitting an explicit join you imply a cross join)

Is the CROSS JOIN between wp_postmeta  and wp_woocommerce_order_itemmeta deliberate?
SELECT DISTINCT
      p1.post_id        AS post_id
    , oi1.order_item_id AS order_item_id
    , p3.meta_value     AS address_2
    , om1.meta_value    AS first_name
    , om2.meta_value    AS last_name
    , om3.meta_value    AS serial_number
    , om4.meta_value    AS total_amount
    , om5.meta_value    AS starting_date
    , om10.meta_value   AS status
FROM wp_postmeta p1
     CROSS JOIN wp_woocommerce_order_itemmeta oi1
     JOIN wp_postmeta p3
                 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
     JOIN wp_woocommerce_order_itemmeta om1
                 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
     JOIN wp_woocommerce_order_itemmeta om2
                 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Policy Holder Last Name - Policy Holder Last Name')
     JOIN wp_woocommerce_order_itemmeta om3
                 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = 'Bicycle Information - Frame Serial Number')
     JOIN wp_woocommerce_order_itemmeta om4
                 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_line_total')
     JOIN wp_woocommerce_order_itemmeta om5
                 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_start_date')
     JOIN wp_woocommerce_order_itemmeta om10
                 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
WHERE p1.meta_value = :user_id
      AND om10.meta_value = 'active'

Open in new window

0
 
LVL 7

Accepted Solution

by:
Vimal DM earned 2000 total points
ID: 40214170
Hi,

A simple solution would be adding the below line,
JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')

very next to

FROM wp_postmeta p1


Like this:


FROM wp_postmeta p1
   JOIN wp_postmeta p3 ON (p3.post_id = p1.post_id AND p3.meta_key = '_billing_address_2')
wp_woocommerce_order_itemmeta oi1
JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Policy Holder First Name - Policy Holder First Name')
0

Featured Post

Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this tutorial viewers will learn how to style transparent/translucent elements using alpha transparency in CSS Start with a normal styled element, such as a div.: Define its "background-color" property as "rgba (255, 255, 255, .5): The numbers in…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses

800 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