| 2.6.x Recent posts list with date and time [message #20639] |
Fri, 22 October 2004 14:51  |
 |
Wild_Cat
Messages: 144
Registered: November 2002
Location: Odessa, Ukraine
Karma: 0
|
Senior Member |
|
|
I make a topic apart since it was buried into a large theme
There was a code to pull a desired number of posts onto any page written for version 2.5.x
The code is here: http://fudforum.org/forum/index.php?t=tree&th=591&mid=12061&&am p;rev=&reveal=
But due to the changes in DB structure in 2.6.x versions it doesn't work any more. I made a quick modification so that it does works, but I have certain questions for the developers of FUD to make sure the posts list is pulled right taking into consideration all restictions, please:
1)
Old query:
SELECT resource_id FROM ".$tbl."group_cache
WHERE user_id=0 AND p_READ='Y'
New query:
SELECT resource_id FROM ".$tbl."group_cache
WHERE group_id=0
Question:
Where the check for read permission did go so that it could be considered in the query?
2)
Old query segment causing troubles:
WHERE
".$tbl."thread.forum_id IN (".$lm.") AND
".$tbl."msg.approved='Y'
New Query:
WHERE
".$tbl."thread.forum_id IN (".$lm.") AND
".$tbl."msg.apr=1
Question:
I am not sure that apr field takes track if the message was approved... What does?
If those questions answered, I will post the new code working for 2.6.x database structure. For now this quick hack is working here: http://avalon.net.ua/forum.php (russian language) with FUDforum 2.6.7
Another helpful improvement would be to know how the links to the thread or message are generated, so that the hardcoded link would change e.g. for path_info style template automatically depending on forum preferences (if possible at all? or easier to change the url bu hand when the change is made? not too often anyway). OR is it better I remember for security reasons to give link just to rview and is uid=0 needed for security set as 0 in this link?
Lady of Avalon
|
|
|
|
| Re: 2.6.x Recent posts list with date and time [message #20641 is a reply to message #20639] |
Fri, 22 October 2004 16:42  |
Ilia
Messages: 13241
Registered: January 2002
Karma: 0
|
Senior Member
Administrator
Core Developer |
|
|
Permissions in 2.6+ are stored as a bitmask.
You can find out what each bit controlls by looking inside sql/fud_group_cache.tbl
For the message table this information can be found inside
sql/fud_msg.tbl
FUDforum Core Developer
|
|
|
|
| Re: 2.6.x Recent posts list with date and time [message #20642 is a reply to message #20641] |
Fri, 22 October 2004 21:52 |
|
Wild_Cat
Messages: 144
Registered: November 2002
Location: Odessa, Ukraine
Karma: 0
|
Senior Member |
|
|
Oh, thanks. I see that I guessed right as to msg.apr, OK
But bit operations are above my understanding as of yet 
For more, to make selection basing on group_cache_opt I guess it should be an index for performace reasons too?
I don't quite understand anyway what all this table does and those options neither (not to mention I don't get how explanations are converted to that data I see in the table), honestly. Remember, initially I took your code (old queries) example and my work was only smoothing it.
What if I make initial selection in fud_group_cache basing on
group_id=0 and user_id=0
does it make the trick of selecting only resources with overall (anonymous) permission to read?
Although... For now it seems it does the trick the way I wrote it anyways, just a little optimization for initial selection number of rows?...
Lady of Avalon
[Updated on: Fri, 22 October 2004 21:54] Report message to a moderator |
|
|
|
|
|
|
|
|
|
 THE CODE! for Recent posts list with date and time [message #20652 is a reply to message #20639] |
Fri, 22 October 2004 23:20 |
|
Wild_Cat
Messages: 144
Registered: November 2002
Location: Odessa, Ukraine
Karma: 0
|
Senior Member |
|
|
OK, here goes the working code with right permissions:
<?php
function fetch_forum_posts($n_posts) /* n_posts is the number of messages you wish to fetch */
{
include_once "YOUR_PATH_TO/GLOBALS.php";
$cid = mysql_connect($DBHOST, $DBHOST_USER, $DBHOST_PASSWORD);
$tbl = $DBHOST_TBL_PREFIX;
$r = mysql_db_query($DBHOST_DBNAME, "SELECT resource_id
FROM ".$tbl."group_cache
WHERE user_id=0
AND group_cache_opt>>1&1=1", $cid);
$lm='';
while( list($id) = mysql_fetch_row($r) ) $lm .= $id.',';
$lm = substr($lm, 0, -1);
$r = mysql_db_query($DBHOST_DBNAME, "SELECT
".$tbl."msg.id,
".$tbl."msg.subject,
".$tbl."thread.last_post_date,
".$tbl."thread.last_post_id
FROM
".$tbl."thread
INNER JOIN ".$tbl."msg
ON ".$tbl."thread.root_msg_id=".$tbl."msg.id
WHERE
".$tbl."thread.forum_id IN (".$lm.") AND
".$tbl."msg.apr=1
ORDER by
".$tbl."thread.last_post_id DESC
LIMIT ".$n_posts, $cid);
while( $obj = mysql_fetch_object($r) ) {
/* Here you do your actual code, below is a working example */
$tm = date("d/m/Y H:i", $obj->last_post_date);
echo "<p><SPAN CLASS=\"date\">".$tm."</SPAN> -
<a href=\"".$WWW_ROOT."index.php?t=msg&goto=".$obj->id."\">".$obj->subject."</a>
<a href=\"".$WWW_ROOT."index.php?t=tree&goto=".$obj->last_post_id."\">»»</a></p>\n";
}
mysql_free_result($r);
}
?>
Put this in your file apart or in the place you need.
Then make a call for it:
<?php
fetch_forum_posts(10);
?>
Done
*Phew*
Lady of Avalon
[Updated on: Fri, 22 October 2004 23:23] Report message to a moderator |
|
|
|
|
|
| Re: 2.6.x Recent posts list with date and time [message #20659 is a reply to message #20655] |
Sat, 23 October 2004 11:44 |
|
Wild_Cat
Messages: 144
Registered: November 2002
Location: Odessa, Ukraine
Karma: 0
|
Senior Member |
|
|
Abraxa, this is me who is not familiar with bit operations (if you read my previous panic questions about it)!  At least hasn't been until yesterday.
What you say was about what I was thinking at first, but counting that it is possible (even if not commonly logical) to make permissions visible=0 and readable=1 means you will get in this case 3 with your code!
I mean, if you have
or did I get the operation meaning wrong?
I thought it's just safer to be independent from visible if I move one bit right and only take into account readable permission.
Lady of Avalon
[Updated on: Sat, 23 October 2004 12:00] Report message to a moderator |
|
|
|
|
|
|
|
| Re: THE CODE! for Recent posts list with date and time [message #21207 is a reply to message #21204] |
Sat, 20 November 2004 14:36 |
viktor
Messages: 12
Registered: November 2004
Karma: 0
|
Junior Member |
|
|
OK, i want to have the
users.alias
instead
msg.poster_id
Please, advise!
<?php
function fetch_forum_posts($n_posts) /* n_posts is the number of messages you wish to fetch */
{
include_once "GLOBALS.php";
$cid = mysql_connect($DBHOST, $DBHOST_USER, $DBHOST_PASSWORD);
$tbl = $DBHOST_TBL_PREFIX;
$r = mysql_db_query($DBHOST_DBNAME, "SELECT resource_id
FROM ".$tbl."group_cache
WHERE user_id=0
AND group_cache_opt>>1&1=1", $cid);
$lm='';
while( list($id) = mysql_fetch_row($r) ) $lm .= $id.',';
$lm = substr($lm, 0, -1);
$r = mysql_db_query($DBHOST_DBNAME, "SELECT
".$tbl."msg.id,
".$tbl."msg.subject,
".$tbl."thread.last_post_date,
".$tbl."thread.last_post_id,
".$tbl."thread.views,
".$tbl."thread.replies,
".$tbl."msg.poster_id
FROM
".$tbl."thread
INNER JOIN ".$tbl."msg
ON ".$tbl."thread.root_msg_id=".$tbl."msg.id
WHERE
".$tbl."thread.forum_id IN (".$lm.") AND
".$tbl."msg.apr=1
ORDER by
".$tbl."thread.last_post_id DESC
LIMIT ".$n_posts, $cid);
while( $obj = mysql_fetch_object($r) ) {
/* Here you do your actual code, below is a working example */
$tm = date("d/m/Y H:i", $obj->last_post_date);
print "<tr>";
print "<td class=\"RowStyleA\">#</td>";
print "<td class=\"RowStyleB\">";
print "<a title=\"Перейти к теме\" mclass =\"menubar\" href=\"";
print $WWW_ROOT . "index.php?t=msg&goto=" . $obj->id . "\"><b> " . $obj->subject;
print "</b></a></td>";
print "<td class=\"RowStyleA\">";
print strftime("%d %b %H:%M", $obj->last_post_date);
print "</td>";
print "<td class=\"RowStyleB\" align=\"center\">". $obj->replies ." / ". $obj->views . "</td>";
print "<td class=\"RowStyleA\" align=\"center\"><a class=\"GenLink\" href=\"";
print $WWW_ROOT ."index.php?t=usrinfo&id=". $obj->poster_id. "\">". $obj->poster_id ."</td></tr>";
}
mysql_free_result($r);
}
?>
<?php
fetch_forum_posts(34);
?>
[Updated on: Sun, 21 November 2004 02:54] Report message to a moderator |
|
|
|
| Re: THE CODE! for Recent posts list with date and time [message #22380 is a reply to message #21207] |
Mon, 31 January 2005 09:17 |
cellarius
Messages: 6
Registered: January 2005
Location: Augsburg/Germany
Karma: 0
|
Junior Member |
|
|
Hi,
try this - works fine for me:
<?php
function fetch_forum_posts($n_posts) /* n_posts is the number of messages you wish to fetch */
{
include_once "GLOBALS.php";
$cid = mysql_connect($DBHOST, $DBHOST_USER, $DBHOST_PASSWORD);
$tbl = $DBHOST_TBL_PREFIX;
$r = mysql_db_query($DBHOST_DBNAME, "SELECT resource_id
FROM ".$tbl."group_cache
WHERE user_id=0
AND group_cache_opt>>1&1=1", $cid);
$lm='';
while( list($id) = mysql_fetch_row($r) ) $lm .= $id.',';
$lm = substr($lm, 0, -1);
$r = mysql_db_query($DBHOST_DBNAME, "SELECT
".$tbl."msg.id,
".$tbl."msg.subject,
".$tbl."thread.last_post_date,
".$tbl."thread.last_post_id,
".$tbl."thread.views,
".$tbl."thread.replies,
".$tbl."msg.poster_id,
".$tbl."users.alias
FROM
".$tbl."thread
INNER JOIN ".$tbl."msg
ON ".$tbl."thread.root_msg_id=".$tbl."msg.id
INNER JOIN ".$tbl."users
ON ".$tbl."msg.poster_id=".$tbl."users.id
WHERE
".$tbl."thread.forum_id IN (".$lm.") AND
".$tbl."msg.apr=1
ORDER by
".$tbl."thread.last_post_id DESC
LIMIT ".$n_posts, $cid);
while( $obj = mysql_fetch_object($r) ) {
/* Here you do your actual code, below is a working example */
$tm = date("d/m/Y H:i", $obj->last_post_date);
print "<tr>";
print "<td class=\"RowStyleA\">#</td>";
print "<td class=\"RowStyleB\">";
print "<a title=\"??????? ? ????\" mclass =\"menubar\" href=\"";
print $WWW_ROOT . "index.php?t=msg&goto=" . $obj->id . "\"><b> " . $obj->subject;
print "</b></a></td>";
print "<td class=\"RowStyleA\">";
print strftime("%d %b %H:%M", $obj->last_post_date);
print "</td>";
print "<td class=\"RowStyleB\" align=\"center\">". $obj->replies ." / ". $obj->views . "</td>";
print "<td class=\"RowStyleA\" align=\"center\"><a class=\"GenLink\" href=\"";
print $WWW_ROOT ."index.php?t=usrinfo&id=". $obj->poster_id. "\">". $obj->alias ."<br></td></tr>";
}
mysql_free_result($r);
}
?>
<?php
fetch_forum_posts(34);
?>
HTH,
Sven
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]