antham1616

sorry im quite new to coding, how do you mean?

im trying to produce a report which queries a database using three tables to pull all relevant data per user, the code works but it produces the data multiple times depending on the results.

the first row contains the job details

the second row would contain the first user details associated with the job

the third would be the next user associated with the job ETC

$result = mysqli_query($con,"SELECT mrbs_participants.username, mrbs_entry.id, mrbs_entry.start_time, mrbs_entry.name, mrbs_entry.fee, mrbs_users.display_name, mrbs_users.address, mrbs_users.town, mrbs_users.postcode, mrbs_users.home_number FROM mrbs_entry LEFT JOIN mrbs_participants ON mrbs_entry.id = mrbs_participants.entry_id LEFT JOIN mrbs_users ON mrbs_participants.username = mrbs_users.name where from_unixtime(start_time, '%Y-%m-%d') = CURDATE() AND driver = 'Ian King'");
/////////////////////start of job list/////////////////////
echo '<div id="Jim Watts">';
echo '<br><p><h1>Jim Watts.</h1></p><br>';
echo "<table border='1'>
<tr>
<th>Start Time</th>
<th>Job</th>
<th>Bus</th>
<th>Fee</th>
<th>Start Mileage</th>
<th>End Mileage</th>
<th>Passengers</th>
</tr>";
while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . date('l d F Y H:i',$row["start_time"]-=3600) . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . date($row["room_id"] +=1) . "</td>";
echo "<td>" . $row["fee"] . "</td>";
echo "<td>" . $row["Start Mileage"] . "</td>";
echo "<td>" . $row["End Mileage"] . "</td>";
echo "<td>" . $row["Passengers"] . "</td>";
echo "</tr>";
echo "<tr>";  
echo "<td>" . $row["username"] . $row["address"] . $row["town"] . $row["postcode"] . $row["home_number"] . $row["wheelchair"] . "</td>";

echo "</tr>";  
}
echo "</table>";

  ////// end of customer list////////////
//echo "<div style = 'page-break-after:always;'>";}
if ($result->num_rows > 0) {echo "<img src='images/bus_checks.jpg' alt='checks' width:1122px; height:794px/>";
        echo "</div>";}

but what happens is the job is produced with the first user

the the job is produced again with the next user and so on.

how do  make it display the job only once but list all associated users with this query

Hi, i dont know where im going wrong here, but when i get the results for the query it duplicates the results with a new instance for each associated result?

where i would expect:

1st line:     start time > job > Bus etc...

2ndline:   1st customer name > address etc....

3rd line:   2nd customer name > address blah blah

i actually get 

1st line:     start time > job > Bus etc...

2ndline:   1st customer name > address etc....

3rdline:   start time > job > Bus etc...

4thline:   1st customer name > address etc....

$result = mysqli_query($con,"SELECT mrbs_participants.username,mrbs_entry.room_id, mrbs_entry.id, mrbs_entry.start_time, mrbs_entry.name, mrbs_entry.fee, mrbs_users.display_name, mrbs_users.address, mrbs_users.town, mrbs_users.postcode, mrbs_users.home_number FROM mrbs_entry LEFT JOIN mrbs_participants ON mrbs_entry.id = mrbs_participants.entry_id LEFT JOIN mrbs_users ON mrbs_participants.username = mrbs_users.name where from_unixtime(start_time, '%Y-%m-%d') = CURDATE() AND driver = 'Tony'");
/////////////////////start of job list/////////////////////
echo '<div id="Tony">';
echo '<br><p><h1>Tony.</h1></p><br>';
echo "<table border='1'>
<tr>
<th>Start Time</th>
<th>Job</th>
<th>Bus</th>
<th>Fee</th>
<th>Start Mileage</th>
<th>End Mileage</th>
<th>Passengers</th>
</tr>";
$lastEntry = null;
while($row = mysqli_fetch_array($result))
{
  if (is_null($lastEntry) || $lastEntry != $row['id'])
    {
echo "<tr>";
echo "<td>" . date('l d F Y H:i',$row["start_time"]-=3600) . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . date($row["room_id"] +=1) . "</td>";
echo "<td>" . $row["fee"] . "</td>";
echo "<td>" . $row["Start Mileage"] . "</td>";
echo "<td>" . $row["End Mileage"] . "</td>";
echo "<td>" . $row["Passengers"] . "</td>";
echo "</tr>";
$lastEntry = $row['id']; }
   else 
   {echo "<span>" . $row["username"] . "</span>";}
   	
}
if (!is_null($lastEntry)) 

{
    echo "</td></tr>";
}
echo "</table>";

  ////// end of customer list////////////