Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

PHP MySQL SELECT Query Syntax From Wordpress

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
rgranlund
Asked:
rgranlund
  • 9
  • 8
1 Solution
 
GaryCommented:
$Q = "SELECT * WHERE meta_key LIKE 'Custom Price - My Deductible %)'"; 

Open in new window

0
 
rgranlundAuthor Commented:
@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
 
GaryCommented:
No, you cannot join on a static value, move it to a WHERE clause
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rgranlundAuthor Commented:
Can you give me an example?  I'm not sure I understand.
0
 
GaryCommented:
Post your full SQL
0
 
rgranlundAuthor Commented:
$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
 
GaryCommented:
$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
 
rgranlundAuthor Commented:
That did not work.  It caused the whole query to fail
0
 
GaryCommented:
Whats the error message?
0
 
rgranlundAuthor Commented:
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
 
GaryCommented:
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
 
rgranlundAuthor Commented:
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
 
GaryCommented:
When you run the query directly in phpmyadmin or similar what do you get?

Are you sure there are any rows being returned.
0
 
rgranlundAuthor Commented:
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
 
GaryCommented:
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
 
rgranlundAuthor Commented:
How do I set that?
0
 
GaryCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now