I have a lot of table INNER JOIN(ing) going on with this one and I have that part figured out. Basically, I am displaying certain emergency calls with limited information so the public can see what's going on in their area. I recently decided to INNER JOIN the units with the ticket table so people can see what units are assigned to certain calls and what their status is with the call.
I have the calls in a table. Each row is a different call. The problem I am running into when I add the units is that if a call has multiple units, it will display a different row for each unit even if two units in two rows are assigned to the same call. Example:
Ambulance 1 - Breathing Difficulty - Call# 1013 Engine 1 - Breathing Difficulty - Call# 1013 Ambulance 2 - Lift Assist - Call# 1012 Engine 2 - Lift Assist - Call# 1012
I would like to list all units involved in one row so the call information is only displayed once. Example:
Ambulance 1, Engine 1 - Breathing Difficulty - Call# 1013 Ambulance 2, Engine 2 - Lift Assist - Call# 1012
I have tried using the GROUP_CONCAT and GROUP BY methods from some tutorials. That works to show each call only once but it also only displays one unit for each call regardless if more than 1 is assigned to that call.
I'm thinking a big issue here lies in the fact that I have A LOT of information in this code between INNER JOIN(ing) multiple tables and what-not but that all is completely necessary in order to display the unit names (Ambulance 1) and not just an id number representing that unit (41).
A basic table I am working with is coded below without using GROUP_CONCAT or any GROUP methods. A huge thank you to anyone that is able to offer any advice as this one has really gotten me lost!
<table cellspacing=10>
<tr>
<th align="left">
<u>Assigned Units-Status</u>
</th>
<th align="left">
<u>City/Area</u>
</th>
<th align="left">
<u>Call Date/Time</u>
</th>
<th align="left">
<u>Call Description</u>
</th>
</tr>
<?php
// Connect to database server
mysql_connect("host", "db", "pass") or die (mysql_error ());
// Select database
mysql_select_db("dbname") or die(mysql_error());
// SQL query
$query="SELECT 911dbticket.id, 911dbticket.city, 911dbticket.problemstart, 911dbticket.description, 911dbassigns.ticket_id, 911dbassigns.responder_id, 911dbassigns.status_id, 911dbassigns.dispatched, 911dbassigns.responding, 911dbassigns.on_scene, 911dbassigns.clear, 911dbresponder.name
FROM 911dbticket
INNER JOIN 911dbassigns ON 911dbticket.id=911dbassigns.ticket_id
INNER JOIN 911dbresponder ON 911dbassigns.responder_id=911dbresponder.id
INNER JOIN 911dbun_status ON 911dbassigns.status_id=911dbun_status.id
WHERE `status` !='1' AND `in_types_id` NOT IN ('3', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14')
ORDER BY problemstart DESC";
// Execute the query (the recordset $rs contains the result)
$rs = mysql_query($query);
// Loop the recordset $rs
// Each row will be made into an array ($row) using mysql_fetch_array
while($row = mysql_fetch_array($rs)) {
echo "<tr><td><font color=orange>".$row['name']."</font></td><td><font color=green>".$row['city']." </font></td><td><font color=green> ".$row['problemstart']."</font></td><td width=500><font color=green><i>".$row['description']."</i></font></td></tr>";
}
// Close the database connection
mysql_close();
?>
Got it figured. I had no idea that the "GROUP_CONCAT" had to be included in the row results like this:
$row['GROUP_CONCAT(name)']
Instead of simply:
$row['name']
I was doing some more research and looking at some more examples and tutorials and I saw this in an echo and tried it. I was happily surprised to see "Ambulance 1, Engine 1" next to the same call on one row! haha
Thank you all so much for taking time to look this over and help me! I really appreciate it! Hopefully this post will help someone else who may come across this issue as well.