Alternative to GROUP_CONCAT

[Yusogga]

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 )

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

[raf]

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.

[Yusogga]

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 )

[raf]

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/showthread.php?s=&threadid=31218&highlight=previous

Also check the links in my post there. The last link is to a more complexe one.

[Yusogga]

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?

[raf]

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.

[Yusogga]

Great!

You're right so simple, thanks !

[raf]

You're welcome. Glad you got it running.