Recursive functions, Illegal variable?

[Sydcomebak]

Assume the following database structure:

HouseTblPeopleTblResidentTbl

-HouseID (unique)-NameID (unique)-ResidentID (unique)

-HouseNum-NameLast-Name_ID

-HouseStreet-NameFirst-House_ID

-HouseCombined[/td][td]

 

Bring in a variable from a link that you want to be the first letter of the last name.  Call it $var.

 

What I need to do is display the address next to the name that's echoed.

 

<?php
$result = mysql_query("SELECT * 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.NameID = ResidentTbl.Name_ID)
WHERE SUBSTRING(NameLast,1,1) = '$var'");
?>

<?php
while ($row = mysql_fetch_array($result) ) { 
   echo "<a href="; 
   echo '"'; 
   echo "results.php?address="; 
   echo $row['House_ID']; 
   echo '"'; 
   echo ">"; 
   echo $row['NameLast'].", ".$row['NameFirst']; 
   echo "</a> - ";

$result1 = mysql_query("SELECT * 
FROM HouseTbl 
INNER JOIN ResidentTbl ON (HouseTbl.HouseID = ResidentTbl.House_ID)
WHERE (HouseID = '$row[House_ID]'");

while ($row1 = mysql_fetch_array($result1) ) { 
   echo $row1['HouseTbl.HouseCombined']; 
}
   echo "<br>";        }

?>

 

Right now, I'm getting the error: "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in..." after each displayed name.

 

Am I not allowed to use "$row1" or "$result1"  I don't think the recursive function will work unless I use different vars  ?

[DarkWater]

$result1 = mysql_query("SELECT *

FROM HouseTbl

INNER JOIN ResidentTbl ON (HouseTbl.HouseID = ResidentTbl.House_ID)

WHERE (HouseID = '$row[House_ID]'") OR die(mysql_error());

 

Try that.  That error that you got is due to the query failing.

[Sydcomebak]

Adams, Homer - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

[DarkWater]

You missed a ).

 

$result1 = mysql_query("SELECT *

FROM HouseTbl

INNER JOIN ResidentTbl ON (HouseTbl.HouseID = ResidentTbl.House_ID)

WHERE (HouseID = '$row[House_ID]')") OR die(mysql_error());

 

That should work properly. =D

[Sydcomebak]

It made it through the functions and displayed all the names, but no addresses appeared. I'm going to try and add the table names and see if that helps.

 

edit:  Changed THIS:  echo $row1['HouseTbl.HouseCombined'];  To THIS:  echo $row1['HouseCombined'];

 

and now it works... sort of.

 

The problem is that In a household where 2 ppl are residents, the address displays twice for each person.  Maybe I should remove the While command?