Using LIKE with multiple tables and a join

[clint]

Good day all,

 

I hope you could help me in the right direction with my dillema. I have 3 tables and I am trying to show data based the user_id's of 2 tables being the same and keywords in 2 the same or at least similar. I have it working if the keywords match exactly but I dont know how to use the LIKE % in this sort of situation. Here is what I have and it works it the 2 colums are an exact match but I dont want exact match:

public function get_job_box($id) {

		$query = $this->db->prepare("SELECT *
FROM seek_details AS s
JOIN seek_employment AS e ON ( s.sd_user_id = e.se_user_id )
JOIN job_box AS j ON ( j.skill_keywords LIKE e.skill_keywords )
WHERE j.jb_user_id = ?");
		
		$query->bindValue(1, $id);

		try{

			$query->execute();

			return $query->fetchALL();

		} catch(PDOException $e){

			die($e->getMessage());
		}

	}

Any help or thoughts would be highly appreciated! Thank you

[Barand]

Can you show us some sample contents of the keywords fields from those two tables?

[clint]

Absolutely...

 

Basically the keywords are for the following function: There are 2 types of users, recruitment agency and job seeker (using this as a test to see how it works).

A job seeker will add skill keywords to the "seek_employment" table as in the above query that will define skills they have and will be something like, PHP, MySQL, Internet, Web Development......

 

A recruiter can then instead of constant searching, add what skills they are looking for to the "job_box" table as above. So if they are looking for a web developer and they add PHP, ASP, Web Development.....

 

The above query was for a page on the recruiters side where they can see the results of all the queries they have added to the job_box table and displays the name and contact number of the job seeker so you can view their profiles. It just eliminates searching and of course not all the kwywords will match in both tables. If that makes sense.....?

 

Additional info: Table(seek_details) contains the name, surname, etc of the job seeker. Table (seek_employment) contains the resume details including the job seekers keywords. table (job_box) contains the keywords of the recruiter.

 

Long story short, the keywords would be something like: PHP, MySQL, ASP, Accountancy.......  

 

Thanks again for the help.

[kicken]

LIKE does not do what you seem to think it does. LIKE is for basic wildcard matching, ie searching for %PHP would find anything ending in the string PHP. What you seem to want is something that will take a bunch of words listed in one column, and see if a second column contains those same words without regard to order. As far as I know, there is not any pure SQL way to do this.

 

Perhaps a better plan would be to change your skill_keywords columns into separate tables, with one row per key word. Then you could join based on that and get your desired results. If you keep it setup as is, you'd likely have to fall back to a not very pleasant PHP solution.

[clint]

Oh I see. Cool, thought I was getting clever

Will make some changes as you suggested. I suppose narrowing input options leaves less chance for something to go wrong at the end of the day in any case!

 

thanks for the advice. Much appreciated!