grouping problem

cbesh2

I have a database of names and address that I want to use to create mailing labels; however, I am having a problem.

I may have an entry for Bob Smith at 123 Main Street, City, ST 12345.
And I may have an entry for his wife, Sue Smith, at 123 Main Street, City, ST 12345.

In my query, (when the conditions are met), I would like for the label to display the following:

Bob and Sue Smtih
123 Main Street
City, ST 12345

How do I do this? I tried to google it, but the information I'm finding does not address my problems. Thanks for your help in advance.

Piranha

Are you sure that Bob and Sue are married? What if they live in an apartment house and have the same address but actually live in two different apartments?

And what about Adam Space and Eve Space that live together? And their son Gary? How to handle that family?

What I'm trying to say is that first you need to know how to handle different cases and to know if they live together or not. It is possible that you did that already, but from your post I didn't get that impression. When you have done that I think it is time to handle the rest, probably with a SQL query that is sorted by the address.

cbesh2

You're absolutely right. Bob and Sue may not be married, so I need to keep that in consideration. I will need to combine the names (sometimes only one last name, sometimes both last names) but use one address.

If they are married, I need:

Bob and Sue Smith
123 Main Street
City, ST 12345

If they do not have the same last name, I need:

Bob Smith and Sue Symthe
123 Main Street
City, ST 12345

And if there happens to be a family with the same last name (three or more people) living at the same address, I will need to have them in this format:

Adam, Eve and Gary Space
123 Main Street
City, ST 12345

Or just a bunch of roommates with different last names:

Adam Space, Eve Green, and Gary Lace
123 Main Street
City, ST 12345

How in the world do I handle this? It's getting quite complicated. Thanks for getting me thinking.

Piranha

And if they actually live in two apartments that are beside each other it might be that there should be two labels:

Bob Smith
123 Main Street
City, ST 12345

and

Sue Smith
123 Main Street
City, ST 12345

Then there may be these two rows as well, the address are not quite the same but it should be:
Bob Smith at 123 Main Street 3rd floor, City, ST 12345.
Sue Smith, at 123 Main Street, City, ST 12345.

To be sure that it gets right every time you need to have information about who lives with who in the database. Maybe you think that it is impossible to handle it without this extra information. But it is possible, you "only" have to decide what rules should apply to let people live together.

Let's assume that you have decided that the exact same address means that people live together. There are many ways to handle this, but I think that the best is to get all information (name, street, city and code) from the database ordered by street, city and code. When deciding if it is a new address or not you check if street, city and code is the same, if it is then it is the same address. And then you need to handle the names in a good way.

It is not as difficult as you might think. Start with one case and when that works go to the next case and you will get the hang of it.

cbesh2

Ok, I understand that it would be best if I decided that the exact same address means that people live together (street, city and zip). But I'm not sure how to "check" to see if they are the same. As of now, I am displaying a list of people that match the situation in my query. (Not trying to complicate things, but I am displaying those who did not attend a weekly meeting and am sending them a postcard if they did not show up. So if there are two or more in the same household, they would get one postcard instead of one for each person.)

I am using the following code to do this:

