[RESOLVED] Combining 2 fields/join another table

[dirkadirka]

I'm trying to do a few things here.

I have 2 tables - resumes and facilities - with no matching fields, however I need to add the FIRST_NAME and LAST_NAME fields together from the resume database then match the result against 2 seperate fields in facilities that have full names in them, then if I do have a match, link the name to the actual resume (resumeID).

Now, I have several thousand of each record. After some thinking, it seems to me it would be a better idea to make a new field in resumes called FULL_NAME and combine all those fields first, then do an INNER JOIN to search for matches. Otherwise I'd be creating tons of overhead for each facility search no?

Basically -

1)join FIRST_NAME and LAST_NAME from resumes
2)check result vs. NHA and DON from facilities
3)link matches to resume details (going to need to pass the resumeID)

  

<?php do { ?>
<?php
		  mysql_select_db($database_mydb, $mydb);
		  $query_matches = "SELECT FULL_NAME
          FROM resumes INNER JOIN facilities
          USING NHA,DON";  

		  $Matches = mysql_query($query_matches, $mydb) or die(mysql_error());
		  if($Matches = $row_Recordset1['NHA'])
		  <a href="resumedetails.php?ResumeID= echo $row_Recordset1['Resume_ID'] ";>
		  else
		  <td nowrap> echo $row_Recordset1['NHA'];</td>
		  if($Matches = $row_Recordset1['DON'])
		  <a href="resumedetails.php?ResumeID= echo $row_Recordset1['Resume_ID'] ";>
		  else
          <td nowrap> echo $row_Recordset1['DON']; </td>
		  ?>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>

Now I just slapped that together and I really don't expect it to work, but its along the right lines? Thoughts? Any help greatly appreciated.

[devinemke]

perhaps i am not fully understanding what you are trying to do but would this not yeild the result you want?

SELECT *, CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) AS resumes_fullname FROM resumes, facilities WHERE resumes_fullname = facilities.NHA AND resumes_fullname = facilities.DON

[dirkadirka]

that looks pretty tight, what kind of results would that give though? How do I extract the ResumeID from matches? That code looks like it would work to me, returning matches from the NHA and DON fields, but how do I also return the ResumeID so that I can link said matches to their respective resumes?

Thanks for the help btw.

[devinemke]

if i understand your data structure correctly, my example query will return all of the data from both tables where the resume table's combined first and last name field equal the NHA and DON fields from the facilities table. all fields will be returned so you will have the ResumeID (along with everything else).

[dirkadirka]

so it will match the correct facility ID with the resume ID in a way that where I'm listing the facility, that has either one or both matching NHA/DON, I can link the match(es) to the respective resumes?

I guess I'm wondering what is the connecting link to the matches? I'd like to run this script once at the beginning, find the matches, then when listing all the facilities, the ones that have matches on the resume database get linked back to their source.

Thanks so much, this is really helpful and will help me more in the future...(and I hope other people can glean info from this thread)

[devinemke]

dirkadirka wrote:

so it will match the correct facility ID with the resume ID in a way that where I'm listing the facility, that has either one or both matching NHA/DON, I can link the match(es) to the respective resumes?

I guess I'm wondering what is the connecting link to the matches? I'd like to run this script once at the beginning, find the matches, then when listing all the facilities, the ones that have matches on the resume database get linked back to their source.

i don't understand your question. you said that there is no PK/FK relationship. the only correlation between the two tables is that the combined first and last names in resumes matches some of the NHA and DON names in facilities. this query will return all of those rows that match. as i said it is returning all of the fields, meaning that each row returned in the result set will have all fields from both of the tables.

what do you intend to do with the results? simply display them?

[dirkadirka]

For example, the page that will be processing this query is for the listing of facilities, 20 per page. The initial page is a table with the 20 facilities listed...now I want each time that one of the facilities has a matching NHA and/or DON in the resume database to link that listing back to the resume it matches. Otherwise it will just list what is in the facilities NHA/DON rows (which could be empty)

[devinemke]

dirkadirka wrote:

...now I want each time that one of the facilities has a matching NHA and/or DON in the resume database to link that listing back to the resume it matches. Otherwise it will just list what is in the facilities NHA/DON rows (which could be empty)

