Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

PHP MySQL SELECT Query Syntax From Wordpress

Posted on 2014-09-03
17
Medium Priority
?
337 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
[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
  • 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
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

670 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