PHP MySQL SELECT Query Syntax From Wordpress
I am trying to write a MySQL Query of a Wordpress Table. In one of the Tables theres is a META_KEY of "Custom Price - My Deductible ($333.10)"
How do I write:
$Q = "SELECT *
WHERE meta_key = 'Custom Price - My Deductible %)'"; And leave of the ($333.10) Part?
How do I write:
$Q = "SELECT *
WHERE meta_key = 'Custom Price - My Deductible %)'"; And leave of the ($333.10) Part?
ASKER
@Gary what if my syntax is as follows:
That does not work
JOIN wp_woocommerce_order_itemmeta om7 ON om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)'That does not work
No, you cannot join on a static value, move it to a WHERE clause
ASKER
Can you give me an example? I'm not sure I understand.
Post your full SQL
ASKER
$check_alt = "SELECT DISTINCT
p1.post_id AS post_id,
oi1.order_item_id AS order_item_id,
om1.meta_value AS name,
om2.meta_value AS serial_number,
om3.meta_value AS total_amount,
om4.meta_value AS starting_date,
om5.meta_value AS ending_date,
om6.meta_value AS location,
om7.meta_value AS deductible,
om10.meta_value AS status
FROM wp_postmeta p1
JOIN wp_woocommerce_order_items oi1 ON (oi1.order_id = p1.post_id)
JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Primary Operator Name - Name')
JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Bicycle Serial Number (if available) - Serial Number')
JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = '_line_total')
JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_subscription_start_date')
JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_expiry_date')
JOIN wp_woocommerce_order_itemmeta om6 ON (om6.order_item_id = oi1.order_item_id AND om6.meta_key = 'Location where bicycle is kept - Location')
JOIN wp_woocommerce_order_itemmeta om7 ON om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)'
JOIN wp_woocommerce_order_itemmeta om10 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
WHERE p1.meta_value = :user_id AND om10.meta_value = 'active'";$check_alt = "SELECT DISTINCT
p1.post_id AS post_id,
oi1.order_item_id AS order_item_id,
om1.meta_value AS name,
om2.meta_value AS serial_number,
om3.meta_value AS total_amount,
om4.meta_value AS starting_date,
om5.meta_value AS ending_date,
om6.meta_value AS location,
om7.meta_value AS deductible,
om10.meta_value AS status
FROM wp_postmeta p1
JOIN wp_woocommerce_order_items oi1 ON (oi1.order_id = p1.post_id)
JOIN wp_woocommerce_order_itemmeta om1 ON (om1.order_item_id = oi1.order_item_id AND om1.meta_key = 'Primary Operator Name - Name')
JOIN wp_woocommerce_order_itemmeta om2 ON (om2.order_item_id = oi1.order_item_id AND om2.meta_key = 'Bicycle Serial Number (if available) - Serial Number')
JOIN wp_woocommerce_order_itemmeta om3 ON (om3.order_item_id = oi1.order_item_id AND om3.meta_key = '_line_total')
JOIN wp_woocommerce_order_itemmeta om4 ON (om4.order_item_id = oi1.order_item_id AND om4.meta_key = '_subscription_start_date')
JOIN wp_woocommerce_order_itemmeta om5 ON (om5.order_item_id = oi1.order_item_id AND om5.meta_key = '_subscription_expiry_date')
JOIN wp_woocommerce_order_itemmeta om6 ON (om6.order_item_id = oi1.order_item_id AND om6.meta_key = 'Location where bicycle is kept - Location')
JOIN wp_woocommerce_order_itemmeta om7 ON (om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)')
JOIN wp_woocommerce_order_itemmeta om10 ON (om10.order_item_id = oi1.order_item_id AND om10.meta_key = '_subscription_status')
WHERE p1.meta_value = :user_id AND om10.meta_value = 'active'";ASKER
That did not work. It caused the whole query to fail
Whats the error message?
ASKER
After the Query I have:
I get "Undefined Variable for $fn, $sn, $ta, etc...
$checking_alt = $pdo->prepare($check_alt);
$checking_alt->execute(array(':user_id' => $user_id)) or die(print_r($checking_alt -> errorInfo()));
$res_alt = $checking_alt->fetchAll();
if ($res_alt) {
foreach ($res_alt as $rows) {
$fn = $rows->name;
$sn = $rows->serial_number;
$ta = $rows->total_amount;
$sd = $rows->starting_date;
$ed = $rows->ending_date;
$loc = $rows->location;
}
} I get "Undefined Variable for $fn, $sn, $ta, etc...
Thats nothing to do with the SQL
What are you trying to do here and the following lines?
$fn = $rows->name;
To get the recordset value you use
$fn = $rows['name'];
What are you trying to do here and the following lines?
$fn = $rows->name;
To get the recordset value you use
$fn = $rows['name'];
ASKER
If I take this line out everything works fine:
JOIN wp_woocommerce_order_itemmeta om7 ON (om7.order_item_id = oi1.order_item_id AND om7.meta_key LIKE 'Custom Price - My Deductible %)')
When you run the query directly in phpmyadmin or similar what do you get?
Are you sure there are any rows being returned.
Are you sure there are any rows being returned.
ASKER
I get the Warning:
#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
I have no idea what that means
#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
I have no idea what that means
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I set that?
This needs to be set in a seperate sql call before your own sql
$pdo->query("SET SQL_BIG_SELECTS=1;");
Open in new window