从多个表循环信息时出现问题

Question

I am creating a page where I can list all of my jobs, both current and past, and the various raises/promotions from each. I would love to have the basic job information listed and then in a separate DIV list all the promotions. My problems arise when I loop them. For every job I want there to be multiple wage listings, but what is happening is that if I have more than one raise, it will list the Job Info for each wage increase. How do I get it to work the way I want it?

Here are the columns for each DB:

JobInfo ID | LocName | Address | Phone

JobPromotions ID | JobID | Date | Wage | Position

And my code:

<div id="CurrentJobs">
    <?php

    $CurrentJobsResults = mysql_query("
        SELECT JobInfo.*, JobPromotions.*
        FROM JobInfo
        LEFT JOIN JobPromotions
        ON JobInfo.ID=JobPromotions.JobID
        WHERE JobInfo.DateEnd = '0000-00-00'
        ");

    while ($row = mysql_fetch_array($CurrentJobsResults)) {
        echo "
            <div class='JobItem'>
                <div class='JobItem_Gen'>
                    <b>{$row['LocName']}</b>
                    <p>{$row['Street']}</p>
                    <p>{$row['City']}</p>
                    <p>{$row['State']}</p>
                    <p>{$row['Zip']}</p>
                    <p>{$row['Phone']}</p>
                </div>
                <div class='JobItem_Prom'>
                    <p>{$row['Date']}</p>
                    <p>{$row['Position']}</p>
                    <p>{$row['Wage']}</p>
                </div>
            </div>
        ";
    }

    ?>
</div>

Any help would be much appreciated as this is getting annoying!

Thanks!

Answer 1

The easiest way I can think of is to make two calls to the database so that you can have the job info and promotion info in two separate arrays.

while($job_rows = mysql_fetch_array($CurrentJobsResults_select))
{ $jobs[] = $job_rows; }

while($promotion_rows = mysql_fetch_array($CurrentPropmotionResults_select))
{ $promotions[] = $promotion; } 


foreach($jobs as $job_info)
{
    echo "<div class='JobItem'>
            <div class='JobItem_Gen'>
                <b>{$job_info['LocName']}</b>
                <p>{$job_info['Street']}</p>
                <p>{$job_info['City']}</p>
                <p>{$job_info['State']}</p>
                <p>{$job_info['Zip']}</p>
                <p>{$job_info['Phone']}</p>
            </div>";

    foreach($promotions as $promotion_info)
    {
        if($promotion_info['JobID'] == $job_info['ID'])
        {
           echo  "<div class='JobItem_Prom'>
                     <p>{$row['Date']}</p>
                     <p>{$row['Position']}</p>
                     <p>{$row['Wage']}</p>
                  </div>";
          }//if
     }//for each promotions
}//for each jobs

Something like that.

edit:
I assumed that the promotions table is linked to the jobs table via the JobID field. Also, I left out the code to query each db to get the seperate result strings