select information, then build an insert query help?

[tjburke79]

I hope I have posted this in the right area but I am hoping someone can help me out. But its kinda mysql and php related...

 

What I am trying to do is, query a members database to see if they have uploaded any new photos to there account.  once i find out what photos are new, i would like to insert a single entry into a feeds table with field that has a comma separated value so that i can later use the explode function to see what new photos where uploaded in my members feed's application.

 

// select latest uploaded photos

 

SELECT MemberID, PhotoID

FROM Photos

WHERE DateTime >= '2011-05-04 13:02:18'

ORDER BY MemberID

 

this will output example...

1 | 11

1 | 15

1 | 89

2 | 68

 

so i guess where i am stuck is based on this select, how would i go about building a single entry per MemberID into my Feeds table?  I would have to do use the loop and an implode function I am guessing but not to sure how to put it all together.

 

// implode mysql result

$comma_separated = implode(",", $array);

 

what i need to do is compile the results so that i can make a single entry into my "Feeds" table per MemberID...

 

insert one entry into feeds per MemberID an example would have to be like this..

 

INSERT INTO `Feed` (`MemberID`, `Data`) VALUES ('1', '11,15,89,97')

INSERT INTO `Feed` (`MemberID`, `Data`) VALUES ('2', '68')

 

I hope I explained myself on what I am trying to accomplish here. Any ideas or a better solution to what i am trying to do? I would rather have one entry then a new entry for every new photo. Thanks for your time.

[fugix]

so for each member id, the photoid field will store an array of all the photos?

[tjburke79]

The Data field on the "Feeds" table will store the temporary information, so in the feed i can select the MemberID and explode $Data to get the latest PhotoID's for that MemberID... My problem is building that initial query and loops compile the information to create the insert query 

[tjburke79]

nobody has any idea? our should i be posting this in a php forum? 

[fugix]

your question is unclear to me, so you are having trouble creating a query to do what?

[mikosiko]

@tjburke79:  HINT: explore the GROUP_CONCAT() function (be aware of the size limitations)

 

[tjburke79]

@tjburke79:  HINT: explore the GROUP_CONCAT() function (be aware of the size limitations)

 

ok that looks like it should work, but when i run the query i get something like [bLOB - 34B] as the group_concat?

[tjburke79]

got it, thank you very much for your help!