Jump to content

cant figure why SQL query within a resultset doesnt seem to be working.


Greywacke
 Share

Recommended Posts

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 :/

Edited by Pierre 'Greywacke' du Toit
Link to comment
Share on other sites

i have selected the individual records required, and this issue has fallen away.

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...