Jump to content

fetch associated duplicates results


antham1616
 Share

Recommended Posts

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

Start Time Job Bus Fee Start Mileage End Mileage Passengers
" . date('l d F Y H:i',$row["start_time"]-=3600) . " " . $row['name'] . " " . date($row["room_id"] +=1) . " " . $row["fee"] . " " . $row["Start Mileage"] . " " . $row["End Mileage"] . " " . $row["Passengers"] . "
" . $row["username"] . $row["address"] . $row["town"] . $row["postcode"] . $row["home_number"] . $row["wheelchair"] . "

 

$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

Link to comment
Share on other sites

Sounds like you have duplication in in your query results. 

Add a GROUP BY to your query.  If that works, figure out, figure out what's causing the dupes in the query. 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...