PHP query string

[benrob82]

Hi can anyone help me,

is there a better way to write this query, it seems very long winded to do what it needs to do

$getcampaign = mysql_query("select id,prospect_list_id from prospect_list_campaigns where campaign_id = '$eventid'"); 
$gotcampaign = mysql_fetch_row($getcampaign);

$getprospect = mysql_query("select id,contact_id from prospect_lists_prospects where prospect_list_id = '$gotcampaign[1]'"); 

while ($gotprospect = mysql_fetch_row($getprospect)) 
{
	echo "<tr>";
	$getcontacts = mysql_query("select id,first_name,last_name from contacts where contacts.id = '$gotprospect[1]' order by last_name desc"); 
	while ($gotcontacts = mysql_fetch_row($getcontacts)) 
	{
	
		$getcocontacts = mysql_query("select id,account_id from accounts_contacts where contact_id = '$gotprospect[1]'"); 
		while ($gotcocontacts = mysql_fetch_row($getcocontacts)) 
		{
			echo "<td nowrap>$gotcontacts[1] $gotcontacts[2]</td>";
		
			$getcompany = mysql_query("select id,name from accounts where accounts.id = '$gotcocontacts[1]'"); 
			while ($gotcompany = mysql_fetch_row($getcompany)) 
			{
				$count++;
				echo "<td nowrap>$gotcompany[1]</td>";
			}
	
		}
	echo "</tr>";
	}
}

 

[sleipnir214]

It looks to me like you're performing a join between tables in your code. You would, I suppose, use a MySQL SELECT query with several nested JOINs in it...



Want the best answers? Ask the best questions! TANSTAAFL!

 

[steven290]

here is a qick example, you can try and figure out the rest :

$getprospect = mysql_query("select plp.id,plp.contact_id from prospect_lists_prospects plp left join prospect_list_campaigns plc on plc.prospect_list_id=plp.prospect_list_id
where plc.campaign_id = '$eventid'");

while ($gotprospect = mysql_fetch_row($getprospect)) 
....