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.


Any ideas how I  can proceed ?

Thanks.
Swati

[hitman6003]

An example...

[code]<?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());

//you may want to execute a query that will remove that name from tableA to prevent it from showing in the future

}
}

$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>';[/code]

Obviously you would have to have database connection code and you would have to modify the field names and table names, but you get the idea.

[swatisonee]

I'm sorry i forgot to mention, i have an autoinc. id field in both tables. Would that be  more helful in doing this ?

[swatisonee]

hi, i guess the posts crossed. I dont want to create a 3rd table. I will then have some records in tableA and some in tableC.  Any suggestions pl? Thanks.