when you say "which could be empty" that needs clarification. some of the rows in resumes have FIRST_NAME and/or LAST_NAME empty? some if the rows in facilities have NHA and/or DON empty? and just to be clear, what is returned from the query will depend on what we mean here by "empty". if these are all string types (which i assume) then the query result will differ on whether or not these field accept NULL values (defined at table creation). there is difference between NULL and an empty string. for example, if all of these fields accept NULL and by "empty" you mean that they are in fact NULL then the query will not return any rows where any one of these field is NULL. if on the other hand the fields are empty strings then those rows will be returned. this is an important distinction. can you post the table creation SQL for these two tables?

[dirkadirka]

the first name and last name fields are all likely to be filled, however there will be many facilities with either one or both of the NHA, DON fields NULL.

The resumes database, or at least the questioned part

RESUME_ID is an int, primary key, auto increment
FIRST_NAME is a varchar(50), NULL (default)
LAST_NAME is a varchar(50), NULL (default)

The facilties database -

FACILITY_ID is an int, primary key, auto increment
NHA is a varchar(255), NULL(default)
DON is a varchar(255), NULL(default)

Ive tried running your code/modifying it, but can't seem to get it to work...something with the logic always comes up wrong. the current error message is this -

You have an error in your SQL syntax near 'FROM resumes, facilities WHERE = facilities.NHA AND = facilities.DON'

<?php
		  $resumes_fullname = "";
                  $query_Recordset2 = "SELECT *, CONCAT(resumes.FIRST_NAME, ' ',   

                  resumes.LAST_NAME) AS $resumes_fullname FROM resumes, facilities WHERE 
                  $resumes_fullname = facilities.NHA AND $resumes_fullname = facilities.DON";
                  $Recordset2 = mysql_query($query_Recordset2, $mydb) or  die(mysql_error());
?>

Now I've tooled around with that and gotten it to give me some strange stuff, like it finally started listing stuff like - Resource ID#4 Resource ID#5...like its concatinating those two things instead of the names in the resumes database...I've also gotten it to give me all 10's lol...don't ask.

I'm just flat out confused now. 🙂

[devinemke]

you need to remove all of those dollar signs ($) from your query. you do not want that variable expansion within the query. re-examine for first post above.

[dirkadirka]

devinemke wrote:

you need to remove all of those dollar signs ($) from your query. you do not want that variable expansion within the query. re-examine for first post above.

when I had no dollar signs, it said, unknown variable resumes_fullname. so i made it one. I'll revert it back but suspect I'll get the error.

[dirkadirka]

specifically it said this -

Unknown column 'resumes_fullname' in 'where clause'

[devinemke]

try it this way:

SELECT * FROM resumes, facilities WHERE CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.NHA AND CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.DON

[dirkadirka]

that seems to work but it only works if the name is in the resume database and in BOTH the NHA and DON fields (that will never happen, there will either be different people in each slot, one person in one slot, or NULL values in their places)

When I have both NHA and DON filled with the same name, I can get a match out of my resume database, otherwise no dice.

[devinemke]

then simply change the "AND" to "OR"

SELECT * FROM resumes, facilities WHERE CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.NHA OR CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.DON

[dirkadirka]

devinemke wrote:

then simply change the "AND" to "OR"

SELECT * FROM resumes, facilities WHERE CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.NHA OR CONCAT(resumes.FIRST_NAME, ' ', resumes.LAST_NAME) = facilities.DON

heh, yeah, I dunno why I put that there, I already did that 🙂

Thanks, now I've got to get it to work for each individual facility and print out the link to the proper resume. right now its just putting the resume_id (checking to see if its the right one) on all the fields, not just that matching one.

I should be able to take it from here, I hope!!!

Thanks, you're a life saver.

[dirkadirka]

well I've decided to instead CONCAT all the first names and last names in the Resumes database into a new field so I can cut down on overhead, no sense to add those every single time the page is loaded.

I've made it so the original input page does that automatically but I do have a question, I'd like to go through all the old records and add the first names and last names at one time and put them in a new field. I use phpmyadin and you can run sql queries from there, could you help me with what queries I'd use to do that?

thanks man!! almost done.