CODEWITHSUNDEEP
phpmysql
DeDevelopers
DeDevelopers

Reputation: 591

Search records in a string

I have 2 tables, one table is of jobs and other is alert table, where keywords is saved.. I want to write a query that if a job is posted than search from alerts table and show all the records which have any value that is in job title ...

Like job Title is : Web Development
Alerts tables  : 1- web,php,java
                 2- Php,Development, Jquery
                 3- Java,Jquery

And query shows the record 1 and 2 from alerts table...

I tried "LIKE" but its not getting the results...

SELECT * FROM `dev_job_alerts` WHERE `jobTitle` LIKE '%Web Development%'

Where JobTitle is the column from alert table...

Please provide a useful soltuion

Upvotes: 0

Views: 41

Answers (2)

GordonM
GordonM

Reputation: 31780

A large reason for your difficulty is that you're serialising non-atomic data and storing it in a single field in your table. This is a violation of 1NF and the upshot is problems like the one you're encountering.

A better solution is to have a table of jobs, a table of keywords, and a third table that serves to link a given job to a given set of keywords. For example your job table might be: 

| job_id | job_title | job_description                      |
=============================================================
|      1 | Developer | Developer description goes here      |
|      2 | Manager   | Manager description goes here        |
|      3 | DBA       | Database Admin description goes here |
|      4 | etc       | etc                                  |

Keywords table:

| keyword_id | keyword_val |
============================
|          1 | Developer   |
|          2 | Manager     |
|          3 | Programmer  |
|          4 | PHP         |
|          5 | DBM         |
|          6 | MySQL       |
|          7 | etc         |

To link jobs to keywords you can use a linking table

| job_id | keyword_id |
=======================
|      1 |          1 | // Link "Developer" keyword to Developer job
|      1 |          4 | // Link "PHP" keyword to Developer job
|      1 |          6 | // Link "MySQL" keyword to Developer job
|      3 |          5 | // Link "DBA" keyword to DBA job
|      3 |          6 | // Link "MySQL" keyword to DBA job

With a structure like this you can easily get all the jobs that are linked to a given keyword. 

SELECT DISTINCT job.* 
FROM job
JOIN job_keyword ON job.job_id = job_keyword.job_id
JOIN keyword ON job_keyword.keyword_id = keyword.keyword_id
WHERE keyword.keyword_val = "MySQL"

You can also get jobs by multiple keywords, get all the keywords linked to a specific job, etc.

It may be a bit more work to set up, but in the long run using linking tables to model many-many relationships is a far more powerful solution than trying to serialise data into fields.

Upvotes: 1

Scott
Scott

Reputation: 180

I would check 'jobTitle'. SELECT * FROM 'dev_job_alerts' WHERE 'jobTitle' LIKE '%Web Development%'

'jobTitle' is a string, and not a column name.

Simply change to: SELECT * FROM dev_job_alerts WHERE jobTitle LIKE '%Web Development%'

Upvotes: 0

Related Questions