Page 1 of 1

Trouble with an Inner Joined Table

Posted: Thu Jul 03, 2008 8:22 pm
by Sydcomebak
Assume the following database structure:

HouseTbl
-HouseID (unique)
-HouseNum
-HouseStreet
-HouseCombined

PeopleTbl
-NameID (unique)
-NameLast
-NameFirst

ResidentTbl
-ResidentID (unique)
-Name_ID
-House_ID

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

Code: Select all

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

Unfortunately, as soon as I go back to the HTML and try to fetch the result, it gives me an error:

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...on line 27
Line 27 starts the fetch that follows:

Code: Select all

 
<?php
while ($row = mysql_fetch_array($result) ) {
       echo $row['NameLast']." ".$row['NameFirst'];
       echo "<br>";
}
?>
Am I missing something?

Re: Trouble with an Inner Joined Table

Posted: Thu Jul 03, 2008 9:08 pm
by califdon
What do you mean by "goes through cleanly"? You don't have an "or die(mysql_error())" in your mysql_query() line. How do you know that the query was successful? Usually that error means that the query failed. The query probably failed because there are no single quotes around the character variable $var.

Entirely apart from your question, I'm curious why you would have a many-to-many relationship between Houses and Residents? You would only need to do that if you needed to allow both of the conditions:
A house can have more than one resident; and
A resident can live in more than one house.