PHP - Presenting data problem .....?

[kool_samule]

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);

 

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> <?php echo $row_rsJobs_Translation['projid'] ?> - </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']; ?>&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']; ?>&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']; ?>&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>

 

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

[lemmin]

I would guess that the problem is in the way you are joining your tables. I would suggest relating the individual values instead of joining them.

[kool_samule]

Hi Lemin . . .

You say:

I would suggest relating the individual values instead of joining them.

How would I do this if I want the results to appear in the same table, grouped by "projid", then "projdue"?

[lemmin]

For that first Select, you should be able to change it to this and get the same results:

 

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,
   tbl_jobs,
   tbl_languaget
WHERE
   tbl_projects.projid = tbl_jobs.FK_projid AND
   tbl_languaget.langtid = tbl_jobs.FK_langid AND
   tbl_jobs.jobtransih='y' AND
   tbl_jobs.jobtranscomplete='n'

 

If you change them all to something like that instead, I don't think you will get the redundant data. At least, I think that will work. I'm not 100% sure what everything looks like.