Query help

[dottomm]

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:

$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.

$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.

[dickersonka]

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

select
user_id,
coalesce(avatar_id, 1) as avatar_id
from members 
left join avatars

[varmadba]

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

Try this :

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}'");

[dottomm]

Thanks dickersonka,

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

$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]

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:

$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.

[dickersonka]

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

[B]left join [/B]avatars
on [B]members.user_id = avatars.user_id[/B]

[dottomm]

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

$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!

[dickersonka]

Whenever you just run this piece

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?

[dottomm]

Wow! Okay. So , with that select statement, and putting
{$row} 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.

[dickersonka]

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?

[dottomm]

Yes, It is a file path to the picture.

If I change back the select statement to:

$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}'");

If there is an avatar for the user, it works fine, but not if the avatar_id=1. Weird.

[dickersonka]

you get the avatar_id from this query

do you have an pic for avatar_id of 1?

[dottomm]

Yes. There is an pic for avatar_id=1.

Do I need to call avatars.pic in my query?

[dickersonka]

You can either get the avatar_id from this query

Then run an additional query to get the pic, or if you want to consolidate everything in a single let me know, and i'll post one more to consolidate everything.

[dottomm]

Thanks for all your help. I really appreciate it.

I would like to consolidate it . I just had a realization of why the pic shows up when there is one associated with the user.

I'm doing two queries. One to get both the members info and the avatar and another one that says if there is not an avatar associated with the user select the members data and the blank avatar (avatar_id=1).

Here is the complete statement

//note the * in the first query
//this one works, but not if I call the columns like in the second query. 
 $result=mysql_query("SELECT *
			 FROM members 
			inner join avatars on members.user_id =avatars.user_id 
			 where members.user_id ='{$user_id}'");
//the above here works fine IF there is an avatar associated with the user
//below is the //If it doesn't have an avatar associated with the user
//get the member data and the blank avatar
			 if (mysql_num_rows($result)==0){

	$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}'");

Perhaps my method is wrong?

[dickersonka]

You could use the bottom statement only and get the avatar id, then issue this query to get the pic

select * from avatar where avatar_id ='{$avatar_id}'

here is the consolidated one, where you need ONLY this select statement

select
        ma.user_id,
        ma.memberfname,
        ma.memberlname,
        ma.avatar_id,
        a.pic
from
(
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
) ma
inner join avatar a
on a.avatar_id = ma.avatar_id
where ma.user_id='{$user_id}';

[dottomm]

Again, Thank you for all of your time, but I regret to say I'm getting the error "not a valid MySQL result resource"

Here is the exact statement I entered.

$result=mysql_query("select       
					ma.user_id,
					ma.memberfname,
        			ma.memberlname,
        			ma.avatar_id,
        			a.pic
					from
					(
					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
					) ma
					inner join avatar a
					on a.avatar_id = ma.avatar_id
					where ma.user_id='{$user_id}'");			 
 				while ($row =mysql_fetch_assoc($result)){
 	
echo "

question: does the second select statement there need quotes? I've tried a few variations with no luck.

[dickersonka]

store the query in $sql
and echo it out before you run it

and by the way, only one row will be returned, not multiples

[dickersonka]

One more thing, you need to terminate it with a semicolon

[dottomm]

Yes, I understand about only getting back one record. That is as I hoped.

I'm still confused. Did I not terminate the statement with a semicolon?
And to store the query in SQL??? Would that be

$sql=("select...

??

But I don't understand echoing out before running it?

I'd appreciate any more help, but I've taken enough of your time.

Thanks!

[dickersonka]

i meant store the query in a variable, then output it, just to make sure all the semicolons and commas are out there, just as a degub step

i think something isn't getting set properly when issuing the query and just want to see it output

[dottomm]

SO yea, I thought that is what you meant. I put the query into a variable and called the variable with an echo

and got back this:

select ma.user_id, ma.memberfname, ma.memberlname, ma.avatar_id, a.pic from ( 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 ) ma inner join avatar a on a.avatar_id = ma.avatar_id where ma.user_id='602';

It looks right. I wonder what is going wrong.

[dickersonka]

user_id isn't a string is it?
if not, it doesn't need the ticks, can you try running it without them?

can you try to run the same query in query browser if you have it?

[dottomm]

Thank You! Thank You!
If only you could see the back flips I'm doing now.

It was two things;
one of the avatar should have been avatar(s) and the ticks around the user_id.

I'm really grateful. Thank you for hanging on with me and seeing this through.

[dickersonka]

ahhh sorry about that, see it now on the inner join

glad to see its fixed