platform independent alternative to group_concate, MYSQL < 4

[JjC]

i am designing a car management module in php and most the servers hosting and especially the one i am one are still using MYSQL<4. Now the problem is that i have two tables:

CAR, CARDETAILS #join by carid (auto on car)

what i initially wanted to achieve would have been dealt with using the GROUP_CONCATE aggregate function, but this is not available on versions less than 4. Point being to build a string out of the car details, if you get what i mean

I have seen alternatives but none boast platform independence, nor any that i have found support the LINUX box i am hosting on....

does anyone have any other alternatives they can send my way

[aonekilla]

i take it that from the design you have each detail for each car is in multiple rows in the cardetails table and you want to be able to concatenate all of the details for each car into one string ... correct me if i am wrong ....

i see that you are going to have to depend a little more on the language you are using to interface the mysql DB than on the SQL....

one solution i can think of right now is for you to just join the two tables and loop though the resultset concatenating the details of each car into a separate string variable .....

here is a limited example in php i can think of right now:

//Initialize $resultset
//This is to be done after the resultset has been obtained from the Db
while ($row = mysql_fetch_array($resultset)){
    $details[$row['carid']] .= $row['details']; //where carid and details are actual columns in the resultset
} 

that code generates an associative array of concatenated details for each carid

try this out and tell me if it helped your cause

[JjC]

thanx aonekilla's , but I should have stated that i have consider options similar to that, my problem is trying to find the least memory intensive query to achieve this.

I am striving to hit the dbase as little as possible, thats why i was hoping for an aggregate function which i could create in the version i will be using and have it working like the latest releases.

Thanx though, hope someone else have another option...but the loop is hopefully my last restort

[owen]

build the string separately, when you do the update for the record insert string into a text field

[RobyG]

I totaly agree with Owen. You will get the best performance doing it that way (assuming that car details is viewed much more frequently that being updated). That's why I love databases (such as Postgresql) that uses triggers. Such a function could be done in an (update/insert) trigger.

Remember though that each time you update both tables, you will have to update this text field in order to keep the info current.

[JjC]

point taken and since i am out of time... i might just exercise that option.

But i may just change it l8r.

when it all boils down, i like the sql to do what it was made for

thanx

[owen]

sometimes people like to get "evil" with the queries - really I've seen it happen