How to write a query that selects / displays form mysql based on users information / input?

Question

I have two tables, users and jobs. I'm trying to write a query that selects and displays a job a user might want based on their input / information. How can I write this query if the data is in two tables?

I'll be displaying the data in a while loop that creates and information card for each match.

any help is much appreciated.

try {

  $sql = $dbh->prepare("SELECT * FROM jobs  WHERE users");

  if($sql->execute()) {
   $sql->setFetchMode(PDO::FETCH_ASSOC);
 }
}
catch(Exception $error) {
  echo '<p>', $error->getMessage(), '</p>';
  }

?>

DB structures: users

user_id` int(5) NOT NULL AUTO_INCREMENT,
`user_name` varchar(25) NOT NULL,
`user_email` varchar(35) NOT NULL,
`user_pass` varchar(255) NOT NULL,
`user_phone` bigint(20) NOT NULL DEFAULT '99',
`user_gender` varchar(20) NOT NULL,
`user_profession` varchar(64) NOT NULL,
`user_min_salary` int(5) NOT NULL,
`user_dob` varchar(10) NOT NULL,
`user_location` varchar(64) NOT NULL,

DB structure jobs:

 `vac_id` int(11) NOT NULL AUTO_INCREMENT,
 `vac_post_date` varchar(10) NOT NULL,
 `vac_deadline` varchar(10) NOT NULL,
 `vac_job_title` varchar(64) NOT NULL,
 `vac_comp_name` varchar(64) NOT NULL,
 `job_description` text NOT NULL,

How exactly your output should look like? I.e., what exactly do you want to display? Show us the structures of both tables as well. Give this a read as well: http://stackoverflow.com/questions/10269809/mysql-join-query — Sergey Vidusov, Feb 20 '16 at 06:34
can u elaborate it more regarding what type of input information will the user give and what columns are in the both tables — pritesh, Feb 20 '16 at 06:34

MikeT · Answer 1 · 2016-02-20T18:31:09.147

I think you need to consider and cater for what the link between the job and the users is to be.

Say for example the job_description included mechanic and the user_profession was mechanic then you would have a link between the two tables and you'd then be able to create a query or queries that retrieved the data from the two tables according to that link(match).

To summarise, from what you have shown, you very likely need to think about what will link the two sets of data and then probably amend your tables to cater for the link. Perhaps job_description and user_profession could meet your requirements. However this would likely be very limited.

Kez, regarding your comment, then you'd be looking to JOIN the tables the ON clause perhaps (limitations may restrict this, eg can't do CONTAIN for both) to link the tables. Something along the lines of SELECT * FROM users JOIN jobs ON job_description CONTAINS user_profession;

However, the above would restrict a user to only 1 profession. Note! I haven't checked the SQL so take it more as a pointer.

There are also various flavours of JOIN so perhaps reading up on JOIN could help.

That's a very good point, and the connection between the data is exactly as you suggested between the job title and the users profession. — Kez, Feb 20 '16 at 11:34
Sorry I forgot to say that I've edited the answer in response to your comment. — MikeT, Feb 20 '16 at 19:01

How to write a query that selects / displays form mysql based on users information / input?

1 Answers1