Solved

Wordpress MySQL Query Syntax PHP

Posted on 2014-09-18
15
190 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
This video teaches users how to migrate an existing Wordpress website to a new domain.

685 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