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
Solved

Wordpress MySQL Query Syntax PHP

Posted on 2014-09-18
15
188 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

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.

Question has a verified solution.

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

Suggested Solutions

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.…
Do you think that WordPress is just for blogs?  Think again!  WordPress is really a fantastic all around platform that you can use to develop websites on.  Integrated into its basic functionality is the ability to create pages using your choice of a…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to Import and export files in WordPress. 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 : Click on Too…

840 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