Query needed which must works in PHP

bharanidharanit · #1 (**permalink**) 02-03-10, 11:11

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.

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.
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
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';

Query needed which must works in PHP-query.jpg

healdem · #2 (**permalink**) 02-03-10, 11:55

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

bharanidharanit · #3 (**permalink**) 02-03-10, 18:39

ya thankyou, can you show me some examples?

healdem · #4 (**permalink**) 02-04-10, 01:30

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

bharanidharanit · #5 (**permalink**) 02-04-10, 20:09

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;";

healdem · #6 (**permalink**) 02-05-10, 01:47

Quote:

Originally Posted by bharanidharanit

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

bharanidharanit · #7 (**permalink**) 02-05-10, 07:11

Quote:

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

healdem · #8 (**permalink**) 02-05-10, 13:39

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.

bharanidharanit · #9 (**permalink**) 02-05-10, 16:03

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

Internet Services

Web Development

Digital Marketing

Consumer Tech