Jump to content

Greywacke

Members
  • Posts

    510
  • Joined

  • Last visited

Everything posted by Greywacke

  1. updated and resolved this - the records are bring returned now - thanks
  2. ah - the answer was: as i was using a compiled regular expression, it was better to pass the modifiers in the second argument of the object document.getElementById("table_results").tHead.children[1].title = title.replace(re = new RegExp(quote("`Prim_Att_Val` = "" + where["Prim_Att_Val"]+"""),"g"),((val!="")?"`Prim_Att_Val` = ""+val+""":val)); hope this helps many others with similar endeavours!
  3. hi everyone, i am currently having an issue with the following excerpt of javascript - particularly the "regular expressions not wanting to replace". case 19: // set where Prim_Att_Val document.getElementById("ajaxbg").style.visibility = "visible"; var val = document.form_services.where_primattval.options[form_services.where_primattval.selectedIndex].value; // new constraint var title = document.getElementById("table_results").tHead.children[1].title; // current constraints //salert("re = ^/[ AND]*" + quote(" `Prim_Att_Val` = ""+where["Prim_Att_Val"]+""") + "/g"); //salert("val = "+val); //salert("title = "+title); if (whereo["Prim_Att_Val"]==-1) { document.getElementById("table_results").tHead.children[1].title += ((wherecnt>0)?" AND":"")+" `Prim_Att_Val` = ""+val+"""; where["Prim_Att_Val"] = val; whereo["Prim_Att_Val"] = wherecnt; wherecnt++; } else { //salert(whereo["Prim_Att_Val"]); //salert(val); document.getElementById("table_results").tHead.children[1].title = title.replace(re = new RegExp("/"+quote("`Prim_Att_Val` = "" + where["Prim_Att_Val"]+""")+"/g"),(val!="")?"`Prim_Att_Val` = ""+val+""":val); if (!where["Prim_Att_Val"]) whereo["Prim_Att_Val"] = -1; salert(title); salert(re.toString()); salert("`Prim_Att_Val` = ""+val+"""); salert(whereo["Prim_Att_Val"]); where["Prim_Att_Val"] = val; } document.getElementById("ajaxbg").style.visibility = "hidden"; break; salert is a custom function which prints to a div at the top of the screen. same results could be achieved by calling alert, it's just neater with all the data being kept and new lines being written. what is the issue with line 680 - it is not replacing the values as expected? in case 13, it does however - this does not have the string inserted into the regular expression to search for... anybody, please help!
  4. ok the function has been updated as follows: function popwhereclauses() { //global $conn; // set the mysql connection string details $server = "*****"; $username = "*****"; $password = "*****"; $query = array( "SELECT DISTINCT UPPER(SC.text_ServiceDescription) AS `service` FROM 2_servicescatalogue SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) ORDER BY service ASC;", "SELECT DISTINCT IF(R.bigint_ParentRegionID =0, R.text_RegionDescription, R1.text_RegionDescription) AS `country` FROM 4_servicesuppliers SS INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) ORDER BY country ASC;", "SELECT DISTINCT UPPER(IF(R.bigint_ParentRegionID !=0, R.text_RegionDescription, R0.text_RegionDescription)) AS `region` FROM 4_servicesuppliers SS INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) ORDER BY region ASC;", "SELECT DISTINCT UPPER(S.text_SupplierName) AS `supplier` FROM 4_servicesuppliers SS INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) ORDER BY supplier ASC;", "SELECT DISTINCT UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END) AS `status` FROM 4_servicesuppliers SS INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) ORDER BY status ASC;", "SELECT DISTINCT UPPER(IF(SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue)) AS `prim_att_val` FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) ORDER BY prim_att_val ASC;" ); $conn = array( mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true) ); $result = array(6); echo " <whereclauses "; $groups = array( 'service', 'country', 'region', 'supplier', 'status', 'prim_att_val' ); for ($i = 0; $i < count($groups); $i++) { mysql_select_db("performatix", $conn[$i]); $result[$i] = mysql_query_errors($query[$i], $conn[$i], __FILE__, __LINE__, true); if ($result[$i]) { $field = $groups[$i]; echo $field."=""; $counter = 0; while ($row = mysql_fetch_row($result[$i])) { $counter++; echo $row[0].($counter == mysql_num_rows($result))?"":","; } echo "" "; mysql_free_result($result[$i]); } mysql_close($conn[$i]); } echo "/>rn";} resulting in: the results, however still do not want to print dynamically... how could i get this right?
  5. ok, the function has been modified to be as follows: function popwhereclauses() { global $conn; // set the mysql connection string details $server = "*****"; $username = "*****"; $password = "*****"; $query = array( "SELECT UPPER(SC.text_ServiceDescription) AS service FROM 2_servicescatalogue SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) ORDER BY service ASC;", "SELECT DISTINCT IF(R.bigint_ParentRegionID =0, R.text_RegionDescription, R1.text_RegionDescription) AS country FROM 4_servicesuppliers SS INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) ORDER BY country ASC;", "SELECT DISTINCT UPPER(IF(R.bigint_ParentRegionID !=0, R.text_RegionDescription, R0.text_RegionDescription)) AS region FROM 4_servicesuppliers SS INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) ORDER BY region ASC;", "SELECT DISTINCT UPPER(S.text_SupplierName) AS supplier FROM 4_servicesuppliers SS INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) ORDER BY supplier ASC;", "SELECT DISTINCT UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END) AS status FROM 4_servicesuppliers SS INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) ORDER BY status ASC;", "SELECT DISTINCT UPPER(IF(SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue)) AS prim_att_val FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) ORDER BY prim_att_val ASC;" ); $conn = array( mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true), mysql_connect($server, $username, $password, true) ); $result = array(6); echo " <whereclauses "; $groups = array( 'service', 'country', 'region', 'supplier', 'status', 'prim_att_val' ); for ($i = 0; $i < count($groups); $i++) { $result[$i] = mysql_query_errors($query[$i], $conn[$i], __FILE__, __LINE__, true); if ($result[$i]) { $field = $groups[$i]; echo $field."=""; $counter = 0; while ($row = mysql_fetch_assoc($result[$i])) { $counter++; echo $row[$field].($counter == mysql_num_rows($result))?"":","; } echo "" "; } mysql_free_result($result[$i]); } echo "/>rn";} the results i get for this function, in the xml output however - are as follows: why would this be? where are service, country, region, supplier, status & prim_att_val?? somebody please help asap!!!
  6. this query has been modified in order to use 4_servicesuppliers as the central table... in my effort to try and get the results i need, i have split the query into 6 - each taking around 30 seconds to complete - i understand it is due to the multiple, complex joins. below are the queries: $query = array( "SELECT GROUP_CONCAT(DISTINCT UPPER(SC.text_ServiceDescription) SEPARATOR ',') AS service FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(SC.text_ServiceDescription);", "SELECT GROUP_CONCAT(DISTINCT IF( R.bigint_ParentRegionID=0, R.text_RegionDescription, R1.text_RegionDescription ) SEPARATOR ',') AS country FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(IF( R.bigint_ParentRegionID=0, R.text_RegionDescription, R1.text_RegionDescription ));", "SELECT GROUP_CONCAT(DISTINCT UPPER(IF( R.bigint_ParentRegionID!=0, R.text_RegionDescription, R0.text_RegionDescription )) SEPARATOR ',') AS region FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(IF( R.bigint_ParentRegionID!=0, R.text_RegionDescription, R0.text_RegionDescription ));", "SELECT GROUP_CONCAT(DISTINCT UPPER(S.text_SupplierName)) AS supplier FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(S.text_SupplierName);", "SELECT GROUP_CONCAT(DISTINCT UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END) SEPARATOR ',') AS status FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(CASE S.smallint_SupplierStatus WHEN 0 THEN 'Premium' WHEN 1 THEN 'Paused' WHEN 2 THEN 'Trial' WHEN 3 THEN 'Inactive' WHEN 4 THEN 'Freemium' END);", "SELECT GROUP_CONCAT(DISTINCT UPPER(IF ( SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue )) SEPARATOR ',') AS prim_att_val FROM 4_servicesuppliers SS INNER JOIN 2_servicescatalogue SC ON (SS.bigint_ServiceID = SC.bigint_ServiceID) INNER JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID) INNER JOIN 5_suppliers S ON (SS.bigint_SupplierID = S.bigint_SupplierID) LEFT JOIN 3_serviceattributes SA0 ON (SC.bigint_PrimaryAttributeKey = SA0.bigint_AttributeID AND SC.bigint_ServiceID = SA0.bigint_AttributeServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SA0.text_AttributeDescription = SA1.text_AttributeDescription AND SC.bigint_ServiceID = SA1.bigint_AttributeServiceID) LEFT JOIN 1_regions R0 ON (R.bigint_ParentRegionID = 0 AND R.bigint_RegionID = R0.bigint_ParentRegionID) LEFT JOIN 1_regions R1 ON (R.bigint_ParentRegionID != 0 AND R.bigint_ParentRegionID = R1.bigint_RegionID) LEFT JOIN 9_supplierattributes SA3 ON (S.bigint_SupplierID = SA3.bigint_SupplierID AND SC.bigint_ServiceID = SA3.bigint_ServiceID AND ((R.bigint_RegionID = SA3.bigint_RegionID OR R.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R0.bigint_RegionID = SA3.bigint_RegionID OR R0.bigint_ParentRegionID = SA3.bigint_RegionID) OR (R1.bigint_RegionID = SA3.bigint_RegionID OR R1.bigint_ParentRegionID = SA3.bigint_RegionID)) AND SA1.bigint_AttributeID = SA3.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA3.bigint_AttributeID = SA2.bigint_AttributeID AND SC.bigint_ServiceID = SA2.bigint_AttributeServiceID) WHERE SA2.text_AttributeValue IS NULL GROUP BY UPPER(IF ( SC.bigint_PrimaryAttributeKey = 0 OR SA1.text_AttributeValue = NULL, 'DEFAULT', SA1.text_AttributeValue ));"); however, the GROUP_CONCAT does not concatenate these fields into one query... the results i am getting, in 6 individual recordsets is from 5 records, to 223 records as follows per individual query executed in pma: we no longer require counting these records, but instead retrieving them in a dynamic selection, which is the combination of the above queries - to be updated and modified via ajax calls. why can't i get these to display one record, concatenating the results? :/ i have a feeling it is similar to the previous issue... i have had a look into pcntl multithreading to execute these all at once but have just realised, that if i could do them in one query, retrieving these values concatenated - that the script could continue to work in linear fashion... only how would i do that - not being as familiar to MySQL GROUP_CONCAT's, GROUP_BY's and JOIN's as i would like to be? what say you, justsomeguy - or anybody else about a single query vs. splitting the processing and keeping it to seperate queries as above? sincerely - Pierre du Toit.
  7. sorry justsomeguy - do not understand/see your perspective... the subquery was edited as follows: SELECT COUNT(DISTINCT S.bigint_SupplierID)FROM 5_suppliers SLEFT JOIN 9_supplierattributes SA0 ON (S.bigint_SupplierID = SA0.bigint_SupplierID) LEFT JOIN 4_servicesuppliers SS ON (SA0.bigint_ServiceID = SS.bigint_ServiceID AND S.bigint_SupplierID = SS.bigint_SupplierID) LEFT JOIN 2_servicescatalogue SC ON (SA0.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SC.bigint_PrimaryAttributeKey = SA1.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA0.bigint_AttributeID = SA2.bigint_AttributeID) WHERE SA0.bigint_ServiceID = 1 AND SS.bigint_RegionID = 1 AND SA1.text_AttributeDescription NOT IN (SELECT DISTINCT SA2.text_AttributeDescription)GROUP BY S.bigint_SupplierID; however, when included within the main query as follows - it returns 0 in PMA! yet we have around 2-4 suppliers per region in our database (i just have to replace the id 1 for region with the region id i need to test - there are about 17 in total)... SELECT SW1.bigint_RegionID, R.text_RegionDescription, SW1.smallint_SuppliersWanted, SW1.bigint_AttributeValue, COUNT( SELECT COUNT(DISTINCT SA1.text_AttributeDescription) FROM 5_suppliers S LEFT JOIN 9_supplierattributes SA0 ON (S.bigint_SupplierID = SA0.bigint_SupplierID) LEFT JOIN 4_servicesuppliers SS ON (SA0.bigint_ServiceID = SS.bigint_ServiceID AND S.bigint_SupplierID = SS.bigint_SupplierID) LEFT JOIN 2_servicescatalogue SC ON (SA0.bigint_ServiceID = SC.bigint_ServiceID) LEFT JOIN 3_serviceattributes SA1 ON (SC.bigint_PrimaryAttributeKey = SA1.bigint_AttributeID) LEFT JOIN 3_serviceattributes SA2 ON (SA0.bigint_AttributeID = SA2.bigint_AttributeID) WHERE SA0.bigint_ServiceID = 1 AND SS.bigint_RegionID = 1 AND SA1.text_AttributeDescription NOT IN (SELECT DISTINCT SA2.text_AttributeDescription) GROUP BY S.bigint_SupplierID ) AS bigint_SuppliersActualAmount, C.tinyint_GetMethod FROM 11_supplierswanted SW1 JOIN 1_regions R ON SW1.bigint_RegionID = R.bigint_RegionID JOIN 32_webformconfigs C ON C.bigint_FormService = SW1.bigint_ServiceID AND C.tinyint_FormDefault WHERE SW1.bigint_ServiceID = 1 ORDER BY R.text_RegionDescription ASC; however - when i include this in the php document and run it, i get the following error from the site console: what does this mean? what needs to change?? and where??? once again, i battle to see the forest for the flowers or even trees within it!
  8. ok, have updated query as follows (the subqueries need to be within brackets within the count function): SELECT ( SELECT COUNT( ( SELECT SL.timestamp_LeadCreated FROM 27_leadssent LS LEFT JOIN 25_serviceleads SL ON (SL.bigint_LeadID = LS.bigint_LeadID) WHERE LS.bigint_SupplierID = 192 AND SL.timestamp_LeadCreated > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) ) + 1 AS `l_Cnt` ) / ( SELECT COUNT( ( SELECT LM.timestamp_LastUpdated FROM 43_leadsmatched LM WHERE CONCAT('%,',192,',%') LIKE CONCAT(',',LM.text_PremiumSupplierIDs,',') AND LM.timestamp_LastUpdated > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) ) AS `t_Cnt`) * 100 AS `lead_share`; but it gives following error instead of counting the records from the subqueries. somebody please help!
  9. hi again... am having an issue with the following error - the query follows the error, and the table layouts are attached if you wish to recreate this issue to try and assist discovering what the heck the problem is here... SELECT ( ( COUNT( SELECT * FROM 27_leadssent LS LEFT JOIN 25_serviceleads SL ON (SL.bigint_LeadID = LS.bigint_LeadID) WHERE SL.bigint_SupplierID = 192 AND SL.timestamp_LeadCreated > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) + 1 ) / ( COUNT( SELECT * FROM 43_leadsmatched LM WHERE CONCAT(',',192,',') LIKE CONCAT(',',LM.text_PremiumSupplierIDs,',') AND LM.timestamp_LastUpdated > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) * 100 )) AS `lead_share` there is also a version of this query for freemium suppliers (which uses 10_serviceprospects and 13_prospectleadsent instead of 25_serviceleads and 27_leadssent), all 5 tables' creation sql was exported in: performatix.sql.zip from phpMyAdmin on our MySQL 5.5 server. what need to change so that the above sql code would work and not throw the error mentioned above??? afraid i do not see the forest for the trees here once again...
  10. afraid i need to try a different approach to resolving this, i'm going to be re-writing the queries from scratch today - according to my client not all of the records tally up... especially when a different working service is used! will reply back here with the queries once working 100%. after discussing with a friend (unfortunately he's terribly busy), i realised that in the second and third selects - that the JOIN types need to change as the perspective changes. will update once this work is complete.
  11. hi there, the following query was developed and works as it should. SELECT ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC; # Order by Total, then Supplier Name took about a day to develop this inbetween other work. it returns the correct data. the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took about an hour inbetween other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took approximately another hour between other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv once again - the data does not corroborate with the first csv, the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied? the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.
  12. awesome sight, Foxy Mod at catching the . instead of the : on line 69! as for the others (lines 77 and 197) i've tested with only the php eol semicolon - the error remains where it is, and without any semicolon - the error jumps to the following line as well as with only the query end - the error jumps to following line... - on both queries. also noticed $wsql was still being used - here $rsql is supposed to be used. but the errors on lines 77 and 197 remain... edit: thanks every body for the assistance - i have located the errors a week or two ago and resolved the issue
  13. hi all, apparently i have errors on these lines within the function below, according to Dreamweaver CS6 (version 12.0 build 5861) - but unable to locate issues with Notepad++ (6.4.5). when this function is processed - it generates one of those forsaken untraceable http 500 issues raised by an invalid mysql query! have looked at error logs - but according to DW CS6 the query does not compile completely. <?phpfunction getleads($tcategory, $table, $order, $country="",$state="",$county="",$zip="",$suppliers="",$search="") { global $conn; $wsql = array(); if ($country!="") { $f_country = explode(",",$country); for ($i = 0; $i < count($f_country); $i++) { $r_country[$i] = "SS.bigint_RegionID = ".$f_country[$i]; //array_push($f_country, "S.bigint_CountryID = ".$f_country[$i]); } //array_push($wsql,"(".implode(" OR ",$f_country).")"); array_push($rsql,"(".implode(" OR ",$r_country).")"); } if ($state!="") { $f_state = explode(",",$state); for ($i = 0; $i < count($f_state); $i++) {; $r_state[$i] = "SS.bigint_RegionID = ".$f_state[$i]; //$f_state[$i] = "S.bigint_StateID = ".$f_state[$i]; } //array_push($wsql,"(".implode(" OR ",$f_state).")"); array_push($rsql,"(".implode(" OR ",$r_state).")"); } if ($county!="") { $f_county = explode(",",$county); for ($i = 0; $i < count($f_county); $i++) { $r_county[$i] = "SS.bigint_RegionID = ".$f_county[$i]; //$f_county[$i] = "S.bigint_CountyID = ".$f_county[$i]; } //array_push($wsql,"(".implode(" OR ",$f_county).")"); array_push($rsql,"(".implode(" OR ",$r_county).")"); } if ($zip!="") { $f_zip = explode(",",$zip); for ($i = 0; $i < count($f_zip); $i++) { $r_zip[$i] = "SS.bigint_RegionID = ".$f_zip[$i]; //$f_zip[$i] = "S.bigint_ZipCodeID = ".$f_zip[$i]; } //array_push($wsql,"(".implode(" OR ",$f_zip).")"); array_push($rsql,"(".implode(" OR ",$r_zip).")"); } if ($suppliers!="") { $f_suppliers = "S.bigint_SupplierID = ".$suppliers; array_push($wsql,$f_suppliers); } array_push($wsql,"SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1]."""); if ($search!="") { $f_search = "(". "C.text_ConsumerName LIKE "%".$search."%" ". "OR C.text_ConsumerPhone LIKE "%".$search."%" ". "OR C.`text_ConsumerE-Mail` LIKE "%".$search."%" ". "OR CONCAT(SL.bigint_LeadID + 11001000,LS.smallint_LeadOrdinal) LIKE "%".$search."%"". ")"; } $tcat = explode(".",$tcategory); // 0: SL.bigint_ServiceID, SC, text_ServiceDescription // 1: LS.bigint_SupplierID, S, text_SupplierName // 2: C.bigint_ConsumerRegion, R, text_RegionDescription $sql = "SELECT DISTINCTROW ".$tcategory.", ".$table.".".$order." FROM 25_serviceleads SL LEFT JOIN 19_consumers C ON SL.bigint_ConsumerID = C.bigint_ConsumerID LEFT JOIN 27_leadssent LS ON SL.bigint_LeadID = LS.bigint_LeadID LEFT JOIN 2_servicescatalogue SC ON SL.bigint_ServiceID = SC.bigint_ServiceID LEFT JOIN 5_suppliers S ON LS.bigint_SupplierID = S.bigint_SupplierID LEFT JOIN 1_regions R ON C.bigint_ConsumerRegion = R.bigint_RegionID LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID ".((isset($GLOBALS["m"])||count($wsql)||$suppliers!=""||$search!="")?"WHERE ".""). ((isset($GLOBALS["m"]))?"SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1]."" ":""). ((isset($GLOBALS["m"])&&count($wsql)>0)?"AND ":""). ((count($wsql)>0)?"(".implode(" OR ",$wsql).") ":""). (((count($wsql)>0||isset($GLOBALS["m"]))&&$suppliers!="")?"AND ":""). (($suppliers!="")?$f_suppliers." ":""). (((count($wsql>0||isset($GLOBALS["m"])||$suppliers!="")&&$search!="")?"AND ":""). (($search!="")?$f_search." ":""). "ORDER BY ".$table.".".$order." ASC;"; error_log("rn".print_r($sql,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."debug.log"); //$result = mysql_query_errors( $sql , $conn , __FILE__ , __LINE__, true); if ($result) { while ($row = mysql_fetch_array($result)) { if ($row[$order] != "") { echo " <order name="".xmlentities($row[$order])."">n"; $sql0 = "SELECT SL.bigint_LeadID, IFNULL( LS.smallint_LeadOrdinal, 0 ) AS smallint_LeadOrdinal, CONCAT_WS( ";", C.text_ConsumerName, C.`text_ConsumerE-Mail`, C.text_ConsumerPhone, C.text_ConsumerCity, C.text_ConsumerCounty, C.text_ConsumerState, C.text_ConsumerCountry ) AS text_Consumer, IFNULL( LS.text_Duplicates, 0 ) AS text_Duplicates, SL.text_LeadAttributes, SL.timestamp_ExpectedBy, SL.text_LeadMessage, SL.bigint_ServiceID, IFNULL( LS.bigint_SupplierID, 0 ) AS bigint_SupplierID, C.bigint_ConsumerRegion AS bigint_RegionID, IFNULL( LS.tinyint_LeadSent, 0 ) AS tinyint_LeadSent, SL.timestamp_LeadCreated, SL.bigint_ServiceID, SC.text_ServiceDescription, SC.bigint_CostPerLead, LS.double_LeadValueScoring, IFNULL( S.text_SupplierName, "" ) AS text_SupplierName, IFNULL( S.text_SupplierW3, "" ) AS text_SupplierW3, IFNULL( S.text_ContactFirstName, "" ) AS text_ContactFirstName, IFNULL( S.text_ContactSurname, "" ) AS text_ContactSurname, IFNULL( S.text_ContactPosition, "" ) AS text_ContactPosition, IFNULL( S.`text_ContactE-mail`, "" ) AS `text_ContactE-mail`, IFNULL( S.bigint_ContactTel, "" ) AS bigint_ContactTel, IFNULL( S.bigint_CurrentBalance, "" ) AS bigint_CurrentBalance, IFNULL( S.tinyint_VariablePricingEnabled, "" ) AS tinyint_VariablePricingEnabled, R.bigint_RegionID, R.text_RegionDescription, FC.text_FormName, IFNULL( (SELECT text_RegionDescription FROM 1_regions WHERE bigint_RegionID = S.bigint_CountryID), "" ) AS text_Country, IFNULL( (SELECT CONCAT(R4.text_RegionDescription, " (", A4.text_RegionAbbrev, ")") FROM 1_regions R4, 35_regionabbrevs A4 WHERE R4.bigint_RegionID = A4.bigint_RegionID AND A4.bigint_RegionID = S.bigint_StateID), "" ) AS text_State, IFNULL( (SELECT CONCAT(text_RegionDescription, ", ", text_RegionAbbrev) FROM 1_regions R3, 35_regionabbrevs A3 WHERE R3.bigint_ParentRegionID = A3.bigint_RegionID AND R3.bigint_RegionID = S.bigint_CountyID), "" ) AS text_County, IFNULL( (SELECT CONCAT(R1.text_RegionDescription, ", ", A2.text_RegionAbbrev, " ", LPAD(R1.bigint_RegionID,5,"0")) FROM 1_regions R1 LEFT JOIN 1_regions R2 ON R1.bigint_ParentRegionID = R2.bigint_RegionID LEFT JOIN 35_regionabbrevs A2 ON R2.bigint_ParentRegionID = A2.bigint_RegionID WHERE R1.bigint_RegionID = S.bigint_ZipCodeID), "" ) AS text_City FROM 25_serviceleads SL LEFT JOIN 19_consumers C ON SL.bigint_ConsumerID = C.bigint_ConsumerID LEFT JOIN 27_leadssent LS ON SL.bigint_LeadID = LS.bigint_LeadID LEFT JOIN 2_servicescatalogue SC ON SL.bigint_ServiceID = SC.bigint_ServiceID LEFT JOIN 5_suppliers S ON LS.bigint_SupplierID = S.bigint_SupplierID LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID LEFT JOIN 1_regions R ON C.bigint_ConsumerRegion = R.bigint_RegionID LEFT JOIN 32_webformconfigs FC ON SL.bigint_FormID = FC.bigint_FormID WHERE ".$tcategory." = ".$row[$tcat[1]]." ". ((isset($GLOBALS["m"]))?"AND SL.timestamp_LeadCreated BETWEEN "".$GLOBALS["m"][0]."" AND "".$GLOBALS["m"][1]."" ":""). ((count($wsql)>0||$suppliers!=""||$search!="")?"AND ":""). ((count($wsql)>0)?"(".implode(" OR ",$wsql).") ":""). ((count($wsql>0&&$suppliers!="")?"AND ":""). (($suppliers!="")?$f_suppliers." ":""). (((count($wsql>0||$suppliers!="")&&$search!="")?"AND ":""). (($search!="")?$f_search." ":""). "ORDER BY SL.timestamp_LeadCreated DESC;"; error_log("rn".print_r($sql0,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."debug.log"); //$result0 = mysql_query_errors( $sql0 , $conn , __FILE__ , __LINE__,true); if ($result0) { while ($row0 = mysql_fetch_assoc($result0)) { $sql2 = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$row0["bigint_ServiceID"].";"; $result2 = mysql_query_errors( $sql2 , $conn , __FILE__ , __LINE__); $row2 = mysql_fetch_assoc($result2); echo " <lead>n"; global $cat, $catid, $servicename, $servicecost, $fixedcost, $dynamiccost, $attribsarr, $leadid, $ordinal, $leadsuccess, $leadcreated, $consumerfullname, $consumeremail, $consumercell, $city_town, $message, $exby, $dtmstyle, $dtlast, $formname, $region, $recipient, $service; $service = $row0["bigint_ServiceID"]; $attribsarr = formattribs($row0["text_LeadAttributes"]); $sqlx = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$service.";"; $resultx = mysql_query_errors($sqlx, $conn, __FILE__, __LINE__); if ($resultx) { if ($rowx = mysql_fetch_array($resultx)) { $servicename = $rowx["text_ServiceDescription"]; $fixedcost = $rowx["bigint_CostPerLead"]; } } $catid = getCategory(); $servicecost = getCost($row0["tinyint_VariablePricingEnabled"]); $leadid = $row0["bigint_LeadID"]; $ordinal = $row0["smallint_LeadOrdinal"]; $leadsuccess = $row0["tinyint_LeadSent"]; $leadcreated = $row0["timestamp_LeadCreated"]; $consumer = explode(";",$row0["text_Consumer"]); $consumerfullname = $consumer[0]; $consumeremail = $consumer[1]; $consumercell = $consumer[2]; $city_town = $consumer[3]; $message = $row0["text_LeadMessage"]; $exby = $row0["timestamp_ExpectedBy"]; $expected = explode(" ",date($dtmstyle,strtotime($exby))); $formname = $row0["text_FormName"]; $region = $row0["bigint_RegionID"]; $recipient = $row0["bigint_SupplierID"]; echo " <id>".$row0["bigint_LeadID"].";".$row0["smallint_LeadOrdinal"]."</id>n"; echo " <header>".xmlentities(populateflags("%CONFIGTEXT Intellisource | Premium Lead Manager Header | ".$formname."%",false,false))."</header>n"; echo " <consumer>".xmlentities($row0["text_Consumer"])."</consumer>n"; echo " <duplicates>".(($row0["text_Duplicates"]=="")?"No Duplicates Found":xmlentities(trim($row0["text_Duplicates"])))."</duplicates>n"; echo " <attribs>".xmlentities(str_replace("r","",str_replace("n","",$row0["text_LeadAttributes"])))."</attribs>n"; echo " <expected>" . $expected[$dtlast] . "</expected>n"; echo " <message>".xmlentities($row0["text_LeadMessage"])."</message>n"; echo " <sent>".xmlentities(($row0["tinyint_LeadSent"]!="")?$row0["tinyint_LeadSent"]:"0")."</sent>n"; echo " <created>".xmlentities($row0["timestamp_LeadCreated"])."</created>n"; echo " <service"; $sql2 = "SELECT * FROM 2_servicescatalogue WHERE bigint_ServiceID = ".$row0["bigint_ServiceID"].";"; $result2 = mysql_query_errors( $sql2 , $conn , __FILE__ , __LINE__); if ($result2) { while ($row2 = mysql_fetch_array($result2)) { $GLOBALS["lvdenabled"] = $row2["tinyint_LeadScoringEnabled"]; $GLOBALS["lvdtot"] = number_format($row0["double_LeadValueScoring"],3); $fixedcost = $row2["bigint_CostPerLead"]; echo " id="".$row2["bigint_ServiceID"]."" name="".$row2["text_ServiceDescription"]."" cost="".getCost($row0["tinyint_VariablePricingEnabled"])."" lvdtot="".$GLOBALS["lvdtot"]."" svccost="".$row2["bigint_CostPerLead"]."""; } } echo " />n"; if ($row0["bigint_SupplierID"] > 0) { echo " <supplier id="".$row0["bigint_SupplierID"]."" name="".xmlentities($row0["text_SupplierName"])."" w3a="".xmlentities($row0["text_SupplierW3"]).""". " contact="".xmlentities($row0["text_ContactFirstName"]." ".$row0["text_ContactSurname"])."" position="".xmlentities($row0["text_ContactPosition"]).""". " mail="".xmlentities($row0["text_ContactE-mail"])."" tel="".$row0["bigint_ContactTel"]."" bal="".$row0["bigint_CurrentBalance"].""". " cntr="".$row0["text_Country"]."" stat="".$row0["text_State"]."" cnty="".$row0["text_County"]."" cty="".$row0["text_City"]."" />n"; } echo " </lead>n"; } } echo " </order>n"; if ($GLOBALS["q"]!=7) { $sql1 = "SELECT IF( (SELECT COUNT(*) FROM 4_servicesuppliers) < 1, S.bigint_SupplierID, SS.bigint_SupplierID ) AS bigint_SupplierID, IF( (SELECT COUNT(*) FROM 4_servicesuppliers) < 1, REPLACE(S.text_ServiceID,";",","), GROUP_CONCAT(SS.bigint_ServiceID SEPARATOR ',') ) AS bigint_ServiceID, S.text_SupplierName, IF( (SELECT COUNT(*) FROM 4_servicesuppliers) < 1, CONCAT_WS( ",", S.bigint_CountryID, S.bigint_StateID, S.bigint_CountyID, S.bigint_ZipCodeID ), GROUP_CONCAT(SS.bigint_RegionID SEPARATOR ',') ) AS bigint_RegionID FROM 5_suppliers S LEFT JOIN 4_servicesuppliers SS ON S.bigint_SupplierID = SS.bigint_SupplierID WHERE S.smallint_SupplierStatus = 0 GROUP BY S.bigint_SupplierID ORDER BY bigint_ServiceID ASC, text_SupplierName ASC;"; $result1 = mysql_query_errors( $sql1 , $conn , __FILE__ , __LINE__); while ($row1 = mysql_fetch_array($result1)) { echo " <forwardpossible serviceid="".$row1["bigint_ServiceID"]."" supplierid="".$row1["bigint_SupplierID"]."" suppliername="".xmlentities($row1["text_SupplierName"])."" regionid="".$row1["bigint_RegionID"]."" />n"; } } } } }}?> what in the world is wrong with the above code??? *futilely tries to slap self awake* please help!
  14. this issue is still awaiting assistance from the 1pg developers - the apparent post data does not get posted as it should! (the webhooks i am trying to integrate here - are still in beta...)
  15. would i have to set permissions within the file function perhaps? the permissions are rw-r--r-- in winscp - and no it successfully gets the key and prints it from the file.
  16. hi all, what is wrong with the following code that it does not write the following to log file when an item is updated using webhooks? <?php/*timestamp int Sent as unix time (http://en.wikipedia.org/wiki/Unix_time)secretkey string If you provided a SecretKey it will be included here, otherwise this field is emptydata hash A data hash containing the same data as returned from a API call but as a POST parameter body.type string What type of data was changed(Contact, Action, Deal etc..)reason string What change triggered the Webhook(Created, Deleted, Updated etc..)*/function microtime_float() { list($usec, $sec) = explode(" ", microtime()); return ((float)$usec + (float)$sec);}$time_start = microtime_float();$keys = file("/var/equote",FILE_USE_INCLUDE_PATH | FILE_SKIP_EMPTY_LINES | FILE_IGNORE_NEW_LINES);if ($_POST["secretkey"]==$keys[0]) { $data = json_encode($_POST["data"],true); $type = $_POST["type"]; $reason = $_POST["reason"]; // contact {"data"=>"{"id"=>"51a8852aceb9976888000007", "pendingdeal"=>true, "starred"=>true, "owner"=>"51a88516ceb9976888000001", "type"=>"contact", "firstname"=>"Jane", "lastname"=>"Doe", "company"=>"Acme Inc.", "status"=>"customer", "tags"=>[], "photo_url"=>"https://opcrm-fd-us-west-1-51a896c4ceb997741c000006.s3.amazonaws.com/51a88516ceb9976888000001/51a8852cceb9976888000009/jane_doe.png", "next_actions"=>{"51a88516ceb9976888000001"=>[{"id"=>BSON::ObjectId('51c422aaceb997128700026c'), "name"=>"Contact Jane to confirm she has received our deal", "type"=>"date", "date"=>2013-06-26 00:00:00 UTC}]}, "company_id"=>nil}", "type"=>"contact", "reason"=>"updated", "secretkey"=>"mysecretkey", "timestamp"=>"1371810060"} if ($type == "contact") { switch ($reason) { case "Created": // Created Object has been created. break; case "Updated": // Updated Object has been updated. break; } }}$time_end = microtime_float();$time = $time_end - $time_start;print_r($_POST);// if (count($_POST)) error_log("rn".$time." second execution completed at ".date("Y/m/d H:i:s",time())."rn".print_r($_POST,true)."rn",3,dirname(__FILE__).DIRECTORY_SEPARATOR."webhook.log");?> the file is located in /var/www/performatix.co/production/public/webhook.php with an md5'd htaccess file as /var/equote containing two username/encoded password strings. however whenever updating the onepage crm - however the array is always empty! what would be wrong here? this is on the onepage crm using the version 3.0 api...
  17. and its finally working 100%, since Friday 20th at 07:55 am (+2 gmt)
  18. updated line 840 to read $manual = (empty($types))?true:false; but still when printing $manual i get nothing with print_r should i try it as a string? ok testing following code: function opconvert($array,$types=NULL,$levels=1) { $manual = (!empty($types))?"true":"false"; if ($manual) $types = gettypes($array); switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); if ($manual=="true") { $out[$key] = $value["type"]."|".$value[$type[$i]]; } else { $out[$key] = $type[$i]."|".$value; } array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }}
  19. why can't i get $manual set to true or false, if NULL is provided as the second parameter in the function opconvert??? this is driving me crazy - it works in other instances just not in this one. the output of $out[$key] is $out[$key] = number|Array this is happening because it is falling past the exception. if i change the $manual to !$manual then it messes up the other instances where this function is used from. ok - am going to try using empty($types) rather, see if this helps. function opconvert($array,$types=NULL,$levels=1) { $manual = empty($types); if ($manual) $types = gettypes($array); switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); if ($manual) { $out[$key] = $value["type"]."|".$value[$type[$i]]; } else { $out[$key] = $type[$i]."|".$value; } array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }}
  20. am currently testing setting $manual initially, the changes i have made are above. i replaced $manual = false; if (is_null($types)) { $manual = true; $types = gettypes($array); //$array = objectToArray($array); } with $manual = ($types===NULL); if ($manual) $types = gettypes($array); and the result from the function, even after clearing memcached - is *STILL* the above! why is $manual never true with the above condition? even when using is_null($types)... ok have edited following line as such: $manual = is_null($types); testing again... ok still 0o am moving away from the inline if's lower in the function (originally lines 857 to 859, but now lines 857 to 861) and executing it as follows: function opconvert($array,$types=NULL,$levels=1) { $manual = is_null($types); if ($manual) $types = gettypes($array); switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); if ($manual) { $out[$key] = $value["type"]."|".$value[$type[$i]]; } else { $out[$key] = $type[$i]."|".$value; } array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }} and testing again...
  21. doesn't show it being false either... and $types gets populated on line 843. why would it not be executing the line before, or at least displaying $manual as true? $manual needs to be set to true for this function to work as expected... how would i accomplish this? the following code is not working either - and was recommended on the reference url below (the post has been around for some 12 years already! hope this still holds water - although what i am actually referring to is contained within the Editors Note at the bottom of the post). http://www.php.net/manual/en/function.is-null.php#12120 function opconvert($array,$types=NULL,$levels=1) { $manual = ($types===NULL); if ($manual) $types = gettypes($array); switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); $out[$key] = (($manual)?$value["type"]:$type[$i]). "|". (($manual)?$value[$type[$i]]:$value); array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }}
  22. hi - have not been able to test the function untill justnow, and unfortunately $manual still does not get set to true. how is this possible with the following version of opconvert? function opconvert($array,$types=NULL,$levels=1) { $manual = false; if (is_null($types)) { $manual = true; $types = gettypes($array); //$array = objectToArray($array); } switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); $out[$key] = (($manual)?$value["type"]:$type[$i]). "|". (($manual)?$value[$type[$i]]:$value); array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }} with the following output from $GLOBALS["sql"] on lines 854 and 864, after clearing memcached. for some reason unbekbownst to me - it is still not setting $manual to true... sincerely, Pierre du Toit.
  23. hi all, i am having a problem regarding the following function - specifically opconvert. it is used in different places, but when $types is set as NULL when calling the function - checking it to be null in the first if statement - does not seem to be executing. so $manual stays false and the first value is not returned but the last. below the code is a data extract which is logged during execution. function objectToArray($d) { if (is_object($d)) { // Gets the properties of the given object // with get_object_vars function $d = get_object_vars($d); } if (is_array($d)) { /* * Return array converted to object * Using __FUNCTION__ (Magic constant) * for recursive call */ return array_map(__FUNCTION__, $d); } else { // Return array return $d; }}function arrayToObject($d) { if (is_array($d)) { /* * Return array converted to object * Using __FUNCTION__ (Magic constant) * for recursive call */ return (object) array_map(__FUNCTION__, $d); } else { // Return object return $d; }}function gettypes($array) { $types = ""; foreach ($array as $id=>$element) { $types .= (($id>0)?",":"").$element["type"]; } return $types;}function opconvert($array,$types=NULL,$levels=1) { $manual = false; if ($types===NULL) { $manual = true; $types = gettypes($array); //$array = objectToArray($array); } switch ($levels) { case 1: // pre-formatted array passed return implode(",",$array); break; case 2: // unformatted hash passed $i = 0; $out = array(); $type = explode(",",$types); array_push($GLOBALS["sql"], "$manual = ".print_r($manual,true), "$types = ".print_r($types,true), "$type = ".print_r($type,true) ); foreach ($array as $key=>$value) { $value = objectToArray($value); $out[$key] = (($manual)?$value["type"]:$type[$i]). "|". (($manual)?$value[$type[$i]]:$value); array_push($GLOBALS["sql"], "$key = ".print_r($key,true), "$value = ".print_r($value,true), "$out[$key] = ".print_r($out[$key],true) ); $i++; } return implode(",",$out); break; }} why does $manual not set to true if parameter $types is passed as NULL? i have converted it from stdClass data type to array data type - which seems to make it more legit. in the end i am looking to have the following types of values returned from the function formatted as DataType|DataValue,DataType|DataValue from the passed json data extract: POSSIBLE EMAIL TYPES work home other POSSIBLE PHONE TYPES work mobile home direct fax other POSSIBLE URL TYPES website blog twitter linkedin facebook other the main issue though, is when calling via "opconvert($data->data->contact->phones,NULL,2)" for instance, it does not set $manual to true - even though it enters the if statement and parses the variables property (actually array element after converting from stdClass type). a swift answer (or even kick in the right direction ), would truly be appreciated, Pierre "Greywacke" du Toit.
  24. ok nevermind got it! started listening to some visualization manifestation music and it came to me // load attribute keys & weighting / 1, containing attribute values & weighting / 10 var lvdk = xmldoc.getElementsByTagName("lvdk"); for (var k = 0; k < lvdk.length; k++) { var i = 0; var arr = new Array(); for (var v = 0; v < lvdk[k].attributes.length; v++) { arr[v] = lvdk[k].attributes[v].value; } var t = lvdk[k].attributes.length; for (var v = 0; v < lvdk[k].childNodes.length; v++) { var lvd = lvdk[k].childNodes[v]; if (lvd.nodeName == "lvdv" && lvd.attributes) { arr[v+t] = new Array(); for (var i = 0; i < lvd.attributes.length; i++) { arr[v+t][i] = lvd.attributes[i].value; } } } salert(arr); //getlvds(arr); } hope this helps somebody else with a similar problem
×
×
  • Create New...