$db-Execute() issue with fields... Please please please help.
OK...
/includes/classes/order.php
I want to suck in an additional field called : gift_message (which is a textarea field on the checkout which we have utilised so that people can add in information about their delivery)... and place this in the confirmation email....
BUT : $db-Execute() is not returning a value for 'gift_message'
I have checked in the database. There is data in this field once the order is submitted... All data and orders get processed just fine.
I have added in 'customers_name' and 'shipping_telephone' to my query below just to see what is going on.
Code:
global $db;
$orderDelvDate_query = "SELECT customers_name, gift_message, shipping_telephone FROM " . TABLE_ORDERS . " WHERE orders_id='" . int($zf_insert_id) . "'";
$orderDelvDate = $db->Execute($orderDelvDate_query);
if (isset($orderDelvDate) && $orderDelvDate!="")
{
foreach ($orderDelvDate->fields as $i => $value)
{
$html_msg['PREFERRED_DELIVERY_DATE'] .= "<br/>" . $i . " = " . $value;
}
}
And, in the email I get:
customers_name = Scott Shaw
gift_message =
shipping_telephone = 448456440537
Key Problem : There should be a value against 'gift_message'.
Has anyone ANY idea why there is no value assigned to this in the Array. Data is in the database 'orders' table. And the field is called 'gift_message'
I have looked at tonnes of other code and they all just do something straightforeward like:
$html_msg['PREFERRED_DELIVERY_DATE'] = $orderDelvDate->fields['gift_message'];
But this just keeps coming up Empty.
Please please please help.
Re: $db-Execute() issue with fields... Please please please help.
While neither of these is likely the cause of your symptoms, I suggest making a few fixes:
a) int($zf_insert_id) should really be written as (int)$zf_insert_id
b) instead of checking for isset or blank, properly iterating through the object would be safer and less confusing to the compiler engine.
ie: replace this:
Code:
if (isset($orderDelvDate) && $orderDelvDate!="")
{
.....
}
with the normal Zen Cart method of:
Code:
while (!$orderDelvDate->EOF)
{
.....
$orderDelvDate->MoveNext();
}
or if it will never need to worry about there being more than one result, add "LIMIT 1" to the end of your SQL query and change your isset check to this instead:
Code:
if ($orderDelvDate->RecordCount() > 0)
As far as the data not being retrieved, MySQL just feeds back the data as it was stored. Have you changed any core code that interacts with the database, such as the query_factory class, or added any plugins that interfere with caching and queries?
Please click Reply below and answer all the questions in the Posting Tips section.
Re: $db-Execute() issue with fields... Please please please help.
Also to expand on the questions drByte asked.
How are you sure that the gift_message field is populated fot the specific order_id you are querying. Have you confirmed this using something like phpMyAdmin ?
Re: $db-Execute() issue with fields... Please please please help.
Yes yes... definitely (double)checked that gift_message for that record is populated in phpMyAdmin.
I have not changed query_factory and I really can't think of any module/plugin that makes amends to it.
It is a mystery. The script dumps every single value from that table except that 'gift_message'.
Anyone?
Re: $db-Execute() issue with fields... Please please please help.
Quote:
Originally Posted by
Ooba_Scott
It is a mystery. The script dumps every single value from that table except that 'gift_message'.
Anyone?
Wrong datatype?
Restricted and/or invalid characters?
Re: $db-Execute() issue with fields... Please please please help.
I was wondering about the datatype too.
What's the schema for this table, since you've altered it?
Re: $db-Execute() issue with fields... Please please please help.
Just had a check, it is a 'Textarea' on the site, and its data type is 'TEXT'.
:(
Re: $db-Execute() issue with fields... Please please please help.
Debugging:
Code:
global $db;
$orderDelvDate_query = "SELECT customers_name, gift_message, shipping_telephone FROM " . TABLE_ORDERS . " WHERE orders_id='" . (int)$zf_insert_id . "'";
$result = $db->Execute($orderDelvDate_query);
die('results=<pre>' . print_r($result->fields, TRUE));
What does it display?
Re: $db-Execute() issue with fields... Please please please help.
Thanks Dr Byte, below is what it displayed:
results_order=
Array
(
[customers_name] => Joe Bloggs
[gift_message] =>
[shipping_telephone] => 456789065654
)
But it has just given us a few ideas .... off possibly why it is not showing. We will do some more testing, and post if we have fixed it or not!
Thanks for your helps so far, much appreciated! :)
Re: $db-Execute() issue with fields... Please please please help.
Solution found:
The file 'order.php' processes the order details from multiple sources (shipping, customer, order) etc... and puts all this together for the final processing. However "Comments" and our new field "gift_message" are stored in sessions.
So, at the point of trying to extract the data from the orders table, the 'gift_message' and 'comments' had not actually been UPDATED into the database. So, indeed, the field empty.
Therefore, we needed to find the session details.
Regretfully, there were many session variables with arrays within them (so we couldn't just do a session dump) and we had no idea as to what these were or what the $key values were so we couldn't extract them.
We were looking for 'gift_message'... and tried a load of stuff like $_SESSION['shipping']['gift_message'] etc to try and find where the gift message was stored. We knew it had to be in a session.
In the end we realised that the field in the checkout was actually called 'gift-message' and tried that
$_SESSION['gift-message']
Bingo...
Great naming policy from Numinix there !!
So, we added the line...
Code:
'gift_message' => $_SESSION['gift-message'],
to :
Code:
LINE 350 (approx)...
$this->info = array('order_status' => DEFAULT_ORDERS_STATUS_ID,
'currency' => $_SESSION['currency'],
'currency_value' => $currencies->currencies[$_SESSION['currency']]['value'],
'payment_method' => $GLOBALS[$class]->title,
'payment_module_code' => $GLOBALS[$class]->code,
'coupon_code' => $coupon_code->fields['coupon_code'],
// 'cc_type' => (isset($GLOBALS['cc_type']) ? $GLOBALS['cc_type'] : ''),
// 'cc_owner' => (isset($GLOBALS['cc_owner']) ? $GLOBALS['cc_owner'] : ''),
// 'cc_number' => (isset($GLOBALS['cc_number']) ? $GLOBALS['cc_number'] : ''),
// 'cc_expires' => (isset($GLOBALS['cc_expires']) ? $GLOBALS['cc_expires'] : ''),
// 'cc_cvv' => (isset($GLOBALS['cc_cvv']) ? $GLOBALS['cc_cvv'] : ''),
'shipping_method' => (is_array($_SESSION['shipping']) ? $_SESSION['shipping']['title'] : $_SESSION['shipping']),
'shipping_module_code' => (isset($_SESSION['shipping']['id']) && strpos($_SESSION['shipping']['id'], '_') > 0 ? $_SESSION['shipping']['id'] : $_SESSION['shipping']),
'shipping_cost' => $_SESSION['shipping']['cost'],
'subtotal' => 0,
'shipping_tax' => 0,
'tax' => 0,
'total' => 0,
'tax_groups' => array(),
'comments' => (isset($_SESSION['comments']) ? $_SESSION['comments'] : ''),
'gift_message' => $_SESSION['gift-message'],
'ip_address' => $_SESSION['customers_ip_address'] . ' - ' . $_SERVER['REMOTE_ADDR']
);
And then (approx Line 1200)
Code:
if ($this->info['gift_message'])
{
$email_order .= zen_db_output($this->info['gift_message']) . "\n\n";
$html_msg['PREFERRED_DELIVERY_DATE'] = nl2br(zen_db_output($this->info['gift_message']));
}
else
{
$html_msg['PREFERRED_DELIVERY_DATE'] = 'No Results found in session for the gift_message';
}
Hope this helps someone !!
We had already added in $html_msg['PREFERRED_DELIVERY_DATE'] successfully to the HTML email template.