PHP - Presenting data problem .....?

Hi Chaps,

I have this code:

mysql_select_db($database_conndb2, $conndb2);
$query_rsJobs_Translation = "
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobs.jobid, 
tbl_languaget.langtname, 
tbl_jobs.jobwnet, 
tbl_jobs.jobtransih, 
tbl_jobs.jobtranscomplete,
tbl_jobs.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format
FROM 
tbl_projects 
INNER JOIN tbl_jobs 
ON tbl_projects.projid=tbl_jobs.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobs.FK_langid 
WHERE tbl_jobs.jobtransih='y' 
AND tbl_jobs.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobtransline.jobid, 
tbl_languaget.langtname, 
tbl_jobtransline.jobwnet, 
tbl_jobtransline.jobtransih, 
tbl_jobtransline.jobtranscomplete, 
tbl_jobtransline.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format 
FROM 
tbl_projects 
INNER JOIN tbl_jobtransline 
ON tbl_projects.projid=tbl_jobtransline.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobtransline.FK_langid 
WHERE tbl_jobtransline.jobtransih='y' 
AND tbl_jobtransline.jobtranscomplete='n'
)
UNION
(
SELECT 
tbl_projects.projid, 
tbl_projects.projtitle, 
tbl_projects.projdue, 
tbl_jobxml.jobid, 
tbl_languaget.langtname, 
tbl_jobxml.jobwnet, 
tbl_jobxml.jobtransih, 
tbl_jobxml.jobtranscomplete, 
tbl_jobxml.jobname,
DATE_FORMAT(tbl_projects.projstart, '%d/%m/%Y') as projstart_format, 
DATE_FORMAT(tbl_projects.projdue, '%d/%m/%Y') as projdue_format
FROM 
tbl_projects 
INNER JOIN tbl_jobxml 
ON tbl_projects.projid=tbl_jobxml.FK_projid 
INNER JOIN tbl_languaget 
ON tbl_languaget.langtid=tbl_jobxml.FK_langid 
WHERE tbl_jobxml.jobtransih='y' 
AND tbl_jobxml.jobtranscomplete='n'
)
ORDER BY
projdue ASC";

$rsJobs_Translation = mysql_query($query_rsJobs_Translation, $conndb2) or die(mysql_error());
//$row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation);
$totalRows_rsJobs_Translation = mysql_num_rows($rsJobs_Translation);

Code (markup):

Which produces results from 3 different tables, and works fine.

I'm presenting the data in a table:

<table border="0" cellpadding="0" cellspacing="0" id="tblrepeat">
<caption><p>Jobs for Translation</p></caption>
  <tr>
    <th scope="col">Due Date</th>
    <th scope="col">Language</th>
    <th scope="col">Title</th>
    <th scope="col">Translated</th>
    <th scope="col">Words - Net</th>
  </tr>
    <?php
  $previousProject = '';
  if ($totalRows_rsJobs_Translation > 0) {
  // Show if recordset not empty
    while ($row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation)) {
      if ($previousProject != $row_rsJobs_Translation['projid']) {
      // for every Project, show the Project ID
?>
  <tr>
    <td colspan="5" class="highlight"><span class="blueBold"><a href="#" onclick="toggle2('proj<?php echo $row_rsJobs_Translation['projid'] ?>', this)"><img src="../Images/plus.gif" border="0" /></a>&nbsp;<?php echo $row_rsJobs_Translation['projid'] ?>&nbsp;-&nbsp;</a></span><span class="blueNOTBold"><em><?php echo $row_rsJobs_Translation['projtitle'] ?></em></span></td>
    </tr>
  <?php $previousProject = $row_rsJobs_Translation['projid']; } ?>
  <tr class="proj<?php echo $row_rsJobs_Translation['projid'] ?>" style="display:none">
  			<td><?php echo $row_rsJobs_Translation['projdue_format']; ?></td>
            <td><?php echo $row_rsJobs_Translation['langtname']; ?></td>
            <td>
    <?php if ($row_rsJobs_Translation['jobname'] == 'Transline') { ?>
          <a href="jobsheet_trans_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&amp;proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php } else if ($row_rsJobs_Translation['jobname'] == 'XML'){ ?>
         <a href="jobsheet_xml_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&amp;proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php } else { ?>
         <a href="jobsheet_details.php?id=<?php echo $row_rsJobs_Translation['jobid']; ?>&amp;proj=<?php echo $row_rsJobs_Translation['projid']; ?>"><?php echo $row_rsJobs_Translation['jobname']; ?></a>
          <?php }?>           
    </td>
            <td><?php if ($row_rsJobs_Translation['jobtranscomplete'] == 'y') { ?>
                <span class="greenBold">Yes</span>
                <?php } else if ($row_rsJobs_Translation['jobtranscomplete'] == 'n') { ?>
                <span class="redBold">No</span>
                <?php } ?>            </td>
            <td><?php echo $row_rsJobs_Translation['jobwnet']; ?></td>
          </tr>
          <?php } while ($row_rsJobs_Translation = mysql_fetch_assoc($rsJobs_Translation)); ?>
      <?php } // Show if recordset not empty ?>
</table>

PHP:

Which should group the results by "projid", with collapsible rows for the job/jobtransline/jobxml results.

The problem is, the grouping doesn't seem to work, i.e., there are two rows for the same "projid", one for each "job"...

How can I solve this?

Cheers

 

Hi,

Sorry, I can't help but what I can see is a bad database design. When you design your database correctly, you will not have any trouble during your application implementation. Your query will be much faster and easier. Database design is important because it act as a backbone of your application. It's just an advice it might help some people.