Solved

PHP MySQL SELECT Query Syntax From Wordpress

Posted on 2014-09-03
17
304 Views
Last Modified: 2014-09-13
I am trying to write a MySQL Query of a Wordpress Table.  In one of the Tables theres is a META_KEY of "Custom Price - My Deductible ($333.10)"

How do I write:
$Q = "SELECT *
WHERE meta_key = 'Custom Price - My Deductible %)'";  And leave of the  ($333.10) Part?
0
Comment
Question by:rgranlund
  • 9
  • 8
17 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40301769
$Q = "SELECT * WHERE meta_key LIKE 'Custom Price - My Deductible %)'"; 

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302076
@Gary what if my syntax is as follows:
JOIN wp_woocommerce_order_itemmeta om7 ON om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)'

Open in new window


That does not work
0
 
LVL 58

Expert Comment

by:Gary
ID: 40302093
No, you cannot join on a static value, move it to a WHERE clause
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302116
Can you give me an example?  I'm not sure I understand.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40302131
Post your full SQL
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302141
$check_alt  = "SELECT DISTINCT
			 	p1.post_id AS post_id,
		
				oi1.order_item_id AS order_item_id,
				om1.meta_value AS name,
				om2.meta_value AS serial_number,
				om3.meta_value AS total_amount,
				om4.meta_value AS starting_date,
				om5.meta_value AS ending_date,
				om6.meta_value AS location,
				om7.meta_value AS deductible,
				
				om10.meta_value AS status
				
				FROM wp_postmeta p1

				JOIN wp_woocommerce_order_items oi1 ON (oi1.order_id = p1.post_id)
				JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Primary Operator Name - Name') 
				JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Bicycle Serial Number (if available) - Serial Number')
				JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = '_line_total')
				JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_subscription_start_date')
				JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_expiry_date')
				
				JOIN wp_woocommerce_order_itemmeta om6 ON (om6.order_item_id = oi1.order_item_id AND om6.meta_key = 'Location where bicycle is kept - Location')
				
				JOIN wp_woocommerce_order_itemmeta om7 ON om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)'

				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 58

Expert Comment

by:Gary
ID: 40302182
$check_alt  = "SELECT DISTINCT
			 	p1.post_id AS post_id,
		
				oi1.order_item_id AS order_item_id,
				om1.meta_value AS name,
				om2.meta_value AS serial_number,
				om3.meta_value AS total_amount,
				om4.meta_value AS starting_date,
				om5.meta_value AS ending_date,
				om6.meta_value AS location,
				om7.meta_value AS deductible,
				
				om10.meta_value AS status
				
				FROM wp_postmeta p1

				JOIN wp_woocommerce_order_items oi1 ON (oi1.order_id = p1.post_id)
				JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Primary Operator Name - Name') 
				JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Bicycle Serial Number (if available) - Serial Number')
				JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = '_line_total')
				JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_subscription_start_date')
				JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_expiry_date')
				
				JOIN wp_woocommerce_order_itemmeta om6 ON (om6.order_item_id = oi1.order_item_id AND om6.meta_key = 'Location where bicycle is kept - Location')
				
				JOIN wp_woocommerce_order_itemmeta om7 ON (om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)')

				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

Author Comment

by:rgranlund
ID: 40302197
That did not work.  It caused the whole query to fail
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 58

Expert Comment

by:Gary
ID: 40302215
Whats the error message?
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302233
After the Query I have:

		$checking_alt = $pdo->prepare($check_alt);		
		$checking_alt->execute(array(':user_id' => $user_id)) or die(print_r($checking_alt -> errorInfo()));
		$res_alt = $checking_alt->fetchAll();	
				
				if ($res_alt) {
					foreach ($res_alt as $rows)  {
						$fn = $rows->name;
						$sn = $rows->serial_number;
						$ta = $rows->total_amount;
						$sd = $rows->starting_date;
						$ed = $rows->ending_date;
						$loc = $rows->location;
					}		
				}	

Open in new window


I get "Undefined Variable for $fn, $sn, $ta, etc...
0
 
LVL 58

Expert Comment

by:Gary
ID: 40302245
Thats nothing to do with the SQL

What are you trying to do here and the following lines?
$fn = $rows->name;

To get the recordset value you use
$fn = $rows['name'];
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302267
If I take this line out everything works fine:
JOIN wp_woocommerce_order_itemmeta om7 ON (om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)')

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 40302310
When you run the query directly in phpmyadmin or similar what do you get?

Are you sure there are any rows being returned.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40302330
I get the Warning:
#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

I have no idea what that means
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40302346
Means you are trying to join too many rows, which with 10 joins in a single sql is understandable

In phpmyadmin again add this line before the above sql

SET SQL_BIG_SELECTS=1;
SELECT DISTINCT...
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40303591
How do I set that?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40303633
This needs to be set in a seperate sql call before your own sql

$pdo->query("SET SQL_BIG_SELECTS=1;");

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

708 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

17 Experts available now in Live!

Get 1:1 Help Now