#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45

    (SOLVED:GROUP_CONCAT) Showing mutiple MYSQL column values as a comma-separated string


    I'm not sure if this belongs in the PHP or MySQL forum, so feel free to move if appropriate.

    Using temporary tables, I have gotten the data close to the way I want it, so I won't bog you down with all of that. I'll just jump right in to where I'm stuck.

    Basically what I need a simple datadump that shows a page listing for each of our available open positions.

    Output table columns are:
    • Position Name
    • Total number of openings in database
    • Page # for each location page that currently shows opening


    Example Output:

    PHP Code:
    echo "<tr>
                <td>Computer Repair</td>
                <td>7</td>
                <td>2,7,9,15,27,44,71</td>
            </tr>" 
    This query contains all the information I need, but not grouped together. I'll explain, but here's the query.

    PHP Code:
    SELECT 
        open_positions
    .position,
        
    facility_pages.facility_page
    FROM 
        open_positions

        
    facility_pages
    WHERE 
        open_positions
    .facility_ID=facility_pages.facility_ID
    ORDER BY
        open_positions
    .positionfacility_pages.facility_page
    This gives me one row for each open position, and the page number associated with that. So my question is, how do I get this:

    Code:
    | Computer Repair | 2 |
    | Computer Repair | 7 |
    | Computer Repair | 9 |
    | Computer Repair | 15 |
    | Computer Repair | 27 |
    | Computer Repair | 44 |
    | Computer Repair | 71 |
    | PHP Programmer | 4 |
    | PHP Programmer | 15 |
    To output like this:

    Code:
    <tr>
        <td>Computer Repair</td>
        <td>7</td>
        <td>2,7,9,15,27,44,71</td>
    </tr>
    <tr>
        <td>PHP Programmer</td>
        <td>2</td>
        <td>4,15</td>
    </tr>
    Thoughts?
    Last edited by BlackAce; October 5th, 2012 at 10:10 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,350
    Rep Power
    594
    Use PDO to query your database then output each fetched row in a loop with <td> tags for your table.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Originally Posted by gw1500se
    Use PDO to query your database then output each fetched row in a loop with <td> tags for your table.
    Suddenly I feel like I'm drowning in the deep end of the pool...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,350
    Rep Power
    594
    So you are saying you don't know how to access a database from PHP? Perhaps this is where you should start.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Originally Posted by gw1500se
    So you are saying you don't know how to access a database from PHP? Perhaps this is where you should start.
    Correct, I have never used PDO. I use mysql_query for all of my database connections. Is PDO the only method available for grouping my responses together? I thought there might be a way to store the page values in an array that could then be imploded using ','.

    But based on your response, maybe it's not that simple...
  10. #6
  11. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    Originally Posted by BlackAce
    Correct, I have never used PDO. I use mysql_query for all of my database connections. Is PDO the only method available for grouping my responses together? I thought there might be a way to store the page values in an array that could then be imploded using ','.

    But based on your response, maybe it's not that simple...
    Hi BlackAce, you CAN use mysql, mysqli or PDO as gw1500se's mentioned ... yes "implode" is also the right tool for the concatenation.

    Use mysql_query to grab the data, shove it into an array and then implode it ... sorry for lack of detail, just about to go get my dinner but will post back in a bit more detail shortly
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Originally Posted by badger_fruit
    sorry for lack of detail, just about to go get my dinner but will post back in a bit more detail shortly
    I'll allow it.

    Looking forward to you fleshing this out a bit. I've queried the database to get the output shown above. Where I'm stuck is trying to take those results and group them together with a count, so they display the way I've been asked to show them.

    I started with this before I posted here:

    PHP Code:
    while($row=mysql_fetch_assoc($results)) {
        foreach(
    $row['position'] as $position) {
            
    $facility_pages[] = $row['facility_page'];
        }
        
    mysql_query("INSERT INTO position_facility_pages (id, position, facility_page) VALUES (NULL, '$position', '".implode(',',$facility_pages)."')") or die(mysql_error());

    But the foreach doesn't work, because I don't know how to tell the loop to only insert one row per position name. Once I get that figured out (position_facility_pages is a temp table just used to contain that data), I will need to join it to m results that include the count i.e:

    Code:
    SELECT 
    	COUNT(open_positions.position) as openings,	
    	open_positions.position,
    	position_facility_pages.facility_page
    FROM 
    	open_positions, 
    	position_facility_pages
    WHERE 
    			open_positions.position=position_facility_pages.position
    GROUP BY
    	open_positions.position
    Etc... Once all of those elements are joined, I should be able to loop through and output the table appropriately. I don't need help on that, I just don't know how to get my data correctly into the position_facility_pages temp table (if that's the best approach.)

    This data will only be accessed by a single server admin, so I'm not terribly concerned with server load, but would still like to be as efficient as possible.
    Last edited by BlackAce; October 4th, 2012 at 01:59 PM.
  14. #8
  15. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    OK, so your while ($row = mysql_fetch_assoc($result)) is the loop, you don't need another one inside of it to achieve what I think you want to achieve ... for example ...

    PHP Code:
    while ($row mysql_fetch_assoc($result)) {
      
    // Anything entered here will be done ONCE PER RECORD FOUND IN THE RESULTSET.  For example, we can echo something from the database or we can increment a counter and display it ... let's do both ....

      // Display something from the database:
      
    echo "{$row['fieldname']}<br />";

      
    // Increment and display a counter:
      
    $x++;
      echo 
    $x;

      
    // If we wanted to create a list of items from fieldname then we can do so here as well ... we need to create an array here first then outside of the while .... loop we'll then implode it ... 
      
    $myarray[] = $row['fieldname'];
    }

    // Outside of the loop, we can now display the content of $myarray ...
    print_r($myarray);

    // and we can make a varaible to hold a list of the items within that array ...
    $mylist implode($myarray",");

    echo 
    "{$mylist}<br />"
    To display in a table you'd need to recreate the $row variable but you could just repeat the 'while ... loop' start your table before the 'while' and then your rows go in the loop, the closing of the table then takes place after the loop. Like this ...

    PHP Code:
    echo "<table>";
    while (
    $row mysql_fetch_assoc($result)) {
      
    $x++;
      echo 
    "<tr>";
        echo 
    "<td>";
          echo 
    "Row {$x} Cell 1";
         echo 
    "</td>";
        echo 
    "<td>";
          echo 
    "Row {$x} Cell 2";
         echo 
    "</td>";
      echo 
    "</tr>";
    }
    echo 
    "</table>"
    I hope that that starts to make things a little clearer for you, the best way to learn is take this code and have a play, see what results you get in your browser!

    Have fun and good luck!!
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    badger_fruit,

    You and I are on the same page here. The basic loop works fine, but I still think I need a loop within a loop to accomplish what I want.

    Going back to my earlier posts, I have a database table that contains the following:

    | Computer Repair | 2 |
    | Computer Repair | 7 |
    | Computer Repair | 9 |
    | Computer Repair | 15 |
    | Computer Repair | 27 |
    | Computer Repair | 44 |
    | Computer Repair | 71 |
    | PHP Programmer | 4 |
    | PHP Programmer | 15 |

    Instead of showing Computer Repair 7 times, I need to take the contents of that table and output the following:

    | Computer Repair | 7 | 2,7,9,15,27,44,71 |
    | PHP Programmer | 2 | 4,15 |

    For the purposes of our discussion, you can ignore the 2nd column which is simply the count of however many total result rows there were for each position. What I'm needing to do is to insert the following into a database table:

    PHP Code:
    mysql_query("
       INSERT INTO position_facility_pages (
          id,
          position, 
          facility_page
       ) VALUES (
          NULL, 
          '
    $position', 
          '"
    .implode(',',$facility_pages)."'
       )"
    ) or die(mysql_error()); 
    $facility_pages is the array that should contain all of the page numbers for each position (i.e. 2,7,9,15,27,44,71, etc). $position is the name of the position (i.e. Computer Repair) that gets added to the new table.

    The original table has 9 rows. The new table should contain 2 rows. The new table will be joined to another table that contains the COUNT information (7 & 2 respectively), and then output to the browser.

    Does that make sense? So basically, I'm thinking that I need an initial while{} statement that loops through all the results, and a second while{} statement within that that builds the array of page numbers and inserts it into the temp table position_facility_pages.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    For what it's worth, I got it working using the following:

    PHP Code:
    $x=0;
    $prev_pos '';
    $facility_pages = array();
    while(
    $row=mysql_fetch_assoc($results)) {
        
    $next_pos $row['position'];
        if(
    $prev_pos != $next_pos && $x != 0) {
            
    //INSERT INTO NEW TABLE
            
    $facility_pages = array();
        }
        
    $facility_pages[] = $row['facility_page'];
        
    $prev_pos $row['position'];
        
    $x++;

    But I'm going to try Badger's suggestion below to see if his method is better.
    Last edited by BlackAce; October 4th, 2012 at 03:45 PM.
  20. #11
  21. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    Just modify the $myarray creation, so instead of it using [], you stuff a value in there ... like so ...

    PHP Code:
    while ($row mysql_fetch_assoc($result)) {
      
    $myarray[$row['anotherfieldname']] = $row['fieldname'];

    That way $myarray has two "base" values (in your case)

    $myarray['Computer repair']
    $myarray['PHP programmer']

    Inside of each element is another array ... if you copy/patse/amend/run this code example you'll see the full "map":

    (assume that we've already created the array from the while loop above ...)
    PHP Code:
    echo "<pre>";
    print_r($myarray);
    echo 
    "</pre>"
    You can the do :
    PHP Code:
    foreach ($myarray as $key => $value) {
      
    $newarray[$key] = implode($value",");

    $newarray should then hold what you're after i.e.

    $newarray['computer repair'] = 7,1,5,4,6,5
    $newarray['php programmer'] = 8,9,5,1,4,2

    (or whatever values they're supposed to be) and you can then use that $newarray however you want.

    Note this is all theory, I'm at home and although I have a webserver available, I'm not up for testing if that actually does what I think it will do ... I can give it a proper go tomorrow PM when I get back into work but no doubt you'll have tried it by then anyway!!

    ps. My son wanted me to use the flame smiley so here it is to keep him happy:
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
  22. #12
  23. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Code:
    SELECT 
        open_positions.position,
        COUNT(1) AS number_of_open_positions,
        GROUP_CONCAT(facility_pages.facility_page ORDER BY facility_pages.facility_page SEPARATOR ', ')
    FROM 
        open_positions
    INNER JOIN 
        facility_pages
    ON
        open_positions.facility_ID=facility_pages.facility_ID
    GROUP BY
        open_positions.position
    ORDER BY
        open_positions.position
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2010
    Posts
    195
    Rep Power
    45
    Originally Posted by E-Oreo
    Code:
    SELECT 
        open_positions.position,
        COUNT(1) AS number_of_open_positions,
        GROUP_CONCAT(facility_pages.facility_page ORDER BY facility_pages.facility_page SEPARATOR ', ')
    FROM 
        open_positions
    INNER JOIN 
        facility_pages
    ON
        open_positions.facility_ID=facility_pages.facility_ID
    GROUP BY
        open_positions.position
    ORDER BY
        open_positions.position
    My immediate reaction was "Give this man a cookie!" However, since you already ARE a cookie, I'll have to just say thanks.

    I will go read up on this GROUP_CONCAT. So glad to do this on the MySQL side instead of the if statements I was concocting, seems so much easier.

    EDIT: I have confirmed that this is working as desired, and I have taken it and further enhanced it to meet additional requests the client had this morning. Thank you again for the suggestion, as well as the education on GROUP_CONCAT. As I read through the details, it's exactly what I was looking for.
    Last edited by BlackAce; October 5th, 2012 at 10:09 AM.

IMN logo majestic logo threadwatch logo seochat tools logo