Solved

Wordpress MySQL Query Syntax PHP

Posted on 2014-09-18
15
176 Views
Last Modified: 2014-09-20
I have a wordpress site.  I need to write query but I'm not 100% sure.

SELECT 
	meta_value AS deductible,
	meta_value AS bike_type,
	meta_value AS bike_liabil,
	meta_value AS status
FROM 
  wp_woocommerce_order_itemmeta
WHERE
meta_key = 'Bicycle Info - Deductible Amount'
 AND meta_key = 'Bicycle Info - Type of Bicycle'
 AND meta_key = 'Bicycle Info - Liability Amount'
AND order_id_number = :order_id

Open in new window

Can anyone shed light on this issue?
0
Comment
Question by:rgranlund
  • 7
  • 5
  • 2
15 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40331624
The SQL makes no sense so it's hard to know what you are trying to do.
Maybe this...

SELECT 
	meta_value
FROM 
	wp_woocommerce_order_itemmeta
WHERE (
	meta_key = 'Bicycle Info - Deductible Amount'
	OR 
	meta_key = 'Bicycle Info - Type of Bicycle'
	OR 
	meta_key = 'Bicycle Info - Liability Amount'
)
AND 
	order_id_number = :order_id

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40333703
@Gary ok, that makes sense but how do I then create variable from that?
checking  = "SELECT DISTINCT meta_value
				FROM wp_woocommerce_order_itemmeta
				WHERE (meta_key = 'Bicycle Info - Deductible Amount'
				OR meta_key = 'Bicycle Info - Type of Bicycle'
				OR meta_key = 'Bicycle Info - Liability Amount'
				OR meta_key = '_subscription_status')
				AND order_id_number = :order_id AND meta_value = 'active'";


	try {
    	$checking->execute(array(':user_id' => $user_id)) or die(print_r($checking -> errorInfo()));
		$res = $checking->fetchAll();		 
	
		if ($res) {

				foreach ($res as $row)  {
				$da = $row->Bicycle Info - Deductible Amount;
				$tb = $row->Bicycle Info - Type of Bicycle;
				$ct = $row->Bicycle Info - Liability Amount;

Open in new window

Does that make sense?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40333727
I don't what you are trying to do...?

foreach ($res as $row)  {
if ($row['meta_key']=="'Bicycle Info - Deductible Amount"){
...
}

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40333755
What about this line:
OR meta_key = '_subscription_status')
				AND order_id_number = :order_id AND meta_value = 'active'";
_subscription_status needs to equal Active;

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40333808
Here is my finished code and the error I am getting is:
Fatal Error: cannot use object of type stdClass as array


$check_final = "SELECT DISTINCT meta_value
				FROM wp_woocommerce_order_itemmeta
				WHERE (meta_key = 'Bicycle Info - Deductible Amount'
				OR meta_key = 'Bicycle Info - Type of Bicycle'
				OR meta_key = 'Bicycle Info - Liability Amount'
				OR meta_key = '_subscription_status')
				AND order_item_id = :order_id ";

$c_alt = $pdo->prepare($check_final);		
						$c_alt->execute(array(':order_id' => $order_id)) or die(print_r($c_alt -> errorInfo()));
						$r_alt = $c_alt->fetchAll();	
						if ($r_alt) {
							foreach ($r_alt as $rowb)  {
								
								if($rowb['meta_key'] == "Bicycle Info - Deductible Amount") {
									$deduc = $rowb['meta_key'] == "Bicycle Info - Deductible Amount";
								}
								
								if($rowb['meta_key'] == "Bicycle Info - Type of Bicycle") {
									$bike_type = $rowb['meta_key'] == "Bicycle Info - Type of Bicycle";
								}
								
								if($rowb['meta_key'] == "Bicycle Info - Liability Amount") {
									$liabil = $rowb['meta_key'] == "Bicycle Info - Liability Amount";
								}

							}
						}

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40333832
[sigh]

remove DISTINCT from that query

DISTINCT is EXTRA WORK, it slows down the query (it is performed as the last step of a query)

Please don't "just add" distinct without knowing why and considering the impact. See: Why I Hate DISTINCT

If that query returns "duplicates" then wordpress has very very serious issues to solve, and adding distinct to a query won't solve them.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40333839
I still don't know what you are trying to do
This (and the following lines) make no sense

if($rowb['meta_key'] == "Bicycle Info - Deductible Amount") {
		$deduc = $rowb['meta_key'] == "Bicycle Info - Deductible Amount";
}

Open in new window

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 7

Author Comment

by:rgranlund
ID: 40333840
I removed DISTINCT but I still get the fatal error.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40333844
My comment is restricted to only the SQL, that error is coming from something else. Sorry i didn't make that clear
0
 
LVL 7

Author Comment

by:rgranlund
ID: 40333850
I m trying to get the meta_value of those specific meta_keys.  I'm not sure how to ask this.

SELECT meta_value
				FROM wp_woocommerce_order_itemmeta
				WHERE (meta_key = 'Bicycle Info - Deductible Amount'
				OR meta_key = 'Bicycle Info - Type of Bicycle'
				OR meta_key = 'Bicycle Info - Liability Amount'
				OR meta_key = '_subscription_status')
				AND order_item_id = :order_id ";

Open in new window

I want to get the meta_vale from where (meta_key = 'Bicycle Info - Deductible Amount')
Then I want to turn that specific meta_vale into a variable.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40333855
$check_final = "SELECT meta_value
				FROM wp_woocommerce_order_itemmeta
				WHERE (meta_key = 'Bicycle Info - Deductible Amount'
				OR meta_key = 'Bicycle Info - Type of Bicycle'
				OR meta_key = 'Bicycle Info - Liability Amount'
				OR meta_key = '_subscription_status')
				AND order_item_id = :order_id ";

$c_alt = $pdo->prepare($check_final);		
						$c_alt->execute(array(':order_id' => $order_id)) or die(print_r($c_alt -> errorInfo()));
						$r_alt = $c_alt->fetchAll();	
						if ($r_alt) {
							foreach ($r_alt as $rowb)  {
								
								if($rowb['meta_key'] == "Bicycle Info - Deductible Amount") {
									$deduc = $rowb['meta_value'];
								}
								
								elseif($rowb['meta_key'] == "Bicycle Info - Type of Bicycle") {
									$bike_type = $rowb['meta_value'];
								}
								
								elseif($rowb['meta_key'] == "Bicycle Info - Liability Amount") {
									$liabil = $rowb['meta_value'];
								}

							}
						}

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 40334431
When I get to this line:
if($rowb['meta_key'] == "Bicycle Info - Deductible Amount") {
									$deduc = $rowb['meta_value'];
								}

Open in new window

I get the Fatal Error:
cannot use object of type stdClass as in array in line... you offer any insight?

Can
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40334433
$check_final = "SELECT meta_value
				FROM wp_woocommerce_order_itemmeta
				WHERE (meta_key = 'Bicycle Info - Deductible Amount'
				OR meta_key = 'Bicycle Info - Type of Bicycle'
				OR meta_key = 'Bicycle Info - Liability Amount'
				OR meta_key = '_subscription_status')
				AND order_item_id = :order_id ";

$c_alt = $pdo->prepare($check_final);		
						$c_alt->execute(array(':order_id' => $order_id)) or die(print_r($c_alt -> errorInfo()));
						$r_alt = $c_alt->fetchAll();	
						if ($r_alt) {
							foreach ($r_alt as $rowb)  {
								
								if($rowb->meta_key == "Bicycle Info - Deductible Amount") {
									$deduc = $rowb->meta_value;
								}
								
								elseif($rowb->meta_key == "Bicycle Info - Type of Bicycle") {
									$bike_type = $rowb->meta_value;
								}
								
								elseif($rowb->meta_key == "Bicycle Info - Liability Amount") {
									$liabil = $rowb->meta_value;
								}

							}
						}

Open in new window

0
 
LVL 7

Author Closing Comment

by:rgranlund
ID: 40334450
That was the ticket!  Thanks for your patience and help!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In Part I (http://www.experts-exchange.com/Web_Development/Blogs/WordPress/A_8410-Getting-Started-In-WordPress-Part-I.html), I introduced you to the powerful WordPress backend, the WordPress administrative Dashboard.  In Part II, I will introduce yo…
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…

746 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