Wordpress MySQL Query Syntax PHP

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?
LVL 7
rgranlundAsked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
$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
 
GaryCommented:
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
 
rgranlundAuthor Commented:
@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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
GaryCommented:
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
 
rgranlundAuthor Commented:
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
 
rgranlundAuthor Commented:
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
 
PortletPaulCommented:
[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
 
GaryCommented:
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
 
rgranlundAuthor Commented:
I removed DISTINCT but I still get the fatal error.
0
 
PortletPaulCommented:
My comment is restricted to only the SQL, that error is coming from something else. Sorry i didn't make that clear
0
 
rgranlundAuthor Commented:
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
 
GaryCommented:
$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
 
rgranlundAuthor Commented:
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
 
rgranlundAuthor Commented:
That was the ticket!  Thanks for your patience and help!
0
All Courses

From novice to tech pro — start learning today.