PHP - Presenting data problem .....?
Posted: Fri Oct 09, 2009 1:34 pm
Hi Chaps,
I have this code:
Which produces results from 3 different tables, and works fine.
I'm presenting the data in a 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
I have this code:
Code: Select all
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);I'm presenting the data in a table:
Code: Select all
<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>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