Search two tables at once.

adrianuk29

Cannot understand why I am unable to search through two tables at once. I want be able to display the users name and their personal property, however when I try to run the script I get an error. Would be much appreciated if anyone could help. Here is my code. I know everything is pretty much spot on, just scratching my head wondering why it is not working.

<?
include "dbc.php";
mysql_connect($servername,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM personal_information, personal_property WHERE personal_information.id=personal_property.id AND id=$id";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$first_name=mysql_result($result,$i,"first_name");
$property_owned=mysql_result($result,$i,"property_owned");

bubblenut

Can you post your table structure, I think the problem may be that you're joining the two tables on what looks like each one's primary key but without seeing the table structure I can't be certain,
Cheers
Bubble

adrianuk29

Doh how stupid of me. Yes both of the tables have an id and are both set as the (primary key). Should I not make one of the ids a foreign key?

Thanks for prompt reply!!!

bubblenut

Not just change one to a foriegn key.
First you must decide what the relationship is between the two tables, I'll assume it's a one to many from personal_information to personal_property so for every entry in personal_information there can me many entries in personal_property but for every entry in personal_property there can be one and only one entry in personal_information.

From this we can see that to link these two tables together we must have a field in peronal_property representing the primary key in personal_information. This is not instead of the primary key in personal_property but as well as, so the table structure might look like.

personal_information
[FONT=courier new]
unique_id
name
telno
[/FONT]

personal_property
[FONT=courier new]
unique_id
pi_id
name
value
[/FONT]

Here I have used underlining to show the primary key and bold to show the foriegn key.

HTH
Bubble