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']; ?>&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!

[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.

[kickstart]

Hi

 

Should be possible but could be inefficient (ie, for every row on the first query you need to execute the 2nd query).

 

Might be best to use JOINs to merge the 2nd query into the 1st query.

 

All the best

 

Keith

[kool_samule]

Hi Keith,

 

Thanks for the info, are JOINs different to INNER JOINs? I'm a newbie at this and not sure where to start.

[kickstart]

Hi

 

There are different kinds of JOINS.

 

An INNER JOIN is one type. An OUTER JOIN is another type.

 

The basic difference is what happens when there are no matching rows. An INNER JOIN wouldn't return a row, while an OUTER JOIN would return a row with some blank fields.

 

Eg, say you had a table of adults and a table of children, with one of the fields on the table of children being who their parent is:-

 

Adults

Adult

Bill

Joan

Fred

 

Children

Child, Parent

Amy, Bill

John, Bill

Wil, Joan

 

If you used an INNER JOIN to link those then Fred wouldn't appear :-

 

SELECT * FROM Adults INNER JOIN Children ON Adults.Adult = Children.Parent.

 

If you used an OUTER JOIN then you could have fred appear but with nothing in the columns for children

 

SELECT * FROM Adults LEFT OUTER JOIN Children ON Adults.Adult = Children.Parent.

 

I suspect what you need to bring back is everything from you first query and any matching rows (if they exist) from the 2nd query, and an OUTER JOIN would give you this.

 

I am unsure looking on your original queries how you would link these up. As your 2nd query doesn't appear to refer to anything brought back by the first query.

 

All the best

 

Keith

[kool_samule]

Hi Keith,

 

Thanks for that, I'll look into the LEFT OUTER JOIN method tomorrow.

 

The queries I posted are completely seperate, i.e., they are on two seperate pages, one for tbl_jobs, the other for tbl_jobtransline.

 

      tbl_projects

            |

  --------  --------

  |                    |

tbl_jobs    tbl_jobtransline

  |                    |

results            results

 

What I want it to merge the results from the two pages into one query/php loop on one page.

 

      tbl_projects

            |

  --------  --------

  |                    |

tbl_jobs    tbl_jobtransline

  |                    |                   

  -----------------

            |

        results

 

Hopefully that makes sense?

[Paystey]

If your just wanting to put the 2 displayed tables onto one page rather than two can't you just run the PHP for one after the other? Am I missing something? Just Copy/Paste the PHP code for one table right after the other.

[kickstart]

Hi

 

Agree with Paystey. If the queries are unrelated then just do them one after the other. However seems a bit unusual to display unrelated data at the same time.

 

All the best

 

Keith

[kool_samule]

Hi,

 

I want to merge the results from both queries into one table. The data is related, just stored in two tables.

 

I need to end up with a table what looks something like this:

 

projid          title          job type          (table)

 

3001            doc_title    normal              (tbl_jobs)

3002            doc_title    transline            (tbl_jobtransline)

3003            doc_title    normal              (tbl_jobs)

 

I hope this is clear and I haven't missed something obvious!?

[kool_samule]

Sorted it with using the UNION syntax.

[kickstart]

Hi

 

Yep, that sounds the best solution.

 

All the best

 

Keith