Greywacke Posted October 5, 2013 Share Posted October 5, 2013 (edited) 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! Edited October 5, 2013 by Pierre 'Greywacke' du Toit Link to comment Share on other sites More sharing options...
Ingolme Posted October 5, 2013 Share Posted October 5, 2013 On like 69, you forgot the colon and put a dot in its place (isset($GLOBALS["m"])||count($wsql)||$suppliers!=""||$search!="")?"WHERE "."" On line 77 and 197 dreamweaver is probably telling you not to end your queries with a semi-colon: "ORDER BYSL.timestamp_LeadCreatedDESC;"; Link to comment Share on other sites More sharing options...
Greywacke Posted October 7, 2013 Author Share Posted October 7, 2013 (edited) 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 Edited October 10, 2013 by Pierre 'Greywacke' du Toit Link to comment Share on other sites More sharing options...
Ingolme Posted October 7, 2013 Share Posted October 7, 2013 Instead of dreamweaver, try running the page on a server to see what error shows up. I know I'm repeating myself, but I think it probably is the semi-colon at the end of the query, just in case it wasn't clear before. The one I'm referring to is this: mysql_query("SELECT * FROM table WHERE field=value;"); Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now