[RESOLVED] A simpler way to match data across 2 tables

[swatisonee]

hi,
This issue crops up only for exisiting data. For new records, i've taken care in the coding.

I have a table A with multiple fields, one of them being Name that occurs multiple times. In another table B I have 2 fields, id(autoinc,int 11) and Family . Table A also has a field called Family

Now John Doe and Jane Doe are both part of the Smith Family. I want to be able to list all the Does in table A and match it to the Smith entry in Table B .

My current way of doing it is to select all the Does, then on another page select Smith and then process both on a third page by doing an UPDATE Table A SET Family = $family WHERE Name = $name

I think there must be a simpler way to do this. Ideally i'd like to list out two columns , Name - one listing all the Does , and Family listing Smith, Barnaby etc. That way, I can check multiple boxes in the Name column and match it to a single checkbox on the Family column.

The code i have ends up creating a 3rd table which is not what i want to achieve as it duplicates data. Any ideas how I can modify it to use just Table A and B ?

Thanks.
Swati

<?php

if ($_POST) {
	foreach ($_POST['names'] as $name) {
		$query = "INSERT INTO tablec (name, family_name) VALUES ('" . mysql_real_escape_string($name) . "', '" . mysql_real_escape_string($_POST['family_name']) . "')";
		mysql_query($query) or die(mysql_error());


}
}	

$query = "SELECT * FROM tableA";
$query = "SELECT * FROM tableB";

$names = mysql_query($query);
$families = mysql_query($query);

while ($row = mysql_fetch_array($names, MYSQL_ASSOC)) {
	$n .= '
		<input type="checkbox" name="names[]" value="' . $row['name'] . '"> ' . $row['name'] . '<br />';
}

while ($row = mysql_fetch_array($families, MYSQL_ASSOC)) {
	$f .= '
		<input type="radio" name="family" value="' . $row['family_name'] . '"> ' . $row['family_name']. '<br />';
}

echo '
	<form method="post">
	<table>
		<tr>
			<th>Names</th>
			<th>Families</th>
		</tr>
		<tr>
			<td colspan="2" style="text-align: center;">Choose the names to associate with a family:</td>
		</tr>
		<tr>
			<td>' . $n . '</td>
			<td>' . $f . '</td>
		</tr>
		<tr>
			<td colspan="2" style="text-align: center;"><input type="submit" name="submit" value="Submit"></td>
		</tr>
	</table>
	</form>';

[konrad]

It all depends on how do you want to connect families with persons. If one person belongs to one family, you've got "one to many" relationship. The database schema could looks like:

Table B
id (primary key)
name (family name or surname)

Table A
id (primary key)
name (first name)
surname (last name) (in this case it could be foreign key pointing on the name in table B as well)
familyname_id (foreign key - connected with id in table 😎

You can connect one person with more then one family ("many to many" relationship). There is another table in the schema which connects persons with families:

Table B
id (primary key)
name (family name or surname)

Table A
id (primary key)
name (first name)
surname (last name) (in this case it could be foreign key pointing on the name in table B as well)

Table C
person_id (foreign key --> id in table A)
family_id (foreign key --> id in table 😎.

Hope its understandable...

[swatisonee]

My data structure is such that i will only have a many to one relationship ie many names, one family. i'm stuck on getting the listing in place and then using checkboxes.

[konrad]

Suppose we've got tables:

Names:
(id) (name)
1 Smith
2 Brown
3 Black
4 Doe

Persons
(id) (name) (surname_id) (familyname_id)
1 John 4 1
2 Jane 4 1
3 Janet 1 2
4 Michael 1 1

Use following query to retrieve all members of Doe family:

SELECT Persons.* FROM Persons, Names WHERE Names.name="Doe" AND Persons.surname_id=Names.id;

And this one to assign 'Black' family name to all persons whose surname is 'Smith'

UPDATE Persons, Names s, Names f SET Persons.familyname_id=f.id WHERE f.name='Black' AND s.name='Smith' AND s.id=Persons.surname_id

[swatisonee]

Gosh that was a simple solution but fit my problem perfectly. Thanks !!