SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)

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

    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
    Code:
    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
    Code:
    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
    PHP Code:
    $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 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
    HTML Code:
    <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?

  2. #2
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    Thanks StarLion I would never of found that on my own

    The current output is:

    HTML Code:
    <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.

    PHP Code:
    $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"


  5. #5
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I have just found a small problem.
    These lines:
    HTML Code:
    <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:
    HTML Code:
    <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:
    PHP Code:
    echo "<td align=\"left\"> ".$row['cell_number']." <a href=\"display.php?stone=".$row['headstone']."\">".$row['names']."</a></td>\n"

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  7. #7
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

    PHP Code:
    $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"


  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,046
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  9. #9
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.

  10. #10
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  11. #11
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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!

    Code:
    $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
    HTML Code:
    <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.

  12. #12
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,046
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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:

    PHP Code:
    $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.
    The only code I hate more than my own is everyone else's.

  13. #13
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  14. #14
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,195
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •