Solved

PHP MySQL SELECT Query Syntax From Wordpress

Posted on 2014-09-03
17
311 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

789 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