Comparing two tables and returning data

[Anonymous]

I have two tables, contacts & zips

In the contacts table I have a field for custProduct, custState and in the zips table I have a field for zState

This is a two character field (ie. SC or TC or MI, etc....) I use the zips table as the reference table.

Basically I want to run a query that checks to see which states do not have a contact currently. I will be adjusting the query to also look for specific products NOT carried in what states. I have included what I have for a query currently ... This is probably archaic and not correct in the manner in which I have written it, because it is not returning the data right. And I am new at this ... so forgive me ... I think I need to do a join somehow but am a little confused. Thanks in advance for any help you can give.

<?php
$query = "SELECT * from zips,contacts where zState != custState ORDER BY zState ASC";
$result = mysql_query($query);
while ($rows = mysql_fetch_object($result))
{
	$query2="Select * FROM contacts";
	$result2= mysql_query($query2);
	
	while ($rows2 = mysql_fetch_object($result2))
	{
		$custState = $rows2->custState;
	}	
	if (($custState) != ($rows->zState)) 
	{
		echo '<font size=2><br><b>State:</b> ' . $rows->zState . '     <b>Zip Range:</b> ' . $rows->zRange . '</font><br>';
	}
}
echo "<br><br>There is no coverage in the following areas listed above.";

 

[gesf]

There are a few possibilities.
Before i can help you (even in the php part) , I need you to please explain better what are your tables' fields and what's refering one table to another or what's the dependence if it exists.

 

[Anonymous]

Hey there sorry for not including enough information:

The tables are as follows (and I am not sure if I have a :depedent: setup or not.

table: contacts
fields:
custID
custName
custProduct
custZip
custState

table: zips
zID
zRange
zState

The contacts table holds the customers that currently have a product line to offer in a state, where as the zips table I wanted to use for reference so I would know what areas did not have a contact for that state by cross refencing the zips table with the contacts table to display back all the states where there is no product being carried. (ie. show all states where this is not a contact in the contacts table for that state)

Thanks kindly.

 

[Alexej Kubarev]

please use code tags for posting PHP codes and such.. this really helps both you and us.

I will edit your first post so you can see how to do that..