Trouble with an Inner Joined Table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Sydcomebak
Forum Newbie
Posts: 1
Joined: Thu Jul 03, 2008 8:19 pm

Trouble with an Inner Joined Table

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Trouble with an Inner Joined Table

Post 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.
Post Reply