SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    output multiple rows in one output row

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    How would I output data from multiple rows into one output from a loop?
    answer: one row at a time
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    answer: one row at a time

    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    've already got a php loop that displays each post.
    could you please show the query that you use for this loop?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! I will give this a go.

  8. #8
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    If I add a link into the output it will link the entire string, it won't link each id separately.
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I will stick to this thread.

    Lets say the group_concat outputs this

    Code:
    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.

    Quote Originally Posted by r937 View Post
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    in php I want to link each id to its own profile.
    why would you want to do that with php?

    you should do it with sql
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    why would you want to do that with php?

    you should do it with sql
    How would I do that in SQL?

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    How would I do that in SQL?
    with joins
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand how I would add a href link using joins..

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    I don't understand how I would add a href link using joins..
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #17
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    echo $row['like_user'];
    The above, outputs all the id's into one string using the GROUP_CONCAT.

    Code:
    echo '<a href="link">' . $row['like_user'] . '</a>';
    The above outputs this:
    Code:
    <a href="link">1,2,3</a>
    BUT, I want it to output this:
    Code:
    <a href="link">1</a>
    <a href="link">2</a>
    <a href="link">3</a>

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Member
    Join Date
    Mar 2011
    Location
    Tyne & Wear
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:

    $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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •