PHP loop through 2 MySQL tables?

[Guest]

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!

[Guest]

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.

[bregent · Accepted solution]

Add tbl_jobtransline to the query and join it to the other two tables? What exactly is the problem?

[Guest]

Hi,

Thanks for the reply, I guessed that I need to do something like that, but don't know how to do it.

tbl_jobs + tbl_jobtransline both use tbl_languaget + tbl_projects and I don't know how to incorporate this into an INNER/OUTER JOIN.

Can you give me an idea of how I can go about this?

Cheers

[bregent]

What are the common columns between all of the tables? Do you require an INNER or an OUTER join?

[Guest]

Not to worry dude, sorted it with the UNION syntax.

Cheers anyway...

[bregent]

littlemookie wrote:

Not to worry dude, sorted it with the UNION syntax.

Cheers anyway...

Cool