Unknown column

[leefish]

1master1, I think that RateU and Zinga are trying to hint to you that if you specify a thread from another table you need to JOIN to that table in the query - like so maybe

Code:

1 2 3 4 5 6 7 8 9 10 11 12

$myquery = $db->query(" SELECT t.*, t.subject AS threadsubject, u.username, u.usergroup, u.displaygroup, f.*, i.*, i.name AS iconname, t.dateline AS threaddate, f.name AS forumname FROM ".TABLE_PREFIX."threads t LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid) LEFT JOIN ".TABLE_PREFIX."icons i ON (i.iid=t.icon) LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid) WHERE t.uid = '".$mybb->user['uid']."' AND t.visible = '1' GROUP BY t.tid ORDER BY threaddate DESC ");

---

See where I have said in the first line

u.displaygroup, f.*, i.*

Then I have said JOIN to the tables that are represented by the shorthand. Here is an extract from an interesting post by Tomm over on Xekko:

Quote: If you're not familiar with joins in queries, check out http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php - but essentially, a LEFT JOIN is just like adding more information from another table in the database in the same query using some related information. If you look at the SELECT criteria, we're selecting everything from the threads table (t.*), a few things from the forums table (f.) and some usergroup info from the users table (u.). The prefixes here are determined when joining tables - for example, we've used f for forums, i for icons and u for users next to the name of the table. If you've got two columns with the same name, remember to rename it in the SELECT criteria (the subject column is in both posts and threads tables, so we've renamed this - t.subject AS threadsubject - for our query). You don't need to add a prefix to the renamed one.

The tables are joined together using info in the brackets - this is just like the WHERE part of a query. For the forums table, we're looking for the thread that has the same tid as the threads's tid (f.fid=t.fid). It's a similar story for the users table - we're looking to retrieve the user (mentioned in the SELECT criteria) for the user with the same uid as the thread's uid (u.uid=t.tid). A LEFT JOIN will only return information if the conditions in the brackets match.