Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Query needed which must works in PHP

    Hello,
    I am working with PHP5 and mysql.
    I am having 3 tables in database, as shown in image.
    So i want to display the avatar and name of the friends for the current user.
    1. So when i query for the user 11 means, it must see the friendid of the user 11 from friends table which are 12 and 13.
    2. Next that 12 and 13 must be looked up on users table for username being the friendid of friend table is equal to id of users table
    3. Finally, for that 12 and 13 avatarid and avatarext must be looked from avatar table.

    I tried this which displays only avatarid for the queried user, but i know how to join the other table.
    Any ideas?
    Thankyou
    Code:
                                           SELECT avatarext,avatarid
    					FROM avatar
    					WHERE userid IN(
    					SELECT friendid
    					FROM friend
    					WHERE userid='11';
    Click image for larger version. 

Name:	query.JPG 
Views:	12 
Size:	24.8 KB 
ID:	10457

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think your ID column in Friends is not needed, unless a person can be friends with one other person more than once. this could happen say if you were modelling teenagers friendships which seem to change day by day, hour by hour, but then you would need something else to indicate the period of that friendship.

    so you have two joins, you join users.id to friends.userid and then you join freinds.freindid to avatars.userid

    again the id column in avatars is superfluous unless you are allowing multiple avatars form any one person.

    its also arguable that you don't need the avatar table atall, after all the avatar is a function/property of the user so it could be stored there quite happily. OK you may have null values, which to some db developers is an anathema but thats the way I'd do it.

    I think you would benefit from getting a good grounding in relational theory and practise. usually these two texts are referred to on this site
    Oops -- r937.com
    or
    The Relational Data Model, Normalisation and effective Database Design
    Last edited by healdem; 02-04-10 at 02:23.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    ya thankyou, can you show me some examples?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    examples of what
    a join statement?
    table design?
    normalisation?

    select my, comma, seperated, list, of, columns from mytable
    join anothertable on anothertable.someothercolumn on mytable.acolumn
    join yetanothertable on yetanothertable.thiscolumn = anothertable.thatcolumn

    when in doubt consult da manuel
    if that fails then as ever google is your matey
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    Hi this works for what i asked? Is this correct?
    Code:
    SELECT u.username,a.avatarid,a.avatarext
    					FROM users u,avatar a
    					WHERE u.id
    					IN(
    					SELECT friendid
    					FROM friend
    					WHERE userid='$loggeduserid'
    					AND accepted='0')
    					AND u.id=a.userid;";

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bharanidharanit View Post
    Hi this works for what i asked? Is this correct?
    Code:
    SELECT u.username,a.avatarid,a.avatarext
    					FROM users u,avatar a
    					WHERE u.id
    					IN(
    					SELECT friendid
    					FROM friend
    					WHERE userid='$loggeduserid'
    					AND accepted='0')
    					AND u.id=a.userid;";
    why don't you try it out, rather than ask a 'does this work question'
    after all its your project, you know what you are looking for, and assuming you have structured your test data in such a way you should be able to work out if it meets your requirements

    part of this process in my books is for you to work out how to use the tools available to you to improve your development and debugging skills

    incidentally I still think your table design is wrong
    you also need to have so test data which meets the test you are trying to sign off, and perhaps just as importantly some data which doesn't. the reason so you know the code works

    so you need some test data for a person which meets the criteria
    some which doesn't
    some which may partially meet the criteria (especially important if you are joining more than 2 tables)
    you need to ensure that you are extracting the data required from the tables, all of the relevant data, and only that data
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2008
    Posts
    117
    Hi this works for what i asked? Is this correct?
    Hi,
    Thankyou, I think you mistook what i quoted. I already tested and that works for me and I meant that.I asked help here whether that syntax is correct? Also, did i made any mistake in joining fields.
    Thankyou

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if it worked then the syntax was correct
    if it worked and provided the correct answers then the logic may be correct

    ultimately we dont' know, we don't have timne to create your tables, populate some test data, then run it.


    think about the problem
    how would you know if the jon was correct
    what symptoms would you look for
    if the join is malformed, how would you know that?
    can you test for that in your current data.

    bear in mind just because something gives the correct value it doesn't necessarily mean that its right. in this trade you need to develop a good debugging methodology. and part of that is runnign test cases. some of those tests should return data, some may not. not all tests return data, some (many) times you need to have tests thatreturn no results, because the data doesnt' support what you are asking the db. you need to make certain the db provides the data you expect, doesn't provide data you don't want and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2008
    Posts
    117
    Ya thankyou,
    Sorry if anything goes wrong,
    I worked for all the cases
    1. empty
    2. changing data in every tables etc..
    That worked for me
    thankyou

Posting Permissions

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