Selecting using a small subset of a table and another full table

[dirkadirka]

Im sure Im doing something simple wrong here, havent really come across this in my own work until now haha, thanks for any help

<?php
		 mysql_select_db($database_mydb, $mydb);
		 $query_Recordset2 = "SELECT * FROM resumes, facilities WHERE resumes.FULL_NAME = $Recordset1['NHA']";
         $Recordset2 = mysql_query($query_Recordset2, $mydb) or die(mysql_error());
		?>  

		<?php if ((($row_Recordset1['NHA']) != "") && ($row_Recordset1['NHA'] == $row_Recordset2['FULL_NAME'])){ ?>
		  <td nowrap> <a href="details.php?recordID=<?php echo $row_Recordset2['RESUME_ID']; ?>">
		  <?php echo $row_Recordset2['NHA']; ?> </a></td>
	      <?php $row_Recordset2 = mysql_fetch_assoc($Recordset2); ?>
		<?php	} else { ?>

	  <td nowrap> <?php echo $row_Recordset1['NHA']; ?></td> 


	<?php	} ?>

Im getting an error with the select statement, I just want to use the current 20 facilities I have, check to see if the field has anything in it, if it does, do a search through the resumes to find a match, then like the match if there indeed is one.

How do I do this?

Thanks again for help hehe!

[toxic_brain]

u may need to add single quotes in the WHERE condition in sql.

$query_Recordset2 = "SELECT * FROM resumes, facilities WHERE resumes.FULL_NAME  ='" .  $Recordset1['NHA'] . "'";

[dirkadirka]

hey thanks Im not getting the error anymore, not exactly working as intended either, but that could be a logic error I'm making, will have to look over it and let you all know! 🙂

[dirkadirka]

can anyone tell me why this isn't working? I dont get any sql errors however the output I am getting suggests that the IF statement is getting bypassed, meaning that the 2 fields do not equal each other...either the IF statement is wrong or the SELECT statement is, I'm guessing the SELECT statement.

<?PHP
 $query_Recordset2 = "SELECT * FROM resumes, facilities WHERE resumes.FULL_NAME = ' " . $Recordset1['NHA'] . "'"; 

     $Recordset2 = mysql_query($query_Recordset2, $mydb) or die(mysql_error());
	?>  
	<?php if ((($row_Recordset1['NHA']) != "") && ($row_Recordset1['NHA'] == $Recordset2['FULL_NAME'])){ ?>
	  <td nowrap> <a href="rd.php?recordID=<?php echo $Recordset2['RESUME_ID']; ?>">
	  <?php echo $Recordset2['NHA']; ?> </a></td>

	<?php	} else { ?>

	  <td nowrap> <?php echo $row_Recordset1['NHA']; ?></td> 


	<?php	} ?>

thanks!

[laserlight]

What's with $row_Recordset1? Could you give an overview of what you are trying to retrieve from the database?

By the way, it may be clearer to write it as:

<?php
$query_Recordset2 = "SELECT * FROM resumes, facilities
	WHERE resumes.FULL_NAME = '{$Recordset1['NHA']}'";

$Recordset2 = mysql_query($query_Recordset2, $mydb) or die(mysql_error());

if (($row_Recordset1['NHA'] != "") && ($row_Recordset1['NHA'] == $Recordset2['FULL_NAME'])) {
	echo '<td nowrap> <a href="rd.php?recordID=' . $Recordset2['RESUME_ID']
		. '">' . $Recordset2['NHA'] . '</a></td>';
} else {
	echo '<td nowrap>' . $row_Recordset1['NHA'] . '</td>';
}
?>

[dirkadirka]

$row_Recordset1 has a facility in it, retrieved earlier, this all happens within a while statement that is controlled by $row_Recordset1, there are 20 per page, so there will be 20 facilities in total in $Recordset1. Does that make sense? lol...I'm trying to find if the NHA field in the facilities database matches any of the FULL_NAME fields in the Resume table, then link back to the resume if it does.

[dirkadirka]

any thoughts?

[Kardall]

Why not toss a new query at it?

 $query_Recordset2 = "SELECT resumes.FULL_NAME, facilities.FULL_NAME FROM resumes, facilities WHERE facilities.FULL_NAME = resumes.FULL_NAME";

That should get only FULL_NAME from both tables and only if they match. use SELECT * if you need to get more than the FULL_NAME column out of the database.

[dirkadirka]

I also need to get the resumeID if those match so I can send a link to the actual resume...this does give me some idea's though.

[Kardall]

Well that's what we're here for 🙂 At any rate... All you should have to do if you need the ID is include it after the SELECT statement. 🙂

orrrr...

$query = "SELECT resumes.resumeID, facilities.FULL_NAME as f_fn, resumes.FULL_NAME as r_fn WHERE f_fn = r_fn";

// I added the 'assignments' of the tables to a variable to make it a little easier to read when you get later on in some of the heavier and more complex sql queries.
// if you want to get everything, you just do

$query = "SELECT * FROM facilities LEFT JOIN resumes ON resumes.FULL_NAME = facilities.FULL_NAME";

So basically, you end up with one HUGE table, with all information where the FULL_NAME's match up. You could then spit it out in a table and such. Just more things to throw at you for possibilities 🙂

[dirkadirka]

Well it works!!! woot, thanks, however it runs pretty slow from my computer when there are even a couple matches...it can have to do up to 40 searches per page (as there is 20 facilities per page and 2 different fields I must check)

Can anyone think of a way I can reduce my overhead? Here's the current code (well half of it, its repeated for a DON field)

<?php
	 $row_Recordset2 = '0';
         if ($row_Recordset1['NHA'] != "") {
              $query_Recordset2 = "SELECT resumes.RESUME_ID, resumes.FULL_NAME FROM resumes, facilities WHERE resumes.FULL_NAME = '"  . $row_Recordset1['NHA'] . "'";
          $Recordset2 = mysql_query($query_Recordset2, $mydb) or die(mysql_error());
	  $row_Recordset2 = mysql_fetch_assoc($Recordset2);}

if ($row_Recordset2['FULL_NAME'] == $row_Recordset1['NHA']) {
   ?>
		  <td nowrap> <a href="details.php?recordID=<?php echo $row_Recordset2['RESUME_ID']; ?>">
		  <?php echo $row_Recordset1['NHA']; 
		  mysql_free_result($Recordset2);
		   ?> </a></td><?php } else { ?>

      <td nowrap> <?php echo $row_Recordset1['NHA']; ?></td>


    <?php    } ?>

thanks everyone for the help!

[dirkadirka]

bah, I'm also having an issue with full names that have a ' character in it...

like Bob Mc'Dyess

So do I have to do a string function and strip all the ' characters before I do this select statement? sounds like more overhead to me, can anyone help me with the statement to strip the ' characters? thanks

[dirkadirka]

dirkadirka wrote:

bah, I'm also having an issue with full names that have a ' character in it...

like Bob Mc'Dyess

So do I have to do a string function and strip all the ' characters before I do this select statement? sounds like more overhead to me, can anyone help me with the statement to strip the ' characters? thanks

anyone? :quiet: