Solved

PHP Query Syntax

Posted on 2014-07-21
3
151 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
  • 2
3 Comments
 
LVL 48

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 48

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 500 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
CSS is a visual language used to classify objects and define rules about how they should be displayed. CSS skills aren’t restricted to developers anymore, there is a big benefit to having a basic understanding of the language, regardless of your occ…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

770 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