Jump to content

Greywacke

Members
  • Posts

    510
  • Joined

  • Last visited

Everything posted by Greywacke

  1. ah i noted that the $f and $t are not defined anymore, also $now to get the offsets from - and that the variables needed to be defined as $GLOBAL's from within the gettransactions function. only problem now, is that the lead information is not unique in the mails. why would this be? it is supposed to set the values for each transaction.here is the updated code of the cronjob: <?PHP/*CANOPYXCHANGE BRANDED WEEKLY STATEMENTSVersion 2.2.4*/include("/home/dwtphovu/public_html/intellisource.co.za/prod_8347379386/includes/content/dwtphovu_f3rr37y.php");include("/home/dwtphovu/public_html/intellisource.co.za/prod_8347379386/includes/mailer/class.phpmailer.php"); // include PHPMailer classinclude('/home/dwtphovu/public_html/intellisource.co.za/prod_8347379386/includes/statistical/timezones/class-timezone-conversion.php'); /** Include timezone conversion class */function convert_tz($timestamp, $timezone) { /** convert local datetime to SAST (South African Standard Time) */ $tz = new TimezoneConversion(); /** Create TimezoneConversion Object */ $tz->setProperty('DateTime', $timestamp); /** Set local 'DateTime' to convert */ $tz->setProperty('Timezone', $timezone); /** Set Timezone Convert To */ return $tz->convertDateTime(); /** Get SAST Timestamp */}function gettransactions($s) { $ret = ""; $sql = "SELECT * FROM 8_transactions WHERE bigint_SupplierID = ".$s." AND timestamp_TransactionEvent >= \"".$GLOBALS["f"]."\" AND timestamp_TransactionEvent <= \"".$GLOBALS["t"]."\" ORDER BY bigint_TransactionID ASC;"; $result = mysql_query_errors($sql , $conn , __FILE__ , __LINE__ ); while ($row = mysql_fetch_array($result)) { $ret .= " <tr>\n"; $ret .= " <td><div class=\"timestamps\">" . $row["timestamp_TransactionEvent"] . "</div></td>\n"; $ret .= " <td><div class=\"transactions\">"; if ($row["bigint_LeadID"] > 0) { $sql1 = "SELECT * FROM 6_serviceleads JOIN (5_suppliers,2_servicescatalogue,1_regions) ON (6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID AND 6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID AND 6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) WHERE bigint_LeadID = ".$row["bigint_LeadID"].";"; $result1 = mysql_query_errors($sql1 , $conn , __FILE__ , __LINE__ , false); if ($row1 = mysql_fetch_array($result1)) { $GLOBALS["leadid"] = 0; $GLOBALS["consumerfullname"] = ""; $GLOBALS["consumeremail"] = ""; $GLOBALS["consumercell"] = ""; $GLOBALS["city_town"] = ""; $GLOBALS["regionname"] = ""; $GLOBALS["attribsarr"] = array(); $GLOBALS["leadid"] = $row["bigint_LeadID"]; $consumer = explode(";",$row1["text_Consumer"]); $GLOBALS["consumerfullname"] = $consumer[0]; $GLOBALS["consumeremail"] = $consumer[1]; $GLOBALS["consumercell"] = $consumer[2]; $GLOBALS["city_town"] = $consumer[3]; $GLOBALS["regionname"] = $row1["text_RegionDescription"]; $attr = preg_split('/<[^>]+>/', $row1["text_LeadAttributes"], -1, PREG_SPLIT_NO_EMPTY); foreach ($attr as $value) { $val = explode(" = ",$value); $GLOBALS["attribsarr"][$val[0]] = $val[1]; } $ret1 = "%CONFIGTEXT Cron | Supplier | Transaction Detail%"; populateflags($ret1); $ret .= $ret1; } } else { $ret .= $row["text_TransactionEvent"]; } $ret .= "</div></td>\n"; $ret .= " <td><div class=\"balances\">" . $row["bigint_TransactionBalance"] . "</div></td>\n"; $ret .= " <td><div class=\"amounts\">" . $row["bigint_TransactionAmount"] . "</div></td>\n"; $ret .= " </tr>\n"; } return $ret;}$now = getdate(strtotime(convert_tz(date("Y-m-d H:i:s", time()),"SAST")));$f = date("Y-m-d 00:00:00",mktime(0,0,0,$now["mon"],$now["mday"] - 7,$now["year"]));$t = date("Y-m-d 23:59:59",mktime(23,59,59,$now["mon"],$now["mday"] - 1,$now["year"]));$limit = "WEEKLY";$service = 1;// select supplier contact names and email addresses$sql = "SELECT 4_servicesuppliers.bigint_ServiceID, 5_suppliers.bigint_SupplierID, CONCAT(5_suppliers.text_ContactFirstName, \" \", 5_suppliers.text_ContactSurname) AS text_ContactFullName, 5_suppliers.`text_ContactE-mail`, 5_suppliers.text_AccMgr, 5_suppliers.`text_AccMgrE-mail` FROM 5_suppliers LEFT JOIN 4_servicesuppliers ON (5_suppliers.bigint_SupplierID = 4_servicesuppliers.bigint_SupplierID) WHERE 4_servicesuppliers.bigint_ServiceID = 1 AND smallint_SupplierStatus = 0 GROUP BY 5_suppliers.bigint_SupplierID;";$result = mysql_query_errors($sql, $conn , __FILE__ , __LINE__ , false);if ($result) { while ($row = mysql_fetch_array($result)) { $toname = $row["text_ContactFullName"]; $recipient[0] = $toname; $toaddr = $row["text_ContactE-mail"]; // build mail and attempt sending, saving response $body = getFile("/home/dwtphovu/public_html/intellisource.co.za/prod_8347379386/templat/statement.html"); // load mail template $mail = new PHPMailer(); // create a new object $transactions = gettransactions($row["bigint_SupplierID"]); // build transactions html $body = populateflags($body); /*$body = str_replace("%MESSAGE%",$message,$body); // insert statement message $body = str_replace("%TRANSACTIONS%",$transactions,$body); // insert transaction records $body = str_replace("%FROMDATE%",date("jS F Y, H:i:s",strtotime($f)),$body); // insert from date $body = str_replace("%TODATE%",date("tS F Y, H:i:s",strtotime($t)),$body); // insert to date $body = str_replace("%LOGOURL%",$logourl,$body); // insert logo url $body = str_replace("%COPYRIGHT%","CanopyXchange",$body); // insert copyright owner $body = str_replace("%YEAR%",date("Y"),$body); // insert copyright year*/ require("/home/dwtphovu/public_html/intellisource.co.za/prod_8347379386/includes/mailer/dwtphovu_auth.php"); // include mailer auth $mail->From = "canopy@canopyxchange.za.net"; // set from email $mail->FromName = "CanopyXchange"; // set from name $mail->Subject = getSubText($body,"<title>","</title>"); // replacing to and from dates $mail->AddBCC("pierre@greywacke.co.za", "Pierre \"Greywacke\" du Toit"); // add the developer to the BCC list $mail->AddBCC($row["text_AccMgrE-mail"], $row["text_AccMgr"]); // add the account manager to the BCC list $mail->ClearAttachments(); // clears attachments $mail->MsgHTML($body); // set HTML Body $mail->AltBody = strip_tags(html_entity_decode(getSubText($body,"<body>","</body>"))); // set Text Body //$mail->AddAddress($toaddr, $toname); // add recipient $mail->IsHTML(true); // set send as HTML if(!$mail->Send()) { // if attempt sending failed $response = "FAILURE: ".$toname.", ".$toaddr." (".$mail->ErrorInfo.")"; // save error report } else { // else $response = "SUCCESS: ".$toname.", ".$toaddr; // save success report } // end if }}//echo mysql_real_escape_string((is_array($sql))?join("<br />\n",$sql):"");mysql_close($conn);?>
  2. ok i've managed to get an error raised: this brings me to these lines - but i am afraid i cannot see the forest for the trees: $sql1 = "SELECT * FROM 6_serviceleads LEFT JOIN (5_suppliers,2_servicescatalogue,1_regions) ON (6_serviceleads.bigint_SupplierID = 5_suppliers.bigint_SupplierID AND 6_serviceleads.bigint_ServiceID = 2_servicescatalogue.bigint_ServiceID AND 6_serviceleads.bigint_RegionID = 1_regions.bigint_RegionID) WHERE bigint_LeadID = ".$leadid.";"; $result1 = mysql_query_errors($sql1 , $conn , __FILE__ , __LINE__ , false);
  3. hi there,i am struggling slightly to figure out why the following function in the global include will not return some of the variables defined in the cronjob.these include functions have not changed. function idflags($input) { foreach ($input as $in) { // use to explode by first space if found, to get flag values $in = preg_split("/ /",$in,2,PREG_SPLIT_NO_EMPTY); // print result array // print_r($in); // build and return the values to replace with switch ($in[0]) { // mail configurable flags case "LOGO": if ($GLOBALS["attribsarr"]["products_description"] || $GLOBALS["limit"]) { return "%CONFIGTEXT File | Logo | CanopyXchange%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"] && $GLOBALS["response"]) { return "%CONFIGTEXT File | Logo | QuoteMe | Canopies%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT File | Logo | CanopyXchange%"; } break; case "CRONLRSUBJECT": if ($GLOBALS["limit"]=="UPPER") { return "%CONFIGTEXT Cron | Supplier | Notification | Header | Very Low%"; } elseif ($GLOBALS["limit"]=="LOWER") { return "%CONFIGTEXT Cron | Supplier | Notification | Header | Low%"; } break; case "CRONSTATESUBJECT": if ($GLOBALS["limit"]=="MONTHLY") { return "%CONFIGTEXT Cron | Supplier | Statement | Header | Monthly%"; } elseif ($GLOBALS["limit"]=="WEEKLY") { return "%CONFIGTEXT Cron | Supplier | Statement | Header | Weekly%"; } elseif ($GLOBALS["limit"]=="MANUAL") { return "%CONFIGTEXT Cron | Supplier | Statement | Header | Monthly%"; } break; case "CRONSTATEMESSAGE": if ($GLOBALS["limit"]=="MONTHLY") { return "%CONFIGTEXT Cron | Supplier | Statement | Message | Monthly%"; } elseif ($GLOBALS["limit"]=="WEEKLY") { return "%CONFIGTEXT Cron | Supplier | Statement | Message | Weekly%"; } elseif ($GLOBALS["limit"]=="MANUAL") { return "%CONFIGTEXT Cron | Supplier | Statement | Message | Monthly%"; } break; case "TRANSACTIONS": return $GLOBALS["transactions"]; break; case "LIMITMESSAGE": if ($GLOBALS["limit"]=="UPPER") { return "%CONFIGTEXT Cron | Supplier | Notification | Message | Low%"; } elseif ($GLOBALS["limit"]=="LOWER") { return "%CONFIGTEXT Cron | Supplier | Notification | Message | Very Low%"; } break; case "RESPONSESIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Consumer | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Consumer | Message | Signature (QM)%"; } break; case "PREMIUMSUBJECT": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Header (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Header (QM)%"; } break; case "PREMIUMINTRO": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Intro (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Intro (QM)%"; } break; case "PREMIUMSIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Signature (QM)%"; } break; case "FREEMIUMSUBJECT": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Header (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Header (QM)%"; } break; case "FREEMIUMINTRO": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Intro (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Intro (QM)%"; } break; case "FREEMIUMSIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Signature (QM)%"; } break; case "RESPONSEMESSAGES": $msg = ((join("",$GLOBALS["suppliers"]))?" %CONFIGTEXT Email | Consumer | Message | Intro Premium%":""). ((join("",$GLOBALS["supplierp"]))?" %CONFIGTEXT Email | Consumer | Message | Intro Freemium%":""); return $msg; break; // attribute flags case "ATTRIBUTE": return $GLOBALS["attribsarr"][$in[1]]; break; // consumer flags case "CONSUMERNAME": return $GLOBALS["consumerfullname"]; break; case "CONSUMEREMAIL": return $GLOBALS["consumeremail"]; break; case "CONSUMERCELL": return $GLOBALS["consumercell"]; case "CONSUMERCITY": return $GLOBALS["city_town"]; break; case "MESSAGE": return $GLOBALS["message"]; break; // supplier flags case "SUPPLIERNAME": return $GLOBALS["recipient"][1]; break; case "CONTACTFNAME": $fname = explode(" ",$GLOBALS["recipient"][0]); return $fname[0]; case "CONTACTNAME": return $GLOBALS["recipient"][0]; break; case "ACCMGR": return $GLOBALS["recipient"][11]; break; case "ACCMGRMAIL": return $GLOBALS["recipient"][12]; break; case "PAYGBALANCE": if ($GLOBALS["leadid"]) { return $GLOBALS["newbalance"]; } else { return $GLOBALS["balance"]; } case "INVOICEAMOUNT": return $GLOBALS["recipient"][13]; break; // lead flags case "LEADREFERENCE": if ($GLOBALS["prospecting"]) { return ($GLOBALS["leadid"] + 9001100)."-".append0($GLOBALS["ordinal"],2); } else { return ($GLOBALS["leadid"] + 11001000); } break; case "LEADCATEGORY": return $GLOBALS["cat"]; break; case "PAYGCOST": return $GLOBALS["servicecost"]; break; case "DUPLICATES": return $GLOBALS["append"]; break; // other flags case "PROSPECTMESSAGE": return $GLOBALS["pmsg"]; break; case "SUPPLIERLIST": return join("",$GLOBALS["suppliers"]); break; case "PROSPECTLIST": return join("",$GLOBALS["supplierp"]); break; case "SERVICENAME": return $GLOBALS["servicename"]; break; case "REGIONNAME": return $GLOBALS["regionname"]; break; // cronjob flags case "FROMDATE": return $GLOBALS["f"]; break; case "TODATE": return $GLOBALS["t"]; break; // configurable text flags case "CONFIGTEXT": $tsql = "SELECT * FROM 18_configurabletexts WHERE 18_configurabletexts.text_TextDescription = \"".$in[1]."\" AND 18_configurabletexts.bigint_ServiceID = ".$GLOBALS["service"].";"; $result = mysql_query_errors($tsql, $conn, __FILE__, __LINE__, false); if ($result) { if ($row = mysql_fetch_array($result)) { return $row["text_TextFullContent"]; } } } }}function populateflags($str) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match_all("/%([A-Z ]+[^%]*)%/",$str,$arr)) { $str = preg_replace_callback("/%([A-Z ]+[^%]*)%/",idflags,$str); } return $str;} this code has been updatedan example of a configurable text would be %CONFIGTEXT Cron | Supplier | Transaction Detail% the fulltext being equals to %LEADREFERENCE% | %CONSUMERNAME% | %CONSUMERCELL% | %CONSUMERCITY%%ATTRIBUTE vehicle_make_model% %ATTRIBUTE products_description% - %ATTRIBUTE year_model%%ATTRIBUTE Bakkie model% | %ATTRIBUTE canopy_req%%ATTRIBUTE Requirement% | %ATTRIBUTE canopy_style%%ATTRIBUTE Colour code%. these attribute and client and lead vales do not poppulate, despite them being defined on page level before calling the populateflags function.i know the function runs, only the variables defined within the gettransactions function are not called by the populateflags function. eg LEADREFERENCE and ATTRIBUTES values. printing the values from the page where they are built before calling populateflags, shows the variables and their values. but inside populateflags where it is called they do not populate.i am slightly debilitated mentally atm due to kidney problems, but some help here would be appreciated. ask away if you are not too clear on any subject.
  4. ahah! i didn't see that it was trying to split an array, after about 18 times of going through the function, i did not consciously register that it was trying to explode an array by ";;;". that was a remnant of the supplier strings, which are now saved as a multidimensional array. the supplier id's were therefore not retrieved, neither were the sql queries valid.finally, this issue is RESOLVED!
  5. AWESOME!!! :)here is the correct conversion of binary to lowercase utf8, to be used in that query: LOWER(CONVERT(3_serviceattributes.text_AttributeDescription USING utf8)) unfortunately this issue is not yet resolved. it finds the records, but does not drop the supplier elements from the page level arrays, $mailadds, $mailaddp and $mailaddl. why would this be? i now also use mb_strtolower too. also converting the passed strings as lower utf8... function dropattribs($status) { switch ($status) { case 2: $sarray = "mailaddp"; // prospecting status 2 break; case 4: $sarray = "mailaddl"; // listed status 4 break; default: $sarray = "mailadds"; // active status 0 } //echo mysql_real_escape_string("\r\ndropattribs(".$status.")"); for ($i = count($GLOBALS[$sarray]) - 1; $i > -1; $i--) { $sdata = $GLOBALS[$sarray][$i]; // get supplier data $arr = explode(";;;",$sdata); // explode supplier array $sid = $arr[6]; // get supplier id //echo mysql_escape_string("\r\n\t".$arr[1]); reset($GLOBALS["attribsarr"]); foreach ($GLOBALS["attribsarr"] as $key => $value) { // begin iterate form attributes // select current attribute key and value for supplier that is not supported $tsql = "SELECT 3_serviceattributes.text_AttributeDescription, 3_serviceattributes.text_AttributeValue FROM 9_supplierattributes LEFT JOIN ( 3_serviceattributes ) ON ( 9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID ) WHERE \"".mb_strtolower($key,"UTF-8")."\" LIKE CONCAT(\"%\",LOWER(CONVERT(3_serviceattributes.text_AttributeDescription USING utf8)),\"%\") AND \"".mb_strtolower($value,"UTF-8")."\" LIKE CONCAT(\"%\",LOWER(CONVERT(3_serviceattributes.text_AttributeValue USING utf8)),\"%\") AND 9_supplierattributes.bigint_ServiceID = ".$GLOBALS["service"]." AND 9_supplierattributes.bigint_RegionID = ".$GLOBALS["region"]." AND 9_supplierattributes.bigint_SupplierID = ".$sid.";"; //echo $tsql."\n"; $result = mysql_query_errors($tsql, $conn , __FILE__ , __LINE__ , false); if ($result) { // begin if mysql result if ($row = mysql_fetch_array($result)) { // begin while suppliers !attribs //echo $GLOBALS[$sarray][$i]."\n"; unset($GLOBALS[$sarray][$i]); mysql_free_result($result); break; // break out of attributes loop } // end while suppliers !attributes mysql_free_result($result); // free mysql result after looping } // end if mysql result } // end iterate form attributes } // end iterate suppliers array}
  6. ahahahaha! it seems one cannot convert a binary to lowercase using the lower function in mysql. therefore, i would need to add a string conversion.unfortunately i do not know of any utf-8 conversion types though for unicode strings. some more research on that later, but right now in pma i get an error with the following sql: SELECT 3_serviceattributes.text_AttributeDescription, 3_serviceattributes.text_AttributeValue FROM 9_supplierattributes LEFT JOIN ( 3_serviceattributes) ON ( 9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID) WHERE "vehicle_make_model" LIKE CONCAT("%",LOWER(CONVERT(3_serviceattributes.text_AttributeDescription latin1)),"%") AND "fiat - strada x space" LIKE CONCAT("%",LOWER(CONVERT(3_serviceattributes.text_AttributeValue latin1)),"%") AND 9_supplierattributes.bigint_ServiceID = 1 AND 9_supplierattributes.bigint_RegionID = 26; it needs to be looking for the right side in the left side - but i don't know what to do!
  7. okay. for namibia, there are 10 suppliers, half of them have the colour-coded, and 3 have a vehicle_make_model attribute matched against them (ie records exists in a table, the sql query below returns the values below that).Query: SELECT 3_serviceattributes.text_AttributeDescription, 3_serviceattributes.text_AttributeValue FROM 9_supplierattributes LEFT JOIN ( 3_serviceattributes) ON ( 9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID) WHERE 9_supplierattributes.bigint_ServiceID = 1 AND 9_supplierattributes.bigint_RegionID = 26; Result: [b]text_AttributeDescription[/b] [b]text_AttributeValue[/b]canopy_req pre-owned_colour_codedRequirement Pre-owned - Colour-codedcanopy_req pre-owned_colour_codedRequirement Pre-owned - Colour-codedRequirement Pre-owned - Colour-codedcanopy_req pre-owned_colour_codedRequirement Pre-owned - Colour-codedcanopy_req pre-owned_colour_codedRequirement Pre-owned - Colour-codedcanopy_req pre-owned_colour_codedproducts_description GWMproducts_description Fiatproducts_description GWMvehicle_make_model GMWvehicle_make_model GMWvehicle_make_model Fiat however, when i try execute the query below, i get nothing 0o SELECT 3_serviceattributes.text_AttributeDescription, 3_serviceattributes.text_AttributeValue FROM 9_supplierattributes LEFT JOIN ( 3_serviceattributes) ON ( 9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID) WHERE "vehicle_make_model" LIKE CONCAT("%",LOWER(3_serviceattributes.text_AttributeDescription), "%") AND "Fiat - Strada X Space" LIKE CONCAT("%",LOWER(3_serviceattributes.text_AttributeValue),"%") AND 9_supplierattributes.bigint_ServiceID = 1 AND 9_supplierattributes.bigint_RegionID = 26; now below is the function that is supposed to drop suppliers from the array of suppliers retrieved from the database by region. function dropattribs($status) { switch ($status) { case 2: $sarray = "mailaddp"; // prospecting status 2 break; case 4: $sarray = "mailaddl"; // listed status 4 break; default: $sarray = "mailadds"; // active status 0 } //echo mysql_real_escape_string("\r\ndropattribs(".$status.")"); for ($i = count($GLOBALS[$sarray]) - 1; $i > -1; $i--) { $sdata = $GLOBALS[$sarray][$i]; // get supplier data $arr = explode(";;;",$sdata); // explode supplier array $sid = $arr[6]; // get supplier id //echo mysql_escape_string("\r\n\t".$arr[1]); reset($GLOBALS["attribsarr"]); foreach ($GLOBALS["attribsarr"] as $key => $value) { // begin iterate form attributes // select current attribute key and value for supplier that is not // supported $tsql = "SELECT 3_serviceattributes.text_AttributeDescription, 3_serviceattributes.text_AttributeValue FROM 9_supplierattributes LEFT JOIN ( 3_serviceattributes ) ON ( 9_supplierattributes.bigint_AttributeID = 3_serviceattributes.bigint_AttributeID ) WHERE \"".strtolower($key)."\" LIKE CONCAT(\"%\", LOWER(3_serviceattributes. text_AttributeDescription),\"%\") AND \"".strtolower($value)."\" LIKE CONCAT(\"%\", LOWER(3_serviceattributes. text_AttributeValue),\"%\") AND 9_supplierattributes.bigint_ServiceID = ". $GLOBALS["service"]." AND 9_supplierattributes.bigint_RegionID = ". $GLOBALS["region"]." AND 9_supplierattributes.bigint_SupplierID = ".$sid.";"; //echo $tsql."\n"; $result = mysql_query_errors($tsql, $conn , __FILE__ , __LINE__ , false); if ($result) { // begin if mysql result if ($row = mysql_fetch_array($result)) { // begin if suppliers !attribs //echo $GLOBALS[$sarray][$i]."\n"; unset($GLOBALS[$sarray][$i]); // drop the supplier from the array mysql_free_result($result); // free mysql result after dropping break; // break out of attributes loop } // end while suppliers !attributes mysql_free_result($result); // free mysql result after looping } // end if mysql result } // end iterate form attributes } // end iterate suppliers array} 5 suppliers are returned (top level $mailaddp, $mailaddl and $mailadds are altogether populated with) 5 suppliers, but when i test the sql query string as it would execute, it returns nothing - what could be going on? i have run out of ideas and am at the end of the line here...if somebody could please assist, i would be greatly appreciative.
  8. well, i have decided to go with the tinyMCE javascript WYSIWYG editor, so the configurable texts will have html from the word go.this issue has now been resolved.
  9. i really don't think i am getting the assertions right here. somebody please help? the text below, would need to be found, the all carriage returns or new lines or both need to be replaced by the <br /> tag. i seem to have exhausted my resources, cant seem to figure out why for the life of me...i'm starting to think i didn't quite get the assertions, but the more i look at the results on that testing site the less sense it seems to make sense. please help someone :/the pcre at the moment is: /(?=<p[^>]*\>[^\r\n]*)\r\n|\r|\n(?=[^\r\n]*\<\/p\>)/the testing page seems to be telling me something, but i'll be damned if i know what :)the string it is running this PCRE against, is below. <p>If you would like to discontinue receiving similar leads in future (e.g. you dont manufacture or stock GMW - LWB canopies), please let us know so we can adjust the settings of our supplier matching & lead distribution engine accordingly.Regards,Client Success ManagerCanopyXchange TeamCopyright © 2010 CanopyXchange. All rights reserved.</p>
  10. it matches without errors according to that pcre testing page - inserts all the values, but it does not replace the line feed, carriage return, or both characters with the <br /> tag. why would this be? what is wrong with the regular expression in red? i've written the lookbehind assertion to match inside the enclosing paragraph (<p></p>), no matter what the attributes. this is where the <br /> tags should be used. function populateflags($str, $br) { // replace flags used in string (parse entire template contents' as string), // populating flags individually and sometimes recursively in callback // function. while (preg_match_all("/%([A-Z_0-9]+[^%]*)%/",$str,$arr)) { $str = preg_replace_callback("/%([A-Z_0-9]+[^%]*)%/",idflags, $str); } [color="#FF0000"]return ($br)?preg_replace("/(?=\<p[^\>]*\>) \r\n|\r|\n(?=\<\/p\>)/","<br />",$str):$str;[/color]}
  11. okay, the multiple replacements have been resolved - it does a while (preg_match_all... now. :)but i can't seem to wrap my head around the replacement of the new lines with the entities after the body tag but before the end body tag.the populated string that needs to be parsed, is at the bottom of the post.this is my current regular expression to try and match all carriage returns or line feeds or both after the body tag, and before the end body tag - to replace with <br />:Regular Expression Testerhowever, this does not seem to work - they match nothing. the pcre that i have compiled is from what i understood on that assertion page you gave me, using both lookahead and lookbehind but something is wrong here, it gets nothing! and i can't seem to see the forest for the trees any help, even if it is a nudge in the right direction, would be immensely appreciated. <!--ACTIVE QUOTE REQUEST MAILER TEMPLATEVersion 2.2.4--><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>[CX] GMW - LWB - lowline_luxury quotation for Pierre Du Toit (Ref.##11004557)</title><style type="text/css"><!--body { background: #ffffff url('http://www.intellisource.co.za/images/mail_bg.jpg') repeat-x fixed top left;}* { margin: 0px; padding: 0px;}h1, h2, div { font: 10pt Tahoma, Verdana, Arial, Helvetica, sans-serif;}p { margin: 12px;}a { font-weight: bold; text-decoration: none;}div.gw000 { /* container */ width: 90%; margin: 0 auto;}div.gw001 { /* header */ background: transparent url('http://www.intellisource.co.za/images/canopyxchange.gif') no-repeat scroll center left; height: 96px;}div.gw002 { /* tools */ text-align: right;}div.gw003 { /* data listing */ clear: both;}A.gw000:hover { color: #008000;}A.gw001:hover { color: #800000;}div.gw004 { /* footer */ padding: 12px; font-size: 9pt; text-align: right; border-top: #b0b0b0 solid 1px;}div.gw005 { /* lead cost / balance */ float: left; text-align: left;}//--></style></head><body><div class="gw000"> <div class="gw001"> </div> <div class="gw002"><a href="mailto:Jaap Venter<jaap.venter@ananzi.co.za>?subject=Blacklist request for pierre@greywacke.co.za" class="gw000">REPORT LEAD</a></div> <div class="gw003"> <p>Hello John Cox,Continental Canopies (Cape Town) matched this lead for GMW - LWB (new_colour_coded) in Western Cape - Cape Town region.This is a Category A lead costing 30. Continental Canopies (Cape Town) current credit balance is 870.</p></body></html>
  12. but unfortunately, still the loop is not replacing all the flags. function populateflags($str, $br) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match("/%([A-Z_0-9]+[\sA-Za-z\|\(\)]*)%/",$str)) { $str = preg_replace_callback("/%([A-Z_0-9]+[\sA-Za-z\|\(\)]*)%/",idflags,$str); } return ($br)? preg_replace( array( "/(?=\<body[^>]+\>)[^\>]\r\n(?=\<\/body\>)/", "/(?=\<body[^>]+\>)[^\>]\r(?=\<\/body\>)/", "/(?=\<body[^>]+\>)[^\>]\n(?=\<\/body\>)/" ), "<br />", $str ) : $str;} it replaces the %FLAGNAME optional | parameters | passed% on the first pass, but why would it cop out and leave the rest of the flags?. why would this be happening?
  13. okay, i've read through it and come up with the following: function populateflags($str, $br) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match("/%([A-Z_0-9]+[\sA-Za-z|]*)%/",$str)) { $str = preg_replace_callback("/%([A-Z_0-9]+[\sA-Za-z|]*)%/",idflags,$str); } return ($br)? preg_replace( array( "/(?=\<body[^>]+\>)\r\n(?=\<\/body\>)/", "/(?=\<body[^>]+\>)\r(?=\<\/body\>)/", "/(?=\<body[^>]+\>)\n(?=\<\/body\>)/" ), "<br />", $str ) : $str;} now just to upload and test, to make sure it works as expected.
  14. i have resolved the / issue, but it is because there is no need for defining the replacement text as a regular expression if it is not. right?one problem though. how would i be able to replace the \n, \r, or \r\n characters with <br /> but only within any xhtml tags. at the moment it is replacing them inside the stylesheet as well (basically wherever it finds these characters).a pcre of this complexity, is beyond my experience i am afraid...basically it needs to replace them only that should be visible - ie within the body tags...i need to get to bed now, got an appointment to make tomorrow morning.ps: i have updated the pcre to search more implicitly on the format of the tags: function populateflags($str, $br) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match("/%([A-Z]+[\sA-Za-z|]*)%/",$str)) { $str = preg_replace_callback("/%([A-Z]+[\sA-Za-z|]*)%/",idflags,$str); } return ($br)?preg_replace(array("/\r\n/","/\n/","/\r/"),"<br />",$str):$str;} pps: this is not an attempt at a replacement for html. it is merely to define the various data components in the mails sent, as comfigurable in the database.
  15. well they are parsed now, i just had to define the regular expressions more explicitly, as such: function populateflags($str, $br) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match("/%([^\r\n%]+[\bA-Za-z|]*)%/",$str)) { $str = preg_replace_callback("/%([^\r\n%]+[\bA-Za-z|]*)%/",idflags,$str); } return ($br)?preg_replace(array('/\r\n/','/\n/','/\r/'),'/<br />/',$str):$str;} also picked up one of the flags in a template with a typo ($ instead of % as opening character).but the consumer response e-mail, appears as follows: i gather somewhere the opening and closing regex / characters are not required. are ' required if you wish to use them and " otherwise? i could not locate these minor details, it is rather hard to google for these characters. i think i didn't make myself quite clear here. a flag is replaced with text that is usually retrieved from the database, if it is not a page variable. the replacement text is allowed to contain further tags, and not the tags themselves. the flags optionally have one parameter, to define which element in a specific array, or which configurable text it should load from the database. sometimes a flag is replaced by another, depending on a page level variable. i seem to have sorted most problems, just this one left that i did not see.
  16. hi there.i need to do a loop untill all the %TEXT all | optional | characters% type flags are replaced. the flag only has one rule, it cannot have a carriage return or a line break in it, nor a percentage symbol - but is defined between two percentage symbols.this is the code i have at the moment that doesn't seem to be looping till all the tags are replaced (this has to be done, because flag replacement texts should be allowed to have other flags within them). the line breaks below have merely been added so that the forum thread would keep its shape and not expand over the width of the screen. function getflags($input) { foreach ($input as $in) { // use to split by first space if found, to get flag values $in = preg_split("/ /",$in,2); // build and return the values to replace with switch ($in[0]) { // mail configurable flags case "LOGO": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT File | Logo | CanopyXchange%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"] && $GLOBALS["response"]) { return "%CONFIGTEXT File | Logo | QuoteMe | Canopies%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT File | Logo | CanopyXchange%"; } break; case "RESPONSESIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Consumer | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Consumer | Message | Signature (QM)%"; } break; case "PREMIUMSUBJECT": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Header (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Header (QM)%"; } break; case "PREMIUMINTRO": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Intro (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Intro (QM)%"; } break; case "PREMIUMSIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Premium | Message | Signature (QM)%"; } break; case "FREEMIUMSUBJECT": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Header (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Header (QM)%"; } break; case "FREEMIUMINTRO": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Intro (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Intro (QM)%"; } break; case "FREEMIUMSIGNATURE": if ($GLOBALS["attribsarr"]["products_description"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Signature (CX)%"; } elseif ($GLOBALS["attribsarr"]["vehicle_make_model"]) { return "%CONFIGTEXT Email | Supplier | Freemium | Message | Signature (QM)%"; } break; // attribute flags case "ATTRIBUTE": return $GLOBALS["attribsarr"][$in[1]]; break; // consumer flags case "CONSUMERNAME": return $GLOBALS["consumerfullname"]; break; case "CONSUMEREMAIL": return $GLOBALS["consumeremail"]; break; case "CONSUMERCELL": return $GLOBALS["consumercell"]; case "CONSUMERCITY": return $GLOBALS["city_town"]; break; // supplier flags case "SUPPLIERNAME": return $GLOBALS["recipient"][1]; break; case "CONTACTNAME": return $GLOBALS["recipient"][0]; break; case "ACCMGR": return $GLOBALS["recipient"][11]; break; case "ACCMGRMAIL": return $GLOBALS["recipient"][12]; break; case "PAYGBALANCE": return $GLOBALS["newbalance"]; case "INVOICEAMOUNT": return $GLOBALS["recipient"][13]; break; // lead flags case "LEADREFERENCE": return "#".($GLOBALS["leadid"] + 11001000); break; case "LEADCATEGORY": return $GLOBALS["cat"]; break; case "PAYGCOST": return $GLOBALS["servicecost"]; break; case "DUPLICATES": return $GLOBALS["append"]; break; // other flags case "PROSPECTMESSAGE": return $GLOBALS["pmsg"]; break; case "SUPPLIERLIST": return join("",$GLOBALS["suppliers"]); break; case "PROSPECTLIST": return join("",$GLOBALS["supplierp"]); break; case "SERVICENAME": return $GLOBALS["servicename"]; break; case "REGIONNAME": return $GLOBALS["regionname"]; break; // cronjob flags case "FROMDATE": return $GLOBALS["f"]; break; case "TODATE": return $GLOBALS["t"]; break; // configurable text flags case "CONFIGTEXT": $sql = "SELECT * FROM 18_configurabletexts WHERE 18_configurabletexts.text_TextDescription = \"". $in[1]."\" AND 18_configurabletexts. bigint_ServiceID = ".$GLOBALS["service"].";"; $result = mysql_query_errors($sql, $conn, __FILE__, __LINE__, false); if ($result) { if ($row = mysql_fetch_array($result)) { return ($br)?preg_replace(array('/\r\n/','/\n/', '/\r/'),'/<br />/', $row["text_TextFullContent"]): $row["text_TextFullContent"]; } } } }} it is using global variables to get the variables set and made available where the function is called. there is something wrong with the while loop - as it only does that once. what would be a better way to make sure there are no more tags in the passed string?also the $GLOBALS variables don't seem to be retreiving the values that are available where the populateflags function is called.from the main page. the functions are defined inside of an include.
  17. okay,i have now started implementing the function into the global database and utf8 include over the site. the functions look as follows: function getflags($input) { // EXAMPLE TO POPULATE WITH REAL FLAGS foreach ($input as $in) { // use to split by first space if found, to get flag values $in = preg_split("/ /",$in,2); // build and return the values to replace with switch ($in[0]) { // attribute flags case "ATTRIBUTE": return $GLOBALS["attribsarr"][$in[1]]; break; // consumer flags case "CONSUMERNAME": return $GLOBALS["consumerfullname"]; break; case "CONSUMEREMAIL": return $GLOBALS["consumeremail"]; break; case "CONSUMERCELL": return $GLOBALS["consumercell"]; case "CONSUMERCITY": return $GLOBALS["city_town"]; break; // supplier flags case "SUPPLIERNAME": return $GLOBALS["recipient"][1]; break; case "CONTACTNAME": return $GLOBALS["recipient"][0]; break; case "ACCMGR": return $GLOBALS["recipient"][11]; break; case "ACCMGRMAIL": return $GLOBALS["recipient"][12]; break; case "PAYGBALANCE": return $GLOBALS["recipient"][3]; case "INVOICEAMOUNT": return $GLOBALS["recipient"][13]; break; // lead flags case "LEADREFERENCE": return "#".($GLOBALS["leadid"] + 11001000); break; case "LEADCATEGORY": return $GLOBALS["cat"]; break; case "PAYGCOST": return $GLOBALS["servicecost"]; break; case "DUPLICATES": return $GLOBALS["append"]; break; // other flags case "SERVICENAME": return $GLOBALS["servicename"]; break; case "REGIONNAME": return $GLOBALS["regionname"]; break; // cronjob flags case "FROMDATE": return $GLOBALS["f"]; break; case "TODATE": return $GLOBALS["t"]; break; // configurable text flags case "CONFIGTEXT": $sql = "SELECT * FROM 18_configurabletexts WHERE 18_configurabletexts.text_TextDescription = \"".$in[1]."\" AND 18_configurabletexts.bigint_ServiceID = ".$GLOBALS["service"].";"; $result = mysql_query_errors($sql, $conn, __FILE__, __LINE__, false); if ($result) { if ($row = mysql_fetch_array($result)) { return $row["text_TextFullContent"]; } } } }}function populateflags($str) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. while (preg_match("/%([^%]+)%/",$str)) { $str = preg_replace_callback("/%([^%]+)%/",getflags,$str); } return $str;} now it's to implement the populateflags("%CONFIGTEXT Configurable|Text|Name%") function all over the site, where the attribute specifies the configurable text to call from the database, this is unique to each service. i believe this "ïssue" has now been resolved - yay for the preg functions!
  18. cool ^^this seems to work: <?phpif (!headers_sent()) header("Content-Type: text/plain",true);function getflags($input) { foreach ($input as $in) { // use to split by first space if found, to get flag values $in = preg_split("/ /",$in,2); // build and return the values to replace with switch ($in[0]) { case "FIRST": return "Hi ".$in[1]; break; case "SECOND": return "Welcome"; break; case "THIRD": return "To ".$in[1]; break; case "FOURTH": return "Hope you enjoy!"; break; case "FIFTH": return "The QuoteMe.ZA.NET Team."; break; } }}function populateflags($str) { // replace flags used in string (parse entire template contents' as string), populating flags individually and sometimes recursively in callback function. return preg_replace_callback("/%([^%]+)%/",getflags,$str);}$string = "%FIRST Pierre du Toit%,%SECOND% - %THIRD a complete nexus regarding consumer quoting on multiple products.%%FOURTH%Love, Light and Life - Namaste.%FIFTH%";echo populateflags($string);?> writing the following: Hi Pierre du Toit,Welcome - To a complete nexus regarding consumer quoting on multiple products.Hope you enjoy!Love, Light and Life - Namaste.The QuoteMe.ZA.NET Team. as the first successful test :)now i need to populate from the flag values set in the mysql database, in the callback function 0o.
  19. i'm thinking now, that perhaps i should rather be using preg_replace_callback instead. got some research to do in the meantime... :)edit:it seems i would have to use the function with the switch functionality within the callback function. but i cannot seem to derive how exactly, this would be done.
  20. hi there,i am currently developing a function to be inserted into the header to replace flags in templates (which are defined by %FLAGNAME optional parameter% anywhere, any amount of times, in any string passed. sofar, testing - i have the following PHP: <?phpif (!headers_sent()) header("Content-Type: text/plain",true);function setflags($arr) { $ret = array(); foreach ($arr as $r) { $r = str_replace("%","",$r); // use to split by first space if found, to get flag values $r = preg_split("/ /",$r,2); $a = $r[0]; $b = $r[1]; switch ($r) { case "FIRST": array_push($ret,"Hi ".$; break; case "SECOND": array_push($ret,"Welcome"); break; case "THIRD": array_push($ret,"To ".$; break; case "FOURTH": array_push($ret,"Hope you enjoy!"); break; case "FIFTH": array_push($ret,"The QuoteMe.ZA.NET Team."); break; } } return $ret;}function populateflags($str) { // get array of flags used in string to parse (parse entire template contents' as string) and loop through array preg_match("/%[^%]+%/",$str,$matches); // get the values to populate with $replace = setflags($matches[0]); // populate flags used by doing a switch flagname procedure, recursively calling populateflags(flag) before returning return str_replace($matches[0],$replace,$str);}$string = "%FIRST Pierre du Toit%,%SECOND% - %THIRD a complete nexus regarding consumer quoting on multiple products.%%FOURTH%Love, Light and Life - Namaste.%FIFTH%";echo populateflags($string);?> now atm this returns the following page... <br /><b>Warning</b>: Invalid argument supplied for foreach() in <b>/home/dwtphovu/public_html/intellisource.co.za/test_8347379386/test.php</b> on line <b>5</b><br />Array,%SECOND% - %THIRD a complete nexus regarding consumer quoting on multiple products.%%FOURTH%Love, Light and Life - Namaste.%FIFTH% from what i have read on PHP.net and what i understand, matches[0] should contain an array of the matches retrieved by the pcre "/%[^%]+%/".ie. $FIRST blah blah%. %SECOND%, and so forth. i am battling to focus here, could someone please assist?basically how would i retrieve an array of matches only, to parse in the function setflags.i have looked into preg_match_all aswell, but the arrays returned are "all over the place" from what i could see. ie multidimentional, with no particular order. i need a single dimentional array of items matched.
  21. ahh... nevermind, it seemed thek checking of wether the element was an array or a string was not working. what worked better was to check if an asterisk (*) existed in the element for the titles, and if the second element of the subarray existed for the flags. :)this issue is now RESOLVED ^^
  22. hi there, i have a little function that receives an array, with the elements that are to be headings, starting with a *.here is what the two dimentional array looks like (as retrieved from xml). "* Option Title", { "option text 1", "option value 1" }, { "option text 2", "option value 2" }, { "option text 3", "option value 3" } and here is the function: function addflags(arr) { var sel = document.getElementById("menu_flags"); for (var i = 0; i < arr.length; i++) { var opt = document.createElement('option'); alert(arr[i]); if (arr[i][0]) { opt.text = strRepeat("\u00a0",5) + arr[i][0]; opt.value = arr[i][1]; try { sel.add(opt, null); // standards compliant; doesn't work in IE } catch(ex) { sel.add(opt); // IE only } } else { opt.text = arr[i]; opt.value = 0; try { sel.add(opt, null); // standards compliant; doesn't work in IE } catch(ex) { sel.add(opt); // IE only } } }} now there is one dropdown, where if you select a flag - it inserts it into a textarea. there are different types of flags, all seperated by the values with 0. if one of these is selected, it does nothing. but why are the rest of the strings as shown by the alert, not in the options? i think the * is causing some sort of regular expression to be performed... truncating i guess i can always insert the unicode value for it - or somehow make sure that regular expressions are not performed on the string arr.oh yeah, the function is called for every flags tag that is found in the xml, here is the current xml piece where the data is retrieved from: <flags name="* Insert Service Attribute"> <flag name="Budget" value="%ATTRIBUTE Budget%" /> <flag name="Fitment" value="%ATTRIBUTE Fitment%" /> <flag name="Requirement" value="%ATTRIBUTE Requirement%" /> <flag name="budget" value="%ATTRIBUTE budget%" /> <flag name="canopy_req" value="%ATTRIBUTE canopy_req%" /> <flag name="canopy_style" value="%ATTRIBUTE canopy_style%" /> <flag name="fitment" value="%ATTRIBUTE fitment%" /> <flag name="products_description" value="%ATTRIBUTE products_description%" /> <flag name="vehicle_make_model" value="%ATTRIBUTE vehicle_make_model%" /></flags><flags name="* Insert Configurable Text"></flags><flags name="* Insert Consumer Variables"> <flag name="Consumer Name" value="%CONSUMERNAME%" /> <flag name="Consumer E-mail" value="%CONSUMEREMAIL%" /> <flag name="Consumer City/Town" value="%CONSUMERCITY%" /></flags><flags name="* Insert Environmental Variables"> <flag name="Service Description" value="%SERVICENAME%" /> <flag name="Region Description" value="%REGIONNAME%" /> <flag name="Supplier Name" value="%SUPPLIERNAME%" /></flags> how can i insert a string retrieved from xml that is starting with a * as an option text? searching the web has failed to get me an answer closer to what i know sofar. any help with this would be greatly appreciated
  23. ah nm it seems we cannot declare a result AS, within a CASE statement.here is the working query: SELECT CASE 0 WHEN 0 THEN # Consumer Name SUBSTRING_INDEX( text_Consumer, ";", 1 ) WHEN 1 THEN # Product Description IF( LOCATE( "products_description", text_LeadAttributes ) = 0 , CONCAT_WS( " - ", SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 6 ), ";", -1 ), " = ", -1 ), SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 2 ), ";", -1 ), " = ", -1 ) ) , SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 1 ), " = ", -1 ) ) END AS text_Result FROM 6_serviceleads WHERE text_Consumer LIKE "%@%" ORDER BY timestamp_LeadCreated DESC LIMIT 1; this issue is now RESOLVED
  24. hi there,here is my SELECT CASE statement, compiled from the select statement in a previous topic. SELECT CASE 0 WHEN 0 THEN # Consumer Name SUBSTRING_INDEX( text_Consumer, ";", 1 ) AS text_ConsumerName WHEN 1 THEN # Product Description IF( LOCATE( "products_description", text_LeadAttributes ) = 0 , CONCAT_WS( " - ", SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 6 ), ";", -1 ), " = ", -1 ), SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 2 ), ";", -1 ), " = ", -1 ) ) , SUBSTRING_INDEX( SUBSTRING_INDEX( text_LeadAttributes, "<br />", 1 ), " = ", -1 ) ) AS text_ProductDescription END CASE FROM 6_serviceleads WHERE text_Consumer LIKE "%@%" ORDER BY timestamp_LeadCreated DESC LIMIT 1; i attempted following the official documentation, but i receive the following error when testing in phpMyAdmin. unfortunately this is the first time i've tried using this, but i keep getting the following error: i will keep trying and post back here if i get to know the resolution to this issue.
×
×
  • Create New...