PHP loop through 2 MySQL tables?
erHi Chaps,
I have a bit of PHP code, that loops through a MySQL table (jobs) and displays all records that have not been complete. The code and the MySQL query work fine.
What I want to know is whether the Query can be altered to loop through 2 tables.
Here is my current query:
mysql_select_db($database_conndb2, $conndb2);
$query_rsJobs = "
SELECT
tbl_projects.projid,
tbl_projects.projtitle,
tbl_jobs.jobid,
tbl_jobs.FK_projid,
tbl_jobs.jobname,
tbl_jobs.FK_langid,
tbl_languaget.langtname,
tbl_jobs.jobpages,
tbl_jobs.jobshipped
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.jobshipped='n'
ORDER BY
FK_projid ASC";
$rsJobs = mysql_query($query_rsJobs, $conndb2) or die(mysql_error());
//$row_rsJobs = mysql_fetch_assoc($rsJobs);
$totalRows_rsJobs = mysql_num_rows($rsJobs);
Here is my table that displays the results:
<table>
<tr>
<td>Document Title</td>
<td>Language</td>
<td>Pages</td>
<td>Edit</td>
<td>Remove</td>
</tr>
<?php
$previousProject = '';
if ($totalRows_rsJobs > 0) {
// Show if recordset not empty
while ($row_rsJobs = mysql_fetch_assoc($rsJobs)) {
if ($previousProject != $row_rsJobs['projid']) {
// for every Project, show the Project ID
?>
<tr>
<td><?php echo $row_rsJobs['projid'] ?></td>
</tr>
<?php $previousProject = $row_rsJobs['projid']; } ?>
<tr>
<td><a href="jobsheet_details.php?id=<?php echo $row_rsJobs['jobid']; ?>&proj=<?php echo $row_rsJobs['projid']; ?>"><?php echo $row_rsJobs['jobname']; ?></a></td>
<td><?php echo $row_rsJobs['langtname']; ?></td>
<td><?php echo $row_rsJobs['jobpages']; ?></td>
<td><a href="jobsheet_edit.php?id=<?php echo $row_rsJobs['FK_projid']; ?>&job=<?php echo $row_rsJobs['jobid']; ?>">Edit</a></td>
<td><a href="jobsheet_remove.php?id=<?php echo $row_rsJobs['FK_projid']; ?>&job=<?php echo $row_rsJobs['jobid']; ?>">Remove</a></td>
</tr>
<?php } while ($row_rsJobs = mysql_fetch_assoc($rsJobs)); ?>
<?php } // Show if recordset not empty ?>
</table>
And finally, this is the Query that I need adding to my current query to show the resutls from both tables:
mysql_select_db($database_conndb2, $conndb2);
$query_rsJobTrans = "
SELECT
tbl_projects.projid,
tbl_projects.projtitle,
tbl_jobtransline.jobid,
tbl_jobtransline.FK_projid,
tbl_jobtransline,
tbl_jobtransline.FK_langid,
tbl_languaget.langtname,
tbl_jobtransline.jobpages,
tbl_jobtransline.jobshipped
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.jobshipped='n'
ORDER BY
FK_projid ASC";
$rsJobTrans = mysql_query($query_rsJobTrans, $conndb2) or die(mysql_error());
//$row_rsJobTrans = mysql_fetch_assoc($rsJobTrans);
$totalRows_rsJobTrans = mysql_num_rows($rsJobTrans);
?>
I'm not sure if this is possible and have tried myself but seem to get in a bit of a muddle, as both tables use a Foreign Key from the Projects and LanguageT tables, any help would be most appreciated!
AdChoices