0

I have a "jobs" table where each job can have multiple specialties. So I have a specialties table with a list of all available specialties and I have a lookup table that just has a job_number and a specialty_code in it so each job can be attached to multiple specialties. What I am trying to figure out is when I do a db_select, how can I have the job record print out once, but have a list of it's specialties print out also as part of that record? Here is what I have right now for my query:

$query = db_select("jobs", "j");
$query->fields('j');
$query->join('job_specialties', 'js', 'j.job_number = js.job_number');
$query->join('specialties', 's', 's.specialty_code = js.specialty_code');
$result = $query->distinct()->execute();

This gives me the correct records, but I want to display the list of specialties each job is attached to and that's where I'm not sure how to do that. Is there even a way using the database API to do that?

Essentially I would want to be able to display the records something like this. This is only one record, but you get the idea.

<table>
  <thead>
    <tr>
      <th>Job Number</th>
      <th>Title</th>
      <th>Location</th>
      <th>Specialty</th>
    </tr>
  </thead>
  <tr>
    <td>12345</td>
    <td>Some Cool Job</td>
    <td>California</td>
    <td>SQL<br>HTML5<br>JavaScript</td>
  </tr>
</table>
0

Add more fields to your query from the other tables that you have joined to:

$query->fields('js');
$query->fields('s');

Use hook_menu() to create your page if you want a custom one, then render it in PHP (I'm guessing at your result variable):

<table>
  <thead>
    <tr>
       ....
    </tr>
    <tr>
      <th><?php>$result['job_number']<?></th>
      .....
 </table>
3
  • Thanks for the response. I actually know how to add fields and display the data. The problem is right now if I add in $query->fields('s'); which will give me all of the fields in the specialties table then the jobs that are attached to multiple specialties get output multiple times in the result set. What I would like it to do is output the job ONCE and then have a delimited list of specialties that I can display for that job record. That's why I added distinct() into the query because the jobs were showing up multiple times for those attached to multiple specialties. – Erich H. Oct 28 '14 at 23:43
  • I did however add $query->fields('s'); which then spits out all of the specialty fields along with the job record as expected, but like I said multiple job records come back with the same job id. I just wanted to see if there was a way to have it return ONE job record with a "list" of specialties in a "specialties" field so I can display that on the front end for each job. – Erich H. Oct 28 '14 at 23:45
  • Sounds like you need multiple queries, one for the job info, then another to gather all of the specialties using the job id as a parameter, rather than joining the tables. You can get SQL to return such things using sub-queries and the like, but it's easier to just grab the info and then manipulate it in PHP. – Darvanen Oct 28 '14 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.