Solved

PHP Query Syntax

Posted on 2014-07-21
3
142 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
Comment Utility
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
Comment Utility
(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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this tutorial viewers will learn how to embed custom externally-hosted Google Fonts using the Google Font API in CSS Go to the Google Fonts website at google.com/fonts: Browse or search based on font properties or name to find a suitable font for…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now