Solved

PHP Query Syntax

Posted on 2014-07-21
3
166 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 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

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

695 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