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

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.

Use left join because its better than inner join. see stackoverflow.com/questions/2726657/…
@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

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.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related