Issue in query with inner join and concat() operation in mysql

Question

I have two tables:

sk_accounts      //details of user

acnt_user_id
acnt_fname //first name
acnt_lname
acnt_profile_picture
acnt_member_class

so on........

 sk_following   //table containing details of users who are following others

id
flwing_follower_id //id of the user who are followed by other followers
flwing_following_user_id

following_date

I want to display details of the follower based on the following Mysql code.Unfortunately it returns zero rows eventhough there are 3 rows.My query is like this:

$query_following = "SELECT sk_following.flwing_following_user_id, 
sk_accounts.acnt_fname,
sk_accounts.acnt_lname,
sk_accounts.acnt_member_class,
sk_accounts.acnt_profile_picture
FROM sk_following 
INNER JOIN sk_accounts 
WHERE sk_following.flwing_follower_id='$id' AND        sk_accounts.acnt_user_id=sk_following.flwing_following_user_id AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'";
$result_following = mysql_query($query_following);
$count_following = mysql_num_rows($result_following);
echo $count_following;

Note:$id and $name contain values Kindly help me.Thanks in advance.

Can you confirm that flwing_following_user_id is a reference to acnt_user_id field table? — sdespont
– sdespont, Commented Jan 31, 2013 at 6:51
@sdespont Ya,I have this query worked in another context without using concat() — Techy
– Techy, Commented Jan 31, 2013 at 6:54
try Applying WHERE clauses one by one..u will realize where is going wrong.. — iJay
– iJay, Commented Jan 31, 2013 at 6:56
@sdespont I have checked the WHERE clause,It is having some error.Its my fault — Techy
– Techy, Commented Jan 31, 2013 at 6:58

Tony Stark · Accepted Answer · 2013-01-31 06:53:19Z

2

Try this,

"SELECT sk_following.flwing_following_user_id,
sk_accounts.acnt_fname,
sk_accounts.acnt_lname,
sk_accounts.acnt_member_class,
sk_accounts.acnt_profile_picture 
FROM sk_following
LEFT JOIN sk_accounts ON sk_accounts.acnt_user_id=sk_following.flwing_following_user_id
WHERE sk_following.flwing_follower_id='$id'
AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'";

may this help you.

answered Jan 31, 2013 at 6:53

Tony Stark

8,1028 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tony Stark Over a year ago

Use left join because its better than inner join. see stackoverflow.com/questions/2726657/…

Tony Stark Over a year ago

@Anaz A you get it about left join or not? i think its better to use left join. i also use more left join then inner join.

sgeddes · Accepted Answer · 2013-01-31 06:44:19Z

1

Hard to completely understand without seeing sample data and desired output, but should your JOIN be on the flwing_follower_id and not the flwing_following_user_id?

SELECT sk_following.flwing_following_user_id,
    sk_accounts.acnt_fname,
    sk_accounts.acnt_lname,
    sk_accounts.acnt_member_class,
    sk_accounts.acnt_profile_picture 
FROM sk_following 
    INNER JOIN sk_accounts ON sk_accounts.acnt_user_id=sk_following.flwing_follower_id 
WHERE sk_following.flwing_follower_id='$id' 
    AND CONCAT(sk_accounts.acnt_fname,' ',sk_accounts.acnt_lname)='$name'

Good luck.

answered Jan 31, 2013 at 6:44

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

1 Comment

Techy Over a year ago

I want to display all the above data in a div.

Collectives™ on Stack Overflow

Issue in query with inner join and concat() operation in mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related