I'm caught in a trap...I can't walk out

[11InchH4mm3r]

Hi all,

 

this is my first post here as I'm new to php/mysql coding.

 

I've written this query and I'm getting caught in an endless loop, for the life of me I've looked at it over and over and can't seem to find my error

[code=php:0]<?php

require('includes/init.php');

//Start the query with a date range of 1 year
$query = "SELECT *, FROM_UNIXTIME(MAX(inserted_timestamp), '%m-%d-%Y') AS `inserted_date`, COUNT(source) AS NumOccurrences FROM leads WHERE source != 'Sales Call' AND email != '' AND inserted_timestamp >= (UNIX_TIMESTAMP()-15778458) GROUP BY email HAVING ( COUNT(source) > 1 ) ORDER BY inserted_timestamp DESC";
$result = mysql_query( $query );
//Check against lead comments
while ($row = mysql_fetch_assoc($result)) {
$query = "SELECT inserted_timestamp FROM leads l, lead_comments lc WHERE l.id=lc.lid AND email='".$row['email']."' AND LOCATE('Received the `',comment)=0 ORDER BY lc.date DESC";
$result2 = mysql_query( $query );
while ($row2 = mysql_fetch_assoc($result2))
{
//Qualify the lead
$goodlead = false;
if($result2->recordCount > 0) {
	$timediff = mktime() - $result2->results['date'];
	$days = timediff/86400;
	if($days>182) {
		$goodlead = true;
	}
} else {
	$goodlead = true;
}
// Save good leads
if($goodlead) {
	$goodleads[] = $row2;	
}
}
}

//Fetch and Display the results
foreach($goodleads as $lead)
{
echo $lead['first_name']. '<br />';
}

?>

[/code]

 

[JasonLewis]

Sometimes to spot where you went wrong, it pays to properly indent your code to make it more readable. This includes spacing your lines where required.

 

You appear to be using mysql_fetch_assoc, which returns an associative array of elements, however your attempting to access them as though they were objects.

 

Your also using recordCount inside of the loop, which is pointless because the loop should only run if results are returned. If mysql_fetch_assoc returns no results, then it returns false meaning the loop won't run.

 

You also don't need a few variables, such as the goodlead variable which you are setting as a boolean.

 

Few other things. You reference timediff as a constant after setting it, not as a variable. You should also have set the goodleads array as an array before running any of the loops, as its good practice to set variables before using them, especially arrays otherwise you can get notices saying that a variable wasn't set when running E_ALL in error reporting.

 

Now I've gone through your code and fixed up what I found to be poorly coded. I'm not saying it will work but give it a shot.

 

<?php

require('includes/init.php');

//Start the query with a date range of 1 year
$query = "SELECT *, FROM_UNIXTIME(MAX(inserted_timestamp), '%m-%d-%Y') AS `inserted_date`, COUNT(source) AS NumOccurrences
	FROM leads
	WHERE source != 'Sales Call'
	AND email != ''
	AND inserted_timestamp >= (UNIX_TIMESTAMP()-15778458)
	GROUP BY email HAVING ( COUNT(source) > 1 )
	ORDER BY inserted_timestamp DESC";
$result = mysql_query($query) or die('MySQL Error: ' . mysql_error());

//Set goodleads to a blank array
$goodleads = array();

//Check against lead comments
while ($row = mysql_fetch_assoc($result)) {
	$query = "SELECT inserted_timestamp
		FROM leads l, lead_comments lc
		WHERE l.id=lc.lid
		AND email='" . $row['email'] . "'
		AND LOCATE('Received the `',comment)=0
		ORDER BY lc.date DESC";
	$result2 = mysql_query($query) or die('MySQL Error: ' . mysql_error());

	while ($row2 = mysql_fetch_assoc($result2)) {
		$timediff = mktime() - $row2['date'];
		$days = $timediff /86400;
		if($days > 182) {
			$goodleads[] = $row2;
		}
	}
}

//Fetch and Display the results
foreach($goodleads as $lead){
	echo $lead['first_name']. '<br />';
}

?>

 

Good luck.