PHP loop through 2 MySQL tables?

[kool_samule]

Hi 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']; ?>&amp;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']; ?>&amp;job=<?php echo $row_rsJobs['jobid']; ?>">Edit</a></td>
    <td><a href="jobsheet_remove.php?id=<?php echo $row_rsJobs['FK_projid']; ?>&amp;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!

[eanimator]

Your question is not clear to me

[kool_samule]

Basically, I have two similar MySQL tables:
1. tbl_jobs
2. tbl_jobtransline

My current query will show results for tbl_jobs (that are incomplete). My PHP code loops through this query to display the results in the way I want.
What I need is to alter the query to show results for both tbl_jobs and tbl_jobtransline.

[kool_samule]

sorted it using the UNION syntax, thanks!

[eanimator]

YUP... use ident to identify the row too.

HAppy coding.