[SOLVED] INNER JOIN works, but why can't I echo the results? - Cleaned up prev. question

[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.

 

 
<?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:

 

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

 

Am I missing something?

[.josh]

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

 

I'm thinking you may need to put some single quotes around $var like I did there, but the die should give you a clue if that's not it.

[Sydcomebak]

So... What does one say after messing with this for almost 2 hours with NO result and then some guy comes along and fixes it with a pair of apostrophes?

 

...

 

Thank-you!!!

 

I'm going to attempt to make these names linkable (passing the House_ID value on) and have the HouseCombined value display next to each name. I'm sure that I'll be back, but I'm marking this one SOLVED!

[.josh]

You don't technically need to pass a session var for it to work. It's a precautionary measure to make sure your var is authentic.

 

<?php
   session_start(); 

   // connect to db here 

   $result = mysql_query("SELECT * 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.NameID = ResidentTbl.Name_ID)
WHERE SUBSTRING(NameLast,1,1) = '$var'") or die(mysql_error());

while ($row = mysql_fetch_array($result) ) {
   echo "<a href = 'target.php?HouseID={$row['HouseID']}'>{$row['NameLastt']} {$row['NameFirst']}</a> {$row['HouseCombined']} <br />";
   $_SESSION['HouseID'] = $row['HouseID'];
}

 

target.php

<?php
   session_start();

   if($_SESSION['HouseID'] == $_GET['HouseID']) {
      // link var was successfully passed, and it matches the session var, so do something here
   } else {
      // something didn't match up.  url var don't exist or it don't match
      // the session var (like, someone tried to alter it directly)
      // send them back to previous screen, log it, tell them to frakk off, whatever
   }
?>