Output multiple rows in one output row

Databases
1

Hello

How would I output data from multiple rows into one output from a loop?

Take facebook for e.g

A user can make a post. Multiple users can ‘like’ that post and it will be displayed under that comment all the people that have liked it.

Assume my table setup is this

post(post_id, user_id, post)
like (post_id, user_id)

2

answer: one row at a time

3

How would I do that?

I’ve already got a php loop that displays each post.
I want to display all the users that have liked that particular post within the loop.

4

could you please show the query that you use for this loop?

5 
SELECT post FROM dbPosts

Basically it outputs every post.

If my ‘like’ table had this data:

post_id user_id
1 | 2
1 | 3
1 | 6

I would want it to display user_id: 2, 3 and 6 next to the post with the id = 1.

6 
SELECT dbPosts.post 
     , GROUP_CONCAT(dbLikes.user_id) AS like_users 
  FROM dbPosts
INNER
  JOIN dbLikes
    ON dbLikes.post_id = dbPosts.post_id
 WHERE dbPosts.post_id = 1
GROUP
    BY dbLikes.post_id
7

Thanks! I will give this a go.

8

Hi

This is working. There is however one issue.
each id will be linked to a profile.

If I add a link into the output it will link the entire string, it won’t link each id separately.

9

sorry, i have no idea what this means

also, i wish you would stick to one thread

you’ve got this same application spread all over the place, and no one is going to be able to put the pieces together meaningfully, all we can do is evaluate only the snippets you post in each thread

10

Okay I will stick to this thread.

Lets say the group_concat outputs this

1, 2, 3

these are the profile id’s

in php I want to link each id to its own profile.

if I do this:

echo "< a href=“link”>’ . $row[‘like_users’] .‘</a>’;

it will link the entire string (1, 2, 3). How can I link each id separately.

11

why would you want to do that with php?

you should do it with sql

12

How would I do that in SQL?

13

with joins :smiley:

14

I don’t understand how I would add a href link using joins…

15

i’m going to let you think about this for a day or so…

i trust that after you have given it sufficient thought, you will see the difference between sql for retrieving data, and php for formatting the data into html output

:cool:

16

Okay, I’ve thought about it and still nothing…

Ive already retrieved the data using sql. (1, 2, 3)
I want to now add href links to each id, which I presume should be done in php.

17 
echo $row['like_user'];

The above, outputs all the id’s into one string using the GROUP_CONCAT.

echo '<a href="link">' . $row['like_user'] . '</a>';

The above outputs this:


<a href="link">1,2,3</a>

BUT, I want it to output this:


<a href="link">1</a>
<a href="link">2</a>
<a href="link">3</a>
18

okay, my apologies – i thought you actually wanted to retrieve data from the profiles

instead, you just want to create the html for them, and yes, that would be php

19 


$likesArray = explode(',' , $row['like_user']);

foreach ($likesArray as $like) {
  echo '<a href="link">' . $like . '</a>';
}

That will return as you require, not sure what the use of this is thou :wink: