SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP loop through 2 MySQL tables?

    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:
    Code:
    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:
    PHP Code:
    <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:
    Code:
    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!

  2. #2
    SitePoint Addict eanimator's Avatar
    Join Date
    Sep 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your question is not clear to me

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorted it using the UNION syntax, thanks!

  5. #5
    SitePoint Addict eanimator's Avatar
    Join Date
    Sep 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    YUP... use ident to identify the row too.

    HAppy coding.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •