Ad:

MySQL Discussion Thread
Marked Solved
Views: 2021

Page 1
2
3
Next

You are currently viewing page 1 of this multi-page discussion thread

Aug 26th, 2008

Query help

Expand Post »

Thanks in advance for anyone who can help me with this query.

I want to select all from table one and the matching image from table two.

Using the following inner join query works great:

MySQL Syntax (Toggle Plain Text)
 
$result=mysql_query("SELECT * 
FROM members inner join avatars
on members.user_id=avatars.user_id 
where members.user_id ='{$user_id}'");
 
$result=mysql_query("SELECT * 
FROM members inner join avatars
on members.user_id=avatars.user_id 
where members.user_id ='{$user_id}'");

but if there is no corresponding record on avatars I get a broken result, so I want to retrieve a default avatar. (avatar_id=1)

so essentially what I am trying to do is two queries with an 'if' statement.

MySQL Syntax (Toggle Plain Text)
 $result=mysql_query("SELECT * FROM members 
	inner join avatars on members.user_id = avatars.user_id 
	where members.user_id ='{$user_id}'");
		if (mysql_num_rows($result)==0)
//how DO I JOIN these two queries so it retrieves all the correct info FROM the //Members table AND the DEFAULT avatar FROM the avatar table?
$result= mysql_query("SELECT  * FROM members 
              where user_id ='{$user_id}' ");
$result=mysql_query("SELECT * from avatars where avatar_id=1");
				 }
 
while ($row = mysql_fetch_assoc($result)){
 
 
 
echo "
 
 $result=mysql_query("SELECT * FROM members 
	inner join avatars on members.user_id = avatars.user_id 
	where members.user_id ='{$user_id}'");
		if (mysql_num_rows($result)==0)
//how do I join these two queries so it retrieves all the correct info from the //Members table and the default avatar from the avatar table?
$result= mysql_query("SELECT  * FROM members 
              where user_id ='{$user_id}' ");
$result=mysql_query("SELECT * from avatars where avatar_id=1");
				 }
 		
while ($row = mysql_fetch_assoc($result)){
 	


echo "

any help is greatly appreciated.

Similar Threads

Removing Query Strings in ASP.NET
Sql Query in VB.NET
Double MySQL Query in PHP
Dynamic Query in JSP
MySQL nested query / joined query conversion help in MySQL

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Aug 26th, 2008

Re: Query help

use a left join and specifiy your columns with a coalesce on avatar id

MySQL Syntax (Toggle Plain Text)
SELECT
user_id,
coalesce(avatar_id, 1) as avatar_id
FROM members 
LEFT JOIN avatars
select
user_id,
coalesce(avatar_id, 1) as avatar_id
from members 
left join avatars

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 27th, 2008

Re: Query help

If you can type all the column names you can get the default avatar_id(1)

Try this :

sql Syntax (Toggle Plain Text)
SELECT members.*,ifnull(avatar_id,1) FROM members 
LEFT OUTER JOIN avatars
   on members.user_id=avatars.user_id 
WHERE members.user_id ='{$user_id}'");
 
SELECT members.*,ifnull(avatar_id,1) FROM members 
left outer join avatars
   on members.user_id=avatars.user_id 
where members.user_id ='{$user_id}'");

Last edited by peter_budo; Aug 29th, 2008 at 7:40 pm. Reason: Keep It Organized - please use [code] tags

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

Aug 27th, 2008

Re: Query help

Thanks dickersonka,

left joins are pretty new to me, but I think I got pretty close with:

MySQL Syntax (Toggle Plain Text)
 
	$result=mysql_query("select 
						members.user_id, 
						members.memberfname,
						members.memberlname,
						members.memberteam,
						members.memberrole,
						members.areaexpert,
						members.memberemail,
						members.memberareacode,
						members.memberphone,
					coalesce(avatar_id, 1) as avatar_id
					from members
					left join avatars on members.user_id=avatars.avatar_id where members.user_id='{$user_id}'");
						
	$result=mysql_query("select 
						members.user_id, 
						members.memberfname,
						members.memberlname,
						members.memberteam,
						members.memberrole,
						members.areaexpert,
						members.memberemail,
						members.memberareacode,
						members.memberphone,
					coalesce(avatar_id, 1) as avatar_id
					from members
					left join avatars on members.user_id=avatars.avatar_id where members.user_id='{$user_id}'");

but I was only able to retrieve the data from the members table. No connection to the avatars table? Did I totally miss something?

Again, thanks!

-t

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Aug 27th, 2008

Re: Query help

Thank you too varmadba.

And like dickersonkas solution, I think I'm getting close with your suggestion too, but I get a blank response. (which is better than a bad query)

I called all the columns with:

MySQL Syntax (Toggle Plain Text)
$result=mysql_query("SELECT 	
								members.user_id, 
						members.memberfname,
						members.memberlname,
						members.memberteam,
						members.memberrole,
						members.areaexpert,
						members.memberemail,
						members.memberareacode,
						members.memberphone, 
						ifnull(avatar_id,1) FROM members
						left outer join avatars
						on members.user_id=avatars.user_id
						where members.user_id='{user_id}'");
$result=mysql_query("SELECT 	
								members.user_id, 
						members.memberfname,
						members.memberlname,
						members.memberteam,
						members.memberrole,
						members.areaexpert,
						members.memberemail,
						members.memberareacode,
						members.memberphone, 
						ifnull(avatar_id,1) FROM members
						left outer join avatars
						on members.user_id=avatars.user_id
						where members.user_id='{user_id}'");

anymore suggestions would be greatly appreciated.

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Aug 27th, 2008

Re: Query help

i'll stick with the coalesce

make sure your join is on the column that will be in both tables, use a left join, and make sure to use an alias on your coalesced column

(Toggle Plain Text)

left join avatars
on members.user_id = avatars.user_id

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 27th, 2008

Re: Query help

Thank you again Dickersonka,

I thought I had, as you suggested, but I'm still not making a connection to the avatars table.

Here's my table layout

Avatars
-------------------------------
avatar_id | user_id | pic
----------- |----------|-------

Members
----------------------------------------------------
user_id | memberfname | memberlname
---------| ----------------- | ----------------

The default 'pic' has a avatar_id of 1 and a user_id of 1( there are no members.user_id near that range.)

and here is the query I'm working with

MySQL Syntax (Toggle Plain Text)
	$result=mysql_query("select 
						members.user_id, 
						members.memberfname,
						members.memberlname,
					coalesce(avatar_id, 1) as avatar_id
					from members
					left join avatars on members.user_id=avatars.user_id where members.user_id='{$user_id}'");
	$result=mysql_query("select 
						members.user_id, 
						members.memberfname,
						members.memberlname,
					coalesce(avatar_id, 1) as avatar_id
					from members
					left join avatars on members.user_id=avatars.user_id where members.user_id='{$user_id}'");

Can you see why this might not be working? I'm in a little over my head.

Thanks again!

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Aug 27th, 2008

Re: Query help

Whenever you just run this piece

MySQL Syntax (Toggle Plain Text)
SELECT 
						members.user_id, 
						members.memberfname,
						members.memberlname,
					coalesce(avatar_id, 1) as avatar_id
					FROM members
					LEFT JOIN avatars on members.user_id=avatars.user_id
select 
						members.user_id, 
						members.memberfname,
						members.memberlname,
					coalesce(avatar_id, 1) as avatar_id
					from members
					left join avatars on members.user_id=avatars.user_id

What do you see?
You should see all the members, with their avatar_id's unless they don't have one, with an id of 1

Is this the result you see?

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Aug 27th, 2008

Re: Query help

Wow! Okay. So , with that select statement, and putting
{$row['avatar_id']} in the echo statement I can see all the members and their avatar_ids

All members who do not have an avatar, print an avatar_id of 1. But now, those who do have avatars print the correct avatar_id but do not show the avatar.

hmm.

dottomm

Reputation Points: 9

Solved Threads: 5

Junior Poster in Training

Offline

89 posts
since Nov 2007

Permalink

Aug 27th, 2008

Re: Query help

It won't show the avatar with grabbing the pic column.

Assuming this is either a byte array or file path to their picture, you need to grab this column as well.

Is that what you are meaning?

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Page 1
2
3
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Query for one-to-many relationship

Next Thread in MySQL Forum Timeline: Sorting

DaniWeb Message

Cancel Changes

User Name
Password