Help querying two tables - (join) possibly?

David_P

I have two tables - employment and duties. The employment table holds information like company, position, dates, etc. and the duties table holds the duties for the job the person just entered. Both tables have rid (resume id) and jid (job seeker id).

When a job seeker submits information from the employment form it's entered into the employment table and the employment_id (primary key, auto-increment) is captured via mysql_insert_id. The resulting page is a form with a single field named duty, which when submitted is entered into the duties table along with the employment_id. Once processed they have the option of adding another duty or moving on to another section of the resume.

I did it this way instead of simply adding a few duty fields named duty1, duty2, etc. on the employment form because I didn't want an excessive number of unused fields in the employment table or not enough in case the person had more duties to list than I had fields.

I thought about using a text area on the employment form, but I want to display the duties as a bullet list and you can't do this with a textarea unless you use an HTML editor, which I don't want to do. Even with an HTML editor there is no guarantee that everyone would use the bullet option on the editor. So the only way is to enter the duties individually.

My problem is querying the database to list all of that person's employment history with the duties listed in bullet form for each separate employment experience. For example:

Company A - Dallas, TX
Store Manager, Sep 2008 - Present

Hiring and training store personnel.
Ensuring sales goals are met.
Ensuring customer satisfaction.

Company B - Tucson, AZ
Asst. Store Manager, Jan 2006 - Sep 2008

Assisted store manager in hiring and training store personnel.
Assisted store manager in ensuring sales goals were met.
Assisted store manager in ensuring customer satisfaction.

One person may enter several jobs each one being assigned a unique employment_id number, and they may enter several duties for each job (employment_id number).

I've tried using several different query joins but I only get one result returned. Below is what I use to query just the employment table. The question I have is how do I also query the duties table to list the duties for each job and display them in a bullet list as in the examples above?

$rid = '1'; // resume id
$jid = '1'; // jobseeker id
$query = "SELECT * FROM employment WHERE jid='$jid' AND rid='$rid'";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$employment_id = stripslashes($row['employment_id']);
$company = stripslashes($row['company']);
$location = stripslashes($row['location']);
$position = stripslashes($row['position']);
$dates = stripslashes($row['dates']);

echo "<p>$company - $location<br />$position, $dates</p><ul>";

$query = "SELECT duty FROM duties WHERE employment_id='$employment_id' AND rid='$rid' and jid='$jid'";
$result = mysql_query ($query);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$duty = stripslashes($row['duty']);

echo "<li>$duty</li>";
}

echo "</ul>";
}

Thanks

David P

Weedpacket

Could you show your table structure? I'm wondering why you need to constrain the search of the duties table using rid and jid as well as employment_id (seeing as how you've already used the first two in the earlier query of the employment table).

Incidentally, when it comes to display, multiple queries like what you're doing are wasteful; you can get all that information from both tables in a single query (ordered by jid and rid). Then as you look at each row, look to see if it's part of the same section (i.e. has the same jid/rid pair) as the previous row - and if it doesn't, end the current section and start a new one.

Finally, convention dictates that I should point out that the MySQL extension is deprecated in favour of the [man]MySQLi[/man] or [man]PDO[/man] extensions.

David_P

Thanks for the reply. Here are the two table structures:

CREATE TABLE IF NOT EXISTS `employment` (
  `employment_id` int(11) NOT NULL auto_increment,
  `jid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  `company` varchar(155) NOT NULL,
  `city` varchar(45) NOT NULL,
  `state` varchar(2) NOT NULL,
  `country` varchar(45) NOT NULL,
  `position` varchar(155) NOT NULL,
  `begin_month` varchar(9) NOT NULL,
  `begin_year` int(4) NOT NULL,
  `end_month` varchar(9) NOT NULL,
  `end_year` varchar(7) NOT NULL,
  PRIMARY KEY  (`employment_id`)
);


CREATE TABLE IF NOT EXISTS `duties` (
  `duty_id` int(11) NOT NULL auto_increment,
  `employment_id` int(11) NOT NULL,
  `jid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  `duty` text NOT NULL,
  PRIMARY KEY  (`duty_id`)
);

Yes, my PHP book has a section on MySQLi but it means having to go through several dozen pages of code and changing them. I seem to recall that the querying order or something is different.

If I were better able to understand PHP and MYSQL then I could probably reduce the size of this program by half or more. But as it is I have to walk three miles to get to where I want to go because I don't have the ability to cut across the field saving two miles.

Once I get the complete program up and fully operational, I can go back and start changing things to MySQLi once I learn more about it. When I learn a better way of writing a piece of code I usually go back and update my work. I know I'm writing old code now but that's the best I can do at the moment.

I've read examples of joins on the Internet but I can't seem to be able to take the examples they use and apply it to my particular situation. And many of the tutorials are copies of another so you have different websites but they have the same tutorials.

David_P

After thinking about what you said, I see where you're right about not needing the jid and rid in the duties table since the employment_id number is unique. I appreciate the heads up on that.

Am I right in assuming that using a single query for the two tables like you're talking about, involves using a type of join? I've been reading about joins but I haven't been able to wrap my mind around it as of yet. But I'll keep trying.