Hi I am working on a search for a little system that we use in work. The systems uses 3 tables, quotes, jobs and details. within these tables there are several fields but th eone im focusing on are as follows:
Here is a sqlfiddle for my table and mysql statement
Quotes
- quote_id
- quotes_title
Jobs
- job_id
- job_quoteid - job_quoteid stores the id of a quote which the job is realted to
Details
- details_id
- details_line
- details_quoteid - details_quoteid stores the id of a quote which the detail relates too, there can be several details linked to the quote
My search is then to look at each of the tables based on my search term that is typed in and display the quote_id, job_id, quote_title and details_line.
here is the code i have currently for my search:
$fetch = mysql_query("SELECT * FROM quotes AS qts
LEFT JOIN jobs AS jbs ON qts.quote_id=jbs.job_quoteid
JOIN details AS dts ON qts.quote_id=dts.quote_detailsid
WHERE concat(' ',qts.quote_code,qts.quote_id) like '%$param%'
OR qts.quote_title REGEXP '^$param'
OR concat(' ',jbs.job_code,jbs.job_id) like '%$param%'
OR dts.details_line LIKE '%$param%'
");
while ( $row = mysql_fetch_object( $fetch ) ) {
$sResults .= '<tr id="'. $row->job_id . '">';
$sResults .= '<td><a href="../../jobs.php?action=viewJob&job_id='. $row->job_id .'">'. $row->job_code . $row->job_id .'</a></td>';
$sResults .= '<td><a href="../../quotes.php?action=viewQuote&quote_id='. $row->quote_id .'">' . 'Q' . $row->quote_id . '</a></td>';
$sResults .= '<td>' . $row->quote_title . '</td>';
$sResults .= '<td>' . $row->details_line . '</td>
</tr>';
}
Now this is where I need some further assistants, the search works, but for example if i was to type in "NCR" as a search term it will show 4 results, this is because I have a quote_title of "NCR Sets" and this quote has 4 detail_line attahced to it (these detail_line dont contain the words NCR) but obvioulsy its displaying 4 because it has found that the quote_id that contains the word "NCR" is in each of those. My results page therefore has the 4 results each in a table row with the quote_id, job_id, job_title and detail_line being diplayed, what I would like it to look is to have the 4 details display under one job_id and quote_id. If i was to code the table manually I would do somehting like the following to give you an idea:
<table>
<tr>
<td>quote_id</td>
<td>job_id</td>
</tr>
<tr>
<td>quote_title</td>
</tr>
<tr>
<td>details_line</td>
<td>details_line</td>
<td>details_line</td>
<td>details_line</td>
</tr>
</table>
I hope this makes sense and some one can push me in the right direction.
Thanks in advance.
Ian