Left Join in query ?

[Imran]

Hi Zinga !

Can you please give me an hint how to use "LEFT JOIN" in sql query. sorry for such an question. I'm running an query where I could find some brief data from "users" table and then from "userfields" table. I don't want to use double query for two different tables, hence little difficult to use this "LEFT JOIN" thingy.

My present query where I am finding brief data from "users" table is;

PHP Code:

$query = $db->simple_select("users", "username, email, regip", "uid=".intval($user_info['uid'])."");

---

then how to insert LEFT JOIN in their to get custom profile field value of that user?

Thanks for helping me out Yumi !

[ZiNgA BuRgA]

MyBB's "simple_select" isn't really designed for that thing.  You'll need to write the full query yourself.

Joins are probably something difficult to conceptualise at first, but the idea is to link a field of one table to a field of another.

SQL Code

SELECT u.username, u.email, u.regip FROM mybb_users u LEFT JOIN mybb_userfields f ON u.uid=f.ufid

---

[Imran]

OK. I used following query;

PHP Code:

$query = $db->query(" SELECT u.uid, u.username, u.usergroup, u.email, regip, uid=".intval($user_info['uid'])." FROM ".TABLE_PREFIX."users u LEFT JOIN ".TABLE_PREFIX."userfields f ON u.uid=f.ufid ");

---

It gets data from users table but userfields / custmom profile field's value remains empty...

What's going wrong with me Yumi ?

[ZiNgA BuRgA]

Please take the time to learn SQL, the syntax of your query is probably not what you want.

Good reads:
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/join.html

I was thinking you might trip up on me not specifically putting in the fields you want (I only did the join), but right now I'm feeling incredibly nice at the moment:

SQL Code

SELECT u.username, u.email, u.regip, f.fid3 FROM mybb_users u LEFT JOIN mybb_userfields f ON u.uid=f.ufid

---

[Imran]

Thank You for your efforts Yumi !