Web Analytics Made Easy -
StatCounter Alternative to GROUP_CONCAT - CodingForum

Announcement

Collapse
No announcement yet.

Alternative to GROUP_CONCAT

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Alternative to GROUP_CONCAT

    Here is my problem:

    I have a db of events with categories and locations. A form w/ checkboxes inserts into 2 tables the following:

    Table 1: event
    (id, title, date, description, contact)

    Table 2: event_search (only foreign keys)
    (event_id,location_id,type_id)

    2 other tables are listing the locations and the types

    Table 3: location (International, national, all 50 us states)
    (id, name)

    Table 4: event_type (promotional, free, concert...)
    (id, name)

    Some events have multiple types and locations, so this is the only way I've figured out to have a normalized db.

    How can I list the events, displaying their locations and type, without using GROUP_CONCAT (only works w/ mySQL 4.1 )

    ????????????????????????????????????????????????????????????

  • #2
    I don't understand your question.

    To display the events with their location and type, you need a four table join. But i don't see what that has to do with GROUP_CONCAT because all you could do with that, is concatinalte the foreign key-values, where you need the names/ddescriptions.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment


    • #3
      Well, maybe I am not doing it the right way...

      Let's say I have this query:


      SELECT E.* FROM event E, event_search S, location L, event_type T WHERE S.e_id = E.id AND S.loc_id = L.id AND S.type_id = T.id GROUP BY E.id;

      if I try to retreive the location name and event_type name, it will return only the last result of the grouping.

      Let's pretend I have these rows in my dbase:


      event_type
      1| music |
      2| tv |
      3| film |
      4| free |
      5| photo |

      location
      1| NY
      2| NJ
      3| MA
      4| KS

      event
      1| film festival
      2| media tv congress
      3| artists show

      event_search
      e_id | event_id | loc_id
      1 | 3 | 1 (film festival | film | NY )
      1 | 4 | 1 (film festival | free | NY )
      2 | 2 | 2 (media tv | tv | NJ )
      2 | 2 | 3 (media tv | tv | MA )
      3 | 1 | 4 (art show | music | KS )
      3 | 4 | 4 (art show | free | KS )
      4 | 5 | 4 (art show | photo | KS )


      I want to get this:

      FILM FESTIVAL
      film, free (NY)

      MEDIA TV
      tv (NJ, MA)

      ART SHOW
      music, photo, free (KS)

      thanks for any of your help
      (thanks raf for your interest in helping )

      Comment


      • #4
        your query should be

        SELECT event_type.secondcolumnname, even.secondcolumnname, location.secondcolumnname FROM ((event_search INNER JOIN event ON event_search.e_id=event.firstcolumnname) INNER JOIN location ON event_search.loc_id=location.firstcolumnname) INNER JOIN event_type ON event_search.event_id=event_type.firstcolumnname ORDER BY event ASC;

        and then you process the recordset in your server side script, by checking. Some example :
        http://www.codingforum.net/showthrea...light=previous

        Also check the links in my post there. The last link is to a more complexe one.
        Last edited by raf; Feb 20, 2004, 04:42 PM.
        Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

        Comment


        • #5
          Thanks,
          but then I'm running into other problems:

          I end up with this result (it's a job search)

          JOB 1
          Location: Colorado, Alaska
          type: Electronics, Computer, Electronics, Computer

          JOB 2
          Location: New York, New Jersey
          type: Programming, PHP, mySQL, Programming, PHP, mySQL

          ....



          Here is my code for more clarity:

          $qry = "SELECT DISTINCT L.name as location,S.*, T.name as job_type, J.*, DATE_FORMAT(J.posted,'%M %D, %Y at %h:%s %p') as post_date FROM ((job_search S INNER JOIN job J ON S.job_id=J.id) INNER JOIN job_location L ON S.loc_id = L.id) INNER JOIN job_type T ON(S.type_id=T.id) ORDER BY S.job_id,S.loc_id DESC LIMIT $from,$max";


          $current_id = NULL; // sets current job id

          while($row = mysql_fetch_array($rst)){

          if($row['id']!=$current_id){ // Checks if row matches a new job
          if($current_id != NULL){ // if not the first entry, displays html and closes row
          $$temp->display();
          echo '</td></tr>';
          }

          echo '<tr><td>';
          $temp = 'c_'.$current_id; // new job name
          $$temp = new job($row); // new job obj instance
          $current_id = $row['id']; // sets current job id

          $current_loc_id = NULL;
          }

          if($current_loc_id != $row['loc_id']){
          echo $row['loc_id'];
          array_push($$temp->location,$row['location']); // adds entry to location

          $current_loc_id = $row['loc_id'];
          }
          array_push($$temp->type,$row['job_type']); // adds entry to type


          }
          $$temp->display();
          echo '</td></tr>';



          I could use a loop to check if the type is already in the array, but this is simplified, It will have more than Location and Type as multiple result field. The whole process might get very very slow.
          ??

          Did I miss something?
          Last edited by Yusogga; Feb 21, 2004, 09:33 AM.

          Comment


          • #6
            Not difficult to solve, because you can use
            array_unique() ( http://www.php.net/manual/en/function.array-unique.php ) to remove duplicate values from the array with the types --> assulig that inside the loop, you first just add them and then create the unique list before printing.

            Or you can use array_search() ( http://be2.php.net/manual/en/function.array-search.php ) before adding the type to the array.
            Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

            Comment


            • #7
              Great!

              You're right so simple, thanks !

              Comment


              • #8
                You're welcome. Glad you got it running.
                Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                Comment

                Working...
                X