<?php
	$result= mysql_query ("SELECT * FROM people WHERE person_id 
                NOT IN(SELECT person_id FROM attendance WHERE date='".$date."') 
                AND person_id IN(SELECT person_id FROM attendance WHERE 
                date='".$dateminus7."') ORDER BY people.lname, people.fname;
                ") or die (mysql_error());

while ($row = mysql_fetch_array($result))
	{
	echo $row["fname"];
	echo "&nbsp;";
	echo $row["lname"];
	echo "<br>";
	echo $row["address"];
	echo "<br>";
	echo $row["city"];
	echo ", ";
	echo $row["state"];
	echo " ";
	echo $row["zip"];
	} 

mysql_free_result($result);
?>

If I am displaying the results with the code above, isn't the data being displayed, more-or-less, one at a time which would not allow me to compare the data? I'm a bit confused on how to do the comparisons while displaying the data at the same time. If I "ordered" them, that would only display the results according to the addresses. It would not combine the data. Am I way off track here?

Piranha

cbesh2 wrote:
Ok, I understand that it would be best if I decided that the exact same address means that people live together (street, city and zip).

No, I said that the best thing would be to have the information in the database. If you don't have it then you have to decide how to choose what information to use. My example was from the assumption that address was a good way, but that do not have to be true.

Anyway I will try to help you out a little with your code. It is not a complete solution, but I think that you will understand how to check.

<?php
	$result= mysql_query ("SELECT * FROM people WHERE person_id 
                NOT IN(SELECT person_id FROM attendance WHERE date='".$date."') 
                AND person_id IN(SELECT person_id FROM attendance WHERE 
                date='".$dateminus7."') ORDER BY people.address, people.city, people.state, people.zip;
                ") or die (mysql_error());

    $oldaddress = "";
    $oldcity = "";
// Same with state and zip

while ($row = mysql_fetch_array($result))
{
	if ($oldaddress == $row['address'] && $oldcity...)
	{
		echo $row['fname'];
		echo $row['lname'];
	}
	else // If it is a new address
	{
		echo $row["address"];
		echo "<br>";
		echo $row["city"];  // And so on, the names as well
	}
} 

mysql_free_result($result);
?>

This is only to show everything by address, to make the labels you have to change the code to suit your special need.

cbesh2

My code below isn't working correctly. It is giving me the following when three names are found with the same addres, city, state and zip:

John Smith
John and Mary Smith
John, Mary and Michael Smith

I only want it to show the very last condition but can't figure out what I'm doing wrong. (Well, I know what I'm doing wrong, I just can't figure out an alternate solution.) This one really has me stumped -- I know there has to be a better way of doing this.

I've thought about using a "switch" but couldn't get it to work properly. I've also tried nested "if statements" by reading the next entry but it throws off the data as one might expect.

Any suggestions? I just can't seem to grasp this one.

Below is a sample of my code:

    while ($row = mysql_fetch_array($result)) 
    { 
        if ($zip2 == $row['zip'] && $state2 == $row['state'] && $city2 == $row['city'] && $address2 == $row['address']) 
        { 
            $fname3 = $row['fname']; 
            $lname3 = $row['lname']; 
            $address3 = $row['address']; 
            $city3 = $row['city']; 
            $state3 = $row['state']; 									
            $zip3 = $row['zip']; 			
			$nameline = $fname1.", ".$fname2." and ".$fname3." ".$lname1;

		echo $nameline."<br><br>";
    } 

	elseif ($zip1 == $row['zip'] && $state1 == $row['state'] && $city1 == $row['city'] && $address1 == $row['address']) 
    { 
        $fname2 = $row['fname']; 
        $lname2 = $row['lname']; 
        $address2 = $row['address']; 
        $city2 = $row['city']; 
        $state2 = $row['state']; 									
        $zip2 = $row['zip']; 			
		$nameline = $fname1." and ".$fname2." ".$lname1;

		echo $nameline."<br><br>";
    } 

	else // If it is a new address 
    { 
        $fname1 = $row['fname']; 
        $lname1 = $row['lname']; 
        $address1 = $row['address']; 
        $city1 = $row['city']; 
        $state1 = $row['state']; 									
        $zip1 = $row['zip']; 
		$nameline = $fname1." ".$lname1;						

		echo $nameline."<br><br>";

    } 
} 

mysql_free_result($result);

cbesh2

What if my query was "grouped" by address and then "counted" the number of people at that address?

<?php
$query = "SELECT *, COUNT(person_id) FROM people GROUP BY address, city, state, zip"; 
$result = mysql_query($query) or die(mysql_error()); 
// Print out result
while($row = mysql_fetch_array($result)){ echo "There are ". $row['COUNT(person_id)'] ." ". $row['address'] ." items."; echo "<br />"; } 
?>

The count could be the "switch" that would put them in a correct format. I'm not exactly sure how to do it but it sounds like it might keep me from having invalid data since a "switch" statement would have the "break" command.