Join two tables but the output row can contain two results from one table.

[Rubble]

I might be going about this the wrong way but this is what I have so far:

I am creating a html table for a graveyard layout and have a mysql table with the persons details and have another mysql table for the location in the graveyard/html table.

I am using a simple join on the mysql tables and if there is one person in one plot/html cell all is OK but sometimes there are 3 or 4 people in each plot/html cell and they come out as seperate cells on the html table.

This is the information for each person

CREATE TABLE IF NOT EXISTS `individual` (
  `ID` int(3) NOT NULL AUTO_INCREMENT,
  `table_cell` int(3) NOT NULL,
  `surname` varchar(20) NOT NULL,
  `forename` varchar(20) NOT NULL,
   PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

This is for the location in the html table

CREATE TABLE IF NOT EXISTS `table_cell` (
  `ID` int(3) NOT NULL AUTO_INCREMENT,
  `cell_number` smallint(3) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

This is my SQL query

$query  = "SELECT
table_cell.ID,
table_cell.cell_number,
individual.table_cell,
individual.surname,
individual.forename
FROM
table_cell,
individual
WHERE
table_cell.ID = individual.table_cell
ORDER BY
table_cell.ID ASC";

$result = @mysql_query( $query );
if($result === FALSE) {
die(mysql_error());
}

$i = 0;
echo "<tr>\n";
while($row = mysql_fetch_array($result)){
if($i > 0 and $i % 12 == 0) {
 echo "</tr>\n<tr>\n";
}

// Bodge for empty cell
if ( empty($row['cell_number'] )) { $number = "<br/>"; } else $number = $row['cell_number'] ;
echo "<td align=\"left\">$number <a href=\"display.php?stone=".$row['headstone']."\">".$row['forename']." ".$row['surname']."</a></td>\n";
    
$i++;
  } 

HTML output

<tr>
<td align="left">288 <a href="display.php?stone=000">D Gipson</a></td>
<td align="left">288 <a href="display.php?stone=000">N Gipson</a></td>
<td align="left">262 <a href="display.php?stone=000">John Fisher</a></td>
<td align="left">236 <a href="display.php?stone=000">R Reynolds</a></td>
<td align="left">210 <a href="display.php?stone=000"> Path</a></td>
<td align="left">158 <a href="display.php?stone=000"> </a></td>
<td align="left"><br/> <a href="display.php?stone=000"> </a></td>
<td align="left"><br/> <a href="display.php?stone=000"> </a></td>
<td align="left"><br/> <a href="display.php?stone=000"> </a></td>
<td align="left"><br/> <a href="display.php?stone=000">Tree stump</a></td>
<td align="left">27 <a href="display.php?stone=000">William Jacklin</a></td>
<td align="left">27 <a href="display.php?stone=000">Georgina </a></td>
</tr>

As you can see the first two items should be in one cell and the last two items should be in another cell.
What would be the best way to overcome the problem of extra html cell generation?

[Rubble]

Just thought I had better mention the rows in the html code that do not have a name are ment to be empty. The graveyard is not a completly square and as you can see there is a path and tree stump as well as empty plots. The plots are also numbered verticaly from the righthand side which is why it goes from 288 to 262 etc.

[StarLion]

Narrow the number of rows.

$query = "SELECT
table_cell.ID,
table_cell.cell_number,
GROUP_CONCAT(CONCAT_WS(" ",individual.surname, individual.forename) SEPARATOR ',') AS names
FROM
table_cell,
individual
WHERE
table_cell.ID = individual.table_cell
GROUP BY
table_cell.ID
ORDER BY
table_cell.ID ASC";

(I removed individual.table_cell from your select returns; you've specified table_cell.ID = individual.table_cell and then returned both values. That's unnecessary.)

[Rubble]

Thanks StarLion I would never of found that on my own

The current output is:

<tr>
<td align="left">288 <a href="display.php?stone=000">D Gipson<br/>N Gipson</a></td>
<td align="left">262 <a href="display.php?stone=000">John Fisher<br/>2005</a></td>
<td align="left">236 <a href="display.php?stone=000">R Reynolds</a></td>
<td align="left">210 <a href="display.php?stone=000"> Path</a></td>
<td align="left">184 <a href="display.php?stone=000"> Disbury</a></td>
<td align="left">158 <a href="display.php?stone=000"> </a></td>
<td align="left"> <a href="display.php?stone=000"> </a></td>
<td align="left"> <a href="display.php?stone=000"> </a></td>
<td align="left"> <a href="display.php?stone=000"> </a></td>
<td align="left"> <a href="display.php?stone=000">Tree stump</a></td>
<td align="left">27 <a href="display.php?stone=000">William Jacklin<br/>Georgina Jacklin</a></td>
<td align="left"> <a href="display.php?stone=000">Robin Hood Tree 1220</a></td>
</tr>

I had to make a change by escaping " and that was it; just need to input the rest of the data now
Couple of format changes as well to neaten up the output.

$query  = "SELECT 
table_cell.ID, 
table_cell.cell_number, 
GROUP_CONCAT(CONCAT_WS(\" \",individual.forename, individual.surname) SEPARATOR '<br/>') AS names,
individual.headstone
 FROM 
table_cell, 
individual 
WHERE 
table_cell.ID = individual.table_cell
 GROUP BY
 table_cell.ID 
ORDER BY 
table_cell.ID ASC"; 

[Rubble]

I have just found a small problem.
These lines:

<td align="left">288 <a href="display.php?stone=000">D Gipson<br/>N Gipson</a></td>
<td align="left">262 <a href="display.php?stone=000">John Fisher<br/>2005</a></td>

Should be:

<td align="left">288 <a href="display.php?stone=000">D Gipson</a><br/><a href="display.php?stone=000">N Gipson</a></td>
<td align="left">262 <a href="display.php?stone=000">John Fisher<br/>2005</a></td>

As the different people can have different headstones.

The display line:

echo "<td align=\"left\"> ".$row['cell_number']." <a href=\"display.php?stone=".$row['headstone']."\">".$row['names']."</a></td>\n"; 

[StarLion]

So; here's an exercise in thought for you.
Take a look at MySQL's CONCAT function; you can use it to construct your output for you. (CONCAT_WS wont work)

You can also do it via PHP, but that's going to require a bit more finegaling.

[Rubble]

I can not see this at the moment and will have another go tomorrow. I have lost the second person in my cell; at least I do not have them in a seperate cell of their own!

I would guess I have a problem with my GROUP BY.

$query  = "SELECT 
table_cell.ID, 
table_cell.cell_number, 
table_cell.background,
CONCAT(individual.forename, ' ', individual.surname) AS names,
individual.headstone
 FROM 
table_cell, 
individual 
WHERE 
table_cell.ID = individual.table_cell
 GROUP BY
 table_cell.ID 
ORDER BY 
table_cell.ID ASC"; 

[oddz]

The group_concat() function is what you are after. The concat function itself would only give you a single arbitrary individual from each set of grouped rows.

[Rubble]

Thanks oddz

I did try the group_concat as well but kept going around in circles. Either I had both names and one link or one name and one link; even at one point everybody in one link!

As I say I will have another go later as I had a long day yesterday. Tried to photograph Christmas lights after work; but the weather was very cold and there was some mist/fog around which made it harder.

[StarLion]

The group concat you originally had was correct.

The inner concat function is what you needed to fix.

Outer function's job is just to stick the <br />'s in between entries.
The inner function should construct each of those entries how you want - keep in mind that you can concat static strings.

[Rubble]

Thank you very much for the help and "hints", to be honist I was looking for a more complicated way to do the concat with one concat inside another!

$query  = 'SELECT 
table_cell.ID, 
table_cell.cell_number, 
table_cell.background,
GROUP_CONCAT( CONCAT( "<a href=\"display.php?stone=", individual.headstone, "\">", individual.forename, " ", individual.surname, "<\a><br/>") ) AS names
 FROM 
table_cell, 
individual 
WHERE 
table_cell.ID = individual.table_cell
 GROUP BY
 table_cell.ID 
ORDER BY 
table_cell.ID ASC';

Output

<tr>
<td align="left">288<a href="display.php?stone=000">D Gipson<a><br/>,<a href="display.php?stone=000">N Gipson<a><br/></td>
<td align="left">262<a href="display.php?stone=000">John Fisher<br/>2005<a><br/></td>

I have an extra , between the names and do not know where that is coming from.

[oddz]

It is probably safe to make <br /> the delimiter for GROUP_CONCAT rather than placing it within CONCAT. Placing it within CONCAT means a single break will always exist after the last individuals name. What you want is to use it as a delimiter for GROUP_CONCAT. The documentation I provided above will go over the different options including changing delimiter from the default (,) to anything else.

What I would probably do though is create a list of names with the query than within the application language explode and create links from the names. Placing the HTML within the query is pretty damn dirty and decreases reuse.

Also, be aware that GROUP_CONCAT has a limit. Any data over that limit will automatically be truncated. For small result sets which can be controlled that isn't an issue but say there were a 1000 names per a row it is likely some data would be lost.

So long as pagination is not necessary you could essentially remove grouping and format the data within the application language. So that each unique cell exists in an array once and each of those cells has many individuals in a nested array. The method for doing that would be rebuilding a result set but making sure that if a cell already exists in it not adding it again to the array. Here would be some pseudo code for that:

$cells = array();

while($row=mysql_fetch_assoc()) {
    
    if(!isset($cells[$row['id']])) {
        $cells[$row['id']] = array(
            // ... cell data
            'individuals'=> array() // individuals belonging to cell
        );
    }
    
    $cells[$row['id']]['individuals'][] = array(
        // individual data
    )
    
} 

If you needed to support pagination than you could remove the join only querying the cells table. Than collect all the cells primary keys. Than use another query against the individuals table to fetch all the individuals for the cells which are going to displayed. Once that is done you would have two result sets. One for cells and another to individuals. So you would than map the individuals to cells within PHP. The primary down side of this would be an extra query but it would be necessary if cell pagination was required.

[StarLion]

as oddz said, the <br> at the end of the outer query in post #4 would be the way to separate your entries. the comma is the default separator.

And yes, you certainly -could- do it through PHP. It all depends on how much data you're looking at using/moving/etc.

[Rubble]

Originaly I thought I would have to do most of the work with php as I did not know about the above method StarLion introduced me to.

I am not sure where I am going with this code; currently I have everything in a hardcoded html table and wanted to find a better and more portable method.

When the user selects an individual a photo of the headstone is displayed and as much of the text I can read - currently in a text file linked to the headstone number.
I may introduce more detail like DOB, death etc. later and it might be nice to link the differnt people together in family groups if they are in a family.

Thanks again.