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

x
?
Solved

Wordpress MySQL Query Syntax PHP

Posted on 2014-09-18
15
Medium Priority
?
202 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
[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
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 49

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 49

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…

705 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