add new field to lengthy dbquery

trying to add to this PHP mysql db query (thats why i posted here, its written in php form)

i want to make it so this very same "$result" will find the "user_clanrank" field from the db fusion_users aka $dbprefix_users... but i cant get it to work, if you understand my problem take a look:

Code:

<?php
$result = dbquery(
"SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu2.user_name AS user_lastuser FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"
);
?>

[Free WordPress Themes]

first, this is a MySQL question (move the post you're a mod too)

You have to debug the sql statement, just "echo" the statement and place the sql into the sql textarea inside phpmyadmin an see what happens (post the error if you get one...)

well nothin happens when i run the statement above, this is because it is stock and unchanged.. so it will not error, and it will grab all the data necessary, i just want to add to it so it finds the user_Clanrank from users table as well

[tu1.user_Clanrank AS clan_rank,]

Nick said this before its difficult to help you without knowing the appliaction but try this:


"SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu1.user_Clanrank AS clan_rank, tu2.user_name AS user_lastuser FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"

nope didnt work.. here is entire page code if it helps, you can find this DBquery in the page twice.

Code:

<?php
require_once "../maincore.php";
require_once BASEDIR."subheader.php";
include LOCALE.LOCALESET."forum/main.php";
include BASEDIR."colors2.php";
if (!isset($lastvisited) || !isNum($lastvisited)) $lastvisited = time();
$threads_per_page = 20;

if (!FUSION_QUERY || !$forum_id || !isNum($forum_id)) fallback("index.php");

$result = dbquery(
"SELECT f.*, f2.forum_name AS forum_cat_name
FROM ".$db_prefix."forums f
LEFT JOIN ".$db_prefix."forums f2 ON f.forum_cat=f2.forum_id
WHERE f.forum_id='".$forum_id."'"
);
if (dbrows($result)) {
$data = dbarray($result);
if (!checkgroup($data['forum_access']) || !$data['forum_cat']) fallback("index.php");
} else {
fallback("index.php");
}
$can_post = checkgroup($data['forum_posting']);

$caption = $data['forum_cat_name']." | ".$data['forum_name'];

opentable($locale['450']);
echo "<table cellspacing='0' cellpadding='0' width='100%'>
<tr>
<td class='smallalt'>
<a href='index.php'>".$settings['sitename']."</a> | $caption</td>\n";
if (iMEMBER && $can_post) {
echo "<td align='right'>
<a href='post.php?action=newthread&amp;forum_id=$forum_id'><img hsrc='".THEME."forum/newthread_on.gif' src='".THEME."forum/newthread.gif' alt='".$locale['566']."' style='border:0px;'></a>
</td>\n";
}
echo "</tr>
</table>\n";

$rows = dbrows(dbquery("SELECT * FROM ".$db_prefix."threads WHERE forum_id='$forum_id' AND thread_sticky='0'"));
if (!isset($rowstart) || !isNum($rowstart)) $rowstart = 0;

if ($rows > $threads_per_page) {
echo "<div align='center' style='margin-top:5px;margin-bottom:5px;'>
".makePageNav($rowstart,20,$rows,3,FUSION_SELF."?forum_id=$forum_id&amp;")."
</div>\n";
}

echo "<table cellpadding='0' cellspacing='0' width='100%' class='tbl-border'>
<tr>
<td>
<table cellspacing='1' cellpadding='0' width='100%'>
<tr>
<td width='20' class='tbl2'>&nbsp;</td>
<td class='tbl2'>".$locale['451']."</td>
<td width='100' class='tbl2'>".$locale['452']."</td>
<td align='center' width='50' class='tbl2'>".$locale['453']."</td>
<td align='center' width='50' class='tbl2'>".$locale['454']."</td>
<td width='120' class='tbl2'>".$locale['404']."</td>
</tr>\n";

if ($rowstart == 0) {
$result = dbquery(
"SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu1.user_Clanrank AS user_clanrank, tu2.user_name AS user_lastuser FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"

);

if (dbrows($result) != 0) {
while ($data = dbarray($result)) {
if ($data['thread_locked']) {
echo "<tr>\n<td align='center' width='25' class='tbl2'><img src='".THEME."forum/folderlock.gif' alt='".$locale['564']."'></td>";
} else  {
if ($data['thread_lastpost'] > $lastvisited) {
$folder = "<img src='".THEME."forum/foldernew.gif' alt='".$locale['560']."'>";
} else {
$folder = "<img src='".THEME."forum/folder.gif' hsrc='".THEME."forum/folder_on.gif' alt='".$locale['561']."'>";
}
echo "<tr>\n<td align='center' width='25' class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\">$folder</td>";
}
$reps = ceil($data['thread_replies'] / $threads_per_page);
$threadsubject = "<a href='viewthread.php?forum_id=$forum_id&amp;thread_id=".$data['thread_id']."'>".$data['thread_subject']."</a>";
if ($reps > 1) {
$ctr = 0; $ctr2 = 1; $pages = "";
while ($ctr2 <= $reps) {
$pnum = "<a href='viewthread.php?forum_id=$forum_id&amp;thread_id=".$data['thread_id']."&amp;rowstart=$ctr'>$ctr2</a> ";
$pages = $pages.$pnum; $ctr = $ctr + $threads_per_page; $ctr2++;
}
$threadsubject .= " - (".$locale['412'].trim($pages).")";
}

echo "<td class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\"><img src='".THEME."forum/stickythread.gif' alt='".$locale['560']."' style='vertical-align:middle;'>
$threadsubject</td>
<td class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\"><a style='color: $mm_color' href='../profile.php?lookup=".$data['thread_author']."'>".$data['user_author']."</a></td>
<td align='center' class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\">".$data['thread_views']."</td>
<td align='center' class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\">".($data['thread_replies']-1)."</td>
<td class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\">".showdate("forumdate", $data['thread_lastpost'])."<br>
<span class='small'>".$locale['406']."<a href='../profile.php?lookup=".$data['thread_lastuser']."'>".$data['user_lastuser']."</a></span></td>
</tr>\n";
}
$threadcount = dbrows($result);
} else {
$threadcount = 0;
}
}

if ($rows != 0) {
$result = dbquery(
"SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu1.user_Clanrank AS user_clanrank, tu2.user_name AS user_lastuser FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"

);

$numrows = dbrows($result);
while ($data = dbarray($result)) {
if ($data['thread_locked']) {
echo "<tr>\n<td align='center' width='25' class='tbl2'><img src='".THEME."forum/folderlock.gif' alt='".$locale['564']."'></td>";
} else  {
if ($data['thread_lastpost'] > $lastvisited) {
$folder = "<img src='".THEME."forum/foldernew.gif' alt='".$locale['560']."'>";
} else {
$folder = "<img src='".THEME."forum/folder.gif' hsrc='".THEME."forum/folder_on.gif' alt='".$locale['561']."'>";
}
echo "<tr>\n<td align='center' width='25' class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\">$folder</td>";
}
$reps = ceil($data['thread_replies'] / $threads_per_page);
$threadsubject = "<a href='viewthread.php?forum_id=$forum_id&amp;thread_id=".$data['thread_id']."'>".$data['thread_subject']."</a>";
if ($reps > 1) {
$ctr = 0; $ctr2 = 1; $pages = "";
while ($ctr2 <= $reps) {
$pnum = "<a href='viewthread.php?forum_id=$forum_id&amp;thread_id=".$data['thread_id']."&amp;rowstart=$ctr'>$ctr2</a> ";
$pages = $pages.$pnum; $ctr = $ctr + $threads_per_page; $ctr2++;
}
$threadsubject .= " - (".$locale['412'].trim($pages).")";
}

echo "<td class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\">$threadsubject</td>
<td class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\"><a style='color: $mm_color' href='../profile.php?lookup=".$data['thread_author']."'>".$data['user_author']."</a></td>
<td align='center' class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\">".$data['thread_views']."</td>
<td align='center' class='tbl2' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055A00'\">".($data['thread_replies']-1)."</td>
<td class='tbl1' onMouseover=\"this.style.backgroundColor='#026700'\" onMouseout=\"this.style.backgroundColor='#055600'\">".showdate("forumdate", $data['thread_lastpost'])."<br>
<span class='small'>".$locale['406']."<a href='../profile.php?lookup=".$data['thread_lastuser']."'>".$data['user_lastuser']."</a></span></td>
</tr>\n";
}
} else {
if ($threadcount == 0) {
echo "<tr>\n<td colspan='6' class='tbl1'>".$locale['455']."</td>\n</tr>\n";
}
}

echo "</table>
</td>
</tr>
</table>\n";

if ($rows > $threads_per_page) {
echo "<div align='center' style='margin-top:5px;'>
".makePageNav($rowstart,20,$rows,3,FUSION_SELF."?forum_id=$forum_id&amp;")."
</div>\n";
}

$forum_list = ""; $current_cat = "";
$result = dbquery(
"SELECT f.forum_id, f.forum_name, f2.forum_name AS forum_cat_name
FROM ".$db_prefix."forums f
INNER JOIN ".$db_prefix."forums f2 ON f.forum_cat=f2.forum_id
WHERE ".groupaccess('f.forum_access')." AND f.forum_cat!='0' ORDER BY f2.forum_order ASC, f.forum_order ASC"
);
while ($data2 = dbarray($result)) {
if ($data2['forum_cat_name'] != $current_cat) {
if ($current_cat != "") $forum_list .= "</optgroup>\n";
$current_cat = $data2['forum_cat_name'];
$forum_list .= "<optgroup label='".$data2['forum_cat_name']."'>\n";
}
$sel = ($data2['forum_id'] == $data['forum_id'] ? " selected" : "");
$forum_list .= "<option value='".$data2['forum_id']."'$sel>".$data2['forum_name']."</option>\n";
}
$forum_list .= "</optgroup>\n";
echo "<table width='100%' cellpadding='0' cellspacing='0' style='margin-top:5px;'>
<tr>
<td align='left' class='tbl'>".$locale['540']."<br>
<select name='jump_id' class='textbox' onChange=\"jumpForum(this.options[this.selectedIndex].value);\">
$forum_list</select></td>\n";
if (iMEMBER && $can_post) {
echo "<td align='right'>
<a href='post.php?action=newthread&amp;forum_id=$forum_id'><img src='".THEME."forum/newthread.gif' hsrc='".THEME."forum/newthread_on.gif' alt='".$locale['566']."' style='border:0px;'></a>
</td>\n";
}
echo "</tr>
</table>\n";

echo "<table cellpadding='0' cellspacing='0' width='100%'>
<tr>
<td class='tbl1'>
<img src='".THEME."forum/foldernew.gif' alt='".$locale['560']."' style='vertical-align:middle;'> - ".$locale['456']."(
<img src='".THEME."forum/folderhot.gif' alt='".$locale['562']."' style='vertical-align:middle;'> - ".$locale['457']." )<br>
<img src='".THEME."forum/folder.gif' hsrc='".THEME."forum/folder_on.gif' alt='".$locale['561']."' style='vertical-align:middle;'> - ".$locale['458']."<br>
<img src='".THEME."forum/folderlock.gif' alt='".$locale['564']."' style='vertical-align:middle;'> - ".$locale['459']."<br>
<img src='".THEME."forum/stickythread.gif' alt='".$locale['563']."' style='vertical-align:middle;'> - ".$locale['460']."</td>
</tr>
</table>\n";
closetable();

echo "<script type='text/javascript'>
function DeleteItem() {
return confirm('Delete this thread?');
}
function jumpForum(forumid) {
document.location.href='".FORUM."viewforum.php?forum_id='+forumid;
}
</script>\n";


require_once BASEDIR."footer.php";
?>

--end viewforum.php

[Free WordPress Themes]

don't think that this will help like I said its a database thing post the database structure(s)

let me see if i can find a copy of the structure..

[Free WordPress Themes]

let me see if i can find a copy of the structure..

you can export the database structure via phpmyadmin

here ya go

Code:

--
-- Table structure for table `fusion_clanranks`
--

CREATE TABLE `fusion_clanranks` (
  `rank_id` tinyint(2) NOT NULL auto_increment,
  `rank_name` varchar(255) NOT NULL default '',
  `rank_color` varchar(7) NOT NULL default '',
  KEY `rank_id` (`rank_id`)
) TYPE=MyISAM AUTO_INCREMENT=26 ;
--
-- Table structure for table `fusion_users`
--

CREATE TABLE `fusion_users` (
  `user_id` smallint(5) unsigned NOT NULL auto_increment,
  `user_name` varchar(30) NOT NULL default '',
  `user_password` varchar(32) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `user_hide_email` tinyint(1) unsigned NOT NULL default '1',
  `user_location` varchar(50) NOT NULL default '',
  `user_birthdate` date NOT NULL default '0000-00-00',
  `user_aim` varchar(16) NOT NULL default '',
  `user_icq` varchar(15) NOT NULL default '',
  `user_msn` varchar(100) NOT NULL default '',
  `user_yahoo` varchar(100) NOT NULL default '',
  `user_web` varchar(200) NOT NULL default '',
  `user_theme` varchar(100) NOT NULL default 'Default',
  `user_offset` char(3) NOT NULL default '0',
  `user_avatar` varchar(100) NOT NULL default '',
  `user_sig` text NOT NULL,
  `user_posts` smallint(5) unsigned NOT NULL default '0',
  `user_joined` int(10) unsigned NOT NULL default '0',
  `user_lastvisit` int(10) unsigned NOT NULL default '0',
  `user_ip` varchar(20) NOT NULL default '0.0.0.0',
  `user_rights` text NOT NULL,
  `user_groups` text NOT NULL,
  `user_level` tinyint(3) unsigned NOT NULL default '101',
  `user_status` tinyint(1) unsigned NOT NULL default '0',
  `user_combat` tinyint(3) NOT NULL default '0',
  `user_clanrank` tinyint(2) NOT NULL default '15',
  `user_cpts` bigint(9) NOT NULL default '0',
  PRIMARY KEY  (`user_id`)
) TYPE=MyISAM AUTO_INCREMENT=38 ;

-- --------------------------------------------------------

--
-- Table structure for table `fusion_vcode`
--

[Free WordPress Themes]

maybe... its easier to use if you remove all  table dumps which are not related to this thread (check the table names from the first post...)

it has been edited down for clan ranks and users

[Join us @ facebook]

SELECT * FROM fusion_users
INNER JOIN fusion_clanranks ON ( fusion_clanranks.rank_id = fusion_users.user_clanrank )

nikolas, how would I put that into this existing statement:

Code:

<?php
$result = dbquery(
"SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu2.user_name AS user_lastuser FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"
);
?>

thanks again

[rank_name]

SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu2.user_name AS user_lastuser , rank_name  FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
INNER JOIN fusion_clanranks ON ( fusion_clanranks.rank_id = fusion_users.user_clanrank )
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC

i had to change it around abit, because i want the rank_color not the rank_name.. but this is the error i get:

Unknown table 'fusion_users' in on clause

here is current PHP code

Code:

<?php
$result = dbquery("
SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu2.user_name AS user_lastuser , rank_color FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
INNER JOIN ".$db_prefix."clanranks ON ( ".$db_prefix."clanranks.rank_id = ".$db_prefix."users.user_clanrank )
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"
);
?>

[Join us @ facebook]

What about this?

Code:

<?php
$result = dbquery("
SELECT t.*, COUNT(p.post_id) AS thread_replies, tu1.user_name AS user_author, tu2.user_name AS user_lastuser , rank_color FROM ".$db_prefix."threads t
LEFT JOIN ".$db_prefix."posts p USING(thread_id)
LEFT JOIN ".$db_prefix."users tu1 ON t.thread_author = tu1.user_id
LEFT JOIN ".$db_prefix."users tu2 ON t.thread_lastuser = tu2.user_id
INNER JOIN ".$db_prefix."clanranks ON ( ".$db_prefix."clanranks.rank_id = tu1.user_clanrank )
WHERE t.forum_id='$forum_id' AND thread_sticky='1' GROUP BY thread_id ORDER BY thread_lastpost DESC"
);
?>

dont get the error anymore, but its not colorizing the usernames cant figure this one out if my life depended on it lol

[Join us @ facebook]

This is because you now have to use this value when diplaying the results

yes i know, i set the Username link style="color: $mm_color" and then it includes the file colors2.php which actually has the data and if/else statements to find their color based on clanrank

[Guest]

[59.812]

Googlebot, WebTalent