Greywacke Posted December 7, 2010 Share Posted December 7, 2010 hi again. i've tested the first query, and it returns with two rows containing all the values used creating the first insert query within the resultset.printing the queries run, it gets to the query and resultset for the outer sql query $sql0 then does not seem to go into the while loop. $now = getdate(strtotime(convert_tz(date("Y-m-d H:i:s", time()),"SAST")));$t = date("Y-m-d 23:59:59",mktime(23,59,59,$now["mon"],$now["mday"] - 1,$now["year"]));// loop through triggers created for previous day$sql0 = "SELECT * FROM 26_surveyqueue LEFT JOIN 25_serviceleads ON ( 26_surveyqueue.bigint_LeadID = 25_serviceleads.bigint_LeadID ) LEFT JOIN 10_serviceprospects ON ( 26_surveyqueue.bigint_ReferenceID = 10_serviceprospects.bigint_ProspectID ) LEFT JOIN 19_consumers ON ( 25_serviceleads.bigint_ConsumerID = 19_consumers.bigint_ConsumerID ) LEFT JOIN 20_surveytypes ON ( 26_surveyqueue.bigint_SurveyID = 20_surveytypes.bigint_SurveyID ) WHERE 26_surveyqueue.tinyint_TriggerInitiated = 0 AND 26_surveyqueue.timestamp_SurveyTrigger <= \"".$t."\";";$result0 = mysql_query_errors($sql0, $conn , __FILE__ , __LINE__ , true );while ($row = mysql_fetch_array($result0)) { // update prospectleads table to include consumer id $sql1 = "INSERT INTO 21_consumersurveys ( bigint_ConsumerID, bigint_LeadID, bigint_ReferenceID, bigint_SurveyType ) VALUES ( ".$row["bigint_ConsumerID"].", ".$row["bigint_LeadID"].", ".$row["bigint_ReferenceID"].", ".$row["bigint_SurveyID"]." );"; $result1 = mysql_query_errors($sql1, $conn , __FILE__ , __LINE__ , true ); $csid = mysql_insert_id($conn); $sql3 = "UPDATE 26_surveyqueue SET timestamp_SurveyTrigger = 1;"; $result3 = mysql_query_errors($sql3, $conn , __FILE__ , __LINE__ , true ); // initiate variables $consumerfullname = $row["text_ConsumerName"]; $consumeremail = $row["text_ConsumerE-Mail"]; $attribs = $row["text_LeadAttributes"]; $attribsarr = formattribs($attribs); $surveytype = $row["text_SurveyDescription"]; $surveyurl = "http://www.intellisource.co.za/survey.php?q=".$csid."&t=".$row["bigint_LeadID"]; $surveyquestions = getquestions($row["bigint_SurveyID"],$row["bigint_LeadID"],$row["bigint_ReferenceID"]); $qcnt = $surveyq[0]; $surveyquestions = $surveyq[1]; $body = getFile("/home/dwtphovu/public_html/intellisource.co.za/test_8347379386/templat/survey.html"); // load mail template $mail = new PHPMailer(); // create a new object $body = populateflags($body); // populate flags with variables require("/home/dwtphovu/public_html/intellisource.co.za/test_8347379386/includes/mailer/dwtphovu_auth.php"); // include mailer auth $mail->From = (stristr($row0["text_LeadAttributes"],"canopy_req")===false)?"canopy@canopyxchange.za.net":"canopy@quoteme.za.net"; // set from email $mail->FromName = (stristr($row0["text_LeadAttributes"],"canopy_req")===false)?"CanopyXchange":"QuoteMe Canopies"; // set from name $mail->Subject = utf8_decode(getSubText($body,"<title>","</title>")); // set subject to template page title $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($consumeremail,$consumerfullname); // add recipient $mail->AddBCC("info@canopyxchange.za.net","CanopyXchange Info"); // bcc jaap the response $mail->IsHTML(true); // set send as HTML if(!$mail->Send()) { // if attempt sending failed array_push($errmsg,"FAILURE: ".$consumerfullname.", ".$consumeremail." (".trim(strip_tags($mail->ErrorInfo)).")"); // save error report $failure++; // increment failures count } else { // else success $sql2 = "UPDATE 21_consumersurveys SET timestamp_SurveyDateE-Mail = CURRENT_TIMESTAMP;"; // save timestamp sent $result2 = mysql_query_errors($sql2, $conn , __FILE__ , __LINE__ , true); array_push($errmsg,"SUCCESS: ".$consumerfullname.", ".$consumeremail); // save success report $success++; // increment success count } // insert into consumer surveys} this prints out the following: Array( [0] => SELECT * FROM 26_surveyqueue LEFT JOIN 25_serviceleads ON ( 26_surveyqueue.bigint_LeadID = 25_serviceleads.bigint_LeadID ) LEFT JOIN 10_serviceprospects ON ( 26_surveyqueue.bigint_ReferenceID = 10_serviceprospects.bigint_ProspectID ) LEFT JOIN 19_consumers ON ( 25_serviceleads.bigint_ConsumerID = 19_consumers.bigint_ConsumerID ) LEFT JOIN 20_surveytypes ON ( 26_surveyqueue.bigint_SurveyID = 20_surveytypes.bigint_SurveyID ) WHERE 26_surveyqueue.tinyint_TriggerInitiated = 0 AND 26_surveyqueue.timestamp_SurveyTrigger <= "2010-12-06 23:59:59"; [1] => NOTICE #0499: The query returned the following result in /home/dwtphovu/public_html/intellisource.co.za/test_8347379386/private/cronjob_surveystatus.php on line 126.Array( [0] => 1 [bigint_SurveyID] => 1 [1] => 2 [bigint_LeadID] => 2 [2] => 4689 [bigint_ReferenceID] => 4689 [3] => 0 [tinyint_TriggerInitiated] => 0 [4] => 2010-12-06 23:59:59 [timestamp_SurveyTrigger] => 2010-12-06 23:59:59 [5] => 2 [6] => 3149 [bigint_ConsumerID] => 3149 [7] => 1 [bigint_ServiceID] => 1 [8] => canopy_req = pre-owned_white<br />canopy_style = highline_standard<br />budget = R5,000 to R7,500<br />fitment = not_within_3_months<br />vehicle_status = possession_no_shopping<br />vehicle_make_model = Ford - SCAB<br />year_model = 2010 [text_LeadAttributes] => canopy_req = pre-owned_white<br />canopy_style = highline_standard<br />budget = R5,000 to R7,500<br />fitment = not_within_3_months<br />vehicle_status = possession_no_shopping<br />vehicle_make_model = Ford - SCAB<br />year_model = 2010 [9] => [text_Duplicates] => [10] => Testing survey manager work [text_LeadMessage] => Testing survey manager work [11] => 2011-04-06 23:59:59 [timestamp_ExpectedBy] => 2011-04-06 23:59:59 [12] => 2010-12-07 17:50:02 [timestamp_LeadCreated] => 2010-12-07 17:50:02 [13] => 4689 [bigint_ProspectID] => 4689 [14] => Andr?abuschagne;pierre@canopyxchange.za.net;0729154799;Kempton Park [text_Consumer] => Andr?abuschagne;pierre@canopyxchange.za.net;0729154799;Kempton Park [15] => 3149 [16] => [text_LeadSubject] => [17] => canopy_req = pre-owned_white<br />canopy_style = highline_standard<br />budget = R5,000 to R7,500<br />fitment = not_within_3_months<br />vehicle_status = possession_no_shopping<br />vehicle_make_model = Ford - SCAB<br />year_model = 2010 [18] => 2011-04-06 23:59:59 [19] => Testing survey manager work [20] => 1 [21] => 37 [bigint_RegionID] => 37 [22] => 0000-00-00 00:00:00 [timestamp_ProspectCreated] => 0000-00-00 00:00:00 [23] => 3149 [24] => pierre@canopyxchange.za.net [text_ConsumerE-Mail] => pierre@canopyxchange.za.net [25] => Andr?abuschagne [text_ConsumerName] => Andr?abuschagne [26] => 0729154799 [text_ConsumerPhone] => 0729154799 [27] => Kempton Park [text_ConsumerCity] => Kempton Park [28] => 37 [bigint_ConsumerRegion] => 37 [29] => 0 [tinyint_ConsumerStatus] => 0 [30] => 2010-11-22 19:37:20 [timestamp_Populated] => 2010-11-22 19:37:20 [31] => 1 [32] => 1 [33] => Test Survey [text_SurveyDescription] => Test Survey [34] => 5 [tinyint_SurveyDaysOffset] => 5 [35] => 0 [tinyint_OffsetFrom] => 0)) this is supposed to print queries, errors or resultsets if any when the last parameter in the mysql_query_errors function is set to true. but for some reason it does not. testing the outer query that is printed here, i get the following result set: [b]bigint_SurveyID bigint_LeadID bigint_ReferenceID[/b]1 2 46894 2 4689[b]tinyint_TriggerInitiated timestamp_SurveyTrigger[/b]0 2010-12-06 15:59:590 2010-12-06 16:59:59[b]bigint_LeadID bigint_ConsumerID bigint_ServiceID[/b]2 3149 12 3149 1[b]text_LeadAttributes text_Duplicates text_LeadMessage[/b]canopy_req = pre-owned_white Testing survey managercanopy_style = highline_standard workbudget = R5,000 to R7,500fitment = not_within_3_monthsvehicle_status = possession_no_shoppingvehicle_make_model = Ford - SCAByear_model = 2010canopy_req = pre-owned_whitecanopy_style = highline_standardbudget = R5,000 to R7,500fitment = not_within_3_monthsvehicle_status = possession_no_shoppingvehicle_make_model = Ford - SCAByear_model = 2010canopy_req = pre-owned_white Testing survey managercanopy_style = highline_standard workbudget = R5,000 to R7,500fitment = not_within_3_monthsvehicle_status = possession_no_shoppingvehicle_make_model = Ford - SCAByear_model = 2010canopy_req = pre-owned_whitecanopy_style = highline_standardbudget = R5,000 to R7,500fitment = not_within_3_monthsvehicle_status = possession_no_shoppingvehicle_make_model = Ford - SCAByear_model = 2010 [b]timestamp_ExpectedBy timestamp_LeadCreated bigint_ProspectID[/b]2011-04-06 16:59:59 2010-12-07 09:50:02 46892011-04-06 16:59:59 2010-12-07 09:50:02 4689[b]text_Consumer[/b]André Labuschagne;pierre@canopyxchange.za.net;0729154799;Kempton ParkAndré Labuschagne;pierre@canopyxchange.za.net;0729154799;Kempton Park[b]bigint_ConsumerID text_LeadSubject text_LeadAttributes[/b]3149 canopy_req = pre-owned_white canopy_style = highline_standard budget = R5,000 to R7,500 fitment = not_within_3_months vehicle_status = possession_no_shopping vehicle_make_model = Ford - SCAB year_model = 20103149 canopy_req = pre-owned_white canopy_style = highline_standard budget = R5,000 to R7,500 fitment = not_within_3_months vehicle_status = possession_no_shopping vehicle_make_model = Ford - SCAB year_model = 2010timestamp_ExpectedBy text_LeadMessage-----------------------------------------------------2011-04-06 16:59:59 Testing survey manager work2011-04-06 16:59:59 Testing survey manager workbigint_ServiceID bigint_RegionID timestamp_ProspectCreated1 37 2010-12-07 09:50:031 37 2010-12-07 09:50:03bigint_ConsumerID text_ConsumerE-Mail text_ConsumerName----------------------------------------------------------------------------------3149 pierre@canopyxchange.za.net André Labuschagne3149 pierre@canopyxchange.za.net André Labuschagnetext_ConsumerPhone text_ConsumerCity bigint_ConsumerRegion------------------------------------------------------------------------------0729154799 Kempton Park 370729154799 Kempton Park 37tinyint_ConsumerStatus timestamp_Populated bigint_SurveyID----------------------------------------------------------------------------0 2010-11-22 11:37:20 10 2010-11-22 11:37:20 4bigint_ServiceID text_SurveyDescription tinyint_SurveyDaysOffset--------------------------------------------------------------------------------1 Test Survey 51 Email Survey -17[b]tinyint_OffsetFrom[/b]01 also displayed on the printed results when testing the cronjob. if anyone could help me, please - i cannot see why the inner query is not running :/ Link to comment Share on other sites More sharing options...
Greywacke Posted December 8, 2010 Author Share Posted December 8, 2010 i have selected the individual records required, and this issue has fallen away. